8. TM1 Turbo Integrator

Introduction to TM1 Turbo Integrator

TM1 Turbo Integrator is an ETL (extraction – transformation – loading) tool. In simpler terms, it can extract data from various data sources and load into TM1 cubes. But data loading is one of the so many things that TI is capable of.

A large number of capabilities make TI an integral part of TM1 offering. You can:

• Create dimensions and cubes

• Run other TI processes

• Do data spreading

• Apply security

• Do almost anything that you do manually, using TI.

We’ve already covered the basic data loading part in our rules tutorial. So in this tutorial, I’ll cover the dimension creation using TI. This will introduce you to the various components of TI. We’ll create the ‘Location’ dimension.

Before we go any further, let’s first create the text file that we’ll use to create the dimension.

 

Location,Country,Total World,Continent,LocationCode
Newyork,USA,Total World,NA,954821
Austin,USA,Total World,NA,954823
Bangalore,India,Total World,Asia,125433
North Carolina,USA,Total World,NA,954824
San fransisco,USA,Total World,NA,954822
Tokyo,Japan,Total World,Asia,235432
Mumbai,India,Total World,Asia,125432
Sydney,Australia,Total World,Aus,456321
Shanghai,China,Total World,Asia,765354

 

First row of data contains the column heading. First 3 columns of data are self explanatory. The fourth column is the continent value. We’ll use it as the attribute of dimension elements. The fifth column is location code. This will be a numeric attribute. This will represent the location code of a particular location.

NOTE: Before proceeding any further, save and close your text csv (Locations.csv) file.

 

Creating Process

Open architect, expand nodes and right click on Processes. Click Create new process. Doing this will launch the TI editor.

 

1. Select Data source

On the first tab, you can select data source. There are various types of data sources here, for now we’ll use the text. Select your text file using Browse button on the page (in front of the data source name). If a warning appears, just click OK.

Input Number of title records as 1 and click Preview.

 

2. Select Variables

Go to the variables tab and rename V3 as vTotalWorld. Under Contents column, set

Location – Element
Country – Consolidation
vTotalWorld – Consolidation
Continent – Attribute
LocationCode – Attribute

 

3. Map Variables

Go to the next tab. In cube tab, do nothing. That’s because we’re just going to create the dimension not the data loading. Go to dimension tab. In Action, select Create. In dimension column, write our dimension’s name Locations. Element type will be numeric and element order will be automatic sort by Hierarchy

 

As there is no data variables, data tab will be disabled. Come to consolidations tab. Here we will set the hierarchy.
Each location’s parent -> Country
Each Country’s parent -> Total World

Click on black sign under child variable and set the values as below

Set the component order too ‘by Name’.

 

Go to Attributes tab and set following values. Under attribute, you’d have to manually type the name of new attributes. You will be able to select the remaining values by clicking.

 

4. Review the Auto Generated Code

Go to the advanced tab and one by one select and review the auto generated code. Doing so is mandatory after every change to the TI process. Now save the process and click Run. The process will complete successfully. The location dimension has been created.

 

Verify the Process Changes

Now go to the dimensions node in Architect and double click Locations. You should see following hierarchy.

 

Also, verify the created attributes. Right click the Locations dimension in Architect and select ‘Edit element attributes…’ . You will see the attributes that we have created.

 

Homework

Edit the above process and create one alias attribute which should have values in following format:
LocationCode – Location

So alias value for element Newyork will be 954821 – Newyork

I’ll put the solution in the comments section.

 

This concludes our article on TM1 Turbo Integrator.

 

For Turbo Integrator functions reference, refer to the following IBM link:

http://publib.boulder.ibm.com/infocenter/ctm1/v9r4m1/topic/com.ibm.swg.im.cognos.tm1_ref.9.4.1.doc/tm1_ref_id13598TM1TurboIntegratorFunctions_N70006.html