FIT5137 Chap.4 Hierarchies, Bridges and Dimension Keys
Hierarchies, Bridges and Dimension Keys
Hierarchies, Bridges and Dimension Keys addresses dimensional relationships that a simple star cannot represent safely. A hierarchy defines how lower-level members roll into higher levels. A snowflake separates some levels into related tables. A bridge enumerates many-to-many membership and can carry an allocation factor. A surrogate key gives the warehouse control over identity and history.
These devices solve different problems, so the chapter compares their semantic promises before considering layout. The difficult part is aggregation: joining a fact through several memberships repeats the measure unless the query uses distinct counts or a documented allocation policy. Historical membership adds effective periods and overlap checks.
The assessment skill is to show query behaviour with one transparent entity, explain whether totals should reconcile or exceed the base, and label results as allocated, full-participation, distinct-entity or event-based.
What this chapter covers
- 01
Define hierarchy levels and parent dependencies
- 02
Compare denormalised dimensions with snowflakes
- 03
Recognise genuine many-to-many membership
- 04
Introduce a bridge without creating new events
- 05
Distinguish participation from allocated measurement
- 06
Validate allocation factors and rounding
- 07
Use surrogate keys for controlled history
- 08
Reconcile bridge results to untouched fact totals
Worked example · free
Allocate revenue through a segment bridge
- 1Check the factors: 0.50 plus 0.30 plus 0.20 equals 1.00, so they form a complete partition.
- 1Allocate to the first segment: $250 times 0.50 equals $125.
- 1Allocate to the second segment: $250 times 0.30 equals $75.
- 1Allocate to the third segment: $250 times 0.20 equals $50.
- 2Reconcile allocated revenue: $125 plus $75 plus $50 equals the base $250.
- 2Explain that an unallocated join would repeat $250 three times and produce $750, which measures participation rather than exclusive revenue.
Key terms
- Dimension hierarchy
- An ordered roll-up path among descriptive levels, such as city, state and country.
- Snowflake schema
- A dimensional structure that separates some hierarchy levels into related tables.
- Bridge table
- A table listing multiple memberships between dimension concepts at a declared period.
- Allocation factor
- A documented proportion used to distribute a fact measure across bridge memberships.
- Effective period
- The interval during which a membership or dimension version is treated as valid.
- Full participation
- Reporting that attaches the entire measure to every membership instead of partitioning it.
Hierarchies, Bridges and Dimension Keys FAQ
When is a bridge table necessary?
Use a bridge when one member can belong to several members of another classification at the same analytical time, such as a product with multiple labels. A single foreign key cannot represent that multiplicity without choosing one value or repeating the dimension row.
Does a bridge prevent double counting automatically?
No. It represents membership. Joining a fact through multiple bridge rows repeats the fact measure. The query must count distinct events or entities, use full-participation semantics deliberately, or apply documented factors that reconcile to the base total.
What does a surrogate key solve?
A surrogate key gives the warehouse a stable identifier for each managed dimension row and enables multiple historical versions of a business entity. It does not by itself resolve many-to-many membership, validate a hierarchy or select the correct history policy.
How should changing hierarchy membership be reported?
Choose whether analysis follows the hierarchy known at event time or restates history under the current structure. Implement effective periods or versioned keys accordingly, test overlap and gap defects, and label the interpretation so users understand why historical totals group as they do.
Assessment move
Make a four-column comparison for hierarchy, snowflake, bridge and surrogate key: problem solved, row meaning, aggregation risk and validation. Then work one many-to-many example twice, first as full participation and then as allocated measurement. Check factors, rounding and history. For hierarchy questions, test whether every child has one intended parent under the selected period.
In assessment answers, do not stop after drawing the device. Show how one base fact behaves through the relationship and whether the category totals should reconcile. Add an effective-date timeline to one bridge case. Place an event before, during and after a membership change, then verify which bridge row applies and whether periods overlap or leave gaps.
Repeat the exercise under a current-classification policy and explain why historical category totals differ. For a snowflake decision, compare join complexity with the control gained from one shared parent table. Finally, label three query outputs as membership count, distinct entity count and allocated measure. Being able to distinguish those outputs quickly prevents the most common many-to-many interpretation error.
Add a rounding case in which fractional allocations leave a residual. Decide where the residual is assigned, record the policy and reconcile to the untouched fact. The goal is to keep small technical adjustments visible instead of allowing them to become unexplained category differences. Re-run the bridge calculation after one membership expires and verify that historical events follow the chosen temporal interpretation.
