Star Schema

Most Power BI reports contain multiple tables. These tables need to be linked together to create a proper data model. Whenever possible, this should be done using the star schema.

In this article

  1. What is a dimension table?
  2. What is a facts table?
  3. Relationship between facts table and dimension tables

    

  

     

Most Power BI reports contain multiple tables. These tables need to be linked together to create a proper data model. Whenever possible, this should be done using the star schema.

First, let's see what a data model according to the star schema looks like. As you can see in the picture below, this data model contains one  fact table and four dimension tables which are all linked to the facts table. It doesn't matter how many dimension tables you have, the star schema is considered best practice and in the following article, you'll learn what you have to look out for.

     

What is a dimension table?

Let's take a closer look at the BusinessUnits table from our data model:

 

As you can see, every  BusinessUnit has one unique BusinessUnitID and belongs to one Group. Each Group is allocated to a Division. When only looking at the Electronics Division, this is the organizational structure this table represents. 

   

So what is this dimension table used for? Basically, it is used to filter the data from the facts table according to different organizational views. With this table, you can show data for Business Unit, Group, or Division level. This is possible because, in the data model, there's a link between the BusinessUnitID in the BusinessUnits dimension table and the BusinessUnitID in the Sales facts table. 

So each row in the Sales facts table contains a BusinessUnitID and since we have linked BusinessUnitID between the tables, filtering is possible. Some more information about filtering later in this article. 

   

What kind of fields are included in a dimension table? 
A dimension table contains a field with a unique ID. In our example, this would be the BusinessUnitID. On top of that, dimension tables can contain other descriptive fields such as the description belonging to the ID (BusinessUnit) and the groups the unique ID belongs to. Other fields that could be added to this example are the name of the person that is responsible for the BussinessUnit, the location of the head office per Group, or something similar. But you would never include the number of sales or other transactions in the dimension table. 

What is a facts table?

This is what our facts table looks like:

   

This table shows individual sales transactions (facts). Of course, you can have multiple transactions on the same day, for the same ProductID, CustomerID, etc. This is why fact tables are usually longer (contain more rows) than dimension tables. 

⚠️  Power BI can deal with very long tables but might slow down when you have tables with a lot of columns. So it's best to have narrow tables. That's also a reason why you want to create a star schema and not just have one single table with all the information. 

You can also see that the table contains values for Revenue, Cost, and Gross Profit. These are facts that are normally aggregated and filtered according to a field from a dimension table. 

Let's assume you want to see all transactions belonging to the Group Video from the BusinessUnit table and you only have the transactions shown above where you only have BusinessUnitID 27 and 16. By filtering on Video, you would only see the transactions from BusinessUnitID 16 but not the ones from 27.

What kind of fields are included in a dimension table?

The facts table includes facts or transactions but even more importantly it also contains a key to each of the dimension tables in order to create the relationship between the tables and be able to slice and dice the data using the fields from the dimension tables.

  

Relationship between facts table and dimension tables

As you can see, every dimension table's unique ID is linked to the facts table.

All these relationships are many-to-one relationships which are indicated by the * ( many) on the side of the facts table and the 1 on the side of the dimension table. This means that there can be multiple sales for the same BusinessUnit, but the details belonging to the BusinessUnit are unique.

Also, note that the filters are always pointing away from the dimension table and towards the facts table. 

  

To avoid using a filtering dimension from the facts table, it's considered best practice to hide them in the report view.

   

If you can follow all these rules, you have a perfect star schema.

   

Sooner or later you might face a situation where a perfect star schema isn't possible anymore due to the high complexity of the data in your report. This is OK but you should always try to be as close to the star schema as possible.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us