Model the data
- By Daniil Maslyuk
- 11/5/2022
Chapter summary
Power BI supports various types of schemas: flat (fully denormalized), star, and snowflake. The preferred schema for Power BI is the star schema.
You can configure various column and table properties in the Model view.
In some cases, it may be preferable to define role-playing dimensions, which allow you to use a single dimension to filter one fact table by using different keys in the table.
Power BI supports the following three cardinality types for relationships: one-to-one, one-to-many, and many-to-many. For one-to-one relationships, the cross-filter direction is always Both (each table filters the other). One-to-many dimensions can have their cross-filter direction be set to either Single (the one side filters the many side) or Both. You choose the cross-filter direction of many-to-many relationships depending on your business requirements. Relationships whose cross-filter direction is set to Both are also known as bidirectional relationships.
For bidirectional relationships, security filters won’t flow in both directions automatically, though you can configure that behavior in the relationship properties.
For best performance, look carefully at the storage mode of each table, the cardinality and cross-filter direction of relationships, and the cardinality of columns (the number of distinct values).
Besides measures, you can use DAX to create calculated tables and calculated columns in Power BI.
You can create a common table in Power BI by using Power Query or DAX, or you can load it from a data source.
Power BI supports the creation of hierarchies, which can be useful to make models more user-friendly, though they have no technical advantages over several fields being used together in a visual without being combined in a hierarchy.
You can secure your data model by using row-level security, which can use static DAX filters on one or more tables, or dynamic row-level security that considers which user is viewing the report.
Power BI allows you to use natural language queries by using the Q&A visual. You can add synonyms to your data model to make Q&A work better.
CALCULATE is one of the most important functions in DAX, and you can use it to manipulate filters. More specifically, you can add, ignore, and update filters. CALCULATE is also used for context transition.
The Time Intelligence family of DAX functions allows you to aggregate values across time; for instance, you can use DATESYTD to calculate year-to-date values, or you can use DATEADD to calculate a value during the same period last year. There are also functions that allow you to create semi-additive measures, such as OPENINGBALANCEMONTH.
Power BI has a feature called Quick Measures, which allows you to define calculations without writing any DAX code.
In some cases, it may be preferable to replace numeric columns with measures to reduce the size of the data model.
In general, you should only load data that is necessary for analysis by removing columns or filtering rows in Power Query, especially for primary keys of fact tables.
Performance Analyzer in Power BI can be useful to identify performance bottlenecks.
You can improve the cardinality of columns by selecting appropriate data types, as well as summarizing data.