<img src="https://trc.taboola.com/1278851/log/3/unip?en=page_view" width="0" height="0" style="display:none">

Little known TM1 Feature - Ad hoc Consolidations

April 11, 2018

In this blog, let me explain you about one of the hidden/unused feature from IBM Cognos TM1.

Before we start, let me ask you a question -

Have you ever come across below requirements or related issues while working with TM1?

  • Urgent requirement - Management needs new consolidation within a Canned report to meet their business needs.
  • An Ad hoc requirement from business to get new consolidation member at the earliest.
  • An alternative roll-up request from the Boss - for a better analysis using existing data.
  • Stakeholders request to have additional consolidations which may be part of ad hoc analysis.
  • An analysis of a new Product Line and its business with additional consolidations rolling up, which is not present in the system.
  • Analysis on what if some of my operating centers move out due to Organizational changes.
  • Any other related scenarios.

– if yes then I am sure you will enhance your knowledge.

The Default (Existing) Method

In these scenarios the first choice has always been to create/edit a roll-up and this would need a change to existing system and the next choice would have been to use excel formulae to build report as per Stakeholder’s expectation – editing existing report

Let’s try understanding dependencies and complexities to perform this change: -

  • If there are many source systems for TM1 and hierarchy is maintained at source, this change may have to be pushed to the following scheduled releases.
  • In big organizations where Business changes are maintained by different Vendors (Service providers) this small change may have a bigger impact and thus becomes a challenge to quickly implement.
  • If these changes are requested during critical month-end, Quarterly or during Year-End it will further get delayed.
  • If it is an Excel report, Excel formulae can be used but when the template is too big then there is a high chance of human error and thus demands a lot of time and effort.

As we now understand that this request though looks simple, but in actual the default method takes more time/ effort to meet the need of the hour.

The Hidden TM1 Feature - Another Method!

Now that we understand the difficulty or constraints in making changes to dimension, let me take your through another option which has not been explored by many.

Here I would like to take you through another option, which can be used to meet the immediate need with little (or) less effort with below example.

Example: -

Here is a simple example and this logic can be applied to any dimension with any number of elements.

Considering Month as a dimension which has a simple and flat hierarchy as shown below.


Now there is a requirement from stakeholders to see the data at consolidations at half year [1st, 2nd] and at quarters[Q1-Q4] to have a better understanding of data quarterly and half yearly.

Created public subsets Q1, Q2, Q3, Q4 and H1, H2, as show below.


We now need to Insert these subsets into existing subsets “FY”.

Steps: -

  • Open subset, here it is FY.
  • Click on Edit and Insert Subset. You will get a new pop-up, select H2 and
  • Click Ok.


Above mentioned steps were followed to get H1,Q1,Q2,H2,Q3 and Q4 into FY Subset and FY Subset will look like one below.


Let us now focus on how data looks while using these subsets.


Who can perform this hidden TM1 method?

Above suggested changes can be done by TM1 Admin, DataAdmin, Power Users, Business Users or any TM1 user even if the user is not a power user. A non-power User can also use this feature through another option which I would discuss more in my coming blogs. Stay tuned.

A key note:

One must remember that TM1 is a wonderful platform to empower management and end-users for their business needs and help them arrive at better long term strategic decisions across various departments/ functions. The design, development and implementation must be of high standard as you aim to remove inefficiencies, create something that is easy to understand, maintainable and adaptive for future development needs.


You may also like reading blogs “ What is IBM Planning Analytics Local ” , “IBM TM1 10.2 vs IBM Planning Analytics

Who are we?

· Octane Software Solutions is an IBM partner specialising in Business Intelligence.

· We provide our clients advice on best practices in Business Intelligence and scaling up applications to optimise your return on investment.

· The key services we provide to our clientele are Consulting, Delivery, Support and Training.

· For more details you can click here to find out more Octane Software Solutions.