IBM Cognos Proven Practices: Improving IBM Cognos Report Studio Master Detail Report Performance

Product(s): IBM Cognos 8; Area of Interest: Performance

This document describes an IBM Cognos Report Studio report design technique that can be used for master-detail style reports running against a relational data source.

Daniel Wagemann, Cognos Proven Practices Advisor, IBM

Daniel Wagemann is an IBM Cognos Proven Practice Advisor for Business Analytics in Canada. In his 11 years working with the IBM Cognos product Suite, he has established a vast understanding of all areas of an IBM Cognos deployment. His areas of expertise include course development, technical writing, consulting and customer support. His work can be found within almost all areas of the Proven Practices Site.



George Xu, Senior Developer, IBM

George Xu is an IBM customer advocate. As a senior developer with IBM Cognos 3LS organization for nine years, he has specialized in Cognos BI system scalability, stability, and performance issues. He has a broad knowledge of BI product family, including data access, presentation, content management, security, data modeling, and system optimization. With a master degree of engineering, he also received certification of Oracle DBA and PMP®. He was a development manager at Nortel.



Henk Cazemier, Senior Software Developer, IBM

Henk Cazemier is an IBM customer advocate. He is a senior software developer with IBM Cognos for 25 years. He is a specialist in relational query planning, working closely with the Framework Manager and the Cognos BI reporting products. Henk has worked on various performance and scalability related issues and has extensive knowledge on the translation of Framework Manager models into SQL to efficiently access the modelled data.



03 August 2011

Also available in Chinese Spanish

Introduction

Purpose

This document describes an IBM Cognos Report Studio report design technique that can be used for master-detail style reports running against a relational data source.

Applicability

The technique and product behaviour outlined in this document was validated using the Go Sales(Query) and the Go Sales(Analysis) package with the following releases:

  • IBM Cognos BI 8.4.1
  • IBM Cognos BI 10

Caveats

The technique outlined in this document was created as a proof of concept and may not work in all master-detail scenarios. This technique should be thoroughly tested within a development environment before being deployed into production.


Master-Detail Overview

A master-detail style report will consist of a master query reference and a detail query reference. These query references are joined through a master-detail relationship. This relationship is used to filter the detail query for every record in the master query, thus resulting in multiple detail queries being issued to the underlying database. As the master query record set increases, the number of detail queries increases as well, slowing down the overall report performance.

In the following scenario a Report Author has created an IBM Cognos Report Studio report which displays a crosstab with Product line on the rows, Year on the columns and Quantity on the measure. The report has been sectioned by Order method. This report layout is depicted by the following screen capture.

Figure 1 Report layout in IBM Cognos Report Studio showing the crosstab and list in a master-detail relationship
Figure 1 Report layout in IBM Cognos Report Studio showing the crosstab and list in a master-detail relationship

When this report is executed within the IBM Cognos Viewer it produces an output which displays a header for each of the E-mail, Fax, Mail Order methods. For each of these headers, it also displays a crosstab with Product line on the rows, Year on the columns and Quantity as the measure. Each one of these crosstab’s values will have been filtered by the order method. The report output in IBM Cognos Viewer is illustrated by the following screen capture.

Figure 2 IBM Cognos Viewer output of the master-detail report
Figure 2 IBM Cognos Viewer output of the master-detail report

As this report executes, a Structured Query Language (SQL) trace on the query database would reveal that the SQL used to populate the crosstab is sent down once for each of the order methods. As there are seven order methods in total, the detail query will be sent down to the database seven times.

Forcing a Single Database Query in a Master-Detail Report

In order to send a single database query for a master-detail style report, the report needs to be authored a certain way and a configuration change needs to be applied to the CQEConfig.xml file. The following sections will provide the steps required for both of these items.

Adding a Configuration Parameter to the CQEConfig.xml File

This technique requires the addition of the RequestHintLocalCacheHasHigherPriority parameter to the CQEConfig.xml file on all IBM Cognos BI application servers performing data access. To add this parameter:

  1. Locate the CQEConfig.xml.sample file location in the <install_directory>\configuration directory.
  2. Make a copy of this file and rename it to CQEConfig.xml.
  3. Open the CQEConfig.xml file using a text editor.
  4. Within the CQEConfig.xml file locate the text <section name="QueryEngine"> and add <entry name="RequestHintLocalCacheHasHigherPriority" value="0"/> on a new line underneath the <section name="QueryEngine"> section.
    When completed the entry should read as follows:
    		<section name="QueryEngine">
    		<entry name="RequestHintLocalCacheHasHigherPriority" value="0"/>
    		…
  5. Save the changes to the file.
  6. The changes to this file will take affect once the IBM Cognos service has been stopped and restarted.

Altering the IBM Cognos Report Studio Master-Detail Report

This technique also requires that the master-detail report is authored a certain way. This section will use the report referenced in Section 2 as a starting point and provide the required changes. The IBM Cognos report specification has been added to this document as Appendix A.

  1. Within IBM Cognos Report Studio Query Explorer, from the available Toolbox items, drag in two separate Query objects.
  2. Rename one of the Query objects to Master and the other to Detail.
    Once completed the IBM Cognos Report Studio Query Explorer should have three query objects. The BaseQuery which consists of the original data items used to run the report and two other Query objects named Master and Detail.
    Figure 3 IBM Cognos Report Studio Query Explorer depicting the three Query objects
    Figure 3 IBM Cognos Report Studio Query Explorer depicting the three Query objects
  3. In order to create sub-queries, drag the BaseQuery object to the right of the Master and Detail Query objects.
    This drag action will add an arrow from the BaseQuery object to each of the Master and Detail objects, indicating that the BaseQuery is being used to populate the other Query objects. This is illustrated in the following screen capture.
    Figure 4 IBM Cognos Report Studio illustrating the BaseQuery as a sub query
    Figure 4 IBM Cognos Report Studio illustrating the BaseQuery as a sub query
  4. Double-click on the Master Query object.
  5. From the left hand Insertable Objects pane, select the BaseQuery Order method object and drag it into the Data Items pane.
  6. Within the middle of the screen click on the Query Explorer and re-select the Master Query object.
  7. Within the left hand bottom Properties pane, locate and set the Use Local Cache property to yes.
  8. Within the middle of the screen click on the Query Explorer and re-select the Detail Query object.
  9. From the left hand Insertable Objects pane, select all the BaseQuery objects and drag them into the Data Items pane.
  10. Within the middle of the screen click on the Query Explorer and re-select the Detail Query object.
  11. Within the left hand bottom Properties pane, locate and set the Use Local Cache property to yes.
  12. Within the left hand bottom Properties pane, locate and set the Use SQL Parameters property to Literal.
  13. Within the middle of the screen click on the Page Explorer and re-select Page1 under the Report Pages folder.
  14. Select the List object and within the List Properties at the bottom left hand side, switch the Query from BaseQuery to Master. The following image shows the List object selected and the Query property changed from BaseQuery to Master query within the bottom left hand List Properties pane.
    Figure 5 IBM Cognos Report Studio displaying the List Properties
    Figure 5 IBM Cognos Report Studio displaying the List Properties
  15. Select the crosstab object and within the Crosstab Properties at the bottom left hand side, switch the Query from BaseQuery to Detail. The following image shows the Crosstab object selected and the Query property changed from BaseQuery to Detail query within the bottom left hand Crosstab Properties pane.
    Figure 6 IBM Cognos Report Studio displaying the Crosstab Properties
    Figure 6 IBM Cognos Report Studio displaying the Crosstab Properties
    With these changes in place as the report executes, a Structured Query Language (SQL) trace on the query database would now reveal that a single SQL statement is sent down to the query database even though the Master query has multiple Order methods.
    For reference purposes, the final IBM Cognos Report Studio report specification has been added to this document as Appendix B.

Appendix A Report Specification for Master-detail with Multiple Database Queries

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-ca">
        <modelPath>/content/folder[@name='Samples']/folder[@name='Models']
          /package[@name='GO Sales (query)']/model[@name='model']</modelPath>
        <drillBehavior modelBasedDrillThru="true"/>
        <queries>
          <query name="Query1">
            <source>
              <model/>
            </source>
            <selection><dataItem name="Quantity" aggregate="total"><expression>
              [Sales (query)].[Sales].[Quantity]</expression></dataItem>
              <dataItem name="Order method" aggregate="none" rollupAggregate="none">
              <expression>[Sales (query)].[Order method].[Order method]</expression>
              </dataItem><dataItem name="Product line" aggregate="none" 
              rollupAggregate="none"><expression>[Sales (query)].[Product].
              [Product line]</expression></dataItem>
              <dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>
              [Sales (query)].[Time dimension].[Year]</expression></dataItem></selection>
          </query>
        </queries>
        <layouts>
          <layout>
            <reportPages>
              <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/>
                </defaultStyles></style>
                <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/>
                  </defaultStyles></style>
                  <contents>
                    <list showColumnTitles="hide" refQuery="Query1">
      <listColumns>
        <listColumn>
          <listColumnTitle>
            <contents/>
          </listColumnTitle>
          <listColumnBody>
            <contents><crosstab refQuery="Query1" horizontalPagination="true"
              name="Crosstab1">
                      <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/>
                        </defaultStyles></style><contents><textItem><dataSource>
                        <dataItemLabel refDataItem="Quantity"/></dataSource></textItem>
                        </contents></crosstabCorner>
                      
                      
                      <style>
                        <defaultStyles>
                          <defaultStyle refStyle="xt"/>
                        </defaultStyles>
                        <CSS value="border-collapse:collapse"/>
                      </style>
                    <defaultMeasure refDataItem="Quantity"/><crosstabFactCell><contents>
                      <textItem><dataSource><cellValue/></dataSource></textItem>
                      </contents><style><defaultStyles><defaultStyle refStyle="mv"/>
                      </defaultStyles></style></crosstabFactCell><crosstabRows>
                      <crosstabNode><crosstabNodeMembers><crosstabNodeMember 
                      refDataItem="Product line" edgeLocation="e2"><style><defaultStyles>
                      <defaultStyle refStyle="ml"/></defaultStyles></style><contents>
                      <textItem><dataSource><memberCaption/></dataSource>
                      </textItem></contents></crosstabNodeMember></crosstabNodeMembers>
                      </crosstabNode></crosstabRows><crosstabColumns><crosstabNode>
                      <crosstabNodeMembers><crosstabNodeMember refDataItem="Year" 
                      edgeLocation="e3"><style><defaultStyles>
                      <defaultStyle refStyle="ml"/></defaultStyles></style><contents>
                      <textItem><dataSource><memberCaption/></dataSource>
                      </textItem></contents></crosstabNodeMember></crosstabNodeMembers>
                      </crosstabNode></crosstabColumns><masterDetailLinks>
                      <masterDetailLink><masterContext><dataItemContext 
                      refDataItem="Order method"/></masterContext><detailContext> 
                      <dataItemContext refDataItem="Order method"/></detailContext>
                      </masterDetailLink></masterDetailLinks></crosstab></contents>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="sb"/>
              </defaultStyles>
            </style>
          </listColumnBody>
        </listColumn>
      </listColumns>
      
      
      
      <style>
        <CSS value="border-collapse:collapse; width:100%"/>
        <defaultStyles>
          <defaultStyle refStyle="sc"/>
        </defaultStyles>
      </style>
    <listGroups><listGroup refDataItem="Order method"><listHeader><listRows><listRow>
      <rowCells><rowCell><contents><textItem><dataSource><dataItemValue 
      refDataItem="Order method"/></dataSource></textItem></contents><style>
      <defaultStyles><defaultStyle refStyle="sh"/></defaultStyles></style></rowCell>
      </rowCells></listRow></listRows></listHeader></listGroup>
      </listGroups></list>
                  </contents>
                </pageBody>
                <pageHeader>
                  <contents>
                    <block><style><defaultStyles><defaultStyle refStyle="ta"/>
                      </defaultStyles></style>
                      <contents>
                        <textItem><style><defaultStyles><defaultStyle refStyle="tt"/>
                          </defaultStyles></style>
                          <dataSource>
                            <staticValue/>
                          </dataSource>
                        </textItem>
                      </contents>
                    </block>
                  </contents>
                  <style>
                    <defaultStyles>
                      <defaultStyle refStyle="ph"/>
                    </defaultStyles>
                    <CSS value="padding-bottom:10px"/>
                  </style>
                </pageHeader>
                <pageFooter>
                  <contents>
                    <table>
                      <tableRows>
                        <tableRow>
                          <tableCells>
                            <tableCell>
                              <contents>
                                <date>
                                  <style>
                                    <dataFormat>
                                      <dateFormat/>
                                    </dataFormat>
                                  </style>
                                </date>
                              </contents>
                              <style>
                                <CSS value="vertical-align:top;
                                  text-align:left;width:25%"/>
                              </style>
                            </tableCell>
                            <tableCell>
                              <contents>
                                <pageNumber/>
                              </contents>
                              <style>
                                <CSS value="vertical-align:top;
                                  text-align:center;width:50%"/>
                              </style>
                            </tableCell>
                            <tableCell>
                              <contents>
                                <time>
                                  <style>
                                    <dataFormat>
                                      <timeFormat/>
                                    </dataFormat>
                                  </style>
                                </time>
                              </contents>
                              <style>
                                <CSS value="vertical-align:top;
                                  text-align:right;width:25%"/>
                              </style>
                            </tableCell>
                          </tableCells>
                        </tableRow>
                      </tableRows>
                      <style>
                        <defaultStyles>
                          <defaultStyle refStyle="tb"/>
                        </defaultStyles>
                        <CSS value="border-collapse:collapse;width:100%"/>
                      </style>
                    </table>
                  </contents>
                  <style>
                    <defaultStyles>
                      <defaultStyle refStyle="pf"/>
                    </defaultStyles>
                    <CSS value="padding-top:10px"/>
                  </style>
                </pageFooter>
              </page>
            </reportPages>
          </layout>
        </layouts>
      <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true"
        output="no"/><XMLAttribute name="listSeparator" value="," output="no"/>
        </XMLAttributes></report>

Appendix B Report Specification for Master-detail with a Single Database Query

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-ca">
        <modelPath>/content/folder[@name='Samples']/folder[@name='Models']
          /package[@name='GO Sales (query)']/model[@name='model']</modelPath>
        <drillBehavior modelBasedDrillThru="true"/>
        <queries>
          <query name="BaseQuery">
            <source>
              <model/>
            </source>
            <selection><dataItem name="Quantity" aggregate="total"><expression>
              [Sales (query)].[Sales].[Quantity]</expression></dataItem>
              <dataItem name="Order method" aggregate="none" rollupAggregate="none">
              <expression>[Sales (query)].[Order method].[Order method]</expression>
              </dataItem><dataItem name="Product line" aggregate="none" 
              rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]
              </expression></dataItem><dataItem name="Year" aggregate="none" 
              rollupAggregate="none"><expression>[Sales (query)].[Time dimension].[Year]
              </expression></dataItem></selection>
          </query>
        <query name="Master">
      <source>
        
      <queryRef refQuery="BaseQuery"/></source>
      <selection><dataItem name="Order method"><expression>[BaseQuery].[Order method]
        </expression></dataItem></selection>
    <queryHints><localCache value="true"/></queryHints></query><query name="Detail">
      <source>
        
      <queryRef refQuery="BaseQuery"/></source>
      <selection><dataItem name="Quantity"><expression>[BaseQuery].[Quantity]
        </expression></dataItem><dataItem name="Year"><expression>[BaseQuery].[Year]
        </expression></dataItem><dataItem name="Order method"><expression>
        [BaseQuery].[Order method]</expression></dataItem><dataItem name="Product line">
        <expression>[BaseQuery].[Product line]</expression></dataItem></selection>
    <queryHints><localCache value="true"/><useSQLParameters value="literal"/>
      </queryHints></query></queries>
        <layouts>
          <layout>
            <reportPages>
              <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/>
                </defaultStyles></style>
                <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/>
                  </defaultStyles></style>
                  <contents>
                    <list showColumnTitles="hide" refQuery="Master">
      <listColumns>
        <listColumn>
          <listColumnTitle>
            <contents/>
          </listColumnTitle>
          <listColumnBody>
            <contents><crosstab horizontalPagination="true" name="Crosstab1"
              refQuery="Detail">
                      <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/>
                        </defaultStyles></style><contents><textItem><dataSource>
                        <dataItemLabel refDataItem="Quantity"/></dataSource></textItem>
                        </contents></crosstabCorner>
                      
                      
                      <style>
                        <defaultStyles>
                          <defaultStyle refStyle="xt"/>
                        </defaultStyles>
                        <CSS value="border-collapse:collapse"/>
                      </style>
                    <defaultMeasure refDataItem="Quantity"/><crosstabFactCell><contents>
                      <textItem><dataSource><cellValue/></dataSource></textItem>
                      </contents><style><defaultStyles><defaultStyle refStyle="mv"/>
                      </defaultStyles></style></crosstabFactCell><crosstabRows>
                      <crosstabNode><crosstabNodeMembers><crosstabNodeMember
                      refDataItem="Product line" edgeLocation="e2"><style><defaultStyles>
                      <defaultStyle refStyle="ml"/></defaultStyles></style><contents>
                      <textItem><dataSource><memberCaption/></dataSource></textItem>
                      </contents></crosstabNodeMember></crosstabNodeMembers>
                      </crosstabNode></crosstabRows><crosstabColumns><crosstabNode>
                      <crosstabNodeMembers><crosstabNodeMember refDataItem="Year" 
                      edgeLocation="e3"><style><defaultStyles><defaultStyle 
                      refStyle="ml"/></defaultStyles></style><contents><textItem>
                      <dataSource><memberCaption/></dataSource></textItem>
                      </contents></crosstabNodeMember></crosstabNodeMembers>
                      </crosstabNode></crosstabColumns><masterDetailLinks>
                      <masterDetailLink><masterContext><dataItemContext 
                      refDataItem="Order method"/></masterContext><detailContext>
                      <dataItemContext refDataItem="Order method"/></detailContext>
                      </masterDetailLink></masterDetailLinks></crosstab></contents>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="sb"/>
              </defaultStyles>
            </style>
          </listColumnBody>
        </listColumn>
      </listColumns>
      
      
      
      <style>
        <CSS value="border-collapse:collapse; width:100%"/>
        <defaultStyles>
          <defaultStyle refStyle="sc"/>
        </defaultStyles>
      </style>
    <listGroups><listGroup refDataItem="Order method"><listHeader><listRows><listRow>
      <rowCells><rowCell><contents><textItem><dataSource><dataItemValue
      refDataItem="Order method"/></dataSource></textItem></contents><style>
      <defaultStyles><defaultStyle refStyle="sh"/></defaultStyles></style></rowCell>
      </rowCells></listRow></listRows></listHeader></listGroup></listGroups></list>
                  </contents>
                </pageBody>
                <pageHeader>
                  <contents>
                    <block><style><defaultStyles><defaultStyle refStyle="ta"/>
                      </defaultStyles></style>
                      <contents>
                        <textItem><style><defaultStyles><defaultStyle refStyle="tt"/>
                          </defaultStyles></style>
                          <dataSource>
                            <staticValue/>
                          </dataSource>
                        </textItem>
                      </contents>
                    </block>
                  </contents>
                  <style>
                    <defaultStyles>
                      <defaultStyle refStyle="ph"/>
                    </defaultStyles>
                    <CSS value="padding-bottom:10px"/>
                  </style>
                </pageHeader>
                <pageFooter>
                  <contents>
                    <table>
                      <tableRows>
                        <tableRow>
                          <tableCells>
                            <tableCell>
                              <contents>
                                <date>
                                  <style>
                                    <dataFormat>
                                      <dateFormat/>
                                    </dataFormat>
                                  </style>
                                </date>
                              </contents>
                              <style>
                                <CSS value="vertical-align:top;
                                  text-align:left;width:25%"/>
                              </style>
                            </tableCell>
                            <tableCell>
                              <contents>
                                <pageNumber/>
                              </contents>
                              <style>
                                <CSS value="vertical-align:top;
                                  text-align:center;width:50%"/>
                              </style>
                            </tableCell>
                            <tableCell>
                              <contents>
                                <time>
                                  <style>
                                    <dataFormat>
                                      <timeFormat/>
                                    </dataFormat>
                                  </style>
                                </time>
                              </contents>
                              <style>
                                <CSS value="vertical-align:top;
                                  text-align:right;width:25%"/>
                              </style>
                            </tableCell>
                          </tableCells>
                        </tableRow>
                      </tableRows>
                      <style>
                        <defaultStyles>
                          <defaultStyle refStyle="tb"/>
                        </defaultStyles>
                        <CSS value="border-collapse:collapse;width:100%"/>
                      </style>
                    </table>
                  </contents>
                  <style>
                    <defaultStyles>
                      <defaultStyle refStyle="pf"/>
                    </defaultStyles>
                    <CSS value="padding-top:10px"/>
                  </style>
                </pageFooter>
              </page>
            </reportPages>
          </layout>
        </layouts>
      <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true"
        output="no"/><XMLAttribute name="listSeparator" value="," output="no"/>
        </XMLAttributes></report>

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business analytics, Information Management
ArticleID=750301
ArticleTitle=IBM Cognos Proven Practices: Improving IBM Cognos Report Studio Master Detail Report Performance
publish-date=08032011