Analytical Data Modeling

Declare the grain first

Before choosing a single column, decide what one row of the table is going to mean. An analytical table is trustworthy only when the meaning of one row is stable.

The grain of a fact table states the business event or measurement represented by one row, giving downstream aggregations a stable interpretation. Everything else follows from it: which keys the row needs, which measures may live in it, and which totals are safe to compute.

Read the grain as a sentence in the language of the business:

A sales fact can be stored at product and date grain while a target fact can use subcategory and quarter grain. Two fact tables in the same model may legitimately disagree about how fine their rows are, as long as each one declares its own grain.

The grain is a contract with everyone who queries the table. Measures in one fact table must be interpreted at that table's declared grain. Adding up a column of daily product sales gives a product total; adding up a column of quarterly subcategory targets gives a subcategory total. The two numbers are comparable only after they have been brought to a common grain.

The contract also constrains how the table is joined. Joining a fact to a dimension must preserve the intended one-row meaning rather than multiply facts through an unintended many-to-many match. If a join turns one order line into three rows, the measures triple and every total built on them is wrong — even though no value in the source data changed.

A useful habit: write the grain down in one sentence before writing the CREATE TABLE. If the sentence needs the word and three times, the grain is probably finer than the business actually needs.

Facts, dimensions, and measures

Dimensional models separate measurable activity from descriptive context, so that a question like revenue by store city has an obvious home for each half.

A fact table stores business events or observations and commonly contains numeric measures. Rows arrive as things happen: a sale, a shipment, a meter reading. Fact tables are the tall part of the model — millions or billions of rows, few columns.

A dimension table stores descriptive attributes used to filter, group, and label facts. Rows describe the things the business talks about: products, customers, stores, dates. Dimensions are the wide part of the model — comparatively few rows, potentially hundreds of attributes, because every attribute is a way somebody might want to slice the facts.

A shortcut for spotting the dimensions a model needs: listen for the word by. "Revenue by salesperson, by month, by product category" names three dimensions and the attributes they must carry.

Measures are values such as quantity or amount that analysts aggregate at a chosen dimensional context. They are the things that get summed, averaged, and counted.

Descriptive attributes such as product category or customer location provide context but are not the measured event itself. They are the things that get grouped and filtered on. A category is never summed; an amount is rarely grouped by.

Tell a measure from an attribute

The same table can hold both kinds of column, so the test is behavioral rather than visual: ask whether the value is something you would add up, or something you would group by.

Numeric does not mean measure. A postal code and a store number are numbers that nobody wants a total of; they label rows, so they behave as attributes. A price, on the other hand, changes often and is genuinely aggregated, which is why a frequently changing numeric attribute is sometimes better stored in the fact table as a measure than in the dimension as text.

Operational shape and analytical shape

Model shape follows workload shape. The same business data is organized differently depending on whether the job is recording activity or explaining it.

Normalized operational models reduce redundancy and support consistent transactional updates across related entities. A customer's city is stored once, in one place, so a change of address is a single update that cannot leave two copies disagreeing. That is exactly what an order-entry system needs.

Dimensional analytical models organize facts and dimensions to make aggregation and business-oriented queries easier. A reporting query does not update anything; it reads enormous numbers of rows and groups them. Spreading the descriptive context across a dozen normalized tables makes every such query pay for another set of joins.

Neither shape is wrong; they answer different questions. The reporting layer is normally built from the operational model rather than instead of it, and the source system keeps its normalized design.

When the reporting priority is frequent aggregation rather than update efficiency, the dimensional star is the better serving design, because it removes the join work that the normalized source imposes on every query. Query authors ask for revenue by customer, product, and date and get it from a fact table and three directly related dimensions.

The choice is driven by the access pattern, not by taste. If a team reads two columns across a billion rows every morning, the model should make that cheap.

Star and snowflake shapes

A star schema places a central fact table around directly related denormalized dimensions. Draw it and you get the shape it is named after: measures in the middle, context radiating out one join away. Because a dimension is small next to a fact table, storing a product's subcategory and category redundantly on every product row is usually a bargain — the storage cost is small and every query saves a join.

The one common exception normalizes a hierarchy back out. A snowflake schema normalizes dimension hierarchies into multiple related tables, trading simpler storage maintenance for more joins. A Product / Subcategory / Category chain is the classic example.

Snowflaking is worth considering in a few specific situations:

• The dimension is extremely large and storage cost outweighs query speed.
• Facts exist at different levels: the sales fact sits at product level while the sales target fact sits at subcategory level, and each needs a key to attach to.
• Historical change has to be tracked at a higher level of the hierarchy.

Going further in the other direction — a separate table for every level of every hierarchy — does not simplify anything. It adds joins to precisely the queries the serving layer was built to make easy.

Business keys and surrogate keys

Keys connect business identity to analytical history, and the two jobs need two different keys.

A business key identifies the source-system entity, while a surrogate key identifies a warehouse dimension row. The business key — a customer ID, an employee number — comes from the source and often means something to people. The surrogate key is generated by the warehouse, means nothing on purpose, and is unique to one row of one dimension table.

Fact rows store dimension keys so each event resolves to the intended descriptive context. The fact carries the surrogate key, not the descriptive text, which is what keeps the fact table narrow and the descriptions in one place.

That second column is the whole point. Surrogate keys also let a warehouse consolidate several sources without duplicate identifiers clashing, collapse a multi-column natural key into one narrow integer, and keep the fact table small.

When descriptive values must retain history, separate dimension versions can share a business key and use different surrogate keys. One customer, one business key, several rows — one per period in which the description was valid. The current row is flagged as current; the older rows keep the values that were true when the older facts happened.

Keep history without multiplying facts

A star schema preserves historical meaning when each fact points to the dimension version valid for that event. The fact is loaded with the surrogate key of the version that was current at the time, so the description it resolves to is the description that was true then.

Work through a move. A customer moves from Madrid to Lisbon. Finance needs last year's sales still attributed to Madrid, this year's to Lisbon, and the source customer ID cannot change.

1. Keep the business key: the source identity is unchanged.
2. Close the Madrid row by setting its end-of-validity date and clearing its current flag.
3. Insert a Lisbon row with a new surrogate key, valid from that date, flagged current.
4. New facts store the Lisbon surrogate key; the old facts keep pointing at the Madrid one.

Overwriting the single row instead would report every historical sale as Lisbon — history quietly rewritten. Copying both cities onto every fact row abandons the versioned relationship and makes consistent context somebody's manual problem.

The failure to watch for is the join that multiplies. Match a fact on the business key alone and it will find every version that ever shared that key: one order line becomes three rows, and the measures triple.

Only the last row satisfies both requirements at once, because it restores the one-row meaning the grain declared and attaches the description that was valid for that event.

Key takeaways

• Grain first. The grain of a fact table states the business event or measurement represented by one row, and every measure in that table is interpreted at that grain.
• Two kinds of table. Facts hold events and numeric measures; dimensions hold the descriptive attributes used to filter, group, and label those facts.
• Two kinds of column. Amounts and quantities are measures to aggregate; categories, regions, and month names are context to group by.
• Shape follows workload. Normalized models protect consistent updates; dimensional models make aggregation direct, with the star keeping dimensions denormalized and the snowflake normalizing hierarchies at the cost of extra joins.
• Two kinds of key. The business key carries source identity, the surrogate key identifies one dimension row, and versions that share a business key with different surrogate keys are what let a star preserve history.