TM1 - Feed data from one cube to another cube

[sfnicole]

Hi,

Cube1: Staff HeadCount
Data:
Year Month Scenario Account Cost Centre Business Unit Measures - Headcount
2010 Full Year QF2 No Account 40 No Business Unit 91
2010 Full Year QF2 No Account 40 25 2

Feeders:
['Full Year','No Account','Headcount']=>
DB('General Ledger', !Year, 'Full Year', !Scenario, 'No Currency', 'No business Unit', !Cost_Centre, 'No Account', 'Head Count');

Cube 2: General Ledger
['Full Year','No Currency','No Business Unit', 'No Account','Head Count']=N:
IF(ELISANC('Scenario', 'Rule_Scenarios',!Scenario) = 1,
DB('Staff HeadCount', !Year, 'Full Year', !Scenario, !GL_Accounts, !Cost Centre, !Business Units, 'Headcount')
,STET);

In Cube 2, it only display 91 instead of 93. It seems like the above rule fail to pull the data of Business Unit: 25 to 'No Business Unit'.

Are there anything wrong with my rule?

[Martin Ryan]

Yes, you've specified the destination business unit in the rule on the left side of your rule, but then said !Business Units on the right hand side. This means your rule only applies to the 'No Business Unit' element and will only look at that element in the source cube. Your rule should either be

Code: Select all

['Full Year','No Currency', 'No Account','Head Count']=N:
IF(ELISANC('Scenario', 'Rule_Scenarios',!Scenario) = 1,
DB('Staff HeadCount', !Year, 'Full Year', !Scenario, !GL_Accounts, !Cost Centre, !Business Units, 'Headcount')
,STET);

(note no 'No Business Unit' on the left side of the rule) or

Code: Select all

['Full Year','No Currency','No Business Unit', 'No Account','Head Count']=N:
IF(ELISANC('Scenario', 'Rule_Scenarios',!Scenario) = 1,
DB('Staff HeadCount', !Year, 'Full Year', !Scenario, !GL_Accounts, !Cost Centre, 'All business units', 'Headcount')
,STET);

Note 'All Business Units' instead of !Business Unit

HTH,
Martin

[sfnicole]

Hi Martin,
25 is under 'All Business Units' but my 'No business Unit' is a separate element. Fyi, my figures was park in both 25 and 'No Business Unit'.

If using 'All Business Units', my figure in 'No Business Unit' would not be picked up by the system.

Do you have any others workaround?

Thanks in advance.

Regards,
Nicole

[paulsimon]

Nicole

One option is to include No Business Unit under All Business Units, with a zero weight.

A better option would be to add a further consolidation

All Business Units and No Business Unit
All Business Units
No Business Unit

And then change the rule in the General Ledger so that it pulls in from 'All Business Units and No Business Unit'

Personally, if I look at a consolidation that says All Business Units, I would generally expect it to include every element in the dimension, not to exclude No Business Unit. I tend to avoid dimensions that don't join up to a single top level element, as this can make them much harder for users to understand. (Even if the dimension has alternate hierarchies you can work around this using zero weighting, eg All Business Units, All Business Unit by Location, All Business Units by Type (zero weight)).

Another option would be to create a sub consolidation All Business Units excl No Bus Unit

All Business Units
All Business Units excl No Bus Unit
No Business Unit

Regards


Paul Simon

[sfnicole]

Hi Paul,
If with zero weight, what does it means?

Cheers,
Nicole