Dimension usage

Example files with this article:
  • Dimension usage
  • Introduction

    In the larger TM1 applications (say, >15 cubes, >50 dimensions), you do not see immediately which dimensions are used in a cube, and which ones are not. Let us look at a small process to add an attribute to the }Dimensions dimension, having a 0 or a 1. 0 means that the dimension is not used in an application cube, while a 1 means that the dimension is used at least once in an application cube.

    A process-based approach

    Here are the steps for the TI process. First create a new process. As the Datasource Type, choose TM1 > Dimension Subset. In the Data Source Name, navigate towards the subset All on the dimension }Cubes. To be able to see this control dimension, in the menu bar you should take View > Display Control Objects. In the Variables tab of the process, call the (only) variable vCube and set Contents to Other.

    To mimize your work, right-click the file above and Save it. Here is the code in the Prolog tab:

    # Wim Gielis # http://www.wimgielis.com
    ##### # TI code to track the usage of the dimensions # in application cubes # 24/12/09 #####
    # Recreate the attribute to store the results AttrDelete('}Dimensions','Dimension usage'); AttrInsert('}Dimensions','','Dimension usage','N');

    Here is the code in the Data tab:

    # exclude control cubes
    If(Subst(vCube,1,1)@<>'}');
    
         # track the number of dimensions for this cube
         vNrOfDimensions=0;
    
         While(Long(Tabdim(vCube,vNrOfDimensions+1))>0);
              vNrOfDimensions=vNrOfDimensions+1;
         End;
    
         # loop over the dimensions in this cube
         iDim=1;
    
         While(iDim<=vNrOfDimensions);
    
              AttrPutN(1,'}Dimensions',Tabdim(vCube,iDim),'Dimension usage');
              iDim=iDim+1;
    
         End;
    
    EndIf;
    

    Extension

    By adding an AttrN function in front of the AttrPutN, you could count the number of times the dimension was used. With the information on this page, you could easily go out and delete unused dimensions. Either add a bit of code to this process, either delete the dimensions manually (in a Subset Editor, filter on attribute Dimension usage being 0).




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links