Describe the concept of dimensional modeling and its benefits.

Official answer

Dimensional modeling organizes analytical data into measurable business events and descriptive context. Start by declaring the grain: for example, one fact row per order line, not a mixture of order totals and line items.

An order-line fact could contain date, product and customer dimension keys plus quantity and extended net amount. Dimensions hold attributes used for filtering and grouping. At a consistent currency and definition, quantity and net amount can often be summed across lines. A unit price or margin percentage should not simply be summed; calculate appropriate ratios from their underlying amounts.

A star schema can make common analytical queries easier to understand. It often repeats descriptive attributes intentionally. Surrogate dimension keys can separate warehouse identity from source keys and identify historical versions, but they do not inherently reduce storage. A type 2 history design creates additional dimension rows.

Define how late events, refunds, missing dimensions and attribute changes are handled. Reconcile totals to the source at the declared grain. Benchmark representative queries on the chosen engine before claiming any performance improvement; good modeling alone does not establish a percentage speedup.

Sources

Kimball: grain; Microsoft: star schema guidance.