Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Easy
A data modeler is designing a semantic model in Microsoft Fabric. The model will contain sales data, customer information, and product details. The sales data is stored in a fact table, and customer and product details are in dimension tables. The modeler needs to ensure that when a user filters by a specific customer, all sales made by that customer are shown, and when filtering by a product, all sales of that product are shown. What is the most appropriate cross-filter direction to configure for the relationships between the fact table and its dimension tables?
- ANo cross-filter direction (Disabled)
- BBoth directions (Bi-directional)
- CSingle direction (One to many)
- DSingle direction (Many to one)
Show answer & explanationAnswer & explanation
Correct answer: C. Single direction (One to many)
In a standard star schema, dimension tables filter fact tables. This is a 'one-to-many' relationship from the dimension to the fact, and the filtering direction should be from the 'one' side (dimension) to the 'many' side (fact). This allows filters applied to customers or products to propagate to the sales data.
Why the other options are wrong
- A. Disabling cross-filter direction would prevent filters from propagating, making the model unusable for the stated requirements.
- B. Bi-directional filtering can introduce ambiguity and performance issues, and is generally avoided unless specific requirements dictate it.
- D. While the relationship itself is typically 'many-to-one' from the fact to the dimension, the *cross-filter direction* for filtering purposes is 'one-to-many' from the dimension to the fact.
Cross-filter Direction (One-to-Many)
Cross-filter direction in semantic models defines how filters propagate across relationships. For typical star schemas, filters flow from the 'one' side (dimension table) to the 'many' side (fact table) in a 'one-to-many' direction.
- Determines how filters applied to one table affect related tables.
- Standard for dimension-fact relationships is 'Single (One to Many)'.
- Filters flow from the dimension (one) to the fact (many).
- Bi-directional filtering should be used cautiously due to potential performance and ambiguity issues.
Memory trick: Filter flow is like a river, usually from one source to many destinations.