Cognos BI reporting on TM1

6843495776_a7b40784de_z

6843495776_a7b40784de_zWe’re about 70% done in the project that took 110% of my time lately and it’s all about using Cognos BI on top of TM1 (with a bit of DWH in Cognos DataManager,  that’ll be a separate post). Good time to write down some notes on how to develop efficient reports with TM1 as a data source.

There’s already quite a lot of material around, so I’ll add as many links as possible. Unfortunately, dimensional reporting isn’t covered that well in both official documentation and trainings, so I’ll try to cover some gaps.

Reporting on top of TM1 is dimensional reporting, so main approaches are the same for any OLAP datasource (MDX became some sort of standard nowadays).

It’s always quite hard to switch from relational reporting to dimensional, but once you do relational reports (SQL) will look quite cumbersome compared to dimensional ones.

Developing dimensional reports usually requires a few common approaches:

MUN construction

Member Unique Name (or MUN) is the identifier Cognos uses to address any dimension element. It always contains reference to cube you’re using, dimension and, most likely, hierarchy and element parent. Why do you need to know this?

Because using Cognos macro functions (## notation), you can “construct” these MUNs at runtime. By far the most common example is using #prompt()# function, that will return you the value of given prompt control. Using this function you can create the MUN of the selected dimension element (selected Month, Product, Department, etc) directly without using any filtering in the query. This is the very essence of dimensional approach, you’re explicitly selecting a slice of cube you need to return instead of searching / filtering it.

MUN construction is thoroughly covered in following posts:

http://www.canrocksolutions.com/2011/10/28/tm1-cognos-10-bi-integration-know-your-muns/

http://cognospaul.com/category/macros/ — Paul wrote heaps on the topic both on his blog and on cognoise, read it up )

Note the substitute macro function, it allows you to avoid using if then else statements in MUN construction.

Slicers instead of filters

The same “select a portion of cube” instead of searching / filtering for it. You should always use slicers instead of detail / summary filters.

Filtering should be only used in the form of filter function that selects only members of set that satisfy the condition you defined. Very useful for zero suppression or any other flag-based custom show/hide scenarios. TM1 rules are pretty cool in this aspect, I define quite tricky flags in cube rules themselves and then just use filter(members flag =1)

Paul wrote about detail filters vs slicers here and here.

No string manipulations, only report expressions

Very true for TM1 (especially due to  this APAR), you can rarely do any string manipulations over OLAP datasources. But most of the time report expression would be quite enough to do the trick (cut a few characters out of member caption, rename something).

See Paul’s post.

Use dimensional functions

Children, members, ParallelPeriod, PeriodsToDate and etc are one of the best things ever and can make your life very easy, make sure you’ve at least glanced through the list.

TM1 adds a quite painful twist: all TM1 dimension hierarchies are treated as unbalanced / parent-child by default, so any level based function (PeriodsToDate, ParallelPeriod, Cousins, etc) wouldn’t work unless you set up }HierarchyPropertiesCube “levelling” the dimension. It’s possible to do in time dimensions, but unlikely applicable in any other, most TM1 dimensions are unbalanced.

On the other hand, TM1 is very good at calculations, so you’ll most likely have all the YTD, MTD and other time-based calculations defined in the cube itself (if not, try adding them there, it’s easier and will work faster), so you’ll just need to do your MUN construction properly to reference July YTD when user picks July as month.

Use “advanced” dimensional functions / approaches

I’ll  try to highlight some techniques I’m using to illustrate dimensional approach to report design.

I have a requirement for report-calculated measure that would be one formula (A) for actual months and something else (B) for forecasted ones.

If A and B are calculated data items, I use intersect with current month to make sure that I get proper results for each month, so the formula becomes something like:

tuple(
[A],
item (
INTERSECT (currentMember([Cube].[Months]), [actualMonths])
,0)
)

currentMember returns current month in formula context

intersecting it with [actualMonths] set will return currentMonth if it’s actual and none if not, zeroing out results for forecast month.

item function converts a single element set (current month for actual months) into a member that we can use in tuple expression.

If you add B formula for forecast months as

tuple(
[B],
item (
INTERSECT (currentMember([Cube].[Months]), [forecastMonths])
,0)
)

you can just sum them up and get desired result.

 

This approach sometimes doesn’t work with Dynamic Query Mode over TM1 (see below for more DQM caveats), so another, even more “funny” approach is to construct a measure dimension member that always equals 1 and then intersect it with required months set (so that it’ll always return 1 for actual months and 0 for forecast) and multiply required formula on this “flag” member.

Formulas look like this:

tuple(
member(1, 'a', 'a',[Cube].[Cube Measure])
,item(
INTERSECT(SET(currentMember([Cube].[Scenario].[Scenario])),
SET([actualScenarios]))
,0)
)

member function in this formula creates a new measure called ‘a’ with 1 as member formula.

Some TM1 specific notes:

A very good general do’s and don’ts  list:  http://www.lodestarsolutions.com/lodestar/?p=435

Just a remark: You can combine 2 cubes as a list, but not as cross tab or chart

You can do a bit of TM1 attribute manipulation: display attributes, filter on them, but you can’t use an elements attribute to get another member (at least I couldn’t) and you can’t do any string manipulation with attributes.

DQM vs CQM

Another major question is whether to use new and fancy Dynamic Query Mode (DQM) or good old proven Compatible Query Mode (CQM).

Pro’s for using DQM are:

  • performance. Complex reports are about 20-30% faster. Small ones (<5s) perform roughly the same with or without DQM
  • caching. CQM doesn’t cache any results, whereas you can notice a bit of caching happening with DQM (second user opening the same report will get slightly faster results with DQM)
  • bright new future. DQM is the way to go and should get only better, so it’s more reliable to develop in DQM to avoid converting reports in future

At some point in the project I’ve converted about 85% of reports to DQM, leaving only the most complex formula & fast enough ones on DQM.

Con’s are more numerous and really painful and are mostly due to  way-way-way more strict parser (as of 10.1.1 fp1):

  • nested tuple expressions are impossible with DQM. CQM allowed you to tuple your tuples even more ), making expressions heaps neater and more readable (you can start with base tuple formula and then add layers of tuples on it to get proper filtering). DQM doesn’t allow that, so I had to unwrap a lot of complex formulas and “flatten” them into a single monstrous expression to achieve the same results as in CQM
  • if then else formulas returning MUNs can’t be used in tuple statements. A lot more tricky MUN construction is required to cater for this (substitute macro function saves the day)
  • nested dimensional functions return unpredictable results. I had an expression like children(firstChild(member)) in CQM report and while converting to DQM it didn’t give any error, just returned some completely messed up items. Again, MUN construction allows to avoid nesting functions, I’ve constructed this “firstChild” element.
  • general error handling. If something doesn’t work in DQM, errors won’t tell you what or you wouldn’t even get the error (as in above), which is really maddening.

 

Oook, that was a long post, but will do as a starting point. I might return and clarify it as this project moves along.

  • Jesper Bjerre

    Hi Yuri

    Nice post. Can see that you have worked quite a bit with BI reporting on top of TM1 :-)

    You wrote: “but you can’t use an elements attribute to get another member (at least I couldn’t)”.

    I have been working on a project, where we needed an element attribute to get another member (in this case it was
    the currency). We ended up with a solution where we created a master-detail relationship and used the attribute value from the master as a parameter in a prompt macro in the detail Query to get the correct member.

  • ykud

    Hi Jesper!

    Thanks for the idea, it’s a very good one ) I’m currently converting all these reports to Active Reports and they are all about master-detail relationships, I’m already seeing them at the back of my eyelids.

    What I meant was more of a direct in-query statement, like if you have a MUN of [selectedMonth] and there’s an attribute of YTD pointing to YTD element in dimension, I would love to write something along the lines of (filter(members(monthDimension), currentMember(monthDimension)=[selectedMonth].[YTD]])) and get YTD member straight away. This would generate a single / fast MDX statement, whereas master-detail query linking fires 2 separate queries, pulls all the data from both queries to Cognos BI Server and joins them locally, which can cause performance issues sometimes.

    What I’m also doing in a few places already is using the parameter maps to pull the attribute value and then use it in MUN construction. If attributes don’t change often (like currency for country or account), it might be a viable workaround. Parameter maps can only be sourced from relational datasource, so this is where our small data mart becomes very handy )

  • kumar

    It is very impressive post.The cognos BI using some techniques like reporting onTM1 and developing dementional reports in projects.

  • cognosbuddy

    Thanks for sharing Yuri! Info on cubes and how dqm helped or didn’t make very much of a difference is good to know.

  • Vlad

    Hi Yuri, is there anyway to filter a list report (built on TM1 cube) by string measure element or string filtering is limited to attributes only? Can you give me an example of expression to filter by attribute using “position” function? Thanks in advance

  • ykud

    Hi Vlad,

    You can filter by string measures using the filter function in query item. Reference the string measure in the filter expression.

    Filtering by attribute can be done via filter function as well, just write [dim].[attribute] = ‘xyz’ in the filter expression.

    Cheers,
    Yuri

  • rmonks

    Nice write up!