Which cubes use a certain dimension?

Example files with this article:
  • Which cubes use a dimension
  • Introduction

    In TM1, you are not able to delete a dimension when this dimension is used in an application cube. As the object of a cube is quite static, you cannot add or remove dimensions to it. You should recreate the cube whenever its dimensions change afterwards.

    Manual work…?

    How can you know in which cubes a certain dimension is used without verifying manually? The code below will tell you. It will create and populate a new dimension with that information. Guidance on how to set up the process follows, or download the file above.

    TI is there

    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.

    Create a parameter in the Advanced > Parameters tab:

    ParameterTypeDefault ValuePrompt Question
    pDimensionStringleave this emptyWhich dimension should be looked for ?

    Here is the code in the Prolog tab:

    # Wim Gielis # http://www.wimgielis.com
    ##### # TI code to create a dimension containing elements # with cube names that use a certain dimension # 25/10/09 #####
    vOutputDimension='_Occurrences of dimension ' | pDimension | ' (' | TIMST(NOW, '\M \D, \Y - \H\p \i \s') | ')'; IF(DIMENSIONEXISTS(pDimension)=0); PROCESSQUIT; ENDIF; IF(DIMENSIONEXISTS(vOutputDimension)=0); DIMENSIONCREATE(vOutputDimension); ELSE; DIMENSIONDELETEALLELEMENTS(vOutputDimension); ENDIF; DIMENSIONSORTORDER(vOutputDimension,'ByName','Ascending','ByHierarchy','Ascending');

    Here is the code in the Metadata tab:

                   DIMENSIONELEMENTINSERT(vOutputDimension,'','Application cubes','C');
                      'Application cubes',vCube|'_'|NUMBERTOSTRING(i),1);
                   DIMENSIONELEMENTINSERT(vOutputDimension,'','Control cubes','C');
                      'Control cubes',vCube|'_'|NUMBERTOSTRING(i),1);

    Process logic

    The process will first check whether the chosen dimension (parameter) exists. Also, it will make a new output dimension ready. The data source tells you that the TI process will make a loop through all of the cubes. For each cube, a loop is executed through its dimensions using the TABDIM functie. When the dimension is found, it is added to the application cubes consolidation or the control cubes consolidation. As a suffix, you also have the rank of the dimension in that cube.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links