FIT5137 Chap.5 Multi-Fact Models and Slicing
Multi-Fact Models and Slicing
Multi-Fact Models and Slicing keeps related business processes separate while making valid comparison possible. Booking, completed journey and maintenance work order facts have different grains, measures and sometimes different dimensional coverage. They should not be forced into one wide table or joined at detail merely because they share customer, date or vehicle columns.
Conformed dimensions provide common analytical language only when keys, attribute definitions, history and roll-up meaning agree. Cross-fact metrics are built by aggregating each fact independently to a compatible level and joining unique summaries. The chapter also uses a compatibility matrix to show which dimensions can slice each fact and which filters need separate role labels.
The key discipline is to preserve numerator and denominator behind every ratio, retain populations with appropriate outer joins, and distinguish a genuine zero from unavailable data.
What this chapter covers
- 01
Separate facts by business process and native grain
- 02
Test dimensional conformance beyond matching labels
- 03
Assign explicit roles to shared date dimensions
- 04
Build a fact-dimension compatibility matrix
- 05
Aggregate facts independently before comparison
- 06
Preserve numerator and denominator behind rates
- 07
Distinguish zero activity from missing coverage
- 08
Reconcile summary joins to untouched fact totals
Worked example · free
Combine route completion rates correctly
- 1Route A rate equals 90 divided by 100, which is 0.90 or 90%.
- 1Route B rate equals 30 divided by 60, which is 0.50 or 50%.
- 2A simple mean of the displayed rates is 90% plus 50%, divided by 2, which equals 70%. This gives both routes equal weight.
- 2Combine additive components instead: completed journeys equal 90 plus 30, which is 120; bookings equal 100 plus 60, which is 160.
- 1The weighted combined rate equals 120 divided by 160, which is 0.75 or 75%.
- 1Reconcile each component to its independently aggregated fact before joining the summaries.
Key terms
- Fact constellation
- Several fact tables connected through shared or role-playing dimensions.
- Native grain
- The event or state meaning inherent to one fact before cross-process comparison.
- Comparison level
- The compatible dimensional grouping at which independently aggregated facts can be joined.
- Dimension role
- The named use of a shared dimension, such as booking date or completion date.
- Compatibility matrix
- A fact-by-dimension map showing valid keys, roles and unsupported slices.
- Weighted rate
- A combined ratio calculated from summed numerators and denominators rather than averaged percentages.
Multi-Fact Models and Slicing FAQ
Why not join two fact tables directly?
Their relationship may be one-to-many or many-to-many, so detailed joining repeats measures. Even shared customer and date values may not identify the same event. Aggregate each fact to a supported common level or use an explicit relationship that the operational process defines.
What makes a dimension conformed?
Conformance requires shared key mapping, attribute meaning, history policy and roll-up behaviour. Identical column names are insufficient. If one fact includes contractors under vehicle while another covers fleet assets only, the dimension populations may need qualification or separate roles.
How do missing rows affect a cross-fact metric?
One process may have valid rows where another has none. Choose a join population deliberately and distinguish zero from unavailable or unarrived data. An inner join can remove the absence cases that the metric is intended to reveal.
Can every shared dimension filter every fact?
No. Use a compatibility matrix. A filter should be offered only where the fact carries a valid conformed key under the intended role. Otherwise separate the roles, map to a common level with evidence, or disclose that the comparison does not support that slice.
Assessment move
Draw three facts with their grains and list dimensions beside each. Circle only the dimensions that are genuinely conformed. For any cross-fact percentage, write the numerator query and denominator query separately, reconcile both, then join their summaries. Practise routes with different volumes so the error in averaging percentages is visible.
Add absence cases and decide whether the population needs a left, right or full outer join. End explanations by stating exactly which dimensions and roles support the metric and which do not. Turn the compatibility matrix into a testing tool. For each supported cell, identify the foreign key and its history policy. For each unsupported cell, write what misleading filter a dashboard author might otherwise add.
Create one collision where the same label means different roles, such as workshop region and passenger route region, and rename both explicitly. Then inject a duplicate summary key before the cross-fact join and verify that the base totals rise. This drill makes uniqueness controls as memorable as the final ratio calculation.
Conclude by designing a metric specification with population, numerator grain, denominator grain, shared dimensions, join type, zero handling and freshness. Give it to another reader and ask which filter would break the comparison. Revise until the unsupported slice is obvious from the specification itself.
Add one example of an unavailable denominator and decide whether the published metric should be null, suppressed or separately qualified.
