Building a Power BI Data Model

"The data model is what feeds and what powers Power BI."

- Kasper de Jonge, Senior Program Manager, Microsoft

The data models developed in Power BI Desktop are at the center of Power BI projects. These data models support data exploration and drive the analytical queries visualized in reports and dashboards. Properly designed data models leverage the data connectivity and transformation capabilities described in Chapter 2, Accessing and Retrieving Data, to provide an integrated view of distinct business processes and entities. Additionally, data models contain predefined calculations, hierarchies and groupings, and metadata to greatly enhance both the analytical power of the dataset and its ease of use. The combination of accessing and retrieving data coupled with the additional enhancements available in the data model serves as the foundation for the BI and analytical capabilities of Power BI.

In this chapter, we explore the primary...

Technical Requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop
• Tabular Editor
• SQL Server with the AdventureWorksDW2019 database installed. This database and instructions for installing it are available here: http://bit.ly/2OVQfG7
• Code samples and other materials can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition

Designing a Data Model

Power BI Desktop lends itself to rapid, agile development in which significant data insights can be obtained quickly despite both imperfect data sources and an incomplete understanding of business requirements and use cases. However, rushing through the design phase can undermine the sustainability of the solution, as future needs cannot be met without structural revisions to the model or complex workarounds. A balanced design phase in which fundamental decisions, such as DirectQuery versus in-memory, are analyzed, while simultaneously using a limited prototype model to generate visualizations and business feedback can address both short- and long-term needs.

This recipe describes a process for designing a data model and identifies some of the primary questions and factors to consider while doing so.

Getting ready

Visually and analytically impressive examples of Power BI's reporting capabilities can often cause stakeholders to underestimate...

Implementing a Data Model

The implementation of a data model proceeds from the design phase described in the previous recipe. The design process and its supporting documentation clarify which entities to model, their granularity, the fact-to-dimension relationships, and the fact measures that must be developed. Additionally, the model mode (Import or DirectQuery) has already been determined, and any additional business logic to be implemented via M or DAX functions is also known. The different components of the model can now be developed, including data source connectivity, queries, relationships, measures, and metadata.

In this recipe, we walk through all the primary steps in the physical implementation of a model design. Three fact tables and their related dimensions are retrieved, relationships are created, and the core measures and metadata are added. When complete, the multi-fact data model can be exposed to business users for initial testing and feedback.

Getting ready...

Creating Relationships

One of the most important data modeling features of Power BI, as well as Analysis Services Tabular, is the control the modeler has over defining the filtering behavior between tables via relationships. In addition to one-to-many, single-direction relationships, Power BI models can contain bidirectional relationships, one-to-one relationships, many-to-many relationships, and even DAX measures that contain their own relationship filtering logic via functions such as USERELATIONSHIP and CROSSFILTER. These relationship tools, along with modifying the filter context of measures through DAX, can be used to support many-to-many modeling scenarios and provide alternative model behaviors for multiple business requirements.

In this recipe, we look at single-direction relationships, as well as the primary use cases for bidirectional relationships, and DAX-based cross-filtering.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power...

Configuring Model Metadata

Metadata is information about information. There are many metadata settings in Power BI data models, such as column names, data types, object descriptions, and others. Four important metadata properties to configure for any column visible on the Power BI report canvas are the format, data category, default summarization, and the option to sort by column. The data formats should be consistent across data types and efficiently convey the appropriate level of detail. Data categories serve to enhance the data exploration experience by providing Power BI with information to guide its visualizations. Default summarization determines the aggregation, if any, to apply to the column when added to the report canvas. The ability to Sort by Column enables the displaying of the values in a column based on the order of a separate column. Although all of these settings are relatively simple to configure, careful attention to these properties helps to deliver higher-quality...

Hiding Columns and Tables

Data models must balance the competing demands of functionality, scope, and usability. As additional tables, columns, measures, and other structures are added to meet various analytical needs, a model can quickly become confusing to end users. Therefore, it is important to minimize the visibility of columns and tables to provide an intuitive interface.

In this recipe, we demonstrate how to hide columns and even entire tables from report consumers.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power BI Desktop.
2. Create an Import mode data source query called AdWorksDW. This query should be similar to the following:

   let
       Source = Sql.Database("localhost\MSSQLSERVERDEV", "AdventureWorksDW2019")
   in
       Source
   

3. Isolate this query in a query group called Data Sources and disable loading.
4. Right-click AdWorksDW and choose Reference.
5. Select the FactInternetSales...

Enriching a Model with DAX

In order to drive user adoption and to provide a foundation to support more advanced analytics, it's essential that the logic embedded in core or 'base' DAX measures accurately reflect business definitions. These fundamental measures deliver version control across the many reports built from a data model and avoid the need for additional logic to be applied at the report layer. Clear definitions for required business metrics should be documented and the corresponding DAX measures should be validated for accuracy prior to deploying any reports using these new measures to a production environment.

Performance, usability, and version control are all fundamental characteristics of effective data models, but it is often the additional analytical context that sets models apart. Once fundamental measures have been implemented, additional DAX measures can be developed to support common and high-priority business analysis. These measures can often...

Supporting Virtual Table Relationships

Virtual table relationships are DAX expressions implemented to filter one table from another when a relationship does not exist between the two. Report visualizations can then be constructed using both tables (and those related to it), and the DAX measures will update as though a normal relationship is defined. Virtual relationships are often used to address disparate grains of tables and to leverage performance segmentation tables.

Although physical relationships are the preferred long-term solution for both performance and manageability, virtual relationships provide an attractive alternative when physical relationships are not feasible. In this recipe, we provide virtual relationship examples that use both a custom performance segmentation table and an aggregated table.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power BI Desktop.
2. Create an Import mode data source query called AdWorksDW...

Creating Hierarchies and Groups

Hierarchies and groups are data model structures that can be implemented to simplify the user and report authoring experience. Hierarchies provide single-click access to multiple columns of a table, enabling users to navigate through pre-defined levels, such as the weeks within a given month. Groups comprise individual values of a column that enable analysis and visualization of the combined total as though it is a single value. Hierarchies and groups have useful applications in almost all data models, and it is important to understand the relationship of these structures to the data model and visualizations.

This recipe provides an example of utilizing DAX parent- and child-hierarchy functions to create columns of a hierarchy. The hierarchy is then implemented into the data model, and a group is created to further benefit analysis.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power BI Desktop.
2. Create...

Designing Column Level Security

While not as common as row-level security (RLS), there are times when you may wish to hide particular columns in a data model from one group of report viewers, but not others. Power BI Desktop does not natively support column security. However, a carefully designed data model can achieve column level security using Power BI's native RLS functionality.

It should be noted that Microsoft has recently released object-level security (OLS) for securing tables and columns in Power BI Premium and Pro. This method of securing objects currently requires third-party tools, such as Tabular Editor. In addition, this method has the advantage of securing even the metadata about the objects such that report viewers without access will not even know that the tables and columns exist in the model. However, there are disadvantages to OLS as well; namely, by completely hiding the tables and columns in the model, it becomes difficult to create measures and report...

Leveraging Aggregation Tables

DirectQuery mode is a great tool for overcoming the scalability issues of Import mode, or for providing real-time reporting to business users. However, DirectQuery can be slow and place a lot of strain on the backend server system, since every visual refresh and interaction causes queries to be sent to the source system. Aggregation tables and dual-mode storage tables were designed to fix the speed issues associated with using DirectQuery against large datasets in the millions, billions, and trillions of rows. In essence, aggregation tables pre-aggregate data and store it in Import mode. Power BI uses these aggregation tables behind the scenes when possible to limit the number of queries sent to the data source.

This recipe demonstrates how to create and configure an aggregation table for use in Power BI reports.

Getting ready

To prepare for this recipe, follow these steps:

1. Open Power BI Desktop.
2. Create a DirectQuery query...

Conclusion

In this chapter, we explored the primary processes of designing and developing robust data models in Power BI Desktop. Common data modeling challenges, including multiple grains and many-to-many relationships, were shown to be handled relatively easily with standard Power BI features. In addition, examples were provided for adding business logic and definitions developed using the DAX language. Finally, use cases for increasing the value and sustainability of models via metadata settings and advanced features were explored.