Advanced Data Modeling Techniques

In the previous chapter, we looked at some data modeling best practices, such as dealing with many-to-many relationships using bridge tables, dealing with inactive relationships, and how to programmatically enable them. We also learned how to use config tables, organize our data model, and reduce the model’s size by disabling the auto date/time feature in Power BI Desktop.

This chapter discusses advanced data modeling techniques that can help us deal with more complex scenarios more efficiently. Many techniques require a Power BI Pro license and sometimes a Premium license. We mention the required licensing tier when explaining each technique. This chapter covers the following topics:

• Using aggregations
• Partitioning
• Incremental refresh
• Understanding parent-child hierarchies
• Implementing roleplaying dimensions
• Using calculation groups

We expect you already have a concrete understanding of...

Using aggregations

From a data analytics viewpoint, the concept of aggregation tables has been around for a long time. The concept is widely used in SQL Server Analysis Services Multi-Dimensional. Aggregation tables summarize the data at a particular grain and make it available in the data model. While analyzing aggregated data usually performs better at runtime, aggregation typically happens at a higher level of granularity by introducing a new table (or set of tables) containing summarized data.

To enable the users to drill down to a lower grain, we must keep the data in its lowest grain in the data model. We also need to implement a control mechanism to detect the granularity of data the user interacts with in the reporting layer. The calculation happens in the aggregated table if the aggregated data is available in the data model. When the user drills down to the lower grain, the control mechanism runs the calculations in the detail table, which contains more granular data...

Incremental refresh and hybrid tables

Incremental refresh or incremental data loading is a renowned technique for controlling how the data loads into a data store, so we keep the existing data and only load the changes. Let us look at it in more detail.

From a technical standpoint in data movement, there are always two options to transfer the data from location A to location B:

• Truncation and load: Transferring the entire data from location A to location B by truncating location B and reloading the entire data from location A to B.
• Incremental load: Transferring the data from location A to location B only the first time. The next time, we only load the data changes from A to B. In this approach, we never truncate B. Instead, we only transfer the data that exists in A but not in B.

Incremental refresh detects updated data as deleting the old data and inserting new data.

When we refresh the data in Power BI, if we have not configured an...

Parent-Child hierarchies

The concept of a Parent-Child hierarchy is commonly used in relational data modeling. A Parent-Child hierarchy is when the values of two columns in a table represent hierarchical levels in the data. Parents have children; their children have children too, which creates a hierarchical graph. This section explains Parent-Child hierarchies and their implementation in relational data modeling. Then, we’ll look at the Parent-Child design in Power BI. The following diagram shows a typical Parent-Child graph. Each node of the graph contains an ID and the person’s name:

Figure 10.44: A Parent-Child graph

We can represent the preceding graph in a data table, as shown in the following image:

Figure 10.45: Parent-Child graph representation in a data table

There is a one-to-many relationship between the ID and ParentID columns. In relational data modeling, we create a relationship between the ID and ParentID columns that turns the...

Implementing roleplaying dimensions

The roleplaying dimension is one of the most common scenarios we face in data modeling. The term was inherited from multidimensional modeling within the SQL Server Analysis Services Multidimensional. Before jumping to the implementation part, let’s take a moment and understand what the roleplaying dimension is. When we create multiple relationships between a fact table and a dimension for logically distinctive roles, we use the concept of a roleplaying dimension. The most popular roleplaying dimensions are the Date and Time dimensions. For instance, we may have multiple dates in a fact table, such as Order Date, Due Date, and Ship Date, which participate in different relationships with the Date dimension. Each date represents a different role in our analysis. In other words, we can analyze the data using the Date dimension for different purposes. For instance, we can calculate Sales Amount by Order Date, which results in different values...

Using calculation groups

Creating calculation groups is one of the most useful features for Power BI data modelers and developers. It reduces the number of measures you have to create. Calculation groups address the fact that we have to create many measures in larger and more complex data models that are somewhat redundant. Creating those measures takes a lot of development time. For instance, in a Sales data model, we can have Sales Amount as a base measure. In real-world scenarios, we usually have to create many time intelligence measures on top of the Sales Amount measure, such as Sales Amount YTD, Sales Amount QTD, Sales Amount MTD, Sales Amount LYTD, Sales Amount LQTD, Sales Amount LMTD, and so on. We have seen models with more than 20 time intelligence measures created on top of a single measure. In real-world scenarios, we have far more base measures than a business that requires all those 20 time intelligence measures for every single base measure. You can imagine how time...

Summary

In this chapter, we learned about some advanced data modeling techniques, as well as how to implement aggregations using big data in Power BI. We also learned how to configure incremental refresh, which helps deal with the challenges of working with large data sources. Then, we looked at the concept of Parent-Child hierarchies and implemented one in Power BI Desktop. After that, we learned how to deal with roleplaying dimensions in Power BI. Last but not least, we implemented calculation groups.

In the next chapter, Row-Level Security, we will discuss a crucial part of data modeling that is essential for organizations that believe the right people must access the right data in the right way.

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