Data Modeling with Microsoft Power BI

Chapter 18. Building a Data Model with SQL

SQL as a language is quite mature, and if you master this language, you can make transformations of your tables very easy. Similar to some applied steps in Power Query, dynamic SQL can make our life even easier as it can make your code resilient to changes. Instead of manually maintaining code, you can write code to maintain the solution for you in an (semi-)automatic way.

After many years working with analytic solutions, I still believe that a data warehouse is the powerhouse in a business intelligence architecture. Putting transformations in a relational layer (which does not necessarily persist all the content) opens your solution to many tools. Of course, Power BI (via Power Query) can consume data from a relational database. But also, plenty of other tools (and users) will be able to connect to a relational database. In a scenario where you or your colleagues have—God forbid!—BI and reporting tools other than Power BI in place, a relational data warehouse (layer) can be the common ground and important puzzle piece to achieve the single source of truth. Instead of re-implementing transformations in all those tools, you can do them in the data warehouse.

All solutions around SQL can be accomplished in different ways:

Persist the content into a table

This is practical if tools or users query content of the data warehouse regularly (e.g., if you use Power BI Report Builder on top of the relational database instead of a Power BI semantic ...