Designing Import, DirectQuery, and Composite Data Models

This chapter utilizes the queries described in Chapter 3, Connecting To Sources And Transforming Data With M, to create import, DirectQuery, and composite data models. Relationships are created between fact and dimension tables to enable business users to analyze the fact data for both Internet Sales and Reseller Sales simultaneously by using common dimension tables and across multiple business dates. In addition, business users can compare these fact tables against the Annual Sales and Margin Plan.

This chapter also contains recommended practices for model metadata such as assigning data categories to columns and providing users with a simplified field list. Finally, we review common performance analysis tools and optimization techniques for import and DirectQuery data models.

As described in the Dataset planning section of Chapter 1, Planning Power BI Projects, data models can have modes of either import, DirectQuery...

Dataset layers

As covered in Chapter 1, Planning Power BI Projects, and Chapter 3, Connecting To Sources And Transforming Data With M, Power BI datasets are composed of three tightly integrated layers, which are all included within a Power BI Desktop file.

The first layer, the M queries described in Chapter 2, Preparing Data Sources, connect to data sources and optionally apply data cleansing and transformation processes to this source data to support the Data Model.

The second layer, the Data Model and the subject of this chapter, primarily involves the relationships defined between fact and dimension tables, hierarchies reflecting multiple levels of granularity of a dimension, and metadata properties such as the sort order applied to columns.

The final layer of datasets is discussed in Chapter 5, Developing DAX Measures and Security Roles, Data Analysis Expressions (DAX) measures. DAX measures leverage the Data Model to deliver analytical insights for presentation...

The Data Model

The Data Model layer of the Power BI dataset consists of the Model view, the Data view, and the Fields list exposed in the Report view. Each of the three views in Power BI Desktop is accessible via an icon in the top-left menu below the toolbar, although the Data view is exclusively available to import mode and composite datasets.

Let’s first take a look at the Model view.

The Model view

The Model view provides the equivalent of a database diagram specific to the tables loaded to the dataset. The relationship lines and icons identify the cardinality of the relationship such as the parent table (1) having a one-to-many (*) relationship with the child table.

A solid line indicates that the relationship is active, while a dotted line denotes an inactive relationship that can only be activated via the USERELATIONSHIP() DAX expression.

The arrow icons on the relationship lines advise whether cross-filtering is single-directional (one arrow...

Relationships

Relationships play a central role in the analytical behavior and performance of the dataset. Based on the filters applied at the report layer and the DAX expressions contained in the measures, relationships determine the set of active rows for each table of the model that must be evaluated. Therefore, it’s critical that dataset designers understand how relationships drive report behavior via cross-filtering and the rules that relationships in Power BI must adhere to, such as uniqueness and non ambiguity, as discussed in the next section.

Uniqueness

Relationships in Power BI data models are always defined between single columns in two separate tables. While Power BI does support direct many-to-many relationships, it is recommended that relationships with a cardinality of many-to-many be avoided because this implies that the related columns both contain duplicate values for the related columns. Relationships based on columns containing duplicate values...

Model metadata

Metadata is simply the concept of data or information about data. In Power BI, metadata is available for tables, columns, and measures within a dataset.

The consistent and complete application of metadata properties, such as Default summarization and Data category, greatly affect the usability of a dataset. With a solid foundation of tables, column data types, and relationships in place, dataset designers and BI teams should consider all primary metadata properties and their implications for user experience as well as any additional functionality they can provide.

In the following sections, we explore many of the most important types of model metadata, starting with the visibility of tables and columns.

Visibility

Data modelers can define the visibility of tables, columns, and measures within a dataset. In other words, each of these elements can either be visible or hidden to report authors and business users within the Report view.

Every table,...

Column and measure metadata

Dataset designers should review the columns and measures of each table exposed to the Report view and ensure that appropriate metadata properties have been configured. These settings, including any custom sorting described earlier in the Custom sort section of this chapter, only need to be applied once and can significantly improve the usability of the dataset.

In the following sections, we explore some of the more important metadata settings for columns and measures. Some of these apply only to columns, such as the Default summarization setting explained in the next section.

Default summarization

As mentioned, the Default summarization property only applies to columns and controls the default aggregation applied to a column such as sum, average, first, last, and so on.

The Default summarization property should be revised from Power BI’s default setting to the Do not summarize value for all columns. Power BI applies a Default summarization...

Optimizing data model performance

One of the main reasons for creating a dataset, particularly an import mode dataset, is to provide a performant data source for reports and dashboards. Although Power BI supports traditional reporting workloads, such as email subscriptions and view-only usage, Power BI empowers users to explore and interact with reports and datasets. The responsiveness of visuals for this self-service workload is largely driven by fundamental data model design decisions, as explained in the following subsections.

Additional performance factors outside the scope of this chapter include the hardware resources allocated to the dataset, such as with Power BI Premium capacities (v-cores, RAM), the efficiency of the DAX measures created for the dataset, the design of the Power BI reports that query the dataset, and the volume and timing of queries generated by users.

We first take a look at optimizing import mode datasets.

Import

The performance of an...

Summary

This chapter built on the queries from Chapter 3, Connecting To Sources And Transforming Data With M, to implement import, DirectQuery, and composite analytical data models. Relationships were created between fact and dimension tables as well as between bridge tables and the Sales and Margin Plan to enable actual versus plan reporting and analysis.

Additionally, the fundamentals of designing Power BI models were reviewed and detailed guidance on metadata and the DMVs available for analyzing memory usage was provided. Finally, guidance was provided for optimizing the performance of import, DirectQuery, and composite data models.

The following chapter continues to build on the dataset for this project by developing analytical measures and security models. The DAX expressions implemented in the next chapter directly leverage the relationships defined in this chapter and ultimately drive the visualizations and user experience demonstrated in later chapters.

Join...