5. Loading Data in TM1 Cubes

There are 2 ways of loading data in TM1 cubes:

1. Using Turbo Integrator

2. Writing rules

 

1. Loading Data Using Turbo Integrator

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 in last tutorial. You can follow this tutorial 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:

1. Check our cube and note down the columns that we’ll need in our data.
2. Create a csv (comma separated) file of dummy data that we have to load to our cube.
3. 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 following 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 will 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 following data in your cube:

 

Homework

Load the following 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 last tutorial (4. Cubes in TM1)

 

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

[ ‘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 following 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 will 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 will 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 following data:

 

Homework

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.