As we saw in Chapter 7, Planning Power BI Projects, and Chapter 8, Connecting to Sources and Transforming Data with M, Power BI datasets are composed of three tightly integrated layers, all included within a Power BI Desktop file. The M Queries described in Chapter 8, Connecting to Sources and Transforming Data with M, connect to data sources and optionally apply data cleansing and transformation processes to this source data to support the Data Model. The Data Model, the subject of this chapter, concerns the relationships defined between fact and dimension tables, hierarchies of related columns, and metadata properties that define default behaviors, such as the sorting of column values. The final layer of datasets discussed in Chapter 10, Developing DAX Measures and Security Roles, Data Analysis Expressions (DAX) Measures, leverages the Data Model (and thus the M Queries) to deliver analytical insights for presentation in Power BI and other tools.

The term Data Model is often used instead of dataset, particularly in the context of Analysis Services. Both Azure Analysis Services models and SQL Server Analysis Services (SSAS) models created in Tabular mode include the same three layers of Power BI datasets. In other contexts, however, Data Model refers exclusively to the relationships, measures, and metadata, but not the source queries. For this reason, and given the use of the term datasets in the Power BI service, the term dataset (and dataset designer) is recommended.

The following diagram summarizes the role of each of the three dataset layers:

Three layers of datasets

At the Data Model layer, all data integration and transformations should be complete. For example, it should not be necessary to define data types or create additional columns at the Data Model level.

Ensure that each layer of the dataset is being used for its intended role. For example, DAX Measures should not contain complex logic, so as to avoid unclean or inaccurate data. Likewise, DAX Measure expressions should not be limited by incorrect data types (for example, a number stored as text) or missing columns on the date table. Dataset designers and data source owners can work together to keep the analytical layers of datasets focused exclusively on analytics.