Eight Measures, One Table

The V1 series ended with a promise unpaid. MeasureTypeLookup declared eight measures; the warehouse computed four. The other four — SaleTypeTotal, SaleTypeAverage, OverallTotal, OverallAverage — sat in the lookup table with the CurrentSalesState view returning hardcoded NULL where they belonged. V2 is the version that fills them in. All eight measures, computed and stored, in a single fact table.

That last clause — single fact table — is the whole plot of this series. Computing aggregates alongside base measures in one SalesFact is the obvious first move, and it works, and it also plants a bug that took a dedicated table in V3 to properly kill. V2 is the version where KC Star became analytical and simultaneously learned that “just add the aggregates” is never just.

What V2 inherits

V2 stands directly on V1's inverted foundation: dimensions as rows in SalesDim, measures as rows in SalesFact, SCD2 windows on both, SHA-256 version hashing for change detection, the two triggers that explode sales into rows and version them on change. Everything from V1 is still here. V2 adds computation, not architecture — the tables grow a couple of columns, one new function appears, and the presentation view finally stops lying about those four NULLs.

The measures split cleanly into two kinds:

• Base measures (1–4) — TotalSale, NetRevenue, DiscountAmount, ProfitMargin. Computable from a single sale's own dimensions. V1 already did these.
• Aggregate measures (5–8) — SaleTypeTotal, SaleTypeAverage, OverallTotal, OverallAverage. These need many sales: a sum or average across a group. This is the new work.

The lookup's CalculationFormula column describes the aggregates in window-function language — SUM(TotalSale) OVER (PARTITION BY SaleType) and the like. That phrasing turns out to be aspirational: the implementation uses GROUP BY, not window functions, for reasons I get into in part 3. But the intent the formula records is exactly right — an aggregate measure is a base measure rolled up across a partition.

Hybrid storage: SaleType leaves the rows

The first structural change is subtle and consequential: SaleType stops being a dimension row and becomes a column. In V1, SaleType was DimTypeId 5 — one of the seven rows every sale exploded into. In V2, DimTypeLookup seeds only six dimension types, SaleType is gone from them, and instead both SalesDim and SalesFact gain a literal SaleType VARCHAR column.

Why promote one dimension out of the row store? Because SaleType is the partition key for half the new measures. SaleTypeTotal and SaleTypeAverage group by it. Keeping it as a buried key-value row would mean joining SalesDim back to itself to recover the partition for every aggregate. Lifting it to a column makes the grouping direct. The README calls this the “hybrid” design — SaleType as a column, everything else as rows — and part 2 is entirely about what that promotion costs and buys in a schema whose whole selling point was not having columns.

All eight, in one table

With SaleType accessible as a column, V2 computes the aggregates and stores them right back in SalesFact alongside the base measures. The mechanism is a new function, CreateAggregatedFacts(), that CreateSalesFacts calls after it writes the base measures. Two new columns support the arrangement:

• SaleType VARCHAR — nullable on facts, because an overall aggregate (measures 7 and 8) belongs to no single sale type.
• BaseFactId INT NULL — a back-reference from an aggregate row to a base fact that contributed to it.

Aggregate rows are stored with SalesReferenceId = NULL — they do not belong to one sale, they summarise many. So SalesFact now holds two populations in one table: per-sale base facts with a real SalesReferenceId, and cross-sale aggregate facts with a NULL reference and a SaleType partition. That coexistence is convenient and, as part 4 shows, precisely where the trouble lives.

The immediate reward is that the view stops lying. CurrentSalesState now surfaces real numbers in the four columns V1 hardcoded to NULL, and a genuinely analytical query becomes possible:

SELECT Id, SaleType, TotalSale, SaleTypeAverage,
       (TotalSale - SaleTypeAverage) AS VarianceFromAverage
FROM CurrentSalesState
ORDER BY SaleType, Id;

Each sale, next to the average for its sale type, next to how far it deviates. That single query is the reason V2 exists — and it was impossible one version ago.

Where the series goes

1. Eight measures, one table — this post.
2. Promoting SaleType from row to column — why one dimension left the row store, the renumbering it forced, and what it did to the hash.
3. GROUP BY in a window function's clothes — how CreateAggregatedFacts actually computes the aggregates, and why the formulas lie.
4. The expiry clause that missed — the shipped bug where per-SaleType aggregates never expire, and why one table cannot cleanly hold two populations.

That last post is the honest hand-off: the single-table design has a leak, the leak motivates a dedicated aggregation table, and that table is where V3 begins.