A data modeler is designing a complex semantic model in Microsoft Fabric. The model includes multiple fact tables (e.g., Sales, Returns, Inventory) and shared dimension tables (e.g., Product, Customer, Date). The modeler needs to define relationships that allow filters to propagate correctly between these tables while avoiding ambiguity and circular dependencies. Which type of relationship cardinality is typically used when connecting a dimension table to a fact table in a star schema design?
- AOne-to-One (1:1)
- BOne-to-Many (1:*)
- CMany-to-One (*:1)
- DMany-to-Many (*:*)
Show answer & explanationAnswer & explanation
Correct answer: B. One-to-Many (1:*)
In a star schema, a dimension table (e.g., Product, Customer) contains unique values for its primary key, while a fact table (e.g., Sales) can have many rows referencing the same dimension key. Therefore, the relationship from the dimension table to the fact table is 'one-to-many'. The 'one' side is the dimension, and the 'many' side is the fact. This allows filters from the dimension to flow down to the fact table.
Why the other options are wrong
- A. One-to-one relationships are rare in dimensional modeling and typically indicate that two tables could potentially be merged.
- C. While the relationship *from* the fact table *to* the dimension table is effectively 'many-to-one', the standard way to describe the cardinality *from dimension to fact* is 'one-to-many'.
- D. Many-to-many relationships are used for more complex scenarios, often requiring bridge tables, and are not the standard for direct dimension-to-fact connections.
One-to-Many Relationship
A one-to-many (1:*) relationship in a semantic model connects a table where each value in a key column appears only once (the 'one' side, typically a dimension table) to another table where the same key value can appear multiple times (the 'many' side, typically a fact table). This is the most common relationship type in star schemas.
- Most common relationship type in star schemas.
- Filters flow from the 'one' side to the 'many' side by default.
- Ensures data integrity and efficient filtering.
- Dimension tables are typically on the 'one' side, fact tables on the 'many' side.
Memory trick: Cardinality: How many times keys appear, one or many, let's be clear.