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