This document is intended to provide IBM Cognos TM1 administrators with a technique to reduce memory consumption by quickly identifying all unused dimensions in an IBM Cognos TM1 server. The administrator can then decide if these unused dimensions can safely be deleted from the IBM Cognos TM1 server. The TurboIntegrator process demonstrated in this document scans the IBM Cognos TM1 server and creates an output file in a .csv format. This file will contain two columns: the first one listing the dimension names and the second column showing the corresponding cube that the dimension is used in. Any dimensions that are not used in a cube will display ‘***’ in the second column. This can help IBM Cognos TM1 administrators quickly understand which dimensions are not being used in an IBM Cognos TM1 server and are therefore candidates for deletion.
IBM Cognos TM1 10.1 and prior.
Exclusions and Exceptions
No known exclusions.
DimensionList.csv file as an output using the TurboIntegrator process
The output file, DimensionList.csv, will be generated by the TurboIntegrator (TI) process below. This output file will list all dimensions and their associated cube dependencies for a specific IBM Cognos TM1 server.
Contents of DimensionList.csv file
The two columns of the output file are, respectively:
- Dimension Name
- Dependent Cube Name
The first column contains the dimension name and the second column contains the cube name that uses the dimension. If the dimension is unused it lists 3 asterisks (***) in the second column, where the dependent cube name would be listed.
Creating the TurboIntegrator process
Follow the procedure described below to create the TurboIntegrator process.
- In IBM Cognos TM1 Architect expand the IBM Cognos TM1 server you will be working with by clicking on the + sign next to the IBM Cognos TM1 server name. Click on the View tab and select Display Control Object from the drop-down menu.
- In the Server Explorer, right-click on Processes and choose Create New Process.
Figure 1 – The Server Explorer window with the IBM Cognos TM1 server 'Planning Sample' expanded and the 'Processes' option highlighted
- In the Data Source tab of the TurboIntegrator Process Editor, change the default Datasource Type to IBM Cognos TM1. Select Dimension Subset from the drop down. Click on the Browse button and select the Server_Name :}Dimension dimension on the left side and double click on All. Click on Preview at the bottom to preview the source and if the preview section returns a limited selection of the dimension names in the selected IBM Cognos TM1 server then proceed to Step 4 below.
Figure 2 - The TM1 Turbo Integrator Data Source tab default display
- Click on the Variables tab and overwrite the default values in the
first variable line with Variable Name as ‘varDim’, Variable
Type as ‘String’, and Content as ‘Other’.
If you have chosen to modify an existing TurboIntegrator process instead of creating a new one, then click on the New Variable button on the right side of the screen and a new variable line will be inserted below any existing variable lines. Modify the new variable line to the specifications listed above and proceed to Step 5.
- Copy and paste the following code into the Advanced > Prolog tab below the line
#****End: Generated Statements****.
# Locate the folder where file should be created. exportFolder='C:\'; exportFile=exportFolder | 'DimensionList.csv'; # The }Cubes control dimension contains the list of cubes on the server. listOfCubes='}Cubes'; numberOfCubes=dimsiz(listOfCubes); count=0; taken=0; if(taken=0); asciioutput(exportFile, varDim, '***'); endif;
- Copy and paste the following code into the Data tab below the line
#****End: Generated Statements****.
# In case this is the first row, write the headers of the columns. if(count=0); asciioutput(exportFile, 'Dimension', 'Cube'); count=1; endif; # this flag indicates whether dimension is found in the cube or not taken=0; # If the dimension is a control item # skip the item, else continue if(subst(varDim, 1, 1)@='}', ItemSkip, 0); # go through the list of cubes i=0; while(i<numberOfCubes); i=i+1; varCube=dimnm(listOfCubes, i); if(subst(varCube, 1, 1)@<>'}'); j=0; # go through the dimensions in the current cube (assuming that there are not # more than 40 non-control dimensions in the cube.) while(j<40); j=j+1; cDim=tabDim(varCube, j); if(cDim@=varDim); taken=1; asciioutput(exportFile, varDim, varCube); endif; end; endif; end; # If the ‘taken’ flag is still zero this means that the dimension is not used # anywhere so export the name along with the *** as the dependent Cube Name. if(taken=0); asciioutput(exportFile, varDim, '***'); endif;
- Name and save the TurboIntegrator process. Click the Run arrow in the TurboIntegrator Process Editor to execute the process manually. After the process has completed successfully, locate and open the output file and review the results.
In the following example, an IBM Cognos TM1 server named SData with 2 cubes (Cube BA and CubeAB) and 3 dimensions (DimA, DimB, and Dim C) with the third dimension (Dim C) not used in any cube, is used with the TurboIntegrator process listed above. This is depicted in Figure 3.
Figure 3 – An IBM Cognos TM1 server named 'SData' with 2 cubes (Cube BA and CubeAB) and 3 dimensions (Dim C, DimA, and DimB)
The results in the DimensionList.csv file are as shown below in Figure 4.
Figure 4 - The DimensionList.csv output file generated from the TurboIntegrator process
Notice that Column A is the dimension name and Column B is the dependent cube name. Also notice that the dimension “Dim C” (row 6), which is an unused dimension on this TM1 server, has no dependent cubes and has three asterisks (***) shown in Column B thereby identifying this dimension as unused and a possible candidate for deletion.