Principles in TM1 are written to do calculations within a cube and to load data into cubes. That is whole purpose of writing principles. You ought write principles in the cube into which you want to load data (target cube) or want to perform calculation. Let’s learn by writing one.

Create a cube named Products2 with 4 dimensions: 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 principles editor it may open as simple rule editor which doesn’t have user friendly interface. For more user-friendly rule editor, you ought 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’ve to update the one which is related to your login folder.

Open the file in notepad and check whether AdvancedPrinciplesEditor = 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 Principles in TM1

Before we go about creating principles, 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 ought write dimension item names in brackets inside single quotes like this: [‘dimension item’] Let’s put the concept to some use.

Create Principles

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 Principles. Write:

[‘Sales’] = [‘Quantity’] * [‘Sale Price’];

Select the cube view and press F9, see what happens. you’ll 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 ought 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 difficulty 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 often how you apply principles 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’];

Assignment

Change the principles 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 each selling item. Apply this logic in the cube.

So, this brings us to the end of blog. This Tecklearn ‘Rules in Cognos TM1’ 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

626