Part 5: Data Warehouse Dimensional Modelling

In this series, the previous post was about Part 4: Types of Data Warehouses. Please go through if you missed it.

What is Dimensional Modelling?

The techniques, concepts and methodologies used in designing Data Warehouse is called Dimensional Modelling. The Data Models are designed in discussion with Subject Matter Experts and Data Governance Representatives of the Business. The Data Modeler is responsible for building the Data Model after series of workshops with the Business representatives. Dimensional Models should not be designed in isolation by the personnel with out business knowledge.  

Dimensional Modelling Process

There are 4 steps involved in the Dimensional Modelling.

Identify the Business Process

This step involves identifying the business process for which the data warehouse is to be built. For example, Sales module of Department Store, Claim module of an Insurance Company, Credit Card module of a Bank, etc.

Once the module is identified. The reporting needs for the modules is determined with the help of data analysts and Business Representatives and they will determine the Grain, Dimensions and Facts for the Business Process Reporting. 

Declare the Grain

The grain is the smallest unit of data in the business modules. For example, Debit or Credit transaction in a Bank, Insurance Premium transaction, Line Item transaction in a Departmental Store, etc.

Identify the Dimensions

The dimensions defines multiples contexts of the business and it tries to answers who, what, when, where, how, why, etc. Product, Location, Store, Sales Man, Time, etc are the examples of dimension in a Department Store. Dimension Table holds the text or descriptive values. The dimension gives the meaning to the reports. More effort is put into the governance and the development of the dimension tables because they are the drivers of the users BI experience.

Sample Dimension Table:

Identify the Facts

The facts are the measurable values that is generated as an outcome of the business process. The quantity of an item, Amount of a Product, Discount for a Product, Margin from a Product, etc are the examples of facts. Facts are mostly numeric values. These facts can be stored in one or more tables based on the grain. The discount and Sale Amount can be stored in the same Fact Table. But Sales Amount of a Bill and Sales Amount of a product cannot be stored in the same table since these are of different grain.

Sample Fact Table:

We will see Types of Schema, Dimensions, Facts in detail in the upcoming posts.

Thanks for reading this blog.