Data Modeling Components

In the previous chapter, we learned about some critical data preparation best practices, such as loading a proportion of data, removing unnecessary columns, and summarization to optimize our data model size. We also learned about query folding and how it can affect our data modeling in Power BI. We also looked at data type conversion and discussed the importance of selecting certain data types to keep our data model more optimized when we import the data into the data model; that brings us to this chapter. All our data preparation efforts pay off by having a cleaner data model that is easier to maintain and performs well. This chapter looks more closely at data modeling in Power BI by covering the following topics:

• Data modeling in Power BI Desktop
• Understanding tables
• Understanding fields
• Using relationships

We use the Chapter 8, Data Modeling and Star Schema.pbix sample file in this chapter. It is a copy of the sample...

Data modeling in Power BI Desktop

The central premise for data modeling in Power BI Desktop is the Model tab in Power BI Desktop’s main window. We can also create and edit calculation-based objects from the Model tab, such as creating a new calculated table, measure, or calculated column.

The following image shows the Model tab in Power BI Desktop:

Figure 8.1: Model view tab in Power BI Desktop

The following sections discuss the modeling features currently available in Power BI Desktop. Then, we continue building the star schema we prepared in Chapter 6, Star Schema Preparation in Power Query Editor.

Understanding tables

From a data modeling perspective, tables are objects that contain related data by using columns and rows. In the Power Query Editor in Power BI, each query with Enable load activated becomes a table after loading into the data model.

Table properties

The table’s properties show up in the Properties pane when clicking on a table from the Model view in Power BI Desktop, as shown in the following image:

Figure 8.2: Table properties pane in the Model view

The following settings are currently available within the Properties pane. General includes the following general table properties:

• Name: The name of the table. We can rename a table from here.
• Description: We can write some descriptions about the table here. These descriptions show up in the Data view, as well as in the Report view, as shown in the following image:

Figure 8.3: The table description shows up in the Report view when hovering over a table

...

Understanding fields

Fields in Power BI include columns and measures. We generally refer to both columns and measures when we talk about fields. For instance, when we talk about fields’ data types, we refer to the data types for both columns and measures. The Fields term is used within Power BI Desktop in different views, so there is a Fields pane in the Report view, the Data view, and the Model view.

Data types

When we import data into the model, the model converts that data, in columns, into one of the Tabular Model data types. When we then use the model data in our calculations, the data is converted into a DAX data type for the duration of the calculation. The model data types are different from Power Query data types. For instance, in Power Query, we have DateTimeZone. However, the DateTimeZone data type does not exist in the data model, so it converts into DateTime when it loads into the model. The following table shows the different data types supported in the...

Using relationships

When modeling relational data, a relationship describes the connection between two tables. For instance, our example shows a relationship between the Customer table and the Sales table. A customer can have multiple sales transactions in the Sales table. To create a relationship between the Customer and Sales tables, we must link CustomerKey from the Customer table to CustomerKey from the Sales table. This linkage enables Power BI to understand that each row of data in the Customer table can have one or more related rows in the Sales table.

To create relationships between tables in Power BI Desktop, we can either use the Model view to drag a column from a table and drop it to the relevant column from the other table or click the Manage relationships button from the ribbon. The Manage relationships button appears in several places in the ribbon.

The following image shows the Manage relationship window:

Figure 8.31: Manage relationships window in...

Summary

In this chapter, we learned about the data modeling components in Power BI Desktop. We learned about table and field properties; we looked at feature tables, how to make a table from our data model accessible across the organization, and how to build summary tables by creating calculated tables in DAX. We then dived deeper into one of the essential concepts in data modeling, which is relationships. We learned about different relationship cardinalities and filter propagation and also understood the concept of bidirectional relationships.

In the next chapter, Star Schema and Data Modeling Common Best Practices, we will look at many of the concepts we have learned about in this chapter in more detail.

Join us on Discord!

Join The Big Data and Analytics Community on the Packt Discord Server!

Hang out with 558 other members and enjoy free voice and text chat.

https://packt.link/ips2H