Creating a Flexible TM1 Allocation Model


TM1 Allocation Model

This past week I had to script several TI Processes to make allocations as flexible as possible. This was done so that the users could pick and choose what they wanted to allocate like it was a buffet at Golden Corral. While doing the usual creating of subsets on the fly in the Prolog tab so that my source data is dynamically created by their choices, I realized their need to pick a consolidated element (i.e., all Departments), but they would also like to exclude a Department or two from the initial selection of all Departments.

I came across the TI function called SubsetGetElementName and thought I found the answer to my problem. The syntax in the User Guide shows it as SubsetGetElementName(DimName, SubsetName, ElementIndex);. Well that looked simple enough and I then used DIMIX( DimName, ElementName) in place of the ElementIndex and thought all was good. I was wrong.

Apparently, the ElementIndex is the position of the element within the subset and not the element’s index in the dimension. So my solution was to add extra coding to the Prolog tab.

Here’s the basic gist of what I did:

  1. Use the function AttrInsert to create a numeric attribute to house the subset position number for each element added to the subset.
  2. Create a subset of only the N level elements from the C level element input in a parameter cube for the department selection (ie Total Dept). This requires looping through its children and inserting them into a subset.
  3. Next, While through the subset and use the SubsetGetElementName and AttrPutN to populate your numeric attribute with the position number of each element in the subset.
  4. You can now loop through your elements that house your exclusion elements and use the SubsetElementDelete to remove those elements from the subset, since you are able to retrieve the element’s position number now that it is a numeric attribute of the element itself.
  5. Finally, on the Epilog tab, I got rid of the numeric attribute that was housing the elements’ positions in the subset using the AttrDelete function, thus allowing for it to be dynamic and update the element position the next time the process is run.

Hope that helps you! If you have any questions about this article or TI Processes in general, do not hesitate to contact Lodestar Solutions at where our Analytics Coaches can assist you.