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
- Facts or Numbers which can be used in aggregate function
- 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
