UDA vs Attribute Dimensions vs Alternate Hierarchies

We’ve all come across this question before as part of the 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 EPM Planning system??

All of the options are great, which makes it more difficult sometimes… so this blog will go through a few different example scenarios, which will hopefully help 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 below functionality matrix details which EPM functionality can be used with the different option:

 

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!


Comments

Post a Comment

Popular posts from this blog

Loading multi-period row data files using Data Management

Image
The ability to load multi-period data with the Period and/or Year in the rows is not particularly new (since release 16.10), however it is something that most people seem to forget is possible in Data Management. This really is something worth remembering as it eliminates the need to either split the data files into one file per month or try to pivot the month into the columns. In order to set this up, let’s firstly look at how to add the Period and Year columns to the import format. Add the Period and Year columns using the Add -> Source Period Row dropdown, and input: The Source Column name – e.g. PERIOD The Field Number (file column) – e.g. 3 Any required expressions for that field  As these are Period/Year dimensions, the mappings are not held in the usual Data Load Mappings area. Instead they are configured in the Period Mappings , under the Source Mapping tab. Select the Source System as File to add a new mapping calendar for loading the data ...
Read more

Executing Smart View Retrievals using VBA

Image
When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections. Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves. This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide .  System Requirements ...
Read more

Loading Actuals from Fusion ERP Cloud to PBCS

Image
If you’re looking to set up a data load of Actuals from ERP to PBCS and have heard this is an “Out of the box” integration, then good news! Not only is the OOTB connector included within PBCS Data Management but the integration is simple to setup and to schedule using EPM Automate. This blog will detail how to setup the ERP to PBCS connector in Data Management and some of the common errors found when running the data load. The first thing to know about this integration is that the data source is actually the ASO Essbase cube which sits underneath the Fusion ERP tables, holding the period Actuals values. This ERP Essbase cube is created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system. This cube must be created before the integration can be set up correctly and can be accessed directly via Smart View for reconciliations. Set up the Source System As this is an Oracle built connector, it’s as simple as providing a name for the source system ...
Read more