Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is optimizing a Power BI model for a large enterprise. The model contains a 'Sales' fact table and several dimension tables. The 'Sales' table has a composite key consisting of 'OrderDateKey', 'ProductKey', and 'CustomerKey'. There are also separate 'Date', 'Product', and 'Customer' dimension tables. To improve query performance and reduce memory usage, the modeler wants to ensure the relationships between the fact and dimension tables are optimally configured. What is the most effective approach for establishing these relationships?
- AEstablish single-directional (many-to-one) relationships from the fact table to each dimension table.
- BCreate bidirectional relationships between all fact and dimension tables.
- CEstablish single-directional (one-to-many) relationships from each dimension table to the 'Sales' fact table.
- DCreate inactive relationships and activate them using USERELATIONSHIP when needed.
Show answer & explanationAnswer & explanation
Correct answer: C. Establish single-directional (one-to-many) relationships from each dimension table to the 'Sales' fact table.
In a star schema, the most effective and performant relationship configuration is single-directional (one-to-many) from the dimension table to the fact table. This allows filters to flow from the dimension (the 'one' side) to the fact (the 'many' side), which is the most common and efficient filtering pattern, reducing ambiguity and improving query performance.
Why the other options are wrong
- A. Relationships should flow from the 'one' side (dimension) to the 'many' side (fact), not the other way around.
- B. Bidirectional relationships can cause ambiguity and performance issues, and are generally avoided unless strictly necessary.
- D. Inactive relationships are useful for specific scenarios with multiple relationships, but not the primary configuration for core active relationships.
Relationship Optimization (Star Schema)
Configuring relationships between fact and dimension tables in a Power BI data model to ensure optimal filter propagation, query performance, and model clarity.
- One-to-many (1:*) relationships are preferred.
- Filter direction should be from dimension to fact.
- Avoid bidirectional relationships unless absolutely required and understood.
Memory trick: Dimensions filter facts, one way is the best path.