Tuesday 15 March 2016

How to make the DE-Normalized DB for OLAP from OLTP

Introduction
In this article we are trying to convert Normalized architecture to DE-Normalized forms that support OLAP. We are trying to say to convert OLTP Data base to OLAP data base. To understand it properly we are taking a simple domain example like Sales Domain.
Hope it will be informative and it put some value in your professional career.


Understanding the Sales Domain in OLTP Environment
The Entity Details:
Entity: Sales Order Header

Attributes

[PK]
Order Id


Order Date


Location Id
[FK].[Location Details]

Customer Id
[FK].[Customer Details]


Entity: Sales Order Detail

Attributes

[PK]
Order Id
[FK].[Sales Order Header]
[PK]
Item Cd
[FK].[Item Details]
[PK]
Srl No


Quantity


Rate


Amount



Entity: Customer Details

Attributes

[PK]
Customer Id


Customer Name


Customer Address


Customer Group Cd
[FK].[Customer Group ]


Entity: Customer Group

Attributes

[PK]
Customer Group Cd


Group Name


Group Description



Entity: Location Details

Attributes

[PK]
Location Id


Location Name


Location Description



Entity: Item Master

Attributes

[PK]
Item Cd


Item Name


Item Description


Item Group Cd
[FK].[Item Group Details]


Entity: Item Group Details

Attributes

[PK]
Item Group Cd


Item Group Name


Item Group Description



ERD Diagram:

How to convert it into De-Normalized OLAP DB


Step-1 [ Fact Table ]
It contains
  1. Facts or Numbers which can be used in aggregate function
  2. The Foreign Key references Key which is needed to connect with Dimension Table.
In our Example we can do like this
Fact-Customer Order Details

Attributes

[PK]
Location Id
[FK].[Location Details]
[PK]
Order Id

[PK]
Item Cd
[FK].[Item Master]
[PK]
Srl No

[PK]
Order Date
[FK].[Dimension-Date]
[PK]
Customer Id
[FK].[Customer Details]

Quantity
[Fact]

Rate
[Fact]

Amount
[Fact]


Step – 2 [ Making Dimensional Table ]
Dimensional table is just like a master table and it related with Fact Table by foreign key relation.
Dimension-Date
It a special dimension contains the Hierarchy of Year à Month à Quarter à Day etc. It basically slices your data according to calendar.

Attributes

[PK]
Order Date


Month


Quarter


Year



Dimension-Customer Details

Attributes

[PK]
Customer Id


Customer Name


Customer Address


Customer Group Cd
[FK].[Customer Group]


Dimension-Location Details

Attributes

[PK]
Location Id


Location Name


Location Description





Dimension-Item Master

Attributes

[PK]
Item Cd


Item Name


Item Description


Item Group Cd
[FK].[Item Group Details]


Step - 3 [ Making Lookup Dimension Table ]
Lookup Dimension is used to support a specific Dimension table. It is not connected with Fact but connected with another dimension table.
Dimension-Customer Group

Attributes

[PK]
Customer Group Cd


Group Name


Group Description



Dimension-Item Group Details

Attributes

[PK]
Item Group Cd


Item Group Name


Item Group Description





Making the STAR Schema

Making Snow flex Schema



Hope you like it.


Posted By: MR. JOYDEEP DAS


3 comments:

  1. I think SSAS People must read this Article...

    ReplyDelete
  2. Please make open suggestion on it. Share your knowledge please.

    ReplyDelete
  3. One can use OLTP database in very detailed way using current data and schema for online transactional databases in the entity model. you can chracterized by relatively in low volume of transactions cognos tm1 training

    ReplyDelete