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!