Michael Mano shares a few tips for data modeling with Power BI.
Data modeling for Power BI
Data Modeling is used to connect multiple data sources in Power BI tool using a relationship. A relationship defines how data sources are connected with each other. You can create interesting data visualizations on multiple data sources, build custom calculations on the existing tables, and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics.
Power BI is a data model based BI reporting tool; not all BI tools are data model based. One example of a non-data model based BI tool is SQL Server Reporting Services (SSRS). SSRS is a non-data model based BI tool. Non-data model based BI reporting tools require the report writer to first generate a query to fetch the data from a database (typically SQL Server) and return the results of that query to SSRS so the results can be rendered in a report. Many non-data model-based reporting tools have a user interface that helps with the generation of the query (if you want to use it), or you can also use a scripting language like T-SQL to fetch the data that you need for each report.
In order for you to get the most out of your data it is important to follow several Power BI data modeling best practices
Use Lookup Tables
It is always more efficient to have a main data table and lookup tables. Whenever there are repeating text values in a data table, you should be using a lookup table. A good rule of thumb, text columns in the main data table can usually be replaced by a lookup table. Not only does this make calculating measures more quickly it also makes the file size significantly smaller. A good rule of thumb is to use narrow data tables.
Use a Date Table
One of the most basic use cases for Power BI and Power Pivot are time intelligence calculations. Using a separate date table or a calendar table is one of the most important tables to have in a data model. Without a date table you couldn’t calculate measures efficiently in your model. Year to Date (YTD), and Month to Date (MTD) calculations become trivial. Power BI and Power Pivot have built-in time intelligence formulas, but these formulas are designed for a standard calendar. Let’s say your fiscal year ends in September, then you would be required to use a custom date table.
Do Not Mix Data Tables
One of the fundamental rules of data modeling in Power BI is that you don’t mix data tables. You can use lookup tables with multiple relationships between data tables. Power BI will won’t let you create a relationship with a many-to-many relationship. Power BI has many built-in features that prevent you from making a data model that won’t work.
Conclusion
Using some of these best practices can help avoid common pitfalls you might encounter implementing a new solution. It is imperative to have a clearly defined goal around how we derive a successful model based on the user experience.
I also recommend you check out this post.
Good