A dimension in Oracle EPM is a structural axis of the Essbase multidimensional database — the categories along which data is organised, aggregated, and retrieved. Every data value in an EPM application exists at the intersection of one member from each active dimension. In an Oracle PBCS planning application, the five standard dimensions are Account (the chart of accounts hierarchy), Entity (the legal entity or organisational unit hierarchy), Period (time — months, quarters, years), Scenario (Budget, Forecast, Actuals), and Version (Working, Final, Approved). Beyond these, up to thirteen additional custom dimensions may be defined — Cost Centre, Product, Geography, Project, and others determined by the model design. The total number of dimensions in an application, and the member count in each, directly determines the size of the Essbase database and the performance characteristics of calculations and queries against it.
How Dimensions Work
Dimensions contain members arranged in parent-child hierarchies. A parent member’s value is the aggregate of its children — unless a consolidation operator overrides this for specific members. Each member can carry a data type tag (numeric, text, date, smart list), a consolidation operator (+, -, *, /, %, ~, ^), and formula logic if it is a formula member. The database physically stores only the leaf-level (lowest-level) members’ data; parent values are calculated by aggregating children either at query time (ASO) or via explicit calculation (BSO).
Dimension type matters more than dimension name. Oracle recognises specific dimension types — Account, Period, Entity, Scenario, Version, Currency — and assigns specific default behaviours to them: the Account dimension type controls the variance reporting direction (expense accounts report unfavourable when actuals exceed budget; revenue accounts invert this). Assigning the wrong dimension type, or not specifying a type, forfeits the system’s automatic handling of these accounting conventions.
Configuration and Design Considerations
The most consequential dimension design decisions are member count and hierarchy depth. A dimension with 50,000 members and ten hierarchy levels will produce BSO blocks of very large size if it is designated as a dense dimension — a design error that manifests as slow calculation and excessive disk usage. Custom dimensions should be sparse by default unless there is a specific, analysed reason to make them dense. The Account dimension is conventionally dense because it contains the calculation formulas that drive the model’s behaviour; all other dimensions are typically sparse.
Alternate hierarchies — multiple parent paths for the same leaf member — are valid in EPM dimensions and are frequently used in the Entity dimension to support both management and statutory reporting views from the same member set. Each alternate parent adds an aggregation path that Essbase must calculate; applications with many alternate hierarchies on large dimensions experience slower consolidation runs.
What Goes Wrong in Practice
The most common dimension design error is placing attributes that should be custom dimensions as member properties instead, and then expecting those properties to drive calculation logic. Member properties (UDAs — User-Defined Attributes) are metadata tags; they do not create independent data storage. When a model needs to report expenses by both cost centre and product simultaneously, Product must be a dimension, not a UDA on the Account dimension. Attempting to use UDA-filtered calculation scripts to substitute for a Product dimension produces scripts that are correct in small models and unmanageably slow in production-scale ones.
How Loop Wise Solutions Handles This
Dimension design is the first analytical deliverable in every PBCS or FCCS implementation we undertake. Before any application is provisioned, we produce a dimension specification document — member count estimates, hierarchy depth, dense/sparse designation rationale, alternate hierarchy requirements, and the mapping from source system data to dimension member codes. This document is reviewed with both the technical and finance teams before build begins, because dimension restructuring after application provisioning is significantly more disruptive than getting the design right at the outset.
