brovanture Oracle Cloud EPM Planning

Oracle Cloud EPM Planning – UDA vs Attribute Dimensions vs Alternate Hierarchies

Lydia Maksoud
Brovanture Consultant

We’ve all come across this question as part of the Oracle Cloud EPM Planning requirements and design:

We have some additional information for a base dimension member, but which is the best way to represent this in our Oracle Cloud EPM Planning system?

All the options are great, which makes it more difficult sometimes to decide the best approach. So, this blog will go through a few different example scenarios, which will hopefully help you when deciding whether a UDA, attribute dimension or alternate hierarchy fits best.

Firstly, let’s make sure we’re clear on the definitions of each option:

  • UDA (User Defined Attribute) – an attribute which is tagged as a member property, which can then be referenced in other areas of the system.
  • Attribute Dimension – a special type of dimension, which is tagged onto a base dimension, to provide additional information about each bottom level member for cross-reporting.
  • Alternate Hierarchy – an alternate hierarchy sits within the main dimension, consisting of subtotals for each attribute which then contain shared members from the main hierarchy.

The functionality matrix below details which Oracle Cloud EPM Planning functionality can be used with the different options:

UDA Attribute dim Alt. hierarchy
On Sparse Dimension Y Y Y
On Dense Dimension Y N Y
Impacts the database size N N Y – if stored parents
Can assign to upper-level members Y N Y
Assign multiple attributes Y Y Y
View combinations of attributes N Y N – messy, duplicate hierarchies to combine
Hierarchy subtotals (dynamic) N Y Y
Hierarchy subtotals (stored) N N Y
Reference in Reports Y (property) Y Y
Visible in Ad Hoc N Y – add attribute dim to grid manually Y – available in dimension
Reference in Forms Y Y Y
Reference in Dashboards Y Y Y
Auto assign (via metadata load) Y Y Y – requires 2 loads
Business Rules – FIX Y – @UDA Y – @ATTRIBUTE or @WITHATTR Y – @DESCENDANTS
Business Rules – IF Y – @ISUDA Y – @ATTRIBUTEVAL Y – @ISDESC
Business Rules – Use subtotal value N Y – @ATTRIBUTE Y – member name

 

Therefore, the key strengths and weaknesses can be summarised as below:

Strengths Weaknesses
UDA (User Defined Attribute) –        Can be assigned to any dimension, including Dense

–        Useful for calculation logic in business rules and member formulas

–        UDA attribute totals do not aggregate for reporting

–        Can’t combine UDAs without adding extras

Attribute Dimension –        Aggregates to provide dynamic subtotals of hierarchies

–        Simple to implement multiple attributes with cross-reporting ability

–        Can be referenced in forms, reports, dashboards and ad hoc reporting

–        Can only be used on Sparse dimensions

–        Subtotals cannot be stored

Alternate Hierarchy –        Can be built in any dimension, including Dense

–        Aggregates to provide subtotals of hierarchies, which can be stored or dynamic

–        Can be referenced in forms, reports, dashboards and ad hoc reporting

–        Numerous alternate hierarchies for different attributes aren’t user friendly and will increase database size

–        Cross-reporting would require duplicate hierarchies

 

And finally, here is an example scenario for each option:

  • UDA (User Defined Attribute) – Tagging a dense dimension to influence calculation logic. For example, adding a “Rev_Alloc” tag to indicate that those accounts should be allocated using the revenue allocation % driver
  • Attribute Dimension – Multiple attributes on a Sparse dimension which are required for cross-reporting. For example, Products with a bottle size and category, where cross-analysis is required to give different bottle sizes by the different categories.
  • Alternate Hierarchy – Aggregating attributes for a dense (or sparse) dimension, which do not require cross-analysis. For example, an alternate account rollup or a subtotal by region.

In summary, if cross tabular reports are required, then an attribute dimension is the only option, although it’s worth noting that an extra level in the main hierarchy is sometimes suitable. UDA’s work best when used in business rule logic and alternate hierarchies are ideal for subtotal rollups in a dense dimension.

I hope this helps as a cheat sheet when deciding between using a UDA, attribute dimension or an alternate hierarchy in future.

Until next time

Lydia

More post by Lydia can be found on her blogspot page HERE