Choosing Between Physical and Attribute Dimensions in Oracle Cloud EPM Profitability and Cost Management (PCM)
A key consideration in any Oracle Cloud EPM Profitability and Cost Management (PCM) design is how best to make use of dimensionality in order to obtain the best mix between information detail and calculation performance. Oracle Cloud EPM PCM offers the ability to create applications with as many as seventeen (17) physical (or base) dimensions. Since this is inclusive of Rule and Balance, the starting point number of available physical dimensions is de-facto fifteen. The addition of the commonly used dimensions such as Period and Year, and the almost as common Scenario and Version, leaves one with eleven potential business dimensions. Add a couple more of the common dimensions such as Account, Legal Entity, and Cost Center/Department/Responsibility Center, and the number reduces even further, in this case, now nine. There may be a temptation to use all of the remaining available dimensions for what could be Product, Service, Project, Cost Pool, Business Unit, Affiliate, Currency, etc., but such a full expansion of dimensionality has potentially unfavorable consequences.
- Calculation performance is impacted by the cartesian product of all dimensions and their associated members, so every dimension can have an exponential impact on the size of the database.
- The use of more dimensions means more complex data management, both from the perspective of creation and maintenance, particularly for importing.
As such, the use of an alternate method to achieve objectives in reporting and analysis, as well as in allocation rules definitions, may be worth pursuing. The use of the Attribute Dimensions may be that alternative.
The benefits to so doing can be numerous. Attribute Dimensions are hierarchal in nature, and up to thirty such dimensions can be created in an application. This far exceeds the default number of available physical dimensions. Further, attributes are not just for reporting. They can be referenced in rules building and can be powerful filters on sources and destinations. In addition, these filtering selections allow the selection of parent members for inclusion, which is not available in the filtering of physical dimensions. This allows for efficient handling of exceptions, sometimes achieving in a single rule, which may require multiple rules with physical dimensions. Further, when attribute dimensions are accessed in reporting such as in SmartView, they look and behave similarly to physical dimensions. These features, along with the prospect of improved calculation time, make attributes an attractive option.
Despite these benefits, one still must consider various trade-offs with attributes. At the outset, a dimension member can only be associated with one attribute member from a given attribute dimension. Attribute dimensions are not applicable for allocation purposes if there is not a one-to-one relationship to a dimension member. For example, if a Customer is a dimension and Region is to be evaluated as either a dimension or an attribute, then a customer must only have one region associated with it for an attribute to be suitable If a customer can transact and be associated with multiple regions, then an attribute would not be appropriate, and the decision would trend toward a physical dimension.
Of further note are attributes’ behavior concerning historical reporting. Attributes do not support the concept of “slowly changing” values. An attribute change is instantaneous and will restate history upon recalculation, whereas dimension combinations can change over time, and retain the history. Additionally, there are aspects of rule building to consider, such as attributes’ lack of a “Same as Source” concept, the inability to be referenced in drivers or custom calculation formulas, and the lack of alternate hierarchies.
The table below highlights some notable comparisons between physical dimensions and attributes.
|Multiple Selections Available for Each Dimension||Y||Cartesian product of All Dimension Values. For example, Cost Center x Entity x Geography x Service||N||Only one value of each applicable Attribute can be selected for a given physical dimension member. Cost Center – One Entity, One Geography, One Service|
|Basis for Rule Selection - Source, Destination, Target||Y||Via Dimension Selection - Can select any level and different combinations. The rule will loop through all children||Y||Via Filter Selection. Multiple combinations of “And”/”Or” selections can be made.|
|Basis for Rule Selection - Driver||Y||Can select level zero member or sum by parent||N||Attribute filtering is not available on Driver.|
|Basis for Rule Selection - Formula||Y||Values of any dimension can be referenced in formulas||N||Attributes are not referenced in formulas|
|Available for Reporting||Y||All dimensions can be selected in SmartView.||Y||There may be a need for separate Attributes for both Source and Destination members.|
|Parent Level Filtering in Rule Selection||N||Only member “Name” and “=” or “<>” are available for filtering. Names must be level zero and are not validated against actual member names.||Y||Filters can reference either level-zero members or parents, and only valid members can be selected. In addition to “=” and “<>”, less than, greater than, and “In” for parents are available.|
|Prior Values Can be Retained when values are Changed||Y||Data-Driven – Can load new values for intersections by all other dimensions including Periods. History can be preserved.||N||The change of value is immediately reflected across all other dimensions. Recalculation restates prior values.|
|Alternate Hierarchy Available||Y||Can create shared members.||N||Only one hierarchy per attribute dimension.|
|New Dimensions can be added after the initial build||N||Requires a rebuild to add a new dimension and all rules must be adjusted.||Y||Attribute dimensions can be added incrementally – Requires reloading of dimensions to make the association.|
|Additional Calculation Time as Number of Dimensions increases||Y||Cube Size Grows as Cartesian Product of Dimensions Grows, thereby impacting calculation time.||N||Additional Attributes on a Dimension do not radically impact calculation time.|
In summary, attribute dimensions provide an alternative to physical dimensions, which can improve calculation performance. They provide flexibility, particularly with rule filtering. There are numerous trade-offs to consider, so each prospective dimension needs to be evaluated independently, particularly with regard to reporting requirements and historical tracking. Ultimately, the best answer will be a mixture of physical and attribute dimensions optimized based on these trade-offs.
For comments, questions, or suggestions for future topics, please reach out to us at email@example.com. Visit our blog regularly for new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management. Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.