The plan star, conformed and gated
The application answers about the current plan; the star answers about every plan it has ever seen. Three facts, five dimensions that conform across catalogs, and four asserts that run before anyone reads a number.
Note: Planning, Oracle's module where budgets and forecasts are built, answers questions about the current version of the plan. Keeping every version, every approval state, every month takes a small database of your own beside it. This page is that design.
The whole module is mapped on the Planning index.
◆ The star, drawn and drillable, three facts, five dimensions, every node opening its page.
Three facts, five dimensions, one rule: every dimension conforms. dim_entity and dim_period_years are the same spine the close and reconciliation stars use, so the plan for 1010-CL, the Chilean subsidiary, its consolidation, and its reconciliations all join on equal keys. fact_plan_level0 keeps every lane and version as loaded each night. fact_approval_daily is the history the application never holds. fact_workforce keeps the employee-job rows with their components.
◆ The four gates, and the ties, the asserts that run nightly, and the join across catalogs.
| Nightly gate | The assert |
|---|---|
| Plan totals | Level-0 rows sum to the reported lane totals: the forecast lane foots to 851.4M revenue, or the load stops. |
| Approvals foot | Unit states sum to the population: 41 + 12 + 6 + 1 = 60, every day. |
| Workforce foot | Active plus requisitions equals headcount, 4,806 + 74 = 4,880, and components sum to total compensation. |
| Snapshot immutability | Past forecast snapshots never change: April's rows in July are April's rows in December. The accuracy report depends on this one. |
The driver decomposition rides the same star: 6.1 + 2.4 + 0.9 explaining the 9.4M lives as computed columns, not a spreadsheet. Cross-catalog, the plan sits beside the close and reconciliation stories: one world, three catalogs, one console over all of it.
The problem: Every question about last quarter's plan requires an administrator, and half the answers no longer exist.
What we build: The plan star: level-0 facts, approval history, and workforce rows on the same entity and period keys as your close and reconciliation data, gated nightly.
What you get: Any version, any month, any entity, one query, forever. The application plans; the warehouse remembers.
- conformed dimension
- One definition, every fact, every catalog. The moat.
- fact_approval_daily
- The history the application never keeps.
- immutability
- Past snapshots never change. Accuracy depends on it.
- one world
- Plan, close, and reconciliation joined on equal keys.