Rules for multiple hierarchies

Post Reply
User avatar
Community Contributor
Posts: 289
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Rules for multiple hierarchies

Post by PavoGa » 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:

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',
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?
Cleveland, TN

Post Reply