Data Modeling is an important skill for Power BI Developers and Analysts to master. Power BI data modeling tips are often overlooked. 
A well-designed data model will result in:
- Better performance
- Simpler DAX code
- Less likelihood of errors in calculations
- Reduced development time
Recently Marco Russo delivered “Introduction to Data Modeling” to the San Francisco Power BI and Bay Area Microsoft Business Intelligence user groups. He’s a prolific author, speaker, and trainer, along with his partner Alberto Ferrari, at SQLBI.
Marco started with a single Excel spreadsheet, and showed various limitations to a single table data model. He then showed relational examples, talked about Facts and Measures, and eventually landed on the Star Schema. He joked that all the pioneering books on database design were written at least 40 years ago, and aren’t even available on Kindle. (Think Codd, Date, and Bay Area residents Fabian Pascal and Ralph Kimball.)
Marco showed some examples of bad database designs in Power BI. Through clever and complex DAX, he could still extract the desired results. However, if you used his Power BI data modeling tips right from the start, you could avoid the need for DAX heroics.
Some examples of bad design included:
- Two related Fact Tables, Invoice Header and Invoice Line Items, with each fact table having its own dimensions. In that example, he cautioned us about the use of Power BI’s dangerous feature “bi-directional filters,” and how you can wind up with the wrong results. Again due to data design mistakes.
- Denormalized Fact Tables, such as Purchases and Sales.
While showing these examples, which reflect real-world data, he showed the problems those designs create. Following Marco’s data modeling tips for Power BI gives you options for resolving the database design issues.
There is a natural life cycle to your data models. Ideally, you want to create a great model, and then leverage that model across many Power BI reports and dashboards. Over time you will extend your model to support new reports. Once your organization is ready, you can move the model(s) to Azure Analysis Services or SQL Server Analysis Services (if your data remains on-premise).
The sequence is often like this:
- Analysts gain access to data sources, and develops Power BI data models, dashboards, and reports
- The organization starts to derive real business value from the reports
- To reach a larger audience the data models are moved to Analysis Services. In this process, we often see performance tuning / optimization, and an increased level of scrutiny about whether all the numbers are correct
- The model is extended or combined with other models, also within the purview of IT
- Analysts and report developers leverage the models, which are now maintained by IT
We’ve written about How To Standardize Reports with Power BI Themes and Templates. However, use Marco’s Power BI data modeling tips to takes it up a level. As Marco put it, Microsoft has optimized Power BI for star schema data models. If your models fit within that long-accepted design pattern, you’ll get the fastest possible performance along with the easiest possible DAX.