Data Modeling with Microsoft Power BI

Chapter 6. Building a Data Model in Power BI

Because Power BI is a data model-driven tool, it’s important to ensure the information you display and interact with is modeled correctly. In this chapter, you will learn pros and cons of building a data model in Power BI. You will learn “how-to” techniques in DAX, Power Query, and SQL in Parts III, IV, and V. Here, I talk about the principles and options in the Model view of Power BI Desktop.

I begin with a short recap on normalizing and denormalizing before broaching calculation writing. As you will see, certain types of calculations can’t be done before loading the data into Power BI but only by defining the formula inside of Power BI.

Power BI can do common calculations for you without a specified formula. It’s not a good idea to depend on this behavior, though. Always explicitly write even simple formulas.

I recap the importance of having a dedicated Date (and maybe Time) dimension in your Power BI data model. You will learn two ways of modeling role-playing dimensions and that slowly changing dimensions need to be modeled outside of Power BI (in a physical data warehouse layer). I end this chapter with a description of how to define and use hierarchies.

Let’s begin with the most important part: normalizing and denormalizing.

Normalizing and Denormalizing

I introduce normalizing fact tables and denormalizing dimension tables to transform any given data model into a star schema in “Data Model”. In the “Normalizing” and “Denormalizing” ...