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

Comments

Content series:

This content is part # of # in the series: IBM Cognos Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Cognos Proven Practices

Stay tuned for additional content in this series.

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
    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)
    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
Illustration 5: View of IBM Cognos Insight displaying subsets per dimension from the TM1 server

Downloadable resources


Comments

Sign in or register to add and subscribe to comments.

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