IBM Business Analytics Proven Practices: Finding Unused Dimensions on a TM1 Server

Product(s): IBM Cognos TM1 10.1; Area of Interest: Financial Management

This document is intended to provide IBM TM1 administrators with a technique to reduce memory consumption by quickly identifying all unused dimensions in an IBM Cognos TM1 server.

Share:

Martin Ryan, Contractor, Herring Ryan

Martin has been working as TM1 contractor for nearly a decade, now contracting through his company Herring Ryan.  He has worked in New Zealand, Australia and the UK spanning multiple industries including government, FMCG, publishing and financial services.  He also started the popular tm1forum.com.  Martin lives in Wellington, New Zealand with his wife and three daughters.



Deepak R Desale, Systems Software Engineer, IBM

Deepak R DesaleDeepak is an IBM Systems Software Engineer focused on Quality Assurance for IBM Business Analytic products. He is currently specializing in the Financial Performance Management products TM1 and Planning as well as Business Intelligence.



04 March 2013 (First published 24 October 2012)

Also available in Portuguese

Introduction

Purpose

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.

Applicability

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.

  1. 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.
  2. 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
    Figure 1 – The Server Explorer window with the IBM Cognos TM1 server 'Planning Sample' expanded and the 'Processes' option highlighted
  3. 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
    Figure 2 - The TM1 Turbo Integrator Data Source tab default display
  4. 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.
  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;
  6. 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;
  7. 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.

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)
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
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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=842408
ArticleTitle=IBM Business Analytics Proven Practices: Finding Unused Dimensions on a TM1 Server
publish-date=03042013