Deleting cubes and dimensions safely

  • Feb. 14, 2010

Introduction

I am probably not telling any news to you if I say that you cannot delete dimensions that are used in a cube. What if you want to delete a cube and its dimensions? Should you go out and look at every cube whether a particular dimension is being used or not? Well, you can try to delete the dimension (if you have sufficient rights to do so), and TM1 will come up with a message if the dimension is used elsewhere in a cube.

Automatisation

To automate this task, below is a small TI process to do the dirty work for you.

Create a parameter called pCube. It should be a String parameter, and will be used to offer the user a way to enter a cube name. That cube will be delete, as well as its dimensions. The code will not hang on deleting dimensions that are not allowed to be deleted. The process will continue in a smooth way. 

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


#####
# This code will delete a cube and its dimensions,
# as long as they are not used in one or more other cubes
# control cubes and dimensions will not be deleted
# USE THIS CODE WITH CAUTION
# 04/04/10
#####

IF(CUBEEXISTS(pCube)=1);

  IF(SUBST(pCube,1,1)@<>'}');

    # PART 1: track dimensions of the cube pCube to be deleted

    i=1;
    vDimensionsToDelete='';

    WHILE(LONG(TABDIM(pCube,i))>0);

      vDimension=TABDIM(pCube,i);

      vDimensionCanBeDeleted=1;

      IF(SUBST(vDimension,1,1)@='}');

        vDimensionCanBeDeleted=0;

      ELSE;

        c=1;
        WHILE(c<=DIMSIZ('}Cubes'));

          vCube=DIMNM('}Cubes',c);

          IF(pCube@<>vCube);

            IF(SUBST(vCube,1,1)@<>'}');

              j=1;
              WHILE(LONG(TABDIM(vCube,j))>0);

                vDimension2=TABDIM(vCube,j);

                IF(vDimension2@=vDimension);

                  vDimensionCanBeDeleted=0;

                ENDIF;

                j=j+1;

              END;

            ENDIF;

          ENDIF;

          c=c+1;

        END;

      ENDIF;

      IF(vDimensionCanBeDeleted=1);
        vDimensionsToDelete=vDimensionsToDelete | vDimension | '$';
      ENDIF;

      i=i+1;

    END;


    # PART 2: delete the dimensions marked for deletion
    CUBEDESTROY(pCube);

    WHILE(LONG(vDimensionsToDelete)>0);

      vDimension=SUBST(vDimensionsToDelete,1,SCAN('$',vDimensionsToDelete)-1);

      DIMENSIONDESTROY(vDimension);

      vDimensionsToDelete=DELET(vDimensionsToDelete,1,SCAN('$',vDimensionsToDelete));

    END;

  ENDIF;

ENDIF;

Important remarks

  • This code is powerful, yet be sure to know what you are doing and to understand the consequences!
  • Control cubes are never deleted, nor control dimensions.
  • You could adapt the process to only delete a cube when all of its dimensions are nowhere used.
  • It does not matter whether a dimension has attributes or not.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links