TM1 Forum

I am trying to covert a Cognos Planning into TM1 9.5 and am trying to use picklists as a virtual dimension in the same was as i would use a formatted d-list item in Planning. And then aggregate / consolidate the totals for each of the picklist items from the source cube into a target cube wihich has the picklist items as an actual dimension.

In my source cube, i have employees and a region picklist to indicate which region they relate to and salary element on the measures dimension. In my target cube I have the same regions as a dimension and salaries. I'd like to consolidate the salaries for each of the employees from my source regions picklists and build a rule to transfer them to the region dimension in my target cube.

In Cognos Planning, I would simply use an "Accumulation D-Link" to map the virtual and actual dimensions in my source and target cubes. easy peasy.

Ive noticed that one or two of us were attempting something very similar when picklists were relatively new with limited success it would appear. Now that picklists have been around for a little longer has anybody come up with a solution ?

Help greatly appreciated as always.
Cheers.
Beast

Hi,

I suspect in TM1 you will need a consolidation cube first in the same dimensionality but including the new dimension/s that you have as "Picklist's".
You can then create a summary cube from that and remove the dimensions you don't need.

The rules based on this approach are fairly obvious and simple.

You could of course do this as one step by using a TI process but it wouldn't be dynamic !


Rgds
James

James Many thanks for your reply. I have managed to get it to work with the help of a consolidation cube, setting it up with the dimension used as picklist as a dimension in my target cube. I then had to set up a string attribute of each to then test picklist in source to string attribute of dimension in my target. Couldnt get it to work any other way so any further thoughts much appreciated. I must say it isnt as elegant or indeed as simple as using a formatted d-list and accumulation d-link as you canin Cognos Planning but I suppose that whole debate is for another day Cheers for help though. Beast

['Measure']=N:IF(DB('SourceCube',!Employee,!Version,'Region')@=ATTRS('Regions',!Regions,'Name'),DB('SourceCube',!Employee,!Version,'Measure'),0);

Souce Cube
Employees
Version
Measures (including Regions dimension picklist)

Consolidation Cube
Employees
Version
Measures
Regions