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.

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.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

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

Sameer Inamdar, Software Engineer, IBM

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

Summary: 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.

View more content in this series

Date: 06 Jul 2012
Level: Intermediate
PDF: A4 and Letter (286 KB | 8 pages)Get Adobe® Reader®
Also available in: Chinese Russian

Activity: 8756 views
Comments:

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.

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

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;

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)

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.

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

or,

Launch IBM Cognos Insight.
Click on Get Data > Quick Import.
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.

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.
A chart representation of the crosstab report.
The Dimension_names dimension.
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

About the authors

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

Close [x]

Report abuse help

Report abuse

Thank you. This entry has been flagged for moderator attention.

Close [x]

Report abuse help

Report abuse

Report abuse submission failed. Please try again later.

Close [x]

developerWorks: Sign in

All information submitted is secure.

Close [x]

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.