Rule/Feeder using attribute not calculating/working

[ScoobyM]

Hi all,

I've been trying to add another rule to a cube that uses an attribute in the calculation but I just cannot get it to work. I've gone through the IBM reference documentation and searched user forums but to no avail and wondered if any of you lovely people could see what I have missed or gotten wrong.

The calculation pulls data from within the cube, but gets the attribute from the attribute cube to map to the right place.

It is the reporting P&L cube (RPT Monthly Profit and Loss) which contains seven dimensions (Budget Type, Cost Centre, Currency, Profit and Loss, Versions, Department and Months).

For some cost centres we have a pre-opening cost centre attached to it (in the 'Pre-Opening CC' text attribute) - both exist in the cost centre dimension as elements. It is the 'Base' (Budget Type) EBITDA (Profit and Loss) for the pre-opening cost centre that I am trying to allocate to a particular nominal (Profit and Loss) in the main cost centre (using the 'CC Name' Alias) in 'TM1 Re-Allocations' (Budget Type). I only want the rule to apply to cost centres that have a pre-opening cost centre attached to it. Hope that makes sense!

This is the rule I created:

[ 'TM1 Re-Allocations', 'COMMON Profit and Loss':'7020 - TRO - NONE' ] = N: IF ( DB ( '}ElementAttributes_}COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @= '', STET, DB ( 'RPT Monthly Profit and Loss', 'Base', DB ( '}ElementAttributes_}COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC'), !COMMON Currency, 'EBITDA', !COMMON Versions, !SAGE Department, !TIME Months ) ) ;

...and this is the Feeder:

[ 'Base', 'COMMON Profit and Loss':'EBITDA' ] => IF ( DB ( '}ElementAttributes_}COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @= '', STET, DB ( 'RPT Monthly Profit and Loss', 'TM1 Re-Allocations', DB('}ElementAttributes_}COMMON Cost Centre', !COMMON Cost Centre, 'CC Name'), !COMMON Currency, '7020 - TRO - NONE', !COMMON Versions, !SAGE Department, !TIME Months ) ;

My other rules are all working as expected, some of which are very similar in nature to this one, just without the attribute look up.
Can anyone see where I am going wrong?

Martin.

[gtonkin]

The right side looks to be the problem at a very quick glance.
I would generally use the DB reference and put any IF statements within the DB to resolve the Cube name when valid and blank when not valid, so as not to feed.

Something like:

Code: Select all

[ 'Base', 'COMMON Profit and Loss':'EBITDA' ] => 
DB(
IF ( DB ('}ElementAttributes_}COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @= '', '',  'RPT Monthly Profit and Loss'),
'TM1 Re-Allocations', DB('}ElementAttributes_}COMMON Cost Centre', !COMMON Cost Centre, 'CC Name'), !COMMON Currency, '7020 - TRO - NONE', !COMMON Versions, !SAGE Department, !TIME Months
); 

Spaced it out for clarity.

In this case however I would probably drop the IF statement altogether as a blank attribute would not feed to anything anyway and the overhead of the IF may impact performance.

[ScoobyM]

Thanks gtonkin.

Started with your suggestion and managed to get it working...sort of.
It now allocates to a new element called 'Pre-opening Re-Allocations' that adds into the P&L hierarchy.
However, I now have a new issue with it in that, for a small selection of cells, it doesn't add up through the natural hierarchy of the 'COMMON Budget Type' dimension.

This is the rule as it stands now:

Code: Select all

[ 'TM1 Re-Allocations', 'COMMON Profit and Loss':'Pre-opening Re-Allocations' ] = 
 DB ( IF ( ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @<> '', 'RPT Monthly Profit and Loss', '' ),
  'Base', ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ), !COMMON Currency, 'EBITDA', !COMMON Versions,
   !SAGE Department, !TIME Months ) ;

And this is the feeder that works 99.99% of the time:

Code: Select all

[ 'COMMON Cost Centre': { 'Total Pre-opening', 'Total Central' } ] => 
 DB ( IF ( ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @<> '', 'RPT Monthly Profit and Loss', '' ),
  !COMMON Budget Type, !COMMON Cost Centre, !COMMON Currency, 'Pre-opening Re-Allocations', !COMMON Versions, 
  !SAGE Department, !TIME Months ) ;

This is a link to a screenshot of what isn't working (sorry, I don't know how to embed an image):

https://drive.google.com/file/d/1Y4DfMx ... sp=sharing

In the rows are three dimensions - 3 months (that you can't see off to the left), 2 P&L lines (EBITDA and the new 'Pre-opening Re-Allocations' line) and the 'COMMON Budget Type' dimension. In the columns are the live CC and it's pre-opening CC.
In the bottom month, it's working as expected. The middle month doesn't have any pre-opening costs. The top month is where I have the problem. It pulls the data from the Pre-opening CC per the rule correctly, but it doesn't add up to the Total in the hierarchy of the 'COMMON Budget Type' dimension.


This is only an issue on three Cost Centres out of maybe a hundred, and only for some months - looks to be months that do not have a value in EBITDA for the live CC.

The 'COMMON Cost Centre' dimension has CCs that are separated into Live CCs, Pre-opening CCs and Central CCs. They do not add into each other.

[MarenC]

Hi,

2 questions,

1) Any reason you removed the =N: from your Rule, you now have = instead?
2) If EBITDA is zero and your rule references EBITDA, why wouldn't you expect it to be zero?

Maren

[ScoobyM]

.

[ScoobyM]

Hi Maren,

The rule takes the 'EBITDA' from the pre-opening cost centre and applies that value in 'Pre-opening Re-Allocations' in the live cost centre.
I have amended the feeder back to the opposite of the rule, so 'Total Pre-opening' (which contains all pre-opening CCs as children), 'EBITDA' and 'Base' are on the left hand side.

I have added the N: back in (I knew I shouldn't have taken it out in the first place really) but am struggling with the feeder - which is what I think is at fault.
I also added a 'Live CC' attribute which is the opposite of the 'Pre-opening CC'. By that I mean the live CC has the corresponding pre-opening CC in the 'Pre-opening CC' text attribute, and the pre-opening CC has the corresponding live CC in the 'Live CC' text attribute.

Current rule is:

Code: Select all

[ 'TM1 Re-Allocations', 'COMMON Profit and Loss':'Pre-opening Re-Allocations' ] = 
	DB ( IF ( ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @<> '', 'RPT Monthly Profit and Loss', '' ), 
	'Base', ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ), !COMMON Currency, 'EBITDA', 
	!COMMON Versions, !SAGE Department, !TIME Months ) ;

Current feeder is:

Code: Select all

[ 'COMMON Cost Centre':'Total Pre-opening', 'EBITDA', 'Base' ] => 
	DB ( IF ( ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @<> '', 'RPT Monthly Profit and Loss', '' ),
 	!COMMON Budget Type, ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Live CC' ), !COMMON Currency,
 	!COMMON Profit and Loss, !COMMON Versions, !SAGE Department, !TIME Months ) ;

Now it calculates up the natural hierarchies of 'COMMON Currency', 'COMMON Versions', 'SAGE Department' and 'TIME Months' but not 'COMMON Budget Type', 'COMMON Profit and Loss' or 'COMMON Cost Centre' in the Live CCs (pre-opening CCs are fine as they don't have any value in Pre-opening Re-Allocations).

Martin.

[Steve Rowe]

Are you sure that Live CC is populated everywhere that the Pre-Opening CC is ?
Does the CC consolidation 'Total Pre-opening' contain all the CC the rule applies too?

Don't forget if you change the consolidation or the attribute values you'll need to recompile the feeders to get a true picture and make sure persistent feeders aren't getting in the way.

Unless the CC dimension is very very large having the C level on the left and the check for a blank attribute value on the right of the feeder feels a bit like too much of a "double-check" that might be getting in the way.

Also your rule isn't showing the N:
Also I wouldn't use the If check around the cube name in the rule, just set the cell to zero. The If around the cube name is a "trick" employed to break the feeder statements as we have limited methods availalbe to write conditional feeders.

Code: Select all

[ 'TM1 Re-Allocations', 'COMMON Profit and Loss':'Pre-opening Re-Allocations' ] = N:
	IF ( ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ) @<> '',0 ,
	DB( 'RPT Monthly Profit and Loss', 
	'Base', ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ), !COMMON Currency, 'EBITDA', 
	!COMMON Versions, !SAGE Department, !TIME Months )) ;

[ScoobyM]

Hi Steve,

Every Live CC that has a Pre-opening CC is populated and vice versa. So if Live CC '01' has a pre-opening CC 'AA', in the dimension attributes, '01' has 'AA' in the pre-opening CC text attribute and pre-opening CC 'AA' has '01' in the Live CC text attribute. I have used the CC Name Alias in the two text attributes, rather than the element name, but that shouldn't make a difference.

'Total Pre-opening' contains all pre-opening CCs as children.
'Total Group' contains all the live CCs as children (eventually) through a brand and operating/not operating hierarchy.
'Total Central' includes all live Central CCs (these still use the live CC text attribute).
The rule should apply to the Live and Central CCs that have a pre-opening CC in the text attribute.

I have run 'CubeProcessFeeders' on the cube numerous times (after each change of rule or attributes).

The CC dimension only contains 370 elements and that includes all the hierarchy so it's not large in my view.

I added the N: back in (I thought I already had tbh) and changed the rule to remove the "if around the cube name trick" but it now doesn't consolidate naturally on any of the dimensions (which is the same problem I have had before when adding the N:). The rule populates the correct cell(s), but that's it.

Martin.

[Steve Rowe]

So either you still have a feeder issue or you have another C level rule that is interfering with the consolidations.

Suggest you do a trace feeder from one of the cells on the left of the feeder and make sure that what is happening is what you think.

If the LHS of your feeder is ruled then it will need to be fed properly too.

[ScoobyM]

Hi all.

Managed to resolve it.
Thanks for all your help.

Took out the conditionals altogether from both rule and feeder and let the blanks in the attributes do their thing - as suggested by gtonkin.
It also turned out I WAS over-feeding (thanks Steve Rowe) so stripped it back to the specific elements for 'COMMON Budget Type' and 'COMMON Profit and Loss'.

This is the working rule:

Code: Select all

[ 'TM1 Re-Allocations', 'COMMON Profit and Loss':'Pre-opening Re-Allocations' ] = 
	N: DB ( 'RPT Monthly Profit and Loss', 'Base', ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Pre-opening CC' ),
	!COMMON Currency, 'EBITDA', !COMMON Versions, !SAGE Department, !TIME Months ) ;

...and the working feeder:

Code: Select all

[ 'COMMON Cost Centre':'Total Pre-opening' ] => DB ( 'RPT Monthly Profit and Loss', 'TM1 Re-Allocations',
	ATTRS ( 'COMMON Cost Centre', !COMMON Cost Centre, 'Live CC' ), !COMMON Currency, 'Pre-opening Re-Allocations',
	!COMMON Versions, !SAGE Department, !TIME Months ) ;

Works as expected and consolidates up all hierarchies correctly.

Thanks again everyone.