TM1 cube > Cognos Report Studio > BI Report

[tosca1978]

Hi all,

Using 9.5.2.

Before you read I will apologise now, I do not have any prior experience with Cognos Report Studio so my definitions/terminology may be away off. We have an experienced BI developer here so don't worry - I'm not trying to do the BI side myself.

The scenario:

We have a system called Maximo that captures all of our committed costs. It does this task well, however trying to get a report out of this system is very painful. All of the data is currently saved to CSV files and loaded into SQL tables in our data-warehouse. We then have a TM1 model which has a much bigger scope, but a small part of it is bringing this commitment data in and holding it in a Commitment cube. This is a 12 dimension cube (1 time, 1 measures and 10 other) designed around the business requirements for how they wish to slice and dice all of their data. The cube size itself is not a problem for TM1. The data held in it is at a transactional level which again is a user requirement. Because this is essentially the only way to view the data in Maximo the requirement is to see transactional level data. A drill through to the SQL database was put forward and rejected.

We have an active form which is then published on TM1 Web which allows the users to view consolidated elements, drill down to leaf level elements etc. Apart from the Time dim being a page and the measures dim on the column, the otehr 10 dims were stacked up in the rows to give full visibility of the data. The response time is good.

However, we wanted to see if we could replicate this Commitment report in BI. As a business we already use BI on top of Cognos Analyst cubes and directly from SQL data-warehouse. This would be pretty much the first time that we were attempting to report directly from a TM1 cube.

The Problem:

In a nutshell the problem seems to be zero-suppression. The active form in TM1 handles this very well wish good response times. When trying to replicate the report in Report Studio we found the follwing:

1. If referencing a consolidated element in each of the 10 dimensions stacked up the report query ran within seconds and a report was ready.
2. If bringing "All" elements in on the same 10 dimensions the report query ran within seconds and a report was ready (although useless as the cube is so sparse - so is the report).
3. When attempting to suppress zero's the BI developer tried 2 different methods:
3a). Writing an MDX query for each cross point. This took about 15 mins to produce a report.
3b). Suppress zero's directly on the dimension (sorry I'm not sure what this is called). For one small dimensions (of the ten) this took a few seconds. For a big dimension it took a minute or so. For 3 dimensions together this took about 10 minutes to create a report.

Our conclusion:

Between the BI developer and I, the conclusion that we drew was that Report Studio does not handle querying transactional level data from a cube and zero suppressing it very efficiently. If the report was querying the SQL table directly it would be very quick. However to achieve this rolling up/drilling down the consolidated data would have to be stored in the SQL table too (which it currently isn't).

Any advice:

As always any advice would be greatly received. I appreciate that the first thought that might go through your mind is that transactional level data should not be held in a TM1 cube anyway or why are we trying to create a BI report from it!

Cheers

[Michel Zijlema]

Hi tosca1978,

I guess you're using Cognos BI 8.x to connect to TM1. You can forget about the zero-suppression on multiple stacked columns in this version - it will not perform. The best way to do this is to use the MDX filter on zeroes on the columns (or innermost column), but I think the performance is degrading exponentially with every column you stack (of course depending on size and sparsity of the regarding dimensions).
The best advice I can give is to upgrade Cognos BI to 10.1 and use this in combination with TM1 9.5.2 or newer. Then you can use Dynamic Query Mode in your framework package and this will improve performance considerably.

Michel

[tosca1978]

Hi Michel,

Many thanks for your reply. I have passed on your tips to the BI developer who was away last week. Hopefully next week we will put our heads together and have another go.

I know that we have both version 8 and version 10. However I think part of the problem is that Report Studio is the only product that has been upgraded to 10, so the Framework Manager is still an old version.

If we have any success or new findings next week using 10 with Dynamic Query Mode as you suggested I will post an update.

Thanks again for your help.

[ParisHilton]

Hi there,
are you reporting in real time?
Is a copy cube with the zero supression already done an option?

P

[PlanningDev]

It shouldn't matter that you have FM as 8.xx although this seems dangerous and odd. You would be better to install FM twice, one for each version. However 8.xx FM packages should be upgraded when moved from 8.xx to 10.xx environments.

Also, I can confirm zero supression works in 9.5.2 FP1 and BI 10.1.1. It should work on 10.1 BI as well. DQ was the only solution that easily worked for analysis studio zero supression although can create a measure filter in Analysis Studio that will do this, its just annoying as it doesn't stay if you keep switching rows/columns.

Also, nested rows/columns can be tricky for zero suppression when passed to TM1 from BI. Highly unlikely that with larger dimensions and nested rows/columns in a crosstab that you will be able to get the same performance as in TM1


Good luck

[AlexanderZ]

Try this:

Change settings to two parameters below in <cognos_install>\configuration\qfs_config.xml file

<parameter name="UseProviderCrossJoinThreshold" value="1000"/>
UseProviderCrossJoinThreshold introduces crosstab row / column edge member retrieval optimisations where the nested edge member crossjoin results in a large member set. The optimisation attempts to eliminate members from the crossjoin which would not return data. The established value is the crossjoin threshold from which thes optimisations start to be invoked.

<parameter name="UseNonEmptyOnDataQueryThreshold" value="1"/>
UseNonEmptyOnDataQueryThreshold ensure the NON EMPTY clause is added to an MDX query.

[tosca1978]

Hi All,

thanks for your responses.

AlexanderZ - we changed the qfs_config.xml file like you said. We didn't notice any change in performance.

Michel, we are bow using Cognos 10 (BI) and TM1 v10.1.0 and have re-published the package with Dynamic Query Mode turned on. However, we are getting the following error message:

SetCogMDXNestContextUsingSetAlias cannot be applied, the provider does not support MDX set aliases.

I think this is because we have MDX filters on the crossjoined levels.

Can you tell me - is it possible to use Dynamic Query Mode and MDX filters on the crossjoined levels?

Many thanks

[AlexanderZ]

I dunno about crossjoined levels, but here is pretty good article on DQM: http://www.ibm.com/developerworks/data/ ... ml?ca=drs-

Also Dqm can be enabled in Framework manager when publishing package: