When is dimensional modelling still the right approach, and what does it cost?

When it is right

When many analysts query the data with business intelligence tools and need to combine measures across dimensions.

The star schema exists to make that specific pattern fast and comprehensible: a fact table of measures, joined to dimension tables of descriptive attributes. Analysts can slice sales by region, product, time and channel without understanding the operational schema, and the query engine can optimise a shape it recognises.

Its enduring value is usability. A well-modelled star schema is understandable by people who are not engineers, which is what makes self-service analytics possible at all.

What it costs

1. Transformation work. Operational data must be reshaped, conformed and maintained — an ongoing pipeline rather than a one-time modelling exercise.

2. Loss of fidelity. Aggregating to a grain discards detail. Questions requiring finer grain need a different model or a return to the source.

3. Slowly changing dimension handling, which is a genuine complexity: what happens when a customer's region changes, and should historical facts reflect the old or the new value?

4. Rigidity. A new question requiring a dimension nobody modelled means changing the model and reprocessing.

Where it is not the right approach

• Machine learning feature engineering, which wants raw granular events, not aggregated facts.
• Exploratory analysis over data whose questions are not yet known.
• Semi-structured or rapidly-changing schemas, which do not fit a fixed dimensional model.
• Very high-volume event data where the fact table would be unmanageable and the questions are better served by direct querying with appropriate layout.

The modern position

Dimensional modelling for the curated business-facing layer; raw and granular data retained beneath it. Storage is cheap enough that discarding detail is unnecessary, so the star schema becomes a consumption model over retained detail rather than the only representation.

That combination gets the usability benefit without the fidelity loss — and it means a new question that the model does not support can be answered from the layer beneath rather than requiring a remodelling project.