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.
IBM Cognos TM1 10.1 and forward.
No known exclusions.
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.
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:
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.
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:
- Parameter Name: vDataDirectory
Default Value: C:\Program Files\IBM\cognos\tm1\PlanSamp
Prompt Question: The path of the Data Directory for the TM1 Server
- Parameter Name: vOutputFile
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
- Parameter Name: vDataDirectory
- 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)
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.
- 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
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.