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:

  1. TOP 5 Cities w.r.t. Sales, here I want to see my sales amount based on geography.
  2. TOP 5 Clients w.r.t. Purchase, here I want to see my best Clients and their purchasing amount
  3. TOP 5  Products w.r.t. Selling price, here I want to see my best products based on their Selling price
  4. TOP 5 Branches w.r.t. Revenue, here I want to see my best Branches based on Revenue they produce.
  5. 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 , , , , , , , , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: