Speed up with TI

  • Mar. 08, 2012

Introduction

Using TM1’s Server Explorer to perform day-to-day actions is very fast. Working with subsets, application entries, dimensions, views in the cube viewer, and so on. Yet, some actions can take up a lot of time if the amount of data is big. Some examples include:

  1. adding an attribute to a dimension with very many elements
  2. setting up an element security control cube

Let us now look at some TI ways to speed up these processes.

Attributes on a dimension with very many elements

The problem here is that the interface shows all attribute values for all elements. This can add up to a very big grid of cells if the dimension contains a lot of elements (level 0 or consolidated). Say, 5 attributes for 10000 elements: 50000 cells to show. What we see in this dialog screen is a (simple) interface on the associate }ElementAttributes_dimensionname cube. Hence, changing some values in that cube (manually, TI or via Excel/the web) will be much faster and efficient in such cases. Creating the alias can be done in the interface I was talking about, but obviously, you need to show all attributes values to get there. How about some simple TI code? To add an alias and fill in the alias value for an existing account: 

# Wim Gielis
# https://www.wimgielis.com


#####
# Speed up with TI code
# 03/08/12
#####


   vDim='FIN_GL_Account';
   vAttr='Second name';

   AttrInsert(vDim,'',vAttr,'A');
   AttrPutS('600001',vDim,'600000',vAttr);

This will add 600001 as an alias to 600000 in the dimension FIN_GL_Account. We could extend this approach to rename an element and delete the old name: 

#####
# Speed up with TI code
# 03/08/12
#####


   vDim='FIN_GL_Account';
   vAttr='Rename_Elements';

   AttrInsert(vDim,'',vAttr,'A');
   AttrPutS('600001',vDim,'600000',vAttr);
   SwapAliasWithPrincipalName(vDim,vAttr,0);
   AttrDelete(vDim,vAttr);

Here, we rename the account number 600000 in 600001 (stupid example, I know). This is very fast and does not need to pass by the slow interface of Edit Element Attributes.

Element security control cube

To create an element security control cube for a given dimension, the manual strategy is as follows:

  1. Right-click on the relevant dimension, changing a cell and saving
  2. Choose Security > Elements Security Assignments…
  3. Change a rights assignment for a non-admin security group
  4. Save the whole thing
  5. Restore the rights assignment you just modified
  6. OK your way out of the screen
  7. The control cube is now created, if needed press F5 to refresh the Server Explorer

The 2-dimensional control cube is created, its name is }ElementSecurity_dimensionname. This cube brings the }Groups dimension and the chosen dimension together in a cube. Fill in cube cells manually or use rules to make the security setup fully dynamic. The code below is NOT to be used: 

#####
# Speed up with TI code
# 03/08/12
#####


   vDim='FIN_GL_Account';
   CubeCreate('}ElementSecurity_'|vDim,vDim,'}Groups');

Rather, our TI process should use the builtin security functions: 

#####
# Speed up with TI code
# 03/08/12
#####


   vDim='FIN_GL_Account';

   i=0;
   vGroup='';
   While(Scan('ADMIN',Upper(Dimnm('}Groups',i+1)))>0);
      vGroup=Dimnm('}Groups',i+1);
      i=i+1;
   End;

   If(i>0);
      v=ElementSecurityGet(vDim,Dfrst(vDim),vGroup);
      ElementSecurityPut('ADMIN',vDim,Dfrst(vDim),vGroup);
      ElementSecurityPut(v,vDim,Dfrst(vDim),vGroup);
   EndIf;

I think this is the very first time I use the DFRST function in TM1. My strategy here is to:

  1. Find the first non-admin security group
  2. Retrieve the rights assignment for that security group on the first element of the dimension
  3. Change the rights to Admin (TM1 creates the ElementSecurity control cube)
  4. Restore the rights to the previous setting

The advantage is that we do not need to go to the interface of Elements Security Assignments. That can be very slow if you have a large number of dimension elements and/or a large number of security groups (for instance, a TM1 Contributor application is defined on that TM1 server).

On a related note: deleting such control cubes for security cannot be done manually in the Server Explorer. But you can run this small TI script: 

#####
# Speed up with TI code
# 03/08/12
#####


   vDim='FIN_GL_Account';
   CubeDestroy('}ElementSecurity_'|vDim);

Alternatively (well, is it really an alternative ?), you delete the relevant .cub file in the TM1 Database directory (if you have enough rights to do so) and restart the TM1 model (or wait until the next automated restart is done).

Conclusion

TI statements can really speed up our work in TM1 in several areas. I discussed 2 cases but I am sure you can come up with other cases too.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links