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 a cube. As the object of a cube is quite static, you cannot add or remove dimensions to it in Architect/Perspectives. 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 # https://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