6. Rules in TM1

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.

Prerequisite

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.

or

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.

Create Rules

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’];

 

Homework

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.