IBM Support

PM98021: COGNOS BI 10.2.1 CROSSTAB REPORT USING ASYMMETRIC QUERIES AGAINS T TM1 RUNS SLOWER COMPARED TO PREVIOUS VERSION OF COGNOS BI

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • With BI 10.2.1, the query engine is optimized to work with
    symmetric queries only where asymmetric queries can potentially
    have poor performance compared to previous releases. This issue
    has been observed only against TM1 datasources in DQM mode.??An
    example of a symmetric query would be a report that only
    contains nested members on an edge with no siblings, such as:?
    
               Column#1:  <#Sales Price Sum#>?ROW#1:
    <#children(CANADA)#> <#children(2010)#>
       <#1234#>?ROW#2:
    <#children(2010)#>                             <#1234#>?ROW#3:
    <#children(CANADA)#> <#children(2010)#>
       <#1234#>?ROW#2:
    <#children(2010)#>                             <#1234#>??It's
    symmetric in that every member in the query intersects with each
    other.??Asymmetric queries can occur when sibling sets are
    introduced on the edge, such as:??
                                                    Column#1:
    <#Sales Price Sum#>?ROW#1: <#children(CANADA)#>
    <#children(2010)#>                             <#1234#>?ROW#2:
                                      <#children(2010)#>
                    <#1234#>?ROW#3: <#children(JAPAN)#>
    <#children(2011)#>                             <#1234#>?ROW#2:
                                      <#children(2011)#>
                    <#1234#>??Where not every member in the query
    intersects with each other.  In this case, the asymmetric part
    is from the children of Canada intersecting with the children of
    2010, whereas the children of Japan intersect with different
    members from 2011.  So Canada does not intersect with 2011 and
    Japan does not intersect with 2010 in the report.??The reason
    this is a problem is BI will query TM1 for the data with a
    single simplified symmetric query.  So even though the
    asymmetric report does not request Japan and 2010 intersections,
    the query we to TM1 will ask for those intersections.  This is
    added overhead and extra calculations are done on TM1.??We are
    working on a future release of BI that will work with asymmetric
    queries more efficiently so extra data is not computed in this
    way.?
    

Local fix

  • These are only potential workarounds. They may not be applicable
     to every report where asymmetric queries are used.
    Workaround #1:
    When there are rows in the crosstab report that do not have a lo
    t in common with each other and thus constitute an asymmetric qu
    ery, the report can be split up into multiple different crosstab
    s, one for each row in the original report.
    This way each query will be symmetric and no extra data will be
    calculated in TM1.
    
    Workaround #2:
    The second option is to wrap a member() function around the memb
    er in the first row.  This will force the engine to handle the t
    hat member row independently from the rest of the edge as though
     it were in it's own query.  This leaves the subsequent rows as
    being asymmetric, but there are not many additional intersection
    s when combining these into a single query so there is less of a
     performance impact.  Most of the performance hit comes from the
     first row.  This is slightly slower than the first workaround,
    but in this case the report output will be the same as the origi
    nal.
    
    Please note this workaround is only temporary for this release o
    f BI.  This workaround will also cause less memory to be used in
     BI and allow the query to run in seconds once TM1 has calculate
    d the values from a cube update.  The first time the query is ru
    n after a cube update should be closer to the previous release o
    f BI.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See error description.                                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to IBM Cognos Business Intelligence 10.2.1 Fix Pack  *
    * 2                                                            *
    ****************************************************************
    

Problem conclusion

  • Code Fix
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM98021

  • Reported component name

    COG ADMINISTRAT

  • Reported component ID

    5724W12AD

  • Reported release

    A21

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-09-27

  • Closed date

    2014-01-31

  • Last modified date

    2014-01-31

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    COG ADMINISTRAT

  • Fixed component ID

    5724W12AD

Applicable component levels

  • RA21 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCHM94","label":"Administration and Configuration v11x"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.2.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
31 January 2014