Hi Nick
Even if IBM allowed a syntax that allowed a rule to apply across all hierarchies, you would still have a problem if the ratio is defined at the N level. Suppose you have defined a Liquidity Ratio as ['Liquidity Ratio'] = ['Current Assets'] \ ['Liabilities'] ;
In your scenario you then have two rules
['Liquidity Ratio'] = ['Account:Current Assets'] \ ['Account:Liabilities'] ;
['Liquidity Ratio'] = ['ReOrg:Current Assets'] \ ['ReOrg:Liabilities'] ;
Rules work by looking from the top of the Rules file down and as soon as something on the LHS Area Specification of the Rule matches what the user is requesting,, the rules engine evaluates the RHS of the rule and it stops looking any further.
That is why your example did not evaluate the ratio differently for the second rule. It was because the rules engine would never look at the second rule.
If Liquidity Ratio is an N Level then there is only one element shared across all hierarchies in the dimension. Therefore it cannot possibly have more than one value. It may look like the same calculation but fundamentally for this to be any use the N level Accounts under eg Current Assets in the default Account hierarchy would have to be different to the set of N Level Accounts under Current Assets in the ReOrg hierarchy. Therefore you are going to get two different values for Liquidity Ratio and it can only hold one value, and that value will be the first one that is encountered in the rules file.
There are only two ways that it can have more than one value. You would need to name Liquidity Ratio differently, eg call the second one Liquidity Ratio after ReOrg. However, I agree that this is not going to be very scalable if you want multiple re-orgs. - Having said that, how many do you need? I can see that on something like the cost centre or product dimension a company might try out several re-orgs but those dimensions are less likely to have ratios - I would have thought that on Account for the major ratios you would not be looking at more than a few at a time, eg different versions of global accounting standards.
The only other way for it to hold more than one value and for it to be more flexibility is to do as your friends suggested and define the ratio at consolidated ratio. Then you can have a ratio with the same name calculated differently in different hierarchies.
To ensure that the ratio shows up in a zero suppressed view, you can 'feed' it by making the ratio a consolidation of one or other of the elements used to calculate it, usually the numerator. In that way it will be calculated if there is a non-zero value in the numerator, but the fact that it is a consolidation of that element won't matter as you are overriding the consolidated value with a calculated one.
I would suggest that what TM1 needs as well as the ability to write rules that apply across all hierarchies, is a new type of element, eg type R which is a Ratio element that is base level but hierarchy specific.
However, as IBM are looking at doing something more fundamental with the rules engine I doubt we will get that soon. However, we might, as the rules language is staying. It is only the feeder side that they are looking at.
Regards
Paul Simon
------------------------------
Paul Simon
------------------------------
Original Message:
Sent: Fri February 07, 2020 05:41 PM
From: Nick Rueda
Subject: Rules in Alternate Hierarhies
Stuart
I apologize for not getting back sooner. I have been extremely busy, and I also wanted to test a couple options. I also wanted to confer with some of my colleagues.
I understand what you're a saying about the leaf element as being the target and of course you are right. I knew this to be the case.
If the entire rule is at leaf level it works and transcends alternate hierarchies.
['Leaf-calc'] = N: ['Leaf1']+['Leaf2'];
I saw a video somewhere on either the "Docs" or this community where it was illustrated how you can use alternate hierarchies to backup or as tool for reorganization. I have a customer where this type of functionality would be a perfect solution and if successful, I can see where it could be used in several places while reorganization is a reoccurring task.
In a real use case in a financial account dimension there can be several financial ratios (or Calculations), EBIDTA just being one example. Typically, these calculations are done using consolidations. While the target or left side of the rule is a leaf.
Example without an alternate Hierarchy, both these work.
['Leaf-Calc1'] = ['Consolidation1']-['Consolidation2'];
Or
['Leaf-Calc2'] = ['Consolidation1']/['Consolidaton2'];
If these calculations are in the base hierarchy of the Account dimension all if fine. But If I wanted to do a "Re-Org" and copy the account hierarchy to an alternate hierarchy and maybe change the consolidations slightly that is where we run into issues.
Meaning…
"Account:Account" -> Copies to -> "Account:ReOrg"
Where the above all members (including ruled members) exist in both hierarchies…but some leaf elements may rollup into different consolidations in the ReOrg hierarchy.
As soon as I do the copy the rules above do not function and I get "Ambiguous" error, even before any changes have been made in the ReOrg hiearchy.
So, I must qualify the rule.
For example:
['Leaf-Calc2'] = N:['Account:Consolidation1'] / ['Account:Consolidation2'];
The rule above works fine, for the primary Account hierarchy. If I qualify the rule for the alternate hierarchy as shown below.
['Leaf-Calc2']=N:['ReOrg:Consolidation1']/['ReOrg:Consolidation2'];
The rule will "save" without error and it will calculate in both hierarchies. However it only calculates correctly in primary hierarchy "Account:Account."
Meaning…
If in Account:Account the values for the consolidations are 1000 and 2 respectively the Leaf-Calc2 will return 500, but in Account:ReOrg if the values for consolidations 1200 and 3 respectively (as the result of ReOrg) the Leaf-Calc2 will still return 500. Which is obviously wrong in the context of the hierarchy it resides. The calculation should return 400. I do understand the issue, it is because the target is a leaf and leaf values transcend hierarchies. So, while not right I do understand why.
This means rules when target is leaf and the calculation refers to consolidations will not work correctly in alternate hierarchies.
I did try to make the target a consolidation.
For Example
['Account:Cons-Calc2'] = C: ['Account:Consolidation1']/['Account:Consolidation2'];
And
['ReOrg:Cons-Calc2'] = C: :['ReOrg:Consolidation1']/['ReOrg:Consolidation2'];
This does work and returns the correct value in both hierarchies. But this solution still requires qualifying each rule, for each hierarchy, in every cube where the dimension exists. This solution can't really be automated, because somebody must go edit the rules. Plus, this seems weird to use a consolidation to do a calculation I would have to change all the targets used in calculations to consolidations even though they are not consolidating anything.
Some people I have contacted suggested using roll-up instead of rules. That might work for my original EBITDA calculation but would not work where you have multiplication or division in the calculation. It was also mentioned that I might be able to do the calculations in PAW. But that doesn't seem like a viable answer either many of my users want to use PAX and these complex calculations should come directly in a cube view(Note for illustration I have simplified these calculations the real calculation can be very complex). These all seem like work arounds for something that should just work.
My conclusion (unless there is something I am missing) is that I don't think alternate hierarchies should be used to assist in Reorganization efforts unless it is a simple hierarchy (if a simply hierarchy I don't see the point) and there are no rules relying on consolidations, or the calculations must be changed consolidations and you live with manual edits to the rules.
Looking forward to additional responses.
------------------------------
Nick Rueda
Original Message:
Sent: Mon February 03, 2020 07:29 AM
From: STUART KING
Subject: Rules in Alternate Hierarhies
Hi NIck,
Leaf elements with the same name in the same dimension are the same element no matter which hierarchy they belong to. Leaf elements with the same name in multiple hierarchies of the same dimension also share security and attributes (e.g you cannot set security or attributes separately for the leaf element in each hierarchy).
Note that the same is not true for consolidated elements. A consolidated element with the same name in two or more hierarchies of the same dimension is a different element, may have different rules apply to it, and have different attributes and security.
If you write a rule that targets a leaf element then it should work no matter which hierarchy you are looking at.
I think we need to know if EBITDA is a leaf or consolidation?
If EBITDA is a consolidation then you must write a unique rule since it's not the same element as explained above. If it's a leaf element, as your rule implies, then I suspect the 'ambiguous' error is because you have an element with the same name in a different dimension. In this case your updated rule should work. I did a quick test in 2.0.9 and it seems to work fine for me. What if you set a unique alias (unique for all dimensions that contain an element name with the same name) for EBITDA and change the rule reference the alias instead of the actual element name?
------------------------------
Stuart King
IBM Planning Analytics Offering Manager
Original Message:
Sent: Thu January 30, 2020 06:48 PM
From: Nick Rueda
Subject: Rules in Alternate Hierarhies
Does anybody know how to create a rule that work across all hierarchies.
For Example:
['EBITDA'] = N: ['Income from Ops']+['Depr & Amort']+['Depr Expense COS'];
Where EBITDA is member and Account dimension. The Account dimension will have several "Alternate Hierarchies" that will be re-orgs of the Account dimension,or may roll up slightly different. But the EBITDA calculation will always be the same.
When I try to copy the EBITDA member to "Alternate Hierarchy" I get an error that states the member in the rule is ambiguous. So then I fully qualify the rule, to nullify the error as below.
['Account:EBITDA'] = N: ['Account:Income from Ops']+['Account:Depr & Amort']+['Account:Depr Expense'];
But now the rule only works on the base account. Otherwise, I will have to write a rule for every hierarchy and that just seems silly.
It seems like the original rule should transcend all hierarchies since it is not qualified. But it does NOT seem to work that way now. I feel like in past versions the unqualified rule did transcend all hierarchies but I am not sure.
Anybody have any ideas?
------------------------------
Nick Rueda
------------------------------
#PlanningAnalyticswithWatson