Transfer data from one cube to another with diff dimensions

[sachin]

We have a cube for cash-flow analysis that is working as desired. I am now tasked to produce another cube off this one, with different structure; the reason being it can then be used to feed our opex cube. And also for reporting purposes. We will be using BI products like Cognos for reporting.

Here are the dimensions CF (cash flow) cube structure -
1) Input - has about 30 elements, about half are string and the other half numeric. The numeric field consists of few months in current year and all months from next year. One of the string item has a picklist associated with it. Users have to choose asset category from this picklist (Equipments, Tools, Furniture)
2) Zones - Has about 50 items, all of which roll into 1 consolidation element
3) Category - Has 2 items (New or Old)
4) Version - Input, Overwrite and Final

Input dimension is in rows, Zones is in columns

I am required to produce another d-cube that has only 2 dimensions in it -
1) Months - This should have the same month elements that INPUT dimension in CF cube has (but only the months)
2) Asset - Will have these items - Equipments, Tools, Furniture

Asset dimension is in rows and Months is in columns

The 2nd cube (let's call it C2) should read the 'Final' numbers for both 'Old' & 'New' categories across all zones and split it by the asset category. Currently I am achieving this through an intermediate cube that has 'Months', 'Asset' and 'Zones' dimension in it. I use this in the intermediate cube rule -
['Furniture' ] = N:
IF ( DB('CF', 'Final', !Zones, 'New', 'Asset Type') @= 'Furniture', DB('CF', 'Final', !Zones, 'New', !Months) , 0 ) +
IF ( DB('CF', 'Final', !Zones, 'Old', 'Asset Type') @= 'Furniture' , DB('CF', 'Final', !Zones, 'Old', !Months) , 0 ) ;
### Repeat this for all other assets - Tools, Equipments

Then in the final cube I have written this rule -
[ ] = N:DB('IntermediateCube', 'Total', !Asset, !Months);

Using rules this is the only way (using an intermediate cube) I am able to get results into C2 Is there a way we can avoid using an intermediate cube and still be able to transfer the data real time

Thank you!

[ajain86]

sachin,

From what I understand you should be able to just move the rule you wrote in the intermediate cube to the final cube.

['Furniture' ] = N:
IF ( DB('CF', 'Final', !Zones, 'New', 'Asset Type') @= 'Furniture', DB('CF', 'Final', !Zones, 'New', !Months) , 0 ) +
IF ( DB('CF', 'Final', !Zones, 'Old', 'Asset Type') @= 'Furniture' , DB('CF', 'Final', !Zones, 'Old', !Months) , 0 ) ;

I would modify the rule for 1 change. Change !Zones to the top level consolidated member in the zones dimension.

[sachin]

Hello Ankur,

Came back from my vacation and seeing your reply now.

'Zones' dimension has different elements in it. Some may have asset type of 'Furniture', while others may have 'Equipment' or 'tools'. I can not use the consolidation element of this dimension as it will be wrong.

The only way I am able to get around is by having the 'zones' dimension in my Final cube or create an intermediate cube.

[ajain86]

Sachin,

When you select the zones dimension consolidation member and select 'Furniture' on asset type, it will only retrieve data from the zones associated with the furniture asset type. It will not pull data that is associated with other asset types.

Ankur