Model Design

Being effective with DAX starts with designing a good analysis model. In this chapter, we address a number of modeling-related topics that are important to understand for strong model designs.

The topics in this chapter include:

• The way the Power BI engine stores data
• Choosing the right data types
• Relationships
• What structure to strive for in your models

To achieve good models, you will need to adapt to the appropriate way of thinking. This is a change needed both when you start working with Power BI coming from a background in Excel, and when you have a background in relational databases. When you are used to working in Excel, the concept of relationships in an analysis model specifically takes time to comprehend; but even when you have a database background, there are many things that are different as well. One of the difficulties in designing for Power BI is that the concepts seem familiar to database professionals, when in...

Columnar data storage

The power of the Power BI model comes primarily from a smart data storage mechanism. Power BI models are, in fact, databases in the sense that they organize and store data. But the internals are very different from other database technologies you may be familiar with.

Relational databases

The traditional, corporate way of working with data is with a relational database management system (RDBMS) like Microsoft SQL Server. In an RDBMS, tables of data are defined with a fixed number of data columns per table. Each column must have a data type, like integer, text, or decimal number, and from this information, the RDBMS can derive how much space is needed to store a single row of data, or record, and how many rows can be stored in a disk-based data file. This concept makes an RDBMS an effective choice for applications that process transactions, like sales transactions from a web shop or transactions in a company's financial ledger.

The concept of...

Data types and encoding

The Power BI model works with a limited number of data types. Choosing the right data type for your data is important, as it determines the way your data is stored, or encoded, and how efficiently the model can process your data. Below is a list of all the data types recognized by Power BI:

• Text: The most generic data type is Text. Virtually all data can be stored as Text. When loading data through Power Query, the generic Power Query data type Any is converted to Text in the Power BI model. This causes numerical columns to be stored as Text when you forget to explicitly make the type conversion in Power Query. (You can, of course, change the data type in the model, which will automatically add a type change step in Power Query.)
• Whole Number: The data type Whole Number is used, as you would guess, to store whole numbers. Because of the way the Power BI model stores and compresses data, this is one of the most efficient data types available...

Relationships

One of the most misunderstood elements in Power BI models is the concept of relationships. Whether you are working with Power BI coming from an Excel background, or you have been educated in relational databases, relationships in Power BI models require an approach that is different from what you are familiar with.

Data in Excel

Let us zoom in on data in Excel first. The concept closest to a database in Excel is that of Excel tables. You could consider an Excel table as a "flat" database. This way of storing data has many disadvantages.

As an example, the figure below shows data as it may be stored in an Excel worksheet:

Figure 1.2.2: A table in Excel

This table contains order amounts and dates for sales orders, which are sold by employees. There are multiple issues with flat databases:

• Clearly, all information about an employee (like job role and date of birth) is duplicated with each order sold by that employee...

Effective model design

The concepts of relationships and filter propagation enable powerful analytics in Power BI models. It is important to think about the design of a model: which tables should the model contain, and which columns need to be in those tables? Which relationships are needed? In short, what is the overall structure of the model? The choices you make in model design will determine what results the model will be able to deliver.

Star schemas and snowflakes

A best practice for analytics using relational databases is to work with a specific database structure, known as a star schema. The basic ideas of star schemas apply to Power BI models as well.

Figure 1.2.12: Generic star schema structure

The central tables in a star schema model are the fact tables. These tables contain things that have happened, will happen, or should happen; like sales transactions, financial ledger transactions, customer inquiries, student enrollments, sales opportunities...

Memory and performance considerations

The design of a Power BI model highly impacts its size, and size is highly correlated with performance. In this section, we share some best practices to optimize the performance of your model, as a recapitulation of the topics discussed in this chapter. As a rule of thumb, smaller models with respect to size are faster. You can use the file size of the Power BI model as an indication; you can also get a more detailed view of size and performance by using specific community-driven tools like DAX Studio.

Keep in mind the following guidelines while designing your Power BI model:

• Having fewer columns is better. The Power BI model achieves a high compression rate of data due to the columnar database concept. However, it still needs to keep track of which values belong together in a row. The more columns a table has, the more overhead the model needs to know what goes where. So, keep the number of columns per table as small as possible...

Summary

In this chapter, we have discussed the foundational concepts of the Power BI model. You have learned what makes a Power BI model fundamentally different from other data management products (the in-memory column store) and what the consequences are for what an optimal design looks like.

A good Power BI model has an effective structure with fact tables, filter tables, and relationships between them. Making good design choices when it comes to structure and data types, as well as carefully considering what your data looks like from the perspective of granularity, unique values, and value distribution, leads to a model that performs well. And perhaps more importantly, such a model forms a good basis for rich calculations in DAX.

In the next chapter, we will look at the ways DAX can be used in Power BI models.