You are optimizing a semantic model that has a large number of tables and complex relationships. Which TWO practices should you follow to improve both performance and usability?
Trap 1: Ensure that all relationships are set to 'Bi-directional'…
Bi-directional relationships should be used sparingly as they can cause performance issues and ambiguity in the model. They force the engine to check for filters in both directions, which increases query complexity and can lead to unexpected results if multiple paths exist between tables.
Trap 2: Create multiple active relationships between two tables to provide…
A semantic model can only have one active relationship between two tables at a time. Multiple paths can be defined using inactive relationships, which must then be explicitly activated in DAX using the USERELATIONSHIP function, preventing ambiguity in how filters propagate through the model.
Trap 3: Use the 'Is Available in MDX' property to disable MDX support for…
For large models, disabling MDX support for specific columns or tables can reduce the processing time and memory usage. This is particularly useful if users only access the model via DAX-based tools like Power BI, as it streamlines the metadata that the engine needs to manage.
- A
Hide foreign key columns in the fact tables from the report view.
Foreign key columns are used to create relationships but are rarely useful for end-users in a report. Hiding them reduces clutter in the Fields pane and prevents users from accidentally using them in visuals, which helps maintain a clean and professional reporting interface.
- B
Ensure that all relationships are set to 'Bi-directional' cross-filter direction.
Why it fails: Bi-directional relationships should be used sparingly as they can cause performance issues and ambiguity in the model. They force the engine to check for filters in both directions, which increases query complexity and can lead to unexpected results if multiple paths exist between tables.
- C
Create multiple active relationships between two tables to provide different filter paths.
Why it fails: A semantic model can only have one active relationship between two tables at a time. Multiple paths can be defined using inactive relationships, which must then be explicitly activated in DAX using the USERELATIONSHIP function, preventing ambiguity in how filters propagate through the model.
- D
Use the 'Is Available in MDX' property to disable MDX support for non-essential tables.
Why it fails: For large models, disabling MDX support for specific columns or tables can reduce the processing time and memory usage. This is particularly useful if users only access the model via DAX-based tools like Power BI, as it streamlines the metadata that the engine needs to manage.
- E
Always use 'Many-to-Many' relationships to avoid data quality errors.
Why it fails: Many-to-many relationships should only be used when the data truly requires it. They are less performant than one-to-many relationships and can hide underlying data quality issues. It is better to clean the data and use standard relationships whenever possible to ensure accuracy and speed.