Category Archives: TM1

Achilles’ heel of TM1: Daylight savings time (DST)

Once again it’s the time of the year (March 31st) when we shift into summer time (or daylight savings time (DST)). That’s great: days are getting longer and you start feeling that the summer really is near. The bad thing is if you happen to have a TM1 production environment somewhere and you have scheduled chores to execute at specific point of time. The thing is: TM1 is unable to handle daylight savings time correctly. It still amazes me how this is possible? In other words how they haven’t made any fixes to correct this? Maybe it’s just so deep deep deep inside the legacy business logic or something. Anyhow, the point is you’re on your own and have to deal with this issue yourself.

So, after Easter Monday remember to go through the chore schedules in your TM1 environment and reschedule them to start up at correct time.

For more information, please check out these:


TM1 9.5.2 crash course: Beware of the LONG-function

During the last few weeks we’ve faced some serious problems with one of our TM1 production servers. The production server instance kept crashing on daily basis and without giving any clear reason why. We arranged surveillance by using Windows’ Performance Monitor and collected some system data but found nothing suspicious there either. The only clue were the last lines in tm1server.log just before the server crashed:

tm1server.log just before the crash

So, instead of trying to find a needle in a haystack we went through the rules of the last mentioned }ElementAttributes cubes and found out that in three of them a built-in-and-safe-sounding function LONG was used. At the time of development it may have been a good idea but nowadays at least with version 9.5.2 it is definitely a no-go: PM38586: LONG FUNCTION IN TI CAUSES TM1-SERVER HANG/INSTABILITY

IBM’s recommendation is to upgrade to version 10.1.0 but that was not an option for us at the moment so we needed to find a workaround. Now after fixing the rules with a workaround that avoids using the LONG function seems to have done the trick: production server is stable again and does not crash. So, my strong recommendation is not to use LONG function in rules if you are running TM1 version 9.5.2 or lower.

TM1 dimension hierarchy levels not showing up in Cognos BI Reporting Studio

Being still a “TM1-freshman” I find myself facing some mysterious problems specially when it comes to the integration part of TM1 and Cognos BI. Recently we made updates to customer’s production environment. We copied new dimension and cube files and the associated {-prefixed control cube files from test environment. Cubes were also used by many reports in Cognos BI and everything else seemed fine but the hierarchy levels were missing:

Dimension levels not showing up in Report Studio

Normally updating the reporting package from Framework Manager does all the tricks but this time it wasn’t enough. It’s a relief to have a community like TM1 Forum where hundreds of professionals gather up and offer their help. A quick search there and I found out that there is a special control cube called {HierarchyProperties and from there you can choose whether to publish the dimensions with level information or not:

{HierarchyProperties cube

So I thought adding the level names there and once again refreshing the reporting package I’ll do the trick but there were still no levels showing up. Going through more threads I discovered that there is also a special TI function called RefreshMDXHierarchy() that one should execute after making changes in the control cube. Finally that missing function call did the trick and hierarchy levels appeared in Report Studio! One more thing to remember in the future …

Edit 19.9.2013: Make sure you ran first RefreshMDXHierarchy(‘ ‘) and then again RefreshMDXHierarchy(‘<your_dimension_name’)

Edit 18.11.2013: IBM released a new technote regarding this same matter. According to it one must ensure also the following things:

  • Determine whether CQM or DQM (Compatable Query Mode or Dynamic Query Mode) is being used in BI.
  • If using CQM, navigate to CognosInstallDIR\data\cqe\RTModels and remove everything within it (folders and files) so that there is an empty RTModels folder.
  • If using DQM, then navigate to CognosInstallDIR\data\mfw4j\PMCs and remove everything within it (folders and files) so there is an empty PMCs folder.

NOTE that you will need to stop BI to perform the actions above.
Alternatively, try republishing the package via FM.

Managing hierarchies being used in IBM Cognos TM1 – precautions

TM1 doesn’t like duplicate names in hierarchies

IBM Cognos TM1 is an excellent tool when it comes to planning, budgeting and forecasting. It also has hierarchy management capabilities but often you want to import hierarchies and data from an enterprise MDM solution such as Master Data Services (MDS) in SQL Server.

When  importing hierarchies and data to TM1 one must be careful with the business codes, ids and names of the entities being imported to TM1 since it has an interesting “limitation” (or feature, depends on from who you ask): all the elements within a dimension must have not only a unique id but also a unique business code and a unique name. It means that the following cost center source data example cannot be imported because the elements have duplicate names (although the business codes differ):

Type Code Name Parent
Consolidated 1000 Administration
Leaf 1 General 1000
Leaf 2 Purchasing 1000
Consolidated 2000 Production
Leaf 3 General 2000

To prevent situations like this and prevent having problems when importing data to TM1 one must be sure that there are no duplicate names or codes in the source data. In MDS this situation can be handled nicely by using MDS business rules.

Deploy Business Rules to prevent duplicates

In MDS you can define business rules to automatically keep your data in shape and also make some automatic processing to your data if wanted. Now we want to prevent duplicate names in the cost center data (codes are automatically unique so we don’t need to think about them).

Force the cost center names to be unique

Force the cost center names to be unique

Now that the business rule is defined and published we can apply it to the data itself:

Apply the business rule to existing data

Apply the business rule to existing data

As we can see two cost centers have the same name “General” and that violates the business rule definition.

Two cost centers have the same name

Two cost centers have the same name

The red flag indicates that there is a problem with the data and it needs to be fixed. You can also define business users to have notification emails when the validation rules fail so that someone can instantly fix the issues before they are causing errors in the downstream systems such like TM1.

After the the names are fixed and the business rules are applied again the situation is OK and the data meets the quality needs of TM1 and its ready to go!

No duplicates exist - the data is good to go

No duplicates exist – the data is good to go

Cost center hierarchy after fixing the data

Cost center hierarchy after fixing the data

Extra notes

Although we can prevent duplicate names within leafs and consolidated members independently the business rules cannot be used to prevent them in both simultaneously: you can still have leaf member with name “General” and a consolidated member with name “General”. And that’s also bad for TM1.

In most cases the hierarchy data goes first into a data warehousing system before it’s loaded to TM1. If that’s the case you can brute force the uniqueness of the names by storing both the consolidated and leaf members into a single table and then defining a unique constraint on the name column. That will keep your data well-formed. It’s not the most beautiful way to do it but a very effective one. Let’s hope that we can tackle this situation also in the future versions of MDS.