Hi guys,
I have a multi level hierarchy in my management account dimension, as shown in Subset with hierarchy image.
PNL00000Q should be a calculation of ABS( PNL000068 ) - ( PNL000067). This is working okay.
However the parents of PNL00000Q,PNL00000D and PNL00000H levels are not working correctly, both D and H levels are actually summarizing level 68 and level 67 instead of picking up value from level Q as shown in CubeView with data image.
All elements have weights set to 1, I have re-created PNL00000Q, PNL0000067 and PNL0000068 levels and tested the hierarchy by rolling up 67/68 and Q levels, nothing seems to be out of place there.
Any idea what is going on here and how to solve this?
Thanks
Kaz
Unexpected behavior of consolidations
-
- Posts: 95
- Joined: Mon Jun 25, 2012 6:58 am
- OLAP Product: TM1, SSAS, Power BI
- Version: 10.2.2
- Excel Version: 2016
Unexpected behavior of consolidations
Thanks,
Kaz
Kaz
-
- Posts: 106
- Joined: Wed Apr 03, 2019 12:10 am
- OLAP Product: IBM PA
- Version: 2.0.9.x
- Excel Version: Microsoft 365 x64
Re: Unexpected behavior of consolidations
Hello Kaz,
Setting a c-level rule, as you do for PNL00000Q, does not impact higher-lever consolidations, in this case PNL00000H or PNL00000D, as those also consolidate leaf-level elements. You’ll need to use ConsolidateChildren, but this gets messy.
Setting a c-level rule, as you do for PNL00000Q, does not impact higher-lever consolidations, in this case PNL00000H or PNL00000D, as those also consolidate leaf-level elements. You’ll need to use ConsolidateChildren, but this gets messy.
-
- Site Admin
- Posts: 1457
- Joined: Wed May 28, 2008 9:09 am
Re: Unexpected behavior of consolidations
Easily fixed - use weightings. This will also be faster. Adam is right that c level rules do not consolidate onwards.
-
- Regular Participant
- Posts: 359
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Unexpected behavior of consolidations
Hi David,
How would you use weightings to solve this issue? I assume you mean 1 and -1 but what about the abs?
Maren
How would you use weightings to solve this issue? I assume you mean 1 and -1 but what about the abs?
Maren
-
- Posts: 106
- Joined: Wed Apr 03, 2019 12:10 am
- OLAP Product: IBM PA
- Version: 2.0.9.x
- Excel Version: Microsoft 365 x64
Re: Unexpected behavior of consolidations
Although the premise around absolute values seems unusual, here's how I would go about this:
1. PNL0000067 set weight to 0.
2. PNL0000068 set weight to 0.
3. Create new element PNL0000067_ABS, place next to PNL0000067 in the hierarchy and set weight to 1.
4. Create new element PNL0000068_ABS, place next to PNL0000068 in the hierarchy and set weight to 1.
5. Add rule so PNL0000067_ABS = ABS(PNL0000067). PNL0000067 =feeds=> PNL0000067_ABS.
6. Add rule so PNL0000068_ABS = ABS(PNL0000068). PNL0000068 =feeds=> PNL0000068_ABS.
This approach will consolidate up.
1. PNL0000067 set weight to 0.
2. PNL0000068 set weight to 0.
3. Create new element PNL0000067_ABS, place next to PNL0000067 in the hierarchy and set weight to 1.
4. Create new element PNL0000068_ABS, place next to PNL0000068 in the hierarchy and set weight to 1.
5. Add rule so PNL0000067_ABS = ABS(PNL0000067). PNL0000067 =feeds=> PNL0000067_ABS.
6. Add rule so PNL0000068_ABS = ABS(PNL0000068). PNL0000068 =feeds=> PNL0000068_ABS.
This approach will consolidate up.
-
- Regular Participant
- Posts: 359
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Unexpected behavior of consolidations
Even more unusually only one element appears to have ABS applied to it!
Maren
Maren
-
- Posts: 106
- Joined: Wed Apr 03, 2019 12:10 am
- OLAP Product: IBM PA
- Version: 2.0.9.x
- Excel Version: Microsoft 365 x64
Re: Unexpected behavior of consolidations
I see what you mean Maren, then just:
1. PNL0000067 set weight to -1.
2. PNL0000068 set weight to 0.
3. Create new element PNL0000068_ABS set weight to 1 and place next to PNL0000068 in the hierarchy.
4. Add rule so PNL0000068_ABS = ABS(PNL0000068). PNL0000068 =feeds=> PNL0000068_ABS.
1. PNL0000067 set weight to -1.
2. PNL0000068 set weight to 0.
3. Create new element PNL0000068_ABS set weight to 1 and place next to PNL0000068 in the hierarchy.
4. Add rule so PNL0000068_ABS = ABS(PNL0000068). PNL0000068 =feeds=> PNL0000068_ABS.
-
- Posts: 95
- Joined: Mon Jun 25, 2012 6:58 am
- OLAP Product: TM1, SSAS, Power BI
- Version: 10.2.2
- Excel Version: 2016
Re: Unexpected behavior of consolidations
Thanks for the response.Adam wrote: ↑Fri Oct 02, 2020 11:39 am Hello Kaz,
Setting a c-level rule, as you do for PNL00000Q, does not impact higher-lever consolidations, in this case PNL00000H or PNL00000D, as those also consolidate leaf-level elements. You’ll need to use ConsolidateChildren, but this gets messy.
I was hoping to avoid ConsolidateChildren - I will try my luck with weights first and see how I go.
Thanks
Kaz
Thanks,
Kaz
Kaz
- Steve Rowe
- Site Admin
- Posts: 2424
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Unexpected behavior of consolidations
Not sure if you have this type of operation a lot or if it is only applying to one specific account. If you have to have this working in lots of places then...
Avoid ConsolidateChildren at all costs, you will regret it eventually.
First thought, can you ABS or flip sign on the values when you are writing to the cube? If the data load was a TI then this ought to be straight forward. The you can just have the weighting set to 1. TM1 is much easier to look after if you are not manipulating weights to keep the result correct.
If you can't do that then I would find space in my cube to put a rule that gets all the inputs into the correct sign convention and then allow natural weighting to roll the data-up. This ignores any concerns about data volume / RAM
HTH
Avoid ConsolidateChildren at all costs, you will regret it eventually.
First thought, can you ABS or flip sign on the values when you are writing to the cube? If the data load was a TI then this ought to be straight forward. The you can just have the weighting set to 1. TM1 is much easier to look after if you are not manipulating weights to keep the result correct.
If you can't do that then I would find space in my cube to put a rule that gets all the inputs into the correct sign convention and then allow natural weighting to roll the data-up. This ignores any concerns about data volume / RAM
HTH
Technical Director
www.infocat.co.uk
www.infocat.co.uk