Sunday, November 20, 2016

Smart Lists (Pick Lists) in IBM TM1

I'm going to write about a fascinating topic in TM1, working with Hyperion we all know Smart Lists and Attribute dimensions, Smart List is a drop-down menu you assign to a member which you can use with calculations or even reporting in some cases. As for attributes they are mainly used for reporting, they still can bee very useful in calculations, yet they are best known when it comes to reporting.

I want to show the equivalent of Smart Lists and Attributes in TM1,which is really interesting, at least for me.

This is my application server with the standard dimensions:

I have two cubes (Assumptions and EMP)

In TM1 your assumptions are most likely going to be a separate cube designed solely for that purpose, unlike Hyperion Planning this case is almost non-existent, assumptions are usually stored in unique intersections within the main cube.

As shown above, my Assumptions cube has two dimensions Account and Grade. my data form/view holding my data looks like this:

Very simple example, I'm having the basic salary, general allowance, and housing allowance (as a percentage of general allowance).

Now my EMP cube:

I don' have a grade dimension here, instead I'll be using a member in Account dimension, and I'm going to assign a Smart List linked to Grade dimension (the interesting part about this post). In TM1 Smart Lists are called PickLists, and you define them using Attribute Editor as shown below:

What I'm doing here is assigning the values of Grade dimension (from another cube) to one member in another cube that does not have the grade dimension. and it looks like this:

Now based on the headcount and the grade, the total salary is getting calculated using the data entered in assumptions cube.

In my main DB function (Assumptions cube) and I'm nesting another DB(EMP cube) to get the value from Assumptions cube based on the grade Account used for that specific intersection in EMP cube.

Change the data and save and your accounts are updated automatically.

So in a nutshell, I managed to optimize the EMP cube by not including the Grade dimension, and instead used a Smart List (PickList).

Pretty cool if you ask me, that is it for today.

May the Cosmos Be With You