The warehouse model, cubes flattened into a star you own

A cube is dimensions around cells. A star is dimensions around a fact. The translation is direct, three decisions make it clean, and at the end the consolidated number reconciles to the general ledger.

RuleLand level-0 cells keyed by member names, flatten each dimension export into a level table, and keep scenario and version as first-class keys on every row.

Neverland aggregated data. The hierarchy already knows how to roll up; a warehouse that stores parents double-counts on the first restatement and can never be reconciled.

◆ The star— the shape every EPM application flattens into, one fact, the dimensions it came with.

The fact holds one row per level-0 cell, amount, member name keys, nothing computed. Each dimension table comes straight from the metadata export, the parent-child file flattened into level columns so any BI tool rolls the hierarchy up without knowing Essbase exists. Aliases ride along as attribute columns; the keys stay member names, because names are what the exports carry and what ties every refresh to the last one. Consolidation applications add dim_movement and dim_view the same way, and every custom dimension follows the identical recipe.

◆ The three decisions, and the reconciliation— what makes this model hold up in month twelve, not just week one.

Decision	Why it holds up
Level-0 only	Parents are opinions the hierarchy computes; cells are facts. Store the cells, roll up through the level columns, and a hierarchy change is a dimension refresh, not a restatement of history.
Names as keys, aliases as attributes	Aliases get renamed by the business whenever it likes. Member names survive. Key on what survives, display what the business reads.
Scenario and version on every row	The classic wreck is a fact table where the forecast overwrote the actuals because neither was a key. Actual, budget, and every forecast version live side by side, and comparing them becomes a filter, not a rebuild.

Then the step that makes the model trustworthy instead of merely present: the consolidated result in the warehouse ties to the general ledger, entity by entity, period by period, before anyone reports from it. It is the same discipline as our payroll-to-ledger reconciliation, applied one level up the stack, and it is the difference between a copy of EPM and a source of truth beside it. The pipeline that feeds this star is the extraction pattern; the six stages around it are on EPM to the warehouse.

◆ Why this shape, and why it matters more now— thirty years of proof, and the reason AI on financial data needs it.

Nothing on this page is invented here. This is dimensional modeling, the Kimball star, the pattern data warehouses have run on for thirty years. Every BI tool is built around it, every analytics team can read it, and it has outlived every tool that promised to replace it. Choosing it means your model is understood by the next hire on day one and by the tool you migrate to in five years.

And there is a newer reason it matters, the one that decides the next decade. Language models are probabilistic, finance is deterministic, and the two only coexist safely when the AI computes nothing. An assistant answering questions on financial data is accurate exactly when the data underneath is structured, keyed, and reconciled, so the model retrieves and assembles instead of guessing. One grain, conformed dimensions, facts that tie to the ledger: that is deterministic ground. Our read-only AI analyst runs on precisely this star, and the reason it can be trusted in a close is this page, not the model. Unstructured exports and hand-refreshed workbooks give an AI nothing but room to hallucinate; a star gives it nowhere to.

◆ The rules, and where each one is enforced— the complete map from principle to artifact, so the discipline survives the people.

The rule	Where it is enforced	The artifact you own
Level-0 only	In the Export Data job definition itself, dynamic members excluded, so aggregated rows cannot enter the pipeline even by accident.	The job definitions, versioned in the runbook.
Names as keys, aliases as attributes	In the dimension build scripts, keys come from the member name column of the metadata export, aliases land as display columns.	The dimension load scripts, commented and owned.
Scenario and version on every row	In the fact table grain, both columns are part of the key, and a load test fails if a file arrives without them.	The schema definition and the load tests.
Reconcile before anyone reports	As a gate, the entity-by-entity tie to the general ledger runs after every load, and publishing waits for it to pass.	The reconciliation query and its pass log.
Build on jobs, never on screens	In the pipeline itself, every step is a named job, a command, or an API call, nothing scripts a browser.	The scheduled scripts and the runbook page for each.

This is what ownership means in practice: not a diagram on a slide, a set of artifacts where each rule lives in something your team holds, reads, and can change. When the auditor, the new hire, or the AI asks why a number is trustworthy, every answer is a file, not a person's memory.

Want this star running beside your ledger?

The facts landed level-0, the dimensions flattened from metadata, scenario and version done right, reconciled entity by entity, and you own every line.

Talk to us

Terms on this page

level-0 cell: The lowest stored intersection in the cube. The only thing the fact table stores.
level table: A dimension flattened from parent-child into columns, level one, level two, and so on. How BI tools roll up.
member name: The stable identifier. The key.
alias: The label the business reads. An attribute, never a key.
first-class key: A column in the grain. Scenario and version belong here, or the forecast eats the actuals.
the reconciliation: Warehouse consolidated result tied to the GL by entity and period. What turns a copy into a source of truth.