If we remember prev we have discussed about the 2 common industry techniques viz. Datawarehousing & Dimensional Modelling. So i have brought a brief overview of wht exactly is dimensional modelling(DM)…
So lets start….
Whats DM????
Dimensional modelling is a logical design technique used for DWs.
Lets understand it in a diff way. If a user want to c some report of dollar value with region for an eg. so what will he want to c???
He wud like to c a column dollar with region…
Based on the user requirement the data needs to be segregated in facts and dimensions and the data should have a proper relationship between the fact & the dimension so that the user can c the data is the desired format.
He wud like to c a column dollar with region…
Based on the user requirement the data needs to be segregated in facts and dimensions and the data should have a proper relationship between the fact & the dimension so that the user can c the data is the desired format.
So, to segregate the data into facts and dimensions and to logically organize the data in a way so that the user can understand it is done in Dimensional Modelling.
Now people have a general tendancy of confusing this with the ER Diagrams, so lets c wht exactly are ER diagrams…
ER Diagrams is also a logical design technique but this technique is used in transactional DBs like OLTP. It aims at removing the redundancy and hence is best used for transactional queries as it makes transactions simpler and deterministic.
DM is a logical design technique which aims to provide a standard framework for a high query performance and is Dimensional in nature.
So, whts the relationship between a DM and ER diagram and how it takes the shape of DM???
Lets c how it happens…..
The key to understand the relationship between the DM & ER is that a single ER diag. breaks into many DM diag.
Think of a large ER diag. having all the business processess & relations of a company. Now
Think of a large ER diag. having all the business processess & relations of a company. Now
1) Separate the ER diag. into separate bnusiness processes and model each separately.
2) Identify many to many relations in ER diag. which have numeric or additive nonkey facts in them as designate them as facts.
3) Denormalize the rest of the tables with single keys (dimension tables) and join them to fact tables.
2) Identify many to many relations in ER diag. which have numeric or additive nonkey facts in them as designate them as facts.
3) Denormalize the rest of the tables with single keys (dimension tables) and join them to fact tables.
Confirmed Dimension : When a dimension table joins to more than 1 fact tables it is repsesented in both the schemas and is referred to as Conformed between 2 dimensional models.
The above process can b better understood by the below images of ER Diag. & DM Diag.
ER Diagram
DM Diagram
This is how we result in doing the DM and create a DW from an OLTP systems…
Posts
