Turbo integrator (TI) is an ETL (extraction – transformation – load) tool, in layman terms a data loading tool, for loading data into TM1 cubes. Although, there is so much that you can do with TI, loading data is just one part of that.

So, let’s load the data into our TM1 cube. We’ll use the Products2 cube that you created it previously. You can follow this section without that too, but you’d have to follow it just on concept, you’ll not be able to follow it step by step.

Now, here is what we’ll do to load the data:

Check our cube and note down the columns that we’ll need in our data.
Create a csv (comma separated) file of dummy data that we have to load to our cube.
Write a TI to read that csv file and load data to our cube.

Ok, now open the products2 cube. The data columns that you see in it are:

Products, Months, Versions, Measures

So, our data should be in subsequent format in csv file:

Product, Month, Version, Measure, DataVal

e.g. Pepsi, Apr, Budget, Quantity, 100

Create a csv file using samples of data such as above. I’ve shown below the ones which I’m going to load to my TI. You can copy it and create your data.csv from this. Remember to create your data.csv in D:\TM1Beginner\InputFiles folder in your app server directory.

Product,Month,Version,Measure,DataVal
Pepsi,Jan,Budget,Quantity,10
Coca Cola,Jan,Budget,Quantity,15
Coca Cola,Mar,Budget,Quantity,3
Coca Cola,Sep,Budget,Quantity,2
Coca Cola,Dec,Budget,Quantity,7
Seven Up,Jan,Budget,Quantity,20
Seven Up,Jul,Budget,Quantity,5
Seven Up,Oct,Budget,Quantity,6
Almond Joy,Feb,Budget,Quantity,8
Almond Joy,May,Budget,Quantity,2
Almond Joy,Jul,Budget,Quantity,5
Amul Chocolate,Feb,Budget,Quantity,9
Bournville,Mar,Budget,Quantity,17
Bournville,Sep,Budget,Quantity,3
Bournville,Dec,Budget,Quantity,5
Wonder Bread,Apr,Budget,Quantity,3
Britannia,May,Budget,Quantity,7

Now launch your TM1 architect, login to your TM1Beginner server, go to Processes node, right click, and click create new process.

NOTE: Close your data.csv file before proceeding further.

Select data source as text. Browse to your D:\TM1Beginner\InputFiles files and select your data.csv file. You’ll get a warning box, click OK on it. There is a field of “Number of title records” on the page (on Data Source tab), write 1 in it. Because we have first record as title record in our data.csv file. Click Preview, it should look like below:

Go to the variables tab, Rename V2 as vMonth. In content column, select “Element” for first 4 variables. For the fifth one, select data. It’ll look like below:

Come to the maps tab, Select Update Cube, select your CubeName (which is Products2). Go to the dimensions tab, select dimensions under column “Dimension”. This will tell Tm1, which column of data file, will be matched with which column of cube. You don’t need to map data column, because there is only one data column.

Go to the advanced tab and one by one, click all tabs under advanced tab (Prolog, Metadata, data, epilog). These tabs contain the code for us to load the data into cubes. When you select your data source, select cube and data columns etc. this code is automatically created. Clicking all 4 of these tabs is necessary because sometimes this code is not updated when you change something in turbo integrator front end. Selecting these 4 tabs ensures that automatically generated code has been properly updated. It’s also a good practice to review the code changes before running TI.

Now save your process with name LoadDatatoProducts2 and click Run. It’s a button next to Save button.

If you’ve followed the tutorial correctly the process will complete successfully. Now launch your Products2 cube, rearrange the dimensions as follows and verify that you see the subsequent data in your cube:

Assignment

Load the subsequent sale price data to your Products2 cube:

Pepsi,Jan,Budget,Sale Price,2
Coca Cola,Jan,Budget,Sale Price,3
Coca Cola,Mar,Budget,Sale Price,4
Coca Cola,Sep,Budget,Sale Price,5
Coca Cola,Dec,Budget,Sale Price,6
Seven Up,Jan,Budget,Sale Price,7
Seven Up,Jul,Budget,Sale Price,8
Seven Up,Oct,Budget,Sale Price,9
Almond Joy,Feb,Budget,Sale Price,1
Almond Joy,May,Budget,Sale Price,2
Almond Joy,Jul,Budget,Sale Price,3
Amul Chocolate,Feb,Budget,Sale Price,4
Bournville,Mar,Budget,Sale Price,5
Bournville,Sep,Budget,Sale Price,6
Bournville,Dec,Budget,Sale Price,7
Wonder Bread,Apr,Budget,Sale Price,8
Britannia,May,Budget,Sale Price,9

2. Loading Data Using Rules

Now that you have your data ready in Products2 cube, we’ll see how to load data using rules in TM1. For this we’ll load the Products2 data in Products cube that we created in our previous section.

Open your Products cube and verify that you don’t have quantity nor do you have sale price.

Now before we proceed further, let me just tell you that we use DB function for loading data in a cube from another cube. Here is the syntax of a rule along with DB function:

1 Selection 2 Filter 3 DB

[Data selection where you want to load data] = Filter: DB (CubeName, Dim1Item, Dim2Item, Dim3Item, ….);

1 = It’s data selection, the cells into which you want to load data. For example, if I want to load just my budget data, I’ll write [ ‘Budget’] in it.

2 = It’s filter. We specify numeric (N:) or string (S:) here to load data to numeric or string cells of target cube.

3 = It’s DB function. First argument is the cube’s name from which you’re pulling the data and remaining arguments are dimension item names against which you’re pulling the data. For example, if I want to take Products2’s Pepsi’s Budgeted Quantity for Apr, I’ll write DB (‘Products2’, ‘Pepsi’, ‘Apr’, ‘Budget’, ‘Quantity’).

Remember, order is important in DB function. You should write all dimension items names in the order they exist in the cube.

Now let’s write our rule to pull data from Products2 to Products cube.

Right click on Products cube and click “Create Rule…” (we’ll right rule in products cube because this is the target cube). A window opens. That’s rule editor.

Write subsequent:

[ ‘Quantity’] = N:

Now click cube icon on the toolbar and select Products2 cube. This is how we select the source cube.

Click OK. You’ll see the subsequent rule in your rule editor:

[ ‘Quantity’] = N: DB (‘Products2’,! Products,! Months,!Measure)

! Products (dimension name with exclamation mark) is a dimension variable. It tells you that for each cell of cube for which this rule runs, Products will take the corresponding item of TARGET dimension. Confusing?? Read that again.

I’ve explained this in detail in my advanced rules tutorial (which is part of a video series and costs you a few bucks), for now just understand that for different cells, the! Products, Months and! Measure represent different dimension items. So for Target cube’s cell [‘Pepsi’ -> ‘Apr’ -> ‘Quantity’], when TM1 calculates the formula, it’ll automatically convert the formula to DB(‘Products2’, ‘Pepsi’, ‘Apr’, ‘Quantity’) in memory and take the value (‘Pepsi’, ‘Apr’, ‘Quantity’) from Products2 cube. But what will formula take from Versions? It’ll take Budget? or Actual? or what?

To specify the version, you need to modify the formula. As we don’t have any version in our target cube, we need to hard code the version in our formula. Let’s take Budget data from source cube. So, change your formula to this:

[ ‘Quantity’] = N: DB (‘Products2’,! Products, Months, ‘Budget’, ‘Quantity’);

Also, change! Measure to Quantity as we just want to take the quantity value from Products2 cube, at least for now. And remember to put a semi colon in the end of formula.

Click on Verify toolbar icon (third toolbar icon from the left) to verify the formula and then click Save icon. Close rules editor and open the Products cube to verify your data. You’ll see subsequent data:

Assignment

Bring the price data too from Products2 cube into your Products cube. You’d write a similar rule for Sale price in rule editor for that.

Now, think about what would you have to do to bring both values (Qty and Price) using a single rule?

After you’ve brought both Quantity and price, open your Products cube and check the Sale Price in rollup levels (at Q1/Q2 etc. or at Total Products level). It’s just summing up, which is not correct right? At rollup, the price should be a weighted average based on quantity. We’ll learn about correcting it in our tutorial on rules.

So, this brings us to the end of blog. This Tecklearn ‘How to Load Data in TM1 Cubes’ blog helps you with commonly asked questions if you are looking out for a job in Cognos TM1. If you wish to learn Cognos TM1 and build a career in Business Intelligence domain, then check out our interactive, Cognos TM1 Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/cognos-tm1/

Cognos TM1 Training

About the Course

Tecklearn’s Cognos TM1 training will make you proficient in building and configuring models in Cognos TM1 using performance modeler tool. Some of the topics included in this Cognos TM1 training are creation and configuration of TM1 servers, building Cubes and Views, creating dimensions, deploying Turbo Integrator for loading and maintaining data and transferring data using scripts. etc., in working with real-time projects, and this will enable you to become a certified Cognos TM1 developer.

Why Should you take Cognos TM1 Training?

The Average salary for a Cognos TM1 Developer is $117,816 per annum – ZipRecruiter.com.
Cognos TM1 has a market share of about 11.87%.
Solution that is owned by Financial and Business analysts, with limited IT involvement, but consistent with IT’s governance.

Curriculum

Introduction to Cognos TM1

Concepts of Multi-dimensional OLAP database
Cognos TM1 Installation
TM1 Servers and Services
TM1 Workflow
TM1 User Interfaces
Turbo Integrator

Cognos TM1 Configuration

Managing TM1 Components and Tasks
Operations on TM1 Servers: Start, Stop and adding TM1 Servers
TM1 Web, Applications and Operations Console
Using Command prompt create or delete Desktop/Windows services
Hands On

Creating Dimensions

Manual creation of Dimensions
Create Product Dimensions and Measure Dimensions
Dynamic Subset Creation
Sparse and Dense Dimensions
Using Turbo Integrator ETL create cubes, review Cubes and Dimensions
Create Subset, assign Subset, Dimensional Manipulation
Performance Monitoring
Control Cubes
TM1 Web and Rules in TM1
Cube related attributes, dimension related attributes
Hands On

Building Cubes and Views

Building Cubes
Building Views
Bulk Load Mode
Creating Data Source through TM1 Turbo Integrator
Hands On

TM1 Security

Various security levels including Server level, Cube level, Dimension level
Concepts of Security in TM1
Creation of Client & Group and access privileges
Cube Level Security
Hands On

TM1 Data Spreading

TM1 Data Spreading and Recreating dimension
Attributes element types
Cube control elements
Dimension elements
Drill Assignment rules
Trace a feeder and feed strings
Hands On

Cognos TM1 Rules

Understanding TM1 rules
TM1 Rules Editor and Rules compilation using functions
Rule performance optimization
Element information rules functions
Cube manipulation Turbo Integrator functions
D-type in TM1
ELCOMP & ELISCOMP Functions
ELISPAR & ELWEIGHT Functions
Hands On

Advanced Scripting with Turbo Integrator

Advanced Scripting with Turbo Integrator
Deploying Dimension subsets and attributes
Editing procedures using scripts
Hands On

Advanced Modelling Techniques

Significance of advanced modelling
Manually adding objects to application design
Understanding application views, dimensions and hierarchy
Virtual cubes, Lookup cubes and spread profile cube
Hands On

TM1 Application Web

TM1 Web Applications
Creating TM1 Application
Working with TM1 Application Web
Snapshots in TM1 application
Adding MDX filters and Action button properties
Active forms, TM1 user and data formatting range
Hands On

693