Dimensional Modeling

Map the existing marts onto a bus matrix, pick the highest-leverage dimension, agree one durable business key and a master attribute set with a single owning team, then retrofit mart by mart - adding the conformed key alongside the local one before retiring the local one.

Almost never by choice. Publish only summaries when atomic rows are legally restricted, unavailable from the source, or genuinely unaffordable. Accept that any question below the summary grain becomes unanswerable until history is reloaded.

Build the atomic transaction facts first — they are the audit record everything else derives from — then add a periodic snapshot only when state questions are otherwise expensive, and an accumulating snapshot only when cycle time and work-in-progress are recurring questions.

Make flat star dimensions the published default, allow normalization only in the layer beneath, and write the exceptions as testable criteria rather than taste. Enforce through review and automated model tests, and measure adoption instead of assuming compliance.

An aggregate fact table is derived — re-summing the atomic transaction rows reproduces it exactly. A periodic snapshot measures state at set intervals, such as month-end balances, which transactions alone cannot reproduce, and its measures are usually semi-additive.

An outrigger is a dimension table referenced by a foreign key from another dimension table, such as a store dimension pointing at the shared date dimension for its opening date. It is a sanctioned, sparing exception to the flat star.

It depends on use. A price measured by the event and aggregated with other measures is a fact; a stable descriptor used to filter and group is a dimension attribute. In practice both are often stored, for different purposes.

A factless fact table records that something happened or that a condition applied, using only dimension keys and no numeric measures. It comes in two flavours: event tracking, such as student attendance, and coverage, such as which products were on promotion.

A fact constellation, also called a galaxy schema, is several fact tables sharing the same dimension tables. Models become one as soon as a second business process is added — sales and inventory both pointing at the same date and product dimensions.

A shrunken conformed dimension is a coarser version of a base dimension - fewer rows and fewer attributes - serving a fact table at a higher grain. It conforms only if its attributes are a strict subset of the base dimension's, carrying identical names and identical values.

Decide by what must stay summable. A bridge preserves the fact total and pays in fan-out and consumer complexity; an exploded grain is simple to query but destroys additivity of the original measure; a primary-value column is simplest and silently discards the other members.