Topic
  • 4 replies
  • Latest Post - ‏2012-04-25T20:38:06Z by DanielWagemann
SystemAdmin
SystemAdmin
1959 Posts

Pinned topic Suppressing zeroes in Cognos reports against TM1 data source

‏2012-04-20T18:17:07Z |
I'm trying to build a Cognos report off a TM1 cube using Cognos version 10.1.1 and TM1 9.5.2. The report attempts to display two dimensions and a measure in a crosstab. Each dimension contains 20k branches (a Top level and 20k branches). If I filter one dimensions down to 100 branches and then try to show all values on the other dimension where the measure has a non-zero value the report does not respond. In theory there are 2 million possible combinations of the 100 times the 20k. But there is actually data on only 5k rows for those combination of dimensions and when I filter on another dimension only 200 rows that should return. I turned on suppress zeroes in the report but I suspect it is retrieving all data from TM1 regardless of whether the measure is zero then doing the filtering in Cognos.

Is there a way to adjust the MDX Cognos is sending so TM1 will only return data with a non-zero measure? Or is there some other method of making this report respond better in Cognos?

This is the existing MDX the report is producing:

WITH MEMBER job role.job role.@MEMBER.COG_OQP_INT_m4 AS 'AGGREGATE(COG_OQP_INT_s2, client_rep_measure.client_rep_measure.@MEMBER.percent_of_rep_gt_2_assigned_to_client)', SOLVE_ORDER = 0 MEMBER client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_USR_percent_of_rep_gt_2_assigned_to_client AS 'client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_INT_m3', SOLVE_ORDER = 2 MEMBER client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_INT_m3 AS 'IIF(client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_INT_m1 > 0, client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_INT_m2, NULL)', SOLVE_ORDER = 2 MEMBER client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_INT_m2 AS '(job role.job role.@MEMBER.COG_OQP_INT_m4, client_rep_measure.client_rep_measure.@MEMBER.percent_of_rep_gt_2_assigned_to_client)', SOLVE_ORDER = 0 MEMBER client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_INT_m1 AS 'COUNT(COG_OQP_INT_s2, INCLUDEEMPTY)', SOLVE_ORDER = 0 SET COG_OQP_INT_s2 AS 'FILTER(job role.job role.MEMBERS, job role.job role.CURRENTMEMBER.PROPERTIES("MEMBER_KEY") = "SW Client Leader")' SET COG_OQP_INT_s1 AS 'CROSSJOIN({client.client.@MEMBER.AA Top 100}, {sales rep.sales rep.@MEMBER.Sales Rep (All)})' SELECT {client_rep_measure.client_rep_measure.@MEMBER.COG_OQP_USR_percent_of_rep_gt_2_assigned_to_client} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), COG_OQP_INT_s1 DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1) FROM client_rep

I'm able to build a cube view in TM1 with this same combination of dimensions and filtering and it returns in seconds.

I appreciate any help in solving this issue.
Updated on 2012-04-25T20:38:06Z at 2012-04-25T20:38:06Z by DanielWagemann
  • DaveCummings
    DaveCummings
    72 Posts

    Re: Suppressing zeroes in Cognos reports against TM1 data source

    ‏2012-04-20T18:25:58Z  
    Yes you can improve how cognos talks to the TM1 server to only request non empty data by following this page:

    http://www-01.ibm.com/support/docview.wss?uid=swg21505253

    The setting you will be changing is in this file:

    <Cognos_BI_install_location>/configuration/qfs_config.xml

    Under the provider TM1OlapODPXQE for Dynamic Query Mode models and TM1OlapODP for the compatible models (probably not the case as you're on 9.5.2). Set UseProviderCrossJoinThreshold to 1000.

    Then enable suppression for your report to take advantage of this.

    Thanks
    Dave
  • DaveCummings
    DaveCummings
    72 Posts

    Re: Suppressing zeroes in Cognos reports against TM1 data source

    ‏2012-04-20T18:53:43Z  
    Yes you can improve how cognos talks to the TM1 server to only request non empty data by following this page:

    http://www-01.ibm.com/support/docview.wss?uid=swg21505253

    The setting you will be changing is in this file:

    <Cognos_BI_install_location>/configuration/qfs_config.xml

    Under the provider TM1OlapODPXQE for Dynamic Query Mode models and TM1OlapODP for the compatible models (probably not the case as you're on 9.5.2). Set UseProviderCrossJoinThreshold to 1000.

    Then enable suppression for your report to take advantage of this.

    Thanks
    Dave
    Hi, looks like you are using compatible packages and not dynamic query mode (MDX shows this). Dynamic query packages would be ideal for TM1 9.5.2 if you can, but the same optimization is in both as outlined on that page and you would be modifying the settings under TM1OlapODP.

    Thanks
    Dave
  • SystemAdmin
    SystemAdmin
    1959 Posts

    Re: Suppressing zeroes in Cognos reports against TM1 data source

    ‏2012-04-25T19:59:25Z  
    Hi, looks like you are using compatible packages and not dynamic query mode (MDX shows this). Dynamic query packages would be ideal for TM1 9.5.2 if you can, but the same optimization is in both as outlined on that page and you would be modifying the settings under TM1OlapODP.

    Thanks
    Dave
    Hi, thanks for the responses. I was able to change the package to dynamic query mode and adjust the configuration setting. Unfortunately we don't have 'Cognos TM1 version 9.5.2, hotfix 7 or later' installed so changing these settings did not effect performance. I checked with the TM1 administrator and he claims this hotfix was tested by another team and it did not improve the performance of Cognos reporting so it was not installed. I suspect that group may not have had the configuration settings adjusted when they did the test. I'm being told to wait for 9.5.2 FP2.
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Suppressing zeroes in Cognos reports against TM1 data source

    ‏2012-04-25T20:38:06Z  
    Hi, thanks for the responses. I was able to change the package to dynamic query mode and adjust the configuration setting. Unfortunately we don't have 'Cognos TM1 version 9.5.2, hotfix 7 or later' installed so changing these settings did not effect performance. I checked with the TM1 administrator and he claims this hotfix was tested by another team and it did not improve the performance of Cognos reporting so it was not installed. I suspect that group may not have had the configuration settings adjusted when they did the test. I'm being told to wait for 9.5.2 FP2.
    Can you please confirm that while using the compatible query mode:

    You have made a back up copy of the qfs_config.xml

    Within the <provider name="TM1OlapODP" libraryName="tm1odp" connectionCode="TM"> changed the parameter "UseProviderCrossJoinThreshold" from 0 to 1000.

    Within the <provider name="TM1OlapODP" libraryName="tm1odp" connectionCode="TM"> changed the parameter "UseNonEmptyOnDataQueryThreshold" from 0 to 1.

    Saved the changes.

    Restarted the IBM Cognos BI service. (If you have multiple report servers, this change will need to be made on each one.)

    Opened the original report in IBM Cognos Report Studio.

    Within the Query Explorer, set the Suppress Query Property to NULLS from blank for the affected query.

    and it had no affect on the generated MDX posted earlier in this thread.

    I get the impression that the TM1 version requirement only applies to the Dynamic Query Mode and the above should affect the MDX in some way in Compatible Query Mode, regardless of the TM1 version. Although a quick test of this on your end should confirm this.