Rules for multiple hierarchies
Posted: Wed Nov 07, 2018 11:46 pm
I need to be able, via rule, retrieve the value of a consolidation(s) in an alternate hierarchy for given elements. The consolidations may change their components over time. The component reference is stored as an attribute for the base element. The "current" hierarchy for a given version is stored in an attribute on the version dimension. To retrieve the value from the component, I present this beast:
The two DB calls retrieve the current active hierarchy for CostCenters and Accounts. Then looks up the consolidation source element for both dimensions. The ugly part is the concatenation to provide a proper element address for cost center and account.
In this particular case, we may be able to populate the cube with a TI, but we have some other cubes depending on being able to do this with rules. It seems like concatenation would be highly undesirable in terms of cube performance. Am I right to be concerned? Is there a better idea?
Code: Select all
['Amount'] = N:DB('Forecast',
ATTRS('dimVersion', !dimVersion, 'Previous Version'),
DB('dimVersion', !dimVersion, 'hier.CostCenters') | ':' | ElementAttrS('dimCostCenters', DB('dimVersion', !dimVersion, 'hier.CostCenters'), !dimCostCenters, 'OrgConsolidation'),
DB('dimVersion', !dimVersion, 'hier.Accounts') | ':' | ElementAttrS('dimAccounts', DB('dimVersion', !dimVersion, 'hier.Accounts'), !dimAccounts, 'SpreadCalcElement'),
'2018 YTG 05',
'Effective');In this particular case, we may be able to populate the cube with a TI, but we have some other cubes depending on being able to do this with rules. It seems like concatenation would be highly undesirable in terms of cube performance. Am I right to be concerned? Is there a better idea?