Blog Post

Power BI and a Star Schema


I sometimes get asked from customers if they should use a star schema or should they use one large single table/flatfile for their dataset in Power BI. The answer is almost always star schema, and Patrick LeBlanc does a great job explaining what a star schema is and why star schemas are so important for Power BI in his video Why Power BI loves a Star Schema. I wanted to list out his four reasons:

  1. Usability: If you have just one large table with all the data, when creating a report it is difficult to find the fields you are looking for in the “Fields” list. With a star schema, the Fields list is much easier to navigate. Star schemas make your data model much cleaner and easier to use
  2. Simpler DAX: The code for DAX will be much simpler against a star schema compared to a wide flat table. It will be shorter, easier to write, easier to read, and easier to maintain
  3. Performance: Star schema’s can handle huge amounts of data without impacting performance. The test Patrick ran was against 7 million rows and a query took 29ms against the one large table compared to 7ms against the equivalent star schema. You can imagine the performance difference against 10’s of millions of rows
  4. Faster refresh: In Power BI Desktop, Patrick used Power Query against 7.8 million records from a SQL Server Database and choose Close and Apply. For the large table, this took 34 minutes (2069 rows/second). The star schema took only 4 minutes (28k rows/second)

Patrick also has a video showing how to turn a flat file into a star schema: Power BI Tutorial | From Flat File To Data Model.

And a report using a flattened table could return inaccurate numbers, as pointed out by sqlbi: The importance of star schemas in Power BI.

For more information on star schema, check out the “bible” by Ralph Kimball: The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling.

More info:

Understand star schema and the importance for Power BI

Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table

Power BI Basics of Modeling: Star Schema and How to Build it

Build Your First Star Schema Model in Action: Power BI Modeling Basics

Power BI Tips for Star Schema and Dimensional Data Modeling

Visualise your Power BI Refresh

The post Power BI and a Star Schema first appeared on James Serra's Blog.

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating