Executive takeaway
A normalized database is built to avoid duplicating data. A dimensional model is built to answer business questions quickly and consistently, every time someone asks. The measure of a dimensional model is not how elegant the schema looks. It is whether two different analysts get the same number when they ask the same question.
The mistake most organizations make
Most teams treat dimensional modeling as a denormalization exercise: take the transactional tables, flatten them, ship a wide table, call it done.
That produces a table. It does not produce a model. A dimensional model starts with a decision about grain, the exact statement of what one row represents, before a single column gets built. Skip that step and every downstream number becomes a guess about what got counted.
A simple example: order fulfillment
Undefined grain delivers:an "orders" table where nobody can say if a row is an order, a line item, or a shipment, so total revenue depends on who wrote the query.
Defined grain delivers: one fact row per order line item per fulfillment event, with a documented statement:"one row = one unit shipped."
Both tables can contain the same underlying data. Only one of them lets finance, ops, and product agree on what "total orders" means without a meeting.
- The undefined table answers different questions depending on who joins it.
- The grain-defined fact table answers the same question the same way every time.
- Every dimension (customer, product, warehouse, date) attaches cleanly once the grain is fixed.
Good dimensional modeling does not start with the tables that already exist. It starts with the business process being measured, then builds the fact and dimension tables that process requires.
Why this distinction matters to leaders
When dimensional modeling is treated as a technical formality, the organization inherits its side effects without ever deciding to accept them.
- Metrics drift apart across teams.Finance's "active customers" and product's "active customers" quietly diverge because nobody agreed on grain or a conformed dimension.
- History gets overwritten.Without slowly changing dimensions, a customer's address, tier, or sales rep changes today and erases what was true when last quarter's deal closed.
- Dashboards get rebuilt instead of reused. Every new report starts from raw tables because there is no shared, trusted dimensional layer to build on.
- AI and analytics initiatives inherit the same instability. A model trained on a fact table with shifting grain produces predictions nobody can audit.
The questions every leader should ask their data team
Not about table counts or schema diagrams. About grain, history, and trust.
What is the grain of this fact table?
Every fact table needs a one-sentence answer to "what does one row represent?" If the team can't state it in one sentence, the table doesn't have a grain yet - it has data.
Are dimensions conformed across the business?
A conformed dimension means "customer" or "date" means the exact same thing in every fact table it touches. Without that, every report is quietly answering a slightly different question.
Does this model preserve history, or overwrite it?
A Slowly Changing Dimension (Type 2) keeps a row for every version of an attribute over time, with effective dates. Without it, "what was true then" silently becomes "what is true now."
Is this a star schema, or does it need to be?
A star schema - one fact table surrounded by denormalized dimension tables - is built for query speed and analyst comprehension, not storage efficiency. Snowflaking dimensions trades both away.
What a well-built dimensional model looks like
The output is not a wide table. It is a fact and dimension layer that lets any analyst ask a business question and trust the answer without checking with the engineering team.
Every fact table has a one-sentence grain statement, documented and enforced.
Shared dimensions like customer, date, and product mean the same thing everywhere they're used.
Slowly Changing Dimension logic (typically Type 2) preserves what was true at the time, not just what's true now.
State that changes gradually, like customer status over time, is modeled as a cumulative table, not recomputed from scratch daily.
The Canonica approach
Every engagement follows the same principle. Define the grain first, then build only what that grain requires.
Discover
Identify the business process being measured and the questions leadership actually asks about it.
Define
Agree on the grain of each fact table and which dimensions need to be conformed across teams.
Model
Build fact and dimension tables backward from the grain, with SCD Type 2 wherever history matters.
Deliver
Hand analysts a star schema they can query directly, with documentation on what every row means.
The Canonica Principle
Dimensional modeling exists to make data trustworthy and fast, not to organize tables for their own sake.
The schema is a means. Two people getting the same answer to the same question is the point. Define the grain before you build anything.
Start a conversation →