Chapter 10. Troubleshooting 275
Use the following steps to view the SQL requests that are passed to the relational database:
1. Right-click the XMdxSelect node to display the context menu of the node. The XMdxSelect
node is the parent node of XQueryStrategy.
2. Select Open sub queries from the context menu to open all the sub query execution
plans that are generated for the report execution. Each sub query opens in its own tab.
10.3.5 Verifying changes to the BranchProfitByYear Report
The BranchProfitByYear report was extended by the report development team to help the
business understand employee sales performance and their interaction with the sales
channels of the company. However, the revised report is slow and is unexpectedly returning
no sales data.
You can use Dynamic Query Analyzer to understand the performance difference and the
report elements that are introducing the changed profile.
Use the following steps to review the report query execution plan:
1. Open Dynamic Query Analyzer.
2. Enable query execution tracing.
3. From the Content Store view, locate the report to be traced, and from its context menu
select Run Report.
4. Open the query execution trace from the Report Logs view if the execution trace does not
open automatically.
Notes:
? If the Open sub queries context menu option is unavailable, no sub query requests
have been recorded.
? Sub query execution plans can also be opened from the Report Logs view. Sub Query
execution plans are nested folders under the parent report execution plan folder.
Individual sub query execution plans can be opened by selecting the sub query Profile
node.
276 IBM Cognos Dynamic Cubes
Figure 10-16 shows the query execution trace for the BranchProfitByYearExtended report.
Figure 10-16 Query execution trace for report: BranchProfitByYearExtended
The report query execution trace highlights that the majority of the query execution is taken
within the XMdxIfExpression node. Of the 75883.03 milliseconds taken, the total time taken
within the query execution, 71659.2163 milliseconds, is spent determining appropriate values
for the Gross profit report measure. From the execution trace, the report design seems to be
taking advantage of a report expression to determine the appropriate Gross profit and
excluding the value for Gross profit where the gross profit is 900000.
To verify the report design, open the report in Report Studio, and explore the report
expression definition for the Gross Profit Data item. The following expression calculates the
report’s Gross Profit measure:
IF ( [gosldw_sales].[Measures].[Gross profit] <> 900000 ) THEN (
gosldw_sales].[Measures].[Gross profit] ) ELSE ( NULL )
It is appropriate to consider the definition of the report expression for Gross profit and whether
the expression is erroneously included in the report definition.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset