Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Easy
A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Customers' table and a 'Product Reviews' table. A customer can submit multiple reviews for different products, and a product can have reviews from multiple customers. The modeler needs to analyze customer demographics alongside product review sentiment. How should the modeler establish the relationship between 'Customers' and 'Product Reviews' to accurately link a customer to their reviews?
- AEstablish a many-to-one relationship from 'Product Reviews' to 'Customers'.
- BEstablish a one-to-many relationship from 'Customers' to 'Product Reviews'.
- CDo not establish a direct relationship; use DAX functions to relate them.
- DEstablish a many-to-many relationship between 'Customers' and 'Product Reviews' using a bridge table.
Show answer & explanationAnswer & explanation
Correct answer: B. Establish a one-to-many relationship from 'Customers' to 'Product Reviews'.
In this scenario, one customer can have multiple product reviews, but each product review belongs to only one customer. This is a classic one-to-many relationship, where 'Customers' is the 'one' side and 'Product Reviews' is the 'many' side. A direct one-to-many relationship is the correct and simplest way to link them.
Why the other options are wrong
- A. A many-to-one relationship from 'Product Reviews' to 'Customers' is equivalent to a one-to-many from 'Customers' to 'Product Reviews', but the convention is usually 'one-to-many' from the dimension to the fact-like table.
- C. While DAX can create relationships on the fly, establishing a direct relationship in the model is best practice for clarity, performance, and broader tool compatibility.
- D. A many-to-many relationship with a bridge table is used when a record in 'Customers' can relate to multiple records in 'Product Reviews' AND a record in 'Product Reviews' can relate to multiple records in 'Customers'. Here, a review only belongs to one customer. The problem statement refers to a customer submitting multiple reviews for *different products*, not multiple customers submitting the *same* review.
One-to-Many Relationship
A one-to-many relationship in a semantic model connects a table where each value in a key column is unique (the 'one' side) to another table where those key values can appear multiple times (the 'many' side).
- Most common relationship type in star schemas.
- Filters propagate from the 'one' side to the 'many' side by default.
- Ensures accurate aggregation and filtering from dimension to fact tables.
- Key column on 'one' side must be unique.
Memory trick: Cardinality: How Many on Each Side?