Cubes are question answering machines.
The goal when building cubes is to ensure they make it easy for us to answer our business questions about the data. Thus, we don't necessarily mimic the data structures of the databases, rather, we mimic the business processes the cubes are being built for. This Field Guide gives an overview of some key design principles to meet these goals.
The building blocks for the most common cubes
Most planning cubes share three structural dimensions, regardless of what business process they model:
VERSION Actuals, Budget, Forecast, Plan. How you compare alternative realities side by side.
TIME A single Time dimension is the common approach in modern PA. Alternate hierarchies for Years, Quarters, Months, Weeks, Days keep things clean and are widely used — though older models sometimes split these out.
MEASURES Amount, Quantity, Rate. The "what" being measured. Numerical data (and sometimes string) values live here.
Everything else is business-specific: the dimensions that make this cube about this process — Accounts, Cost Centers, Products, Employees, Customers. These are the dimensions to design carefully.
Once you can see these, the design decisions become obvious
Most cube-design judgment calls reduce to four underlying patterns. Each panel below contrasts the good shape with the bad shape. Train your eye to recognize them and you'll resolve most design questions on sight.
Is this thing a dimension, an attribute, or something else?
Why splitting is better than stuffing everything into one place
Having all the data in one cube may sound great, but it turns out it leads to a cumbersome model that is not intuitive to use. Having several cubes — with each cube fit properly to the data and the business purpose — makes the model much more intuitive to use.
One edge case worth knowing: when data almost shares the same dimensions — but one dimension simply doesn't apply to part of the data — a placeholder N/A element can sometimes avoid a full cube split. Here's how to decide.
When related elements belong together
A single dimension element can roll up through multiple independent parent paths simultaneously. This is the right tool when the same leaf data needs to be summarized through different lenses without adding extraneous dimensions to the cube.
For example:
Start here when making an FP&A model
A typical FP&A model has many data sources feeding in from source systems, a central Finance cube at the hub, and a set of supporting planning cubes that each own one business process and write their results back to Finance is a classic pattern.
Reports, dashboards, and input templates can be built off the Finance cube or directly off any supporting cube — whichever holds the right level of detail for the task.
The diagram below shows a representative pattern. For smaller entities, the diagram below may encapsulate the entire model. For larger enterprises, or more complex modeling, each "cube" listed below may be a full set of cubes and processes.
When more information is needed, connect summarized data to details
The Finance cube summarizes at the GL account level — one cell per Account × Department × Entity × Month. Perfect for reporting and analysis, but a single summarized cell is rarely the whole story. Behind every actual amount there are transactions; behind every forecast amount there are line items a planner created and named. These two ideas anchor most FP&A models, and each gets its own cube. These details are not always needed for reporting and analysis, so housing them separately makes the model more streamlined and easy to use.
From one number in the Finance cube to the detail behind it
Drill-back is the path from a summarized Finance cube cell down to the rows that compose it — the invoices behind an expense total, the employees behind a salary line, the planner entries behind a forecast amount. It's the answer to "where did this number come from?" and it's what separates a credible FP&A model from a black box. The good news: if your model is built with the patterns shown throughout this Field Guide, drill-back is simply configuration, not new engineering.
The detail or line item cube should use the same Account, Entity, Time, and Version dimensions as Finance. Shared dimensions mean drilling is automatic — PA can map a Finance cell to its detail rows without translation logic.
Where drill-back is in scope, detail totals should tie back to Finance. For actuals, a TI validation step comparing Finance totals to the source-system trial balance is a good safeguard. For forecast, where a Line Item cube feeds Finance via rule, that rule is generally the single source of truth — though some items (forecast BS balances, certain KPIs) are often input directly to Finance and aren't expected to reconcile back to a detail cube.
In PA, drill-back is configured with Drill Assignment Rules and a Drill Process on the Finance cube. The rule directs a cell to a TI that selects the matching rows from the detail or line item cube — based on the cell's coordinates.
It's far easier to design shared dimensions and a reconciling detail cube from day one than to retrofit them after the model is in production. If drill-back is a stated requirement, architect for it directly, not as an afterthought.
Cell annotations, string measures, and separate comment cubes each have their place
There is no single right approach to commentary in PA — the best choice depends on data volume, where comments need to appear in reports, and how structured the commentary is. The three patterns below cover the full spectrum from a quick note on one cell to an enterprise-scale comment store.
