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

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 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