Monthly Archives: June 2012

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.

Advertisements

RSAAD – “Reporting Services as a Dashboard”

Some might think that the SQL Server Reporting Services (SSRS) is only for creating static and standardized reports with simple charts like the following picture describes:

This can be the most common feeling about SSRS but I can tell you that is not the case. Thanks to the acquisition of Dundas chart components SSRS has to provide fully parametrized, nice-looking and fresh Gauge panels and other chart components that can be used to create dashboards without making investments to platforms such as SharePoint PerformancePoint Services as they can be used straight from the Reporting Services portal just like any other report. Here is one example from my projects, a weather conditions dashboard made with SSRS:

Weather conditions dashboard made with SSRS

Weather conditions dashboard made with SSRS

Once in a while you hear business deciders struggling with how they want to have some simple dashboards where they can see in one glance how the business is performing. The problem often is that people think that they need to make (big?) investments in a specific dashboard tools or bigger platforms that provide the dashboard capabilities. That is not the case because with the latest SSRS Gauge panels and charts you can provide fresh insights to your data and have the dashboard look-and-feel. They can also be easily integrated to existing sites and portals.

SSRS Gauges collection

SSRS Gauges collection

I’m not telling you to get rid of or totally ignore products like SharePoint PerformancePoint Services etc. but often you can also get nice results without them. So, next time when you hear someone telling you about how one needs to invest lots of money to get dashboard capabilities you should first see what SSRS has to offer and see if it already fits to his needs.