Unexpected behavior of consolidations

[kaazimraza]

Hi guys,

I have a multi level hierarchy in my management account dimension, as shown in Subset with hierarchy image.

Subset with hierarchy

1.2 Subset with hierarchy.png (17.08 KiB) Viewed 3193 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.

CubeView with data

1.1 CubeView data.png (21.56 KiB) Viewed 3193 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

[Adam]

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.

[David Usherwood]

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

[MarenC]

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]

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.

[MarenC]

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

Maren

[Adam]

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.

[kaazimraza]

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

[Steve Rowe]

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