TM1 Forum

I need some advise for an ti upload.

The cube to load data in has the following dimensions : employee number, employee role, year, periode(month) and a measure dimension ( one measure is e.g. Calls)
The raw data to load has the employee number, year, periode and the measure, but not the employee role.

I need a way during the upload to lookup the employee role. Normally I would add the employee role as a attribute to the employee number dimension and then during upload look this attribute up via the ATTRS function. However in this case the employee role can change from month to month. So I was thinking of an lookup cube, where I keep the employee role by employee number, year and month. But how can I reference this cube in the TI process and pull the role so to load data with this ?

Thanks for any help you can provide to my problem !

Are you creating the TI process in Performance Modeler or in Architect, and are you using the wizardy mapping stuff or are you writing it yourself?

I am using architect, I use the wizard to start and if needed do coding.

I am on version 10.1

manne wrote:I am using architect, I use the wizard to start and if needed do coding.

I am on version 10.1

I must admit I have never dabbled in the wizard so can't advise on how to do this using that.

However it should be quite simple, for your lookup cube there are 2 main ways you could build it:

• [1] Dims - Employee Number, Time, Measures (String) - Populate the employee role as string
  [2] Dims - Employee Number, Employee Role, Time, Measures (numeric) - Populate as a 1 or 0

If you go with method 1 then the lookup is a simple CellGetS against the employee number - job done.

If you go with method 2 you will need to loop through the employee roles until you find the relevant one that has a non zero.
Depending on how many roles you have, this could be very quick and simple.
If you have a lot of roles however I would recommend using a second TI process (executed in the prolog of your upload) that uses the lookup cube as a zero suppressed data source and creates subsets for each employee number in the "Employee Role" dimension.
Then your data tab of the upload process can do a simple SubsetGetElementName to retrieve the employee role for any given employee.
In the epilog of you upload process do a while loop that goes through and deletes the subsets you created earlier.


HTH


Edit - I should point out that one employees subset may have multiple roles for the different months, so you could create a subset for each employee and month or just use 1 subset and the do a loop through that in the upload data tab. The loop would be quick as I doubt 1 person would have more than a couple of job titles over a year or 2.

If you are using the wizard you can create a new entry for role on the "Variables" tab and put the code defining it into the box you get when you click the "formula" button. This will automatically put it in the generated code sections above where the variable is used in the auto-generated CellPutN line.

Thank you,

If I understand now correctly, I should use the cellgets function in the formular box to define / pull the role from the lookup cube ?

Appreciate the assistance

You could do this with just attributes too, you'll just need more of them, i.e Role - Oct 2013, Role - Nov 2013 and so on. Not really much different to using a lookup cube with the role held in it.

Thank you, I will go with the lookup cube as the role is been captured by week now and not only month

One more question to this, in the manual it says that if you use it in an IF sceanario you should prepare for errors differently.

Any advise on this ?

I am currently going the wizzard route