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:
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://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.
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.
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.
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.
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) )
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.
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.
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:
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):
Oook, that was a long post, but will do as a starting point. I might return and clarify it as this project moves along.