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 'Customers' table and a 'Sales' table. The 'Customers' table has a 'CustomerID' column, and the 'Sales' table also has a 'CustomerID' column. The data quality in the 'Sales' table is inconsistent, with some 'CustomerID' values that do not exist in the 'Customers' table (orphaned rows). The modeler needs to create a relationship that allows correct filtering from 'Customers' to 'Sales' but also handles these orphaned 'CustomerID' values gracefully without breaking the model. What is the most appropriate cardinality and referential integrity setting for this relationship?
- ACardinality: Many-to-many (*:*), Assume referential integrity: No
- BCardinality: One-to-many (*:1), Assume referential integrity: Yes
- CCardinality: Many-to-one (*:1), Assume referential integrity: Yes
- DCardinality: One-to-many (1:*), Assume referential integrity: No
Show answer & explanationAnswer & explanation
Correct answer: D. Cardinality: One-to-many (1:*), Assume referential integrity: No
The relationship from 'Customers' (one) to 'Sales' (many) should be 1:*. Because there are orphaned rows in 'Sales' (CustomerID values without a matching customer in 'Customers'), assuming referential integrity would lead to incorrect results or errors. Therefore, 'Assume referential integrity' must be set to 'No' to handle these inconsistencies gracefully.
Why the other options are wrong
- A. Many-to-many is not necessary here for a standard customer-sales relationship, and while 'Assume referential integrity: No' is correct for orphaned rows, the cardinality is overly complex for the scenario described.
- B. Cardinality is incorrect (should be 1:*, not *:1 for Customers to Sales), and assuming referential integrity would be problematic with orphaned rows.
- C. Cardinality is incorrect, and assuming referential integrity is wrong due to orphaned rows.
Referential Integrity
Referential integrity in a semantic model ensures that every foreign key value in a child (many) table has a matching primary key value in the parent (one) table.
- Crucial for accurate relationship behavior.
- Assuming it can optimize query performance.
- Disabling it is necessary when orphaned rows exist to prevent errors.
Memory trick: Cardinality counts, integrity checks, orphaned rows need careful effects.