Skip to main content

Data & Intelligence

Cognos TM1 Rules – how do they really work?

 

 

 

 

 

Since Cognos TM1 uses a very efficient data compression algorithm to allow large datasets to fit in relatively small amounts of RAM, TM1 calculates the values only when needed by TM1—resulting in improved performance and reduced storage requirements.

Here is the sequence of events:

  1. A value is requested from (a location in) a cube.
  2. TM1 Server checks if the location corresponds to the area definition of any calculation statements associated with the cube.
  3. If the location does correspond to a statement, TM1 evaluates the formula portion of the calculation statement.
  4. TM1 returns the calculated value to the relevant area.

It is important to know:

  • Values are calculated only once for a cell
  • Rules are executed with order preference and the first rule that gets applied to the cell, wins

Like consolidations within dimensions, TM1 rules are calculated on demand. But unlike consolidations, TM1’s sparse consolidation algorithm is not able to determine in advance which results will be empty without additional information. In fact, when consolidation occurs in TM1 cubes that have rules defined, the sparse consolidation algorithm is turned off. This is done to avoid incorrect results getting generated by the TM1 Rules. So when the sparse consolidation algorithm is turned off, every single cell in the cube is checked for a value during a consolidation and this can slow down cubes that are very large or sparse.

 

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

 

 

 

 

General rules of thumb:

  • Remember that, if most values in your cube are zeros, this is an indication that the cube is relatively sparse.
  • Multidimensional cubes are almost always sparse.
  • The more dimensions a cube has, the greater is the degree of sparsity.
  • In TM1, there is a distinction between a zero and a value that is missing (or non-applicable).
  • In TM1, values can only be real numbers, and the value zero is used to represent zero, no (or missing) value, and even the non-applicable values.
  • The impact of sparsity on calculations can be tremendous.

Sparsity

During consolidations, TM1 uses a sparse consolidation algorithm to skip over cells that contain zero or are empty. This algorithm speeds up consolidation calculations in cubes that are highly sparse. A sparse cube is a cube in which the number of populated cells as a percentage of total cells is low (http://publib.boulder.ibm.com). Cognos TM1 provides the use of feeders to identify which cube cells need to have rules evaluated, and which can be skipped. The effective use of Cognos TM1 feeders is essential for making your rules efficient and able to avoid combinatorial explosion.

Cognos TM1 provides the use of feeders to identify which cube cells need to have rules evaluated, and which can be skipped. The effective use of Cognos TM1 feeders is essential for making your rules efficient and able to avoid combinatorial explosion.

Combinatorial explosion!

In mathematics, a combinatorial explosion describes the effect of functions that grow very rapidly as a result of combinatorial considerations.

Wikipedia

A simple example that illustrates the power of the sparse consolidation algorithm is to consider the consolidation value (8433) at the intersection of total regions and surfboard lengths. To calculate the total, you must add up every possible cell which might total 119. However, if you add only the cells with non-zero values, the number of components to add may drop down significantly.

Dissection of Cognos TM1 Rules

Cognos TM1 Rules is nothing more than calculation statements. These calculation statements define how to compute values in the cells of the cube to which the rules are assigned. Usually, every rule you create will require one or more corresponding feeders. Feeder statements, when used correctly, ensure that the correct values are calculated by your Rules and can significantly impact on the performance of your Rules. If you use feeder statements in a rule (and you should), the rule must also contain a SKIPCHECK declaration and a FEEDERS declaration. The SKIPCHECK declaration must immediately precede any calculation statements in the rule, while the FEEDERS declaration must precede the feeder statements.

Rules – they are only “calculation statements”…

A calculation statement consists of the following:

  • Area definition
  • Leaf, consolidation, or string qualifier
  • Formula
  • Terminator

next time, a deep dive into Cognos TM1 rules!

Antoninus: Are you afraid to die, Spartacus?

Spartacus: No more than I was to be born.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jim Miller

Mr. Miller is an IBM certified and accomplished Senior Project Leader and Application/System Architect-Developer with over 30 years of extensive applications and system design and development experience. His current role is National FPM Practice Leader. His experience includes BI, Web architecture & design, systems analysis, GUI design and testing, Database modeling and systems analysis, design, and development of Client/Server, Web and Mainframe applications and systems utilizing: Applix TM1 (including TM1 rules, TI, TM1Web and Planning Manager), dynaSight - ArcPlan, ASP, DHTML, XML, IIS, MS Visual Basic and VBA, Visual Studio, PERL, Websuite, MS SQL Server, ORACLE, SYBASE SQL Server, etc. His Responsibilities have included all aspects of Windows and SQL solution development and design including: analysis; GUI (and Web site) design; data modeling; table, screen/form and script development; SQL (and remote stored procedures and triggers) development and testing; test preparation and management and training of programming staff. Other experience includes development of ETL infrastructure such as data transfer automation between mainframe (DB2, Lawson, Great Plains, etc.) systems and client/server SQL server and Web based applications and integration of enterprise applications and data sources. In addition, Mr. Miller has acted as Internet Applications Development Manager responsible for the design, development, QA and delivery of multiple Web Sites including online trading applications, warehouse process control and scheduling systems and administrative and control applications. Mr. Miller also was responsible for the design, development and administration of a Web based financial reporting system for a 450 million dollar organization, reporting directly to the CFO and his executive team. Mr. Miller has also been responsible for managing and directing multiple resources in various management roles including project and team leader, lead developer and applications development director. Specialties Include: Cognos/TM1 Design and Development, Cognos Planning, IBM SPSS and Modeler, OLAP, Visual Basic, SQL Server, Forecasting and Planning; International Application Development, Business Intelligence, Project Development. IBM Certified Developer - Cognos TM1 (perfect score 100% on exam) IBM Certified Business Analyst - Cognos TM1

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram