Introduction to Chores in TM1
Chores in TM1 are objects that execute one or more processes of TM1 at a user-defined frequency.
For example, if you have a process called UpdateSalesData and want to automate the process of updating the sales data (meaning you want to run the process automatically without user intervention from time to time), you can create a chore for it and schedule to run the chore at a specific time.
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.
Introduction to Formatting Cells in TM1
Sometimes you may need to format your data according to your client requirements. For example:
Show your numeric value up to 3 decimal points
Show the date values in MM-DD-YYYY format
Show the % values followed by a %
Show negative values in ()
So in this tutorial we’ll learn formatting cells in TM1.
Here is how to calculate cube size in TM1
Open your architect. Right click your application server and click Start Performance Monitor.
Go to View > Display Control Objects. You’ll see various control cubes among your cubes. Open }StatsByCube
This cube contains statistics of all cubes. To see the memory consumption Click the }TimeInterval dimension and select Latest.
Select the Total Memory Used dimension item from the }StatsStatsByCube dimension. Press F9. You’ll see Cubes Total. Expand it. You’ll find all your cubes in rows with total memory used in each. The values are in Bytes.
Introduction to Rules in TM1
Rules in TM1 are written to do calculations within a cube and to load data into cubes. That is whole purpose of writing rules. You should write rules in the cube into which you want to load data (target cube) or want to perform calculation. Let’s learn by writing one.
A. You must have completed 4. Cubes in TM1 tutorial and have manually filled in data for sales quantity (= 10) and sales price (= 5) in your Products2 cube. We’ll calculate sales from this. Arrange the cube as shown in the below screenshot.
B. If you haven’t completed 4. Cubes in TM1 tutorial then; Create a cube named Products2 with 4 dimension: Products, Months, Version, Measure. The cube should look like this:
Now fill in the quantity = 10 and Sale Price = 5 in all months.
Turn Advanced Rule Editor On
When you open the rules editor it may open as simple rule editor which doesn’t have user friendly interface. For more user friendly rule editor, you should turn the advanced rule editor on.
Go to the documents and settings folder on your windows machine and search for tm1p.ini. You may find more than one but you have to update the one which is related to your login folder.
Open the file in notepad and check whether AdvancedRulesEditor = Texists. If it’s set to F then change it to T.
Save and close the file. Close the Architect. Relaunch the architect. You’re all set now.
Syntax of Rules in TM1
Before we go about creating rules, let’s first understand the syntax. A rule is written in following way:
[ Selection ] = Filter : Rule Calculation ;
Selection => It’s the area into which the rule should be affected. If you just want to populate the Sales values in Jan month for all products, you’ll write [ ‘Jan’ ] in this part. If you want to populate the sales values Jan month for Pepsi product, you’ll write [‘Jan’, ‘Pepsi’] in this part.
Filter => It specifies the type of cell in which the value will be populated. It can specify 3 types. N, S, and C. N for numeric, S for string and C for consolidated such as Q1, FY, Total Products etc.
Rule Calculation => It’s simply the calculation that you want to apply.
Also, you should write dimension item names in brackets inside single quotes like this: [‘dimension item’] Let’s put the concept to some use.
You must have the Products2 cube open. You’ll notice that the Quantity is getting calculated at Quarter and at FY level automatically correctly whereas Sales values are all zero. Let’s populate the sales values. Right click the Products2 cube in Architect and select Create Rules. Write:
[‘Sales’] = [‘Quantity’] * [‘Sale Price’];
Select the cube view and press F9, see what happens. you will see this:
You’ll notice that the sales at quarters is not getting calculated correctly. If each month’s sales is 50, Q1’s sales must be 150, instead, it’s 450. Similarly, for FY, it’s 7200 whereas it actually should be 600.
The values are incorrect because the above formula is being applied to all cells, whether it be simple cell or a consolidated cell, we’re doing Sales = Qty x Price, which, for Q1 = 450 = 30 x 15. To correct this you should apply this formula only at the numeric level, not at the consolidated level. So change the above formula to:
[‘Sales’] = N: [‘Quantity’] * [‘Sale Price’];
The Q1, Q2 etc. are getting calculated correctly now but you’ll notice that the Quarter values of sales price are incorrect. That’s because we’ve not yet put any formula for them, they’re just getting consolidated like other measure items, which in case of price is incorrect.
You know that the price at quarter level must be weighted average based on quantity. Jan Sale = 10 x 5 = 50 Feb Sale = 10 x 5 = 50 Mar Sale = 10 x 5 = 50 Q1 Sale = 50 + 50 + 50 = 150 Q1 Price = ( 10 x 5 + 10 x 5 + 10 x 5 ) / (10 + 10 + 10) = Q1 Sales / Q1 Quantity
So to correct the issue we must change the consolidated value of sales price (Q1, Q2, Q3, Q4, FY and other roll ups in other dimensions such as products roll ups) Add a new formula to correct the sales price at rollup level:
[‘Sale Price’] = C: [‘Sales’] / [‘Quantity’];
It says that at consolidated level for Sale Price, just take the value of Sales, and Quantity; and just arrive at sales price by dividing sales with quantity. See the result by pressing F9 in cube view.
The price is getting calculated correctly. This is how you apply rules to a cube. Here is the content of rule file that must match with your rule file:
[‘Sales’] = N: [‘Quantity’] * [‘Sale Price’]; [‘Sale Price’] = C: [‘Sales’] / [‘Quantity’];
Change the rules in this cube so that the sales gets calculated only for Budget but not for Actuals or any other version. Also, what would the net sales be if you’re giving 10% discount on your each selling item. Apply this logic in the cube.
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:
- 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 following format in csv file:
Product, Month, Version, Measure, DataVal
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.
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:
Load the following sale price data to your Products2 cube:
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
Wonder Bread,Apr,Budget,Sale Price,8
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.
[ ‘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:
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.