Unexpected behavior of consolidations

Post Reply
kaazimraza
Posts: 92
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

Post by kaazimraza » Fri Oct 02, 2020 8:04 am

Hi guys,

I have a multi level hierarchy in my management account dimension, as shown in Subset with hierarchy image.
1.2 Subset with hierarchy.png
Subset with hierarchy
1.2 Subset with hierarchy.png (17.08 KiB) Viewed 396 times
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.
1.1 CubeView data.png
CubeView with data
1.1 CubeView data.png (21.56 KiB) Viewed 396 times
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
Thanks,

Kaz

Adam
Posts: 19
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.3
Excel Version: Office 365 x32

Re: Unexpected behavior of consolidations

Post by Adam » 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.
Take care.
Adam

David Usherwood
Site Admin
Posts: 1425
Joined: Wed May 28, 2008 9:09 am

Re: Unexpected behavior of consolidations

Post by David Usherwood » Fri Oct 02, 2020 12:01 pm

Easily fixed - use weightings. This will also be faster. Adam is right that c level rules do not consolidate onwards.

MarenC
Posts: 126
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

Post by MarenC » Fri Oct 02, 2020 12:13 pm

Hi David,

How would you use weightings to solve this issue? I assume you mean 1 and -1 but what about the abs?

Maren

Adam
Posts: 19
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.3
Excel Version: Office 365 x32

Re: Unexpected behavior of consolidations

Post by Adam » Fri Oct 02, 2020 2:22 pm

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.
Take care.
Adam

MarenC
Posts: 126
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

Post by MarenC » Fri Oct 02, 2020 2:43 pm

Even more unusually only one element appears to have ABS applied to it!

Maren

Adam
Posts: 19
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.3
Excel Version: Office 365 x32

Re: Unexpected behavior of consolidations

Post by Adam » Fri Oct 02, 2020 2:49 pm

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.
Take care.
Adam

kaazimraza
Posts: 92
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

Post by kaazimraza » Mon Oct 05, 2020 4:16 am

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.
Thanks for the response.

I was hoping to avoid ConsolidateChildren - I will try my luck with weights first and see how I go.

Thanks
Kaz
Thanks,

Kaz

User avatar
Steve Rowe
Site Admin
Posts: 2117
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Unexpected behavior of consolidations

Post by Steve Rowe » Mon Oct 05, 2020 8:44 am

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
Technical Director
www.infocat.co.uk

Post Reply