Skip to content
Apr 21 / Administrator

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.

13 Comments

leave a comment
  1. Gavin Smith / Apr 22 2012

    Thanks for the tutorial, it was easy to follow and I’ve learned the concept of data loading in TM1. It’s not as easy as it is in EP but I think I’d find it better once I get a hang of it.

    thanks again.

  2. MD Solutions4 / Apr 22 2012

    Thanks for explaining this concept so easily. I’m learning more and more.

  3. Rick / Apr 22 2012

    I’m not able to metally visualize the rules syntax. What’s N: C: ? and when do we use S:

    I’ve never seen a calculation involving strings. You can never do any calculations in strings. and what’s this exclamation?? :-S

    It’s soooooooo frustrating not being able to get this simple thing work. can you please help me.

    • Administrator / Apr 22 2012

      No need to get frustrated over a simple thing. The concept is very easy.

      You must remember the basic algebra.

      Z = X + Y [simple addition]

      P = Q [simple assignment]

      Now, think about what would you do if you want to assign addition of X and Y to Z but only at the simple numeric cell level. Not at the consolidated level (e.g. FY etc.)

      So you shall write Z = N: X + Y;

      Simple.

      But if you wanted to assign the value only to consolidated cell level, you’ll write:

      Z = C: X + Y;

      Also, if you have string cells and wanted to assign the name of the student then you would write:

      [‘Student Name’] = S: ‘Rick’;

      It’s this simple. You should try with some very basic assignment examples.

    • Administrator / Apr 22 2012

      And treat each !Dimension as a variable which for each cell represents the correponding element of that dimension.

      • Rick / Apr 22 2012

        Thanks.

  4. Code Snippets / Apr 22 2012

    You have much more command over data loading into cubes if you know how to write TIs. You can do a lot in turbo integrator. I’ll provide different code snippets on TIs and Rules which would help you understand the concepts. Code Snippets will be our author admin account to provide various snippets.

  5. Code Snippets / Apr 22 2012

    Conditional transfer

    If(vMonth @= ‘Jan’);
    CellPutN(NVALUE, ‘Products’, vMonth, vVersion, vQuantity);
    EndIf;

  6. chonlanotr / Aug 26 2012

    Thanks for your share knowledge.

  7. srinivas / Oct 16 2013

    Hi i followd your tutorial and learned a lot about how to write the rule to load the data from one cube to another. thank you very much!!

    Now my question is if we have two measures as variables like Sales price and Unit cost..i would like to push these two variables’ data into one dimension i,e Expense Measures dimension. (it has two elements Sales Price and Unit cost) how to load them now? thanks in advance

  8. Gitanjali / Jan 31 2014

    Hi,
    thanks for sharing this knowledge.
    I am facing an issue. The reference column in ” Insert Cube references” in Rules editor, is missing for all my available cubes except the cube I want to load data into. Can you please help me.
    thanks.

  9. emma / Nov 28 2016

    hi when I am loading the data to the cube it is leaving my first column starting with ” and the last column has a ” at the end of each word
    I have followed the steps precisely can anyone advise what I need to do?

    • Administrator / Nov 28 2016

      Check your quote character in the TI. Also, you can try removing double quotes from your source text file (open in notepad, remove all double quotes)

Leave a Comment

Thank you for Downloading.

Please input valid email id so that we can send you download link.

Email id Required.

We respect your privacy and never share your email id with anybody.