IBM Cognos Proven Practices: Listing all Subsets per Dimension in an IBM Cognos TM1 Server

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

This document describes a technique to create a .csv file showing all subsets in all dimensions in a TM1 server. The file is then viewed as a report in Cognos Insight.

Sameer Inamdar, Software Engineer, IBM

Sameer InamdarSameer Inamdar is a Software Engineer working for IBM India Software Lab for the last two and half years. He is currently working on customer centric issues on various products like TM1, Cognos BI, Cognos Insight etc.



Vinny Bhagchandani, System Software Engineer, IBM

Vinny BhagchandaniVinny Bhagchandani is a Software engineer currently working for IBM India Software Labs. She is currently working on IBM Cognos TM1. Vinny has 21 months experience on Cognos Planning and TM1.



06 July 2012

Also available in Chinese Russian

Introduction

Purpose

In any IBM Cognos TM1 Server, many subsets can be built on each dimension. When designing TM1 applications it is often necessary to understand all the subsets that currently exist on the TM1 server. This article describes a technique to create a comma separated file (.CSV) that contains all subsets for all dimensions. This .CSV file can then be viewed and analyzed in IBM Cognos Insight.

Applicability

IBM Cognos TM1 10.1 and forward.

Exclusions and Exceptions

No known exclusions.


Creating A .CSV File Output Using A TurboIntegrator Process

The output of this IBM Cognos TM1 TurboIntegrator (TI) process is a .CSV file. In this document, the .CSV file will be named SubsetCount.csv. All you need to do is create a TurboIntegrator process and run it. This process will create a .CSV file which lists all subsets in the corresponding dimension for all dimensions.

Columns in the CSV file

Two columns get created in the .CSV file which is the output of a TI process described below in section 2.2. They are as follows:

  • Dimension_names
  • Subset_names

The name of the subset in column Subset_names belongs to the corresponding dimension in the column Dimension_names. Later, we will see how to import and view this file as a cube in IBM Cognos Insight.

Creating the TurboIntegrator Process

Follow the procedure described below for creating the TurboIntegrator process.

  1. In IBM Cognos TM1 Architect expand the TM1 Server you will be working with by clicking on the '+' sign next to the TM1 Server name. Then right-click on Process and choose Create New Process.
    Illustration 1: TM1 Architect Server Explorer - Planning Sample server with Processes highlighted
    Illustration 1: TM1 Architect Server Explorer - Planning Sample server with Processes highlighted
  2. In the Data Source tab of the TurboIntegrator Process Editor leave the default Datasource Type of None as the selection.
    Illustration 2: TM1 Turbo Integrator - Data Source tab - Datasource Type set to None
    Illustration 2: TM1 Turbo Integrator - Data Source tab - Datasource Type set to None
  3. Click on the Advanced tab and the Parameters sub-tab. Add two parameters in the TurboIntegrator process as shown in Illustration 3 below. They are:
    1. Parameter Name: vDataDirectory
      Type: String
      Default Value: C:\Program Files\IBM\cognos\tm1\PlanSamp
      Prompt Question: The path of the Data Directory for the TM1 Server
    2. Parameter Name: vOutputFile
      Type: String
      Default Path: C:\
      Prompt Question: The path for the output file SubsetCount.csv
    Illustration 3: TM1 TurboIntegrator Editor Advanced tab with file paths and prompt questions defined for parameters
    Illustration 3: TM1 TurboIntegrator Editor Advanced tab with file paths and prompt questions defined for parameters
  4. Copy and paste the following code into the Prolog procedure of the process.
    #****Begin: Generated Statements***
    #****End: Generated Statements****
    
    # If the Allsubsets dimension exits then we delete it and again create it.
    
    IF(DimensionExists('AllSubsets')=1);
    DimensionDestroy('AllSubsets');
    ENDIF;
    DimensionCreate('AllSubsets');    
    
    IF(DimensionExists('Dimension_names')=1);
    DimensionDestroy('Dimension_names');
    endif;
    
    no_dimensions = DIMSIZ('}Dimensions');
    DimensionCreate('Dimension_names');
    DimensionElementDelete('}Dimensions','Dimension_names');
    
    dim_counter = 1;
    
    WHILE(dim_counter <= no_dimensions);
    dim_name=DIMNM('}Dimensions',dim_counter);
    
    # Skip the control dimensions 
    
    var1 = scan('}',dim_name);
    
    if(var1<>1);
    DimensionElementInsert('Dimension_names','',dim_name,'N');
    i=0;
    vFilenew='';
    compare='a';
    
    # Search for corresponding folder and .sub file for a dimension
    while(compare@<>'');
    
    vFile = WildCardFileSearch( vDataDirectory|'\'| dim_name | '}subs\'|
      '*.sub',vFilenew) ;
    result=fileExists( vDataDirectory | '\'| dim_name | '}subs\'| vFile);
    if(vFile@<>'');
    if(vFIle@<>'Default.sub');
    strlength=LONG(vFile);
    vSubsetfinal=DELET(vFile,strlength-3,4);
    DimensionElementInsert('AllSubsets','',vSubsetfinal,'N');
    
    endif;
    vFilenew=vFile;
    
    endif;
    if(vFile@='');
    compare='';
    endif;
    END;
    endif;
    
    dim_counter = dim_counter +1;
    END; 
    
    dim_counter=1;
    subset_counter=1;
    
    no_dim = DIMSIZ('Dimension_names');
    no_subsets = DIMSIZ('AllSubsets');
    
    ASCIIOutput(vOutputFile |'\'| 'SubsetCount.csv','Dimension_names','Subset_names');
    WHILE(subset_counter<=no_subsets);
    subset_name=DIMNM('AllSubsets',subset_counter);
    
    dim_counter_new=1;
    
    WHILE(dim_counter_new<=no_dim);
    dim_name1=DIMNM('Dimension_names',dim_counter_new);
    var_dim_name = scan('}',dim_name1);
    
    if(var_dim_name<>1);
    if(dim_name1@<>'AllSubsets');
    
    #If the subset exists for the dimension then write its name
    # in front of the dimension name in csv file
    if(SubsetExists(dim_name1,subset_name)=1);
    ASCIIOutput(vOutputFile |'\'| 'SubsetCount.csv',dim_name1,subset_name);
    endif;
    endif;
    endif;
    
    dim_counter_new=dim_counter_new+1;
    
    END;
    subset_counter=subset_counter+1;
    END;
  5. Name and save the TI process. Click the Run arrow in the TI Editor to execute the process manually. After executing this process what you get is the SubsetCount.csv file with the dimension names in column A and the subset names in column B.
    Illustration 4: View of .CSV output file showing dimension names (column A) and subset names (column B)
    Illustration 4: View of .CSV output file showing dimension names (column A) and subset names (column B)

Importing The .CSV file In IBM Cognos Insight

Steps to import the .CSV file in IBM Cognos Insight

There are two ways to perform import the .CSV file in IBM Cognos Insight.

  1. Launch IBM Cognos Insight (CI).
  2. Drag and drop the .CSV file to the CI workspace.

or,

  1. Launch IBM Cognos Insight.
  2. Click on Get Data > Quick Import.
  3. Select the .CSV file in the Open File dialog.

This will create a cube with two dimensions, Dimension_names and Subset_names. Additionally, a measure Count is created within the cube which counts the number of subsets in a dimension.

To view the subsets for the corresponding dimension, drag and drop the two dimensions from the content pane to the workspace. Click on the Widget Actions Button for the SubsetCount widget and uncheck the option Listen to All Widgets to get the view with the following four widgets as shown below in Illustration 5.

  1. A crosstab report that lists the dimension names in the TM1 Server and provides a count of the number of subsets that reference each dimension.
  2. A chart representation of the crosstab report.
  3. The Dimension_names dimension.
  4. The Subset_names dimension.

Now, when you select a dimension in the Dimension_names widget only those subsets will be selected in the Subset_names widget which are present in that dimension. Other subset names will be grayed out.

Illustration 5: View of IBM Cognos Insight displaying subsets per dimension from the TM1 server
Illustration 5: View of IBM Cognos Insight displaying subsets per dimension from the TM1 server

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. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. 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 Business analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=824157
ArticleTitle=IBM Cognos Proven Practices: Listing all Subsets per Dimension in an IBM Cognos TM1 Server
publish-date=07062012