TM1 Cube Package-Report development Issue

[sathishh.mk]

Hi All,

We published TM1 cube in our Cognos 8.4.1 BI environment and tried to build new report on top of this package.
So here i am getting cross join records are coming in the Report.
Means getting more than expected records..

Please help me on this how to filter unnecessary records in the report.

Below are examples for the issue:

In my TM1 cube,
I have one dimension which is having around 13000 elements,let us say country_dim having elements like India,UK,USA,Canada....etc
And one more dimension time_dim having like 10 -15 elements like 2011 Q1,2011 Q2,2011 Q3 etc..
and one measure dimension having only two elements like Quantity and Revenue..

For Quarter 2011 Q3,the quantity and Revenue having some values only for India and UK..for USA there is no data for this Quarter.
and Canada having values for 2011 Q2 only there is no data for 2011 Q3 for Canada.

If I am building report on top this cube in Cognos ..I am getting below data,
That means the report is showing for all 13000 records of first dim as metrics values as 0 for Q3
In TM1 we are using Zero Suppression..so it is eliminating.
But Canada belongs to Q2 and remaining all belongs to Q3..if put filter in report as 2011 Q3 then Canada should not be display in the report.

Report Data:

Filter :2011 Q3

Country_dim Quantity Revenue
India 100 150
UK 200 250
US 0 0
Canada 0 0

Thanks in Advance for your help and time

Regards,
Sathish

[Marcus Scherer]

with your package you are connecting to a certain TM1 cube, not a certain view with zero suppression turned on or off. Cognos BI is flattening the cube and then putting it together again in the frontend. Cognos BI is even using a different MDX than TM1. If you need zero suppression this is out of the box available in Analysis Studio, as for Report Studio - which you are probably using - I have no experience. There should be a button also in 8.4. This is not a TM1 issue.

[PlanningDev]

I can't remember if this exists in 8.4.1 but your lists and crosstab objects should have a suppression setting. You can set this here but be warned that performance can suffer when using suppression. The reason is that untill you get the combination of BI 10.1 64 Bit and TM1 9.5.2 FP1 64 Bit and use of Dynamic Query mode you will be performing the suppression locally on the BI dispatcher in many cases. There are 2 settings in one of the BI .xml files that can help here as they control how many records TM1 will return in in each batch without TM1 performing the zero suppress.

If you don't have the suppress option on your list or crosstab then you have to either filter the measure itself or in some cases where the report is a little more complex you may actually have to use the filter() function in order get rid of the items in your dimension that have zeros.

[paulsimon]

Hi

I am not an expert in Report Studio by any means but I believe that there you need to set zero suppression on individual measures, eg Zero suppress where Balance is not zero. I think you do this on the Data Items within the Query objects.

However, that may not be the end of it. The problem we encountered was that BI was still requesting everything from TM1, completely ignoring TM1s ability to suppress zeroes, and its was then supressing zeroes within BI itself, which led to terribly slow performance.

I would contact IBM support. We are using Cognos BI 8.4.3 with TM1. There were a number of issues related to zero suppression. Several PMRs were raised which resulted in Hot Fixes for the Cognos BI tool, and a few on the TM1 side (TM1 itself is fine, but it needed fixes to support BI). I would contact IBM support and ask them to check for similar issues and send you the hot fixes (or whatever they call patches on the Cognos BI side). You may need to upgrade to BI 8.4.3.

We are using TM1 9.5.1 rather than 9.5.2 so it is possible that a few of the issues were fixed in 9.5.2, but I know that a number of other hot fixes came out for 9.5.1 after 9.5.2 was released. The majority of the issues were in BI rather than TM1.

Regards

Paul Simon

[LoganNSE]

Sathish,

Can't you just add a query-level filter like Quantity <> 0 and Revenue <> 0 - this will filter out all 0-value records.

Logan

[mce]

play with the following configuration parameters in your Cognos 8 BI server qfs_config.xml file. This file is under Configuration folder of your Cognos 8 BI server. These parameters help in passing zero suppression to TM1, rather than handling in TM1. But you may need to play with these parameters to find the best one for your cube(s).

Code: Select all

					<!-- Threshold at which the adapter will ask the TM1 server to reduce the size of a large cross join set by filtering out the empty tuples according to the measures in the query.  If the measure context is unknown when the cross join operation is evaluated, then the default measure wil be used to filter the set.-->
					<!-- The default setting of 0 disables this optimization.  A threshold of 10,000 is a sensible initial value to test.  Please note that the TM1OlapODP provider cannot process a set with more than four billion tuples locally. -->
					<parameter name="UseProviderCrossJoinThreshold" value="0"/>
					<!-- Threshold at which the adapter will ask the TM1 server to omit empty cells from the MDX data query result.  For sparse cubes, the request response time can greatly improve however some members with no associated data for the measure in context may be filtered out.-->
					<!-- The default setting of 0 disables this optimization.  A threshold of 10,000 is a sensible initial value to test.-->
					<parameter name="UseNonEmptyOnDataQueryThreshold" value="0"/>