Dimensions and fact tables – what is it and how to work with them in Power BI

This (very simplistic) article describes what a dimension and a fact table are, how they differ from each other, and especially how to work with them in Power BI.

In our example, we will start from these tables – in the left there are items on orders, then there are orders and products. We can see that:

  • Tables can be logically connected, using numbers of orders and numbers of products (red and green color).
  • The table on the left side is called “Fact table”, the smaller tables are called “Dimensions”.

So what is the difference between fact tables and dimensions?

Fact table

Fact table is a table, which typically:

  • contains values we want to calculate
  • is longer than dimension, because it repeats values that are unique in dimensions (like order ID´s, product ID´s…).

Dimension

Dimenzson is a table, which adds some additional info to a fact table information. For example to ID product 11 it provides the info about price (91) and name (Cream).

Power BI

In reports generated from data models we normally use columns from both dimensions and fact tables.

When loaded to Power BI, the tables look like this:

Now lets define which columns are connected. These connections are called “relationships” and can be simply done by dragging of column from one table to another.

These columns are called “keys”. The key column must have unique values in dimension, but usually it has repeated values in a fact table.

The relationship line has “1” on one side and star on the other. The star is on the of fact table side (repeated values), number 1 is on the side of dimension (unique values).

Reports

What does it mean for report creation (or Pivot tables in Power Pivot)?

  1. Usually, the columns from fact tables take place in fields like “Values” – they contain numbers we want to calculate.
  2. Columns from dimensions usually belong to fields for grouping – like “Rows”, “X Axis”, “Category”…

Something more about dimensions

  • Some table can be a dimension and fact table in one moment (however, it is not recommended in Power BI). For example here the “Orders” table is a dimension for “Sales” and a fact table for “Customers”.
  • Dimension is quite often connected to multiple fact tables
  • There is a specific type of dimension – a calendar dimension