Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' table and a 'Promotions' table. A promotion can apply to multiple sales, and a sale can be influenced by multiple promotions (e.g., a customer buys an item on sale and also uses a loyalty discount). The modeler needs to accurately analyze the impact of promotions on sales. Which type of relationship should be established between 'Sales' and 'Promotions'?
- AOne-to-many
- BMany-to-one
- COne-to-one
- DMany-to-many
Show answer & explanationAnswer & explanation
Correct answer: D. Many-to-many
The scenario describes a many-to-many relationship: a single promotion can affect many sales, and a single sale can be affected by multiple promotions. This type of relationship requires careful handling in a semantic model, often through a bridging table.
Why the other options are wrong
- A. One-to-many implies one promotion affects many sales, but a sale is affected by only one promotion, which is incorrect.
- B. Many-to-one implies many promotions affect one sale, but that sale is affected by only one promotion, which is incorrect.
- C. One-to-one implies a single promotion affects a single sale, which is incorrect.
Many-to-Many Relationship
A type of relationship in a data model where a record in Table A can relate to multiple records in Table B, and a record in Table B can also relate to multiple records in Table A. Often implemented using a bridging (or junction) table.
- Used when direct one-to-many relationships are not sufficient.
- Requires an intermediate bridging table to resolve in relational models.
- Supported directly in Fabric semantic models, but bridging tables are still best practice.
Memory trick: Many-to-many, a bridge you'll need, for complex data, it's the right deed.