FIT5137 Chap.6 Granularity and Multi-Input Warehouses
Granularity and Multi-Input Warehouses
Granularity and Multi-Input Warehouses explains how detail changes across warehouse layers and how data from several operational systems can be combined without losing meaning. An event fact can be summarised into day or month layers, but a summary cannot recreate discarded events or dimensions.
Measures also aggregate differently: counts and amounts may be additive, balances may be semi-additive across time, and rates should be rebuilt from components. Multiple inputs introduce another challenge. Vertical stacking appends comparable events; horizontal integration adds attributes to matched entities or events. Both require semantic conformance, not merely matching column names.
The chapter covers source lineage, unit and time conversion, effective crosswalks, unmatched populations, source-level freshness and independent replay. A defensible integrated warehouse reconciles every source before and after combination and states which questions each retained grain can no longer answer.
What this chapter covers
- 01
Define each fact layer with an explicit row meaning
- 02
Distinguish detail from levels of aggregation
- 03
Preserve additive components behind derived rates
- 04
Reconcile summaries to detailed facts
- 05
Compare vertical stacking with horizontal integration
- 06
Conform identifiers, units, codes and event rules
- 07
Preserve source lineage and unmatched populations
- 08
Isolate source failures and publish freshness
Worked example · free
Aggregate event detail into a daily row
- 1Declare the summary grain as one row per depot per completion date.
- 1Event count equals 1 plus 1 plus 1 plus 1, which is 4.
- 2Total duration equals 18 plus 22 plus 35 plus 45, which is 120 minutes.
- 1On-target count is 3, and the denominator is 4 events.
- 1On-target rate equals 3 divided by 4, which is 0.75 or 75%.
- 2Store event count, duration and on-target count so a later period can recompute the rate. Reconcile all three fields to detailed events.
Key terms
- Detailed grain
- The finest retained event level in a warehouse, before summary distinctions are removed.
- Aggregate layer
- A fact representation grouped to fewer dimensions or coarser levels for repeated analysis.
- Vertical stacking
- Appending semantically compatible rows from several inputs after conformance.
- Horizontal integration
- Joining complementary attributes about the same entity or event through governed identity.
- Source lineage
- The retained identity of the contributing operational system and original record.
- Crosswalk table
- A governed mapping between local identifiers and shared warehouse keys, often with effective dates.
Granularity and Multi-Input Warehouses FAQ
Does lower detail always mean a bad design?
No. Summaries can improve repeated query performance and match a stable reporting need. The design is weak only when discarded distinctions are still needed, aggregation rules are invalid, or the summary is presented as if it retained transaction detail.
Can matching schemas always be stacked?
No. One source might create a booking at initiation while another sends only confirmed bookings. The columns match but event populations differ. Conform the qualifying rule, retain source and status context, or keep metrics separate until comparison is valid.
Why preserve original values after conversion?
Original values support audit, debugging and policy review. A converted distance, timestamp or category may depend on a rule that later changes. Retaining lineage and the conversion version lets the warehouse restate affected outputs without losing the original evidence.
How should unmatched identifiers be treated?
Measure their record count and business magnitude by source, preserve them in a controlled path and investigate the mapping gap. Silent deletion can bias combined totals. A managed unknown may retain facts, but it should not disguise a serious or growing conformance failure.
Assessment move
Create a granularity ladder for one process and write retained dimensions, additive components and lost questions at each level. Recompute a rate from components rather than averaging rates. For multi-input cases, classify the operation as stacking or enrichment, then test event meaning, identifiers, units, time zones and code sets. Practise a source crosswalk with one unmatched and one multiply matched key.
Your final answer should include source-level reconciliation, combined reconciliation, freshness and a scoped claim of fitness for the named analysis. Add a late-arrival event after the daily summary has been built. Update every additive component and prove that the recalculated rate agrees with detail.
Next, create two source systems with matching columns but different event definitions and write the rule needed before stacking. Include one unit conversion and test it with a known value. Build the combined control table by source so a defect in one feed remains visible. End by stating how that feed could be replayed or excluded without rebuilding trusted inputs.
This turns architecture resilience into a concrete revision exercise rather than a slogan. Add one freshness mismatch between detail and summary and explain how it can mimic an aggregation defect. Require the report to display its latest complete source window. This helps separate arithmetic, arrival and integration problems during review.
Include a query that isolates every arrival window represented in the final table so freshness can be verified with data rather than labels.
