Using picklist/virtual dimension in intercube rule....

[Christian]

Hi all,

This is kind of a follow up to another question I posted last week. Maybe I'm missing something conceptually being newer to TM1 compared to CP, but I think this is something I/we are going to be asked to do with picklists.

Cube A is my Capex cube for purchases. My client wants as little dimensionality in this cube as possible. Naturally, I feed the purchase data to the Depreciation cube (cell called Budgeted Acquisition) where the depreciation schedule is applied to the new purchases. The Depreciation cube is Cube B.

These are the dimensions (and elements I used in the formula; parens to the right)

Dimensions Cube A:
Store-Department
Cost Center
Cap Purchases ('Total Purchases')
Asset Types
Month
Cap Measures ('Approved Cost')

Dimensions Cube B:
Store-Department
Cost Center
Asset Types
Month
Depr Measures ['Budgeted Acquisition']

The rule I wrote below works fine given the dimensions above.

# Rule #1 Budgeted Acquisition fed from Capital Acquisition
['Budgeted Acquisition' ] = N: DB('CAPITAL ACQUISITION', !Store_Department, !COST CENTER,
'Total Purchases', !ASSET TYPES, !MONTH, 'Approved Cost');

My question is; in order to condense the Capex input view would it be possible to replace the "Month" dimension in the Capex (A) cube, and instead, put "Start Month" as an element in the Capex measures dimension. That element would be a "picklist" with jan-dec from which the client can choose. Then (more importantly and this is where I'm stuck), can the element "Start Month" (which is the second element in this dimension that must be selected, 'Approved Cost' is the other) be mapped to the actual dimension "Month" in the depreciation cube?

In Cognos Planning, this would simply be done by assigning an "embedded d-list" (jan-dec) to the "Start Month" element in the "Cap Measures" dimension and then utilizing an "Accumulation D-Link" to map the dimensions between Capex and Depreciation. Then, you would write a conditional statement off of "Start Month" in the depreciation cube to determine when depreciation starts.

I hope I've outlined this OK. Trying to be as succinct as possible.

Thanks,
Christian

[Christian]

I just wanted to raise this question one more time.

I'm relatively new to the forum so I don't know if;

a) my question was too detailed
b) 9.5 and the picklist functionality is so new that people aren't sure; or
c) if there is question as to why I would want to use a picklist this way

Giving it one more shot

Thanks

[Jeroen Eynikel]

b.

I haven't had the time to work with the picklists yet so I am just basing this on what I have seen in demo's.

I assume it should be possible though by referencing the actual contents of the cell for which the picklist is used in your rule.

Your rule would then look like this

DB('CAPITAL ACQUISITION', !Store_Department, !COST CENTER,
'Total Purchases', !ASSET TYPES, DB REFERENCING THE CELL CONTROLLED BY THE PICKLIST, 'Approved Cost');

[Steve Rowe]

Hi Christian,

I think I understand where you are coming from though I've not used pick lists, from reading what you have said though it seems like they are just a string value that is an element from a dimension.

It sounds like what you want to do is to say something like "We spent 200k of capex on asset type B on May-2010" but instead of May-2010 being an element in a dimension you want it to be a string reference in Cap Measures dimension?

To some extent I can understanf the drive to reduce the dimensionaltiy but I don't think it's going to work in this case, irrespective of any issues with pick lists. The reasons IMO are.

1. What happens when you buy more of asset type B in Aug-2010? Since you have dropped the month dimension you have no space in the cube to put the new record. This might drive you to putting a "asset purchace transaction number" dimension into the cube so that you can have multiple purchases of the same asset type. Not a good idea and you would be back to the same number of dimensions in any case.
2. You couldn't easily refer to the start month in cube B and your rule would be an if statment covering all the periods in the second cube. Something like
['Budgeted Acquisition']=N: If ( !Month@=DB( blah blah 'Start Month'), DB (blah blah,'Approved Cost'), 0);

All in all I don't think it's the "right" way of doing it.

HTH, please ask if you have more Qs
Cheers

[alibaba]

Hello
I have a question that is similar to this one.
In CP we can use a cube dimension with tow dimensions for example :
no of lines : 1, 2, 3 , 4
and attribute dimension with the following elements : cost center, Account, value
and once the user has input detailed data, i want to accumulate this into a cube dimensioned by cost center and acount using accumulated links

In tm1, i can either define a cube using a dimenion no and the dimension attriubte (cost center (pick list), account (pick list) and value) and my question how would you do to accumulate data into another cube dimensioned with cost center and account

Thanks for your help.

regards

[rambo]

You can use an accumulation link in Performance Modeller. Very handy, as before it was hard to accumulate on the Item Dimension.