Dimensional Modelling in brief
This is a technique for designing Data Warehouse which can fulfill all the requirements of reporting in BI engagements.
Database (as we call it Data Warehouse) after dimensional modeling is view of FINAL multidimensional cube. A BISM model can use this data warehouse (or data mart) as base of different dimensions and facts. It depends upon quality of dimensional modeling which ease and deliver best cubes. (for more details on concept of data warehouse and data mart refer posts How MSBI drive BI Part2)
This is a slight different approach from ER diagram, here aim is to create a database based on Dimension and Fact tables.
What is required before starting dimensional modeling?
We need a database or say a staging database which holds all the data required for completing BI engagement. Which sometimes we call as single database for all the data required for analytics. It’s important that we understand domain of project and reporting requirements i.e. fields of reports, different allowed perspectives for end users to see the data.
Now we decide what will be under dimension and fact tables.
So before starting the interesting part of story lets get to know background of characters in it.
Lets say I want to see few reports that could be from examples below:
- TOP 5 Cities w.r.t. Sales, here I want to see my sales amount based on geography.
- TOP 5 Clients w.r.t. Purchase, here I want to see my best Clients and their purchasing amount
- TOP 5 Products w.r.t. Selling price, here I want to see my best products based on their Selling price
- TOP 5 Branches w.r.t. Revenue, here I want to see my best Branches based on Revenue they produce.
- Top 5 Months of the year in Sales, here I want to see Months with their highest selling amount
To cover more on Dimension, Facts and Relationship wait for the next post :).
Posted on October 6, 2012, in Begin BI and tagged Brief introduction to Data Modelling, Data Modelling, Data warehouse, Define Data Modelling, Dimensions, ER Diagram, fact tables, Facts, Measures, Theory of Data Modelling. Bookmark the permalink. 1 Comment.