We’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.