The galaxy schema: several fact tables, one set of conformed dimensions

Also called: fact constellation, the Kimball bus architecture, conformed dimensions, multi-star schema

What a star schema becomes the moment you model a second business process — and the point at which the difficult questions stop being technical.

What it is

A galaxy schema is several fact tables sharing the same dimension tables. One customer table serving sales, returns and support; one product table serving all three; one calendar serving everything. Draw it and you get several stars with their points touching, which is where the name comes from.

A dimension used by more than one fact is conformed. That word means something precise and demanding: every process that uses it agrees on what it means, what its grain is, and what its attributes are called. A customer table that sales and support both read but interpret differently is not conformed — it is shared, which is a different and more dangerous thing.

Conformed dimensions are what let a question cross between processes. “Which customers bought the most and complained the most?” is answerable only if both facts point at the same customer table with the same key. Without that, the answer has to be assembled by hand, and two people assembling it get two answers.

How the tables are actually shaped

The shape itself

        DimDate    DimCustomer    DimProduct    DimStore
           |            |              |           |
   +-------+------------+--------------+-----------+
   |                    |                          |
FactSales          FactReturns          FactSupportTicket
(one order line)   (one returned line)  (one ticket)

Every fact keeps its own grain — one row of FactSales is a sales line, one row of FactSupportTicket is a ticket — and that is fine and expected. What must match is the dimensions, and the keys into them.

The bus matrix

The bus matrix is the one-page artefact that makes a galaxy manageable: processes down the side, dimensions across the top, a mark in every cell where that process uses that dimension.

                  Date  Customer  Product  Store  Employee
Sales               X       X         X       X
Returns             X       X         X       X
Support ticket      X       X                        X
Inventory           X                 X       X

• It shows at a glance which dimensions are load-bearing — the ones with marks in every row are the ones that must be got right first.
• It shows what is not shared, which is just as useful: a dimension used by one process does not need conforming and does not need the meeting.
• It is a planning tool, so it is worth keeping in the warehouse itself. What uses what is a fact about the design rather than about the source data, so it can be generated from the model rather than maintained by hand.

Conforming, in practice

Two dimensions are conformed if one is identical to the other, or if one is a strict subset of the other — the same attributes, the same meanings, a rollup rather than a rewrite. A monthly dimension conformed to a daily one is fine. A “customer” that means an account in one place and a person in another is not, and no amount of key management fixes it.

When it fits, and when it does not

A galaxy is where a warehouse naturally arrives. The question is whether the organisation is ready for what conforming requires of it.

Those two lists are Netune’s own judgement of this methodology, copied from the rule file it scores a real database with.

What it costs

The mistakes that are expensive

Sharing a dimension that was never conformed

This is the failure the whole model turns on. Two departments point at one customer table while meaning different things by “active”, and every cross-process report is now confidently wrong. It produces no error and no warning — just numbers that do not reconcile, argued about in meetings for months.

Conforming is an organisational act. Get the definition written down and agreed before the dimension is shared, not after the first argument.

Building all the stars at once

The bus matrix tempts people into treating the whole grid as one project. Kimball’s own advice is the opposite and it has held up: deliver one process end to end, conform its dimensions properly, then add the next process against those dimensions. Each star is usable the day it lands.

Forcing a shared dimension where the facts share nothing

If two processes genuinely have no customers or products in common, they are two stars that happen to live in one database, and inventing a conformed dimension over them adds meetings and no answers.

How it compares

Galaxy or Kimball?

Galaxy is Kimball, at the point where a second business process arrives. Everything true of a star — grain, additivity, surrogate keys, SCD types — is true here, with conforming added on top. If you have one fact table, you have a star, and there is nothing further to decide.

Galaxy or Inmon?

Both answer “how do several processes agree on one customer?”. Inmon answers it in a normalised core that nobody queries, with marts on top. A galaxy answers it directly in the dimensional layer. Inmon scales better across many source systems; a galaxy gets you reporting sooner.

Questions people ask