A dimension table stores the descriptive, categorical context for the numeric measures in the fact table — the “who, what, where, when, and how” that gives the measures meaning. In a finance BI model, dimension tables represent entities like Account (the chart of accounts with its hierarchy), Entity (legal entities, business units, and their consolidation hierarchies), Time (fiscal and calendar periods with their rollup relationships), Cost Centre, Scenario (budget, forecast, actuals), and Currency. The dimension table’s rows are relatively few but wide — each dimension member gets one row, with many attribute columns describing it. The Account dimension may have 2,000 rows but 30 columns covering the account code, description, account type, reporting category, management reporting line, statutory classification, and multiple hierarchy levels.
How It Works
Dimension tables connect to fact tables through surrogate keys — integer primary keys generated by the ETL process, distinct from the natural business keys used in source systems. Surrogate keys serve two purposes: they insulate the fact table from changes in natural key formats in source systems, and they enable the slowly changing dimension (SCD) pattern that tracks historical attribute values. When an account is reclassified from one reporting category to another, the SCD mechanism creates a new surrogate key for the new version of the dimension record, leaving the old surrogate key — and the fact table rows that reference it — intact with the original classification.
Design Decisions and Trade-offs
The hierarchy design within a dimension table is the most consequential design decision for finance BI. Finance dimensions — especially Account and Entity — have complex, multi-level hierarchies that must be navigable in BI reports: drilling from a reporting line to a management account to a natural account, or from the group to a division to a legal entity. Two hierarchy storage patterns are common:
- Level-based hierarchy: Each hierarchy level is a separate column in the dimension table — Level1_Name, Level2_Name, Level3_Name, Level4_Name. Simple to query, directly compatible with most BI tools’ native hierarchy navigation. Inflexible when the number of hierarchy levels varies by branch.
- Parent-child hierarchy: Each dimension row stores its parent’s key — the hierarchy is traversed at query time through recursive joins or DAX/MDX path functions. Flexible for variable-depth hierarchies (ragged hierarchies are natural) but more complex to query and slower without materialisation.
Common Implementation Errors
The most common and damaging dimension table error in finance BI is failing to design the hierarchy structure before the ETL is built. When hierarchy levels are added to the dimension table incrementally — adding Level5 when the model was designed for four levels, adding a new management reporting node that doesn’t fit the existing structure — the hierarchy becomes inconsistent across historical periods and the BI reports produce different aggregation results depending on which hierarchy version is used. Hierarchy design must accommodate all foreseeable reporting requirements before the ETL is built, not be extended reactively as requirements emerge.
How Loop Wise Solutions Designs for This
We spend significant design time on dimension tables — specifically on the Account and Entity dimensions that carry finance reporting hierarchies. We map the full hierarchy structure (all levels, all reporting views required for management and statutory reporting) before any ETL code is written, and we design the SCD strategy for each dimension attribute group. The hierarchy design is reviewed with the finance team and approved before development begins, because adding a hierarchy level after the ETL is built requires a dimensional model rebuild that is materially more expensive than getting the hierarchy right at design time.
