What is Dimensional Modelling?

Dimensional Modelling (DM) is a data structure technique optimized for data storage in a Data warehouse. The purpose of dimensional model is to optimize the database for fast retrieval of data. The concept of Dimensional Modelling was developed by Ralph Kimball and consists of "fact" and "dimension" tables.

A Dimensional model is designed to read, summarize, analyse numeric information like values, balances, counts, weights, etc. in a data warehouse. In contrast, relation models are optimized for addition, updating and deletion of data in a real-time Online Transaction System.

These dimensional and relational models have their unique way of data storage that has specific advantages.

For instance, in the relational mode, normalization and ER models reduce redundancy in data. On the contrary, dimensional model arranges data in such a way that it is easier to retrieve information and generate reports.

Often in Power BI, articles will relate to the “star schema” which is based on dimensional modelling.

Elements of Dimensional Data Model

Fact

Facts are the measurements/metrics or facts from your business process. For a Sales business process, a measurement would be quarterly sales number.

Dimension

Dimension provides the context surrounding a business process event. In simple terms, they give who, what, where of a fact. In the Sales business process, for the fact quarterly sales number, dimensions would be

• Who – Customer
• Where – Location
• What – Product

In other words, a dimension is a window to view information in the facts.

Attributes

The Attributes are the various characteristics of the dimension.

In the Location dimension, the attributes can be

• State
• Country
• Postcode etc.

Attributes are used to search, filter, or classify facts. Dimension Tables contain Attributes.

Fact Table

A fact table is a primary table in a dimensional model.

A Fact Table contains

• Measurements/facts
• Foreign key to dimension table

Dimension table

A dimension table contains dimensions of a fact.

• They are joined to fact table via a foreign key.
• Dimension tables are de-normalized tables.
• The Dimension Attributes are the various columns in a dimension table
• Dimensions offers descriptive characteristics of the facts with the help of their attributes
• No set limit set for given for number of dimensions
• The dimension can also contain one or more hierarchical relationships

Useful resources

• The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd edition, 2013) by Ralph Kimball
• Star Schema & Power BI: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
• Power BI Basics of Modeling: Star Schema and How to Build it (Posted on May 21, 2019): https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it