IBM Cognos Proven Practices: IBM Cognos 10 Dynamic Query Analyzer User Guide

Product(s): IBM Cognos 10; Area of Interest: Infrastructure

A description of the various features available within IBM Cognos Dynamic Query Analyzer (DQA) that can be used for debugging and optimizing the performance of IBM Cognos Dynamic Query mode enabled IBM Cognos 10 reports.

Tod Creasey, Software Developer, IBM

Tod Creasey works on the Cognos Data Access Platform and the lead developer on the Dynamic Query Analyzer. He has worked previously on the Rational Team Concert and Eclipse projects for IBM.



20 July 2011 (First published 12 July 2011)

Also available in Chinese

Introduction

Purpose

This document describes the various features available within IBM Cognos Dynamic Query Analyzer (DQA) that can be used for debugging and optimizing the performance of IBM Cognos Dynamic Query mode enabled IBM Cognos 10 reports.

Applicability

The product behaviours described in this document apply to the initial release of the IBM Cognos Dynamic Query Analyzer and may not be applicable to later releases.


Installing and Configuring Dynamic Query Analyzer

Java Virtual Machines

When it is first installed Dynamic Query Analyzer uses the java virtual machine that is on your path. To determine if you have a java virtual machine on your path type java -version in a command prompt (windows) or terminal window (linux). If you do not get a result then you will need to define a java virtual machine for Dynamic Query Analyzer.

You can specify the path of the virtual machine by defining a file called DQA.ini in the eclipse directory of your DQA install with a -vm parameter of the form –vm <directory>. The path passed to -vm should be the bin directory of your jre.

On Windows any Cognos 10 install including Dynamic Query Analyzer will have a java virtual machine available in the bin directory under your Cognos 10 install. You can refer to the supplied virtual machine using the line

-vm ..\..\..\bin\jre\6.0\bin (Windows 32)

or

-vm ..\..\..\bin64\jre\6.0\bin (Windows 64)

The windows versions of DQA will work with any Java 5 or Java 6 virtual machine. The linux version requires any Java 5 virtual machine.


Getting Started

Dynamic Query Analyzer requires no initial setup if you are working offline. However most users will want to access their logs and run reports remotely. The logs are not accessible via the regular Cognos 10 portal and must access to them must be setup by your server administrator using a URL that uses the http or file protocols. The Cognos 10 settings are for the Dispatcher port and address of your Cognos 10 install. For more details see the Server preference page description.

Opening a Log Using the Dialog

When you first open IBM Cognos Dynamic Query Analyzer you will see an empty window with a single icon. Selecting this icon will open the Open Logs dialog. The Open Logs dialog consists of 3 areas, Previously Opened, From Directory and From URL. This is also illustrated by the following screen capture which shows the open logs dialog with its initial selection of the previously opened logs.

Figure 1 The Open Logs Dialog displaying the 3 areas
Figure 1 The Open Logs Dialog displaying the 3 areas

The Previously opened logs are logs that have been cached by the IBM Cognos Dynamic Query Analyzer on your local disk so that you can continue to work on them without a server connection. These can be cleared using the Clear previously opened menu entry found under File.

The From directory dialog allows you to select a log that is saved somewhere accessible with a system Directory dialog. These entries will be cached in your local cache and show up in Previously Opened in subsequent invocations of the Open Log dialog.

From URL is a way to access logs using the http or file protocol like you would in a web browser. By default this value is the Remote Log URL set up in the Server preference page.

There are 3 types of logs that can be loaded within the Open Log dialog. These are profile, runtree and plan. The profile is a log of the execution of a report with timing information captured. The runtree is a log of the execution of a report no timing information. This will only be shown if there is no profile. The plan log shows the initial query and the final query just before an execution was attempted. The plan is only shown if execution failed.

An image of a collapsed NodeCollapsed Node There are nodes underneath this one not currently shown. Double click or select Show Subtree from the pop up menu to expand.
Filtered children Node has children but these are currently filtered by the Node Filtering preference. Select show filtered children from the pop up menu to show them.
Selected Node is the current selection. See the properties of the node in the properties view.
Has subqueries A node with a double box has subqueries that can be opened in another graph. Select Show Subqueries from the pop-up menu.
Timing A node with a red or yellow bar beside it has timing information. The red portion indicates the overall time for the report spent in the node. The yellow portion in the percentage of the overall time in the children of the node. If there is a grey portion that is the overall time spent outside of this node and its children.

Graph Nodes in Further Detail

The nodes shown in an IBM Cognos Dynamic Query Analyzer log graph are a hierarchical representation of the xml generated in the log. When the node has information that we can display it is colored or filled with text accordingly. The colours for the node types can be viewed in the summary view and changed in the Colors and Fonts preference page. The colours beside the node show the timing information for the node.

Generally speaking the properties shown in a node is its name and the id of the operation it executed. Square nodes, which are not shown by default, do not have an id as they represent information that was not part of an execution of an operation. You can enable them in the Node Filtering page. All of the properties of the node can be seen in the properties view. You can also click on the XML tag to see the contents of the log file as raw XML.

Views

All views within IBM Cognos Dynamic Query Analyzer can be opened using the Window \Show View off the menu bar. Some are opened by default when a graph opens. There are two available from the file menu - the Navigation and Content Store views using Show In Navigation and Open IBM Cognos Portal respectively.

Navigation

The Navigation view is a tree representation of a graph. Navigation views are opened one per graph so they do not switch their contents as the graph is selected like the summary view does. This allows you to have multiple navigation views open if you want to compare graph.

If you wish to navigate more rapidly you can select the link to editor icon on the tool bar and selections in the editor will select the graph and vice versa. The navigation view allows you to navigate to entries in the tree and to focus on them by double clicking on the entry in the tree.

The image below shows the navigation of the graph in the navigation view and the corresponding node that is highlighted via double click or the Link to Editor button.

Figure 2 Navigation View displaying the graph structure as a tree
Figure 2 Navigation View displaying the graph structure as a tree

Summary View

The Summary view shows the overall information about a graph in a fast, easy to navigate format. There are four sections to the view. The overview section shows the name, package and time of the report running and a graphic to quickly indicate whether the data source type is relational, OLAP or dimensionally modeled relational (DMR). Note that the name and the package of the report may not be known if it was run before it was saved to the content store.

The second section is the timing section where users will spend most of their time if profiling. The working and waiting time for each node is shown in descending order. Double clicking on any of the entries will take you to the node that timing is for.

The Analysis section shows extra information about the report running including if planning was successful, any query hints applied and any filters applied. These entries can alter the performance of a report significantly and should be checked if two seemingly identical reports have very different timing characteristics.

The final section is a legend that shows node types and colorings for a quick reference as to what they mean. The following screenshot shows the summary view as it is opened by default with the summary and timing information shown.

Figure 3 Summary View with summary and timing showing
Figure 3 Summary View with summary and timing showing

Query View

The Query view shows the Multi Dimensional Expression (MDX) or Structured Query Language(SQL) query that was executed to generate this report. The SQL query can be re-run to see what the result would be. The MDX query is much more tightly linked to the report execution and can be used to find the places in the graph that match the commands in the MDX. To find the matching node select the MDX command and if the Link MDX to graph button is select the graph entry will be highlighted. In the image below an MDX command selected in the view and the corresponding node highlighted after the Link MDX to graph button is selected.

Figure 4 Query View with command selected in graph
Figure 4 Query View with command selected in graph

Report Logs View

The Report Logs view is a view of all of the logs current available on the server and is the same list as the remote logs in the Open Log dialog. This is a convenient way to browse without having to use the model open dialog or the content store view which requires you to browse through packages.

The image below shows a log expanded to show its main profile and the sub-queries that occurred during execution.

Figure 5 Report Log View
Figure 5 Report Log View

Content Store View

The content store view is a view to see what reports are in the portal and to run them with logging on. The list of folders and reports is the same as that in the IBM Cognos 10 web portal. Unlike reports run from the portal, reports run from the IBM Cognos Dynamic Query Analyzer can generate a log on a report by report basis.

After a report runs you can see the log for that run under the report in the content store view. If you do not see your log double check the Logs View to see if it is there. In the screenshot below we can see the log of the report that was just run underneath the report entry in the content store view.

The screenshot below shows a package expanded to show it’s reports and one of those reports expanded to show it’s logs.

Figure 6 Content store view displaying a log generated from a report
Figure 6 Content store view displaying a log generated from a report

IBM Cognos Dynamic Query Mode Server Logs

The IBM Cognos Dynamic query Mode (DQM) Server logs view is a view that shows the contents of the main server logs. These logs are for all events on the server and are not on a report by report basis. However, some operations occur outside of the IBM Cognos Dynamic Query execution. For these scenarios, these logs may provide more details. If you wish to see if any entries are tied to the report you have open you can select "Show in Server Log" from the File menu.

The entries in the IBM Cognos Dynamic Query Server Logs View are organized by server session. You can select any entry in the view and choose to filter them on anything that matches any of its columns. These filters ca be cleared using Clear Filters.

The following screenshot shows the latest server log expanded to show all of the entries that have occurred. There is a new server log started each time the server is restarted.

Figure 7 Dynamic Query Mode Logs displaying the server entries for the latest session
Figure 7 Dynamic Query Mode Logs displaying the server entries for the latest session

Preferences

Preference pages can be found in the Window->Preferences menu. This can be used to set how graphs are viewed and what servers’ reports and logs are being accessed from.

General Preferences

The General preference page has 2 groups, the General group and the Generating Logs group. The General group covers the following work flow options:

Always run in background: When set the Operation in Progress dialog will no longer come up as if you had already selected Run in Background.

Show heap status: When enabled Show heap status shows a box at the bottom of the window that shows how much heap has been allocated by DQA and how much it is using. As logs can get very large this is a good way to track performance.

Open Navigation View with Log: When enabled the Navigation View is opened when a graph is opened. As a Navigation View is more memory intensive than a graph this is off by default.

Default zoom: The zoom level of graphs. If you are working with large graphs or a large monitor you may want to adjust the default zoom.

The Generating Logs group encompasses the preferences for logs that are generated when reports are run from the Content Store view. IBM Cognos Dynamic Query Analyzer allows you to run reports and log on a report by report basis. You otherwise have to turn reporting on for all users which can generate a lot of unnecessary content, particularly on a large user base. By default only the execution is traced and report plans are not. Report plans are only shown when execution fails so turn these on to debug a report. Plan logs are generally much larger than report logs and should be turned off for general use.

The screenshot below shows the General preference page with the default settings.

Figure 8 General Preferences Page
Figure 8 General Preferences Page

Content Store Preferences

The content store preference page is used to determine what should be done after a report is run from the content store. You can choose whether or not to open a log when a report is run and if you want to be prompted or not.

The following screenshot shows the content store preference page with the default settings.

Figure 9 Content Store Preference Page
Figure 9 Content Store Preference Page

Server Preferences Page

The Server preference page is for setting up your connection information for the Cognos portal you execute reports on and for the URL of the logs directory you wish to browse. Neither of these settings are required if you access logs using the file system only.

The Server area is for specifying the IBM Cognos 10 server reports are run on. These settings are used to populate the entries in the Content Store view so that you can run reports from IBM Cognos Dynamic Query Analyzer. Reports run from IBM Cognos Dynamic Query Analyzer can generate a log independent of the server settings so this is a popular way to run reports without turning on logging for the whole server. See the General preference page for more details on running reports and generating logs without changing the administration settings.

All of the settings in the Server area are the same as the values you used for logging into IBM Cognos 10 using the portal. Namespaces are determined on a per server basis so if the Namespace you need is not in the list select refresh to get it. Note that servers with anonymous access will not need Name, Password or Namespace settings. The port number is the port for your Dispatcher - ask your administrator for this value.

The Remote Log Access settings are for setting the connection to your log directory which will be set up by your administrator for you. If the address is password protected you can enter your name and password here. This is used to populate the Logs view, the log entries under the reports in the Content Store view and the list of remote logs in the open dialog.

The screenshot below shows the server preferences page with the default settings for both the server and the logs directory. Note that most deployments of IBM Cognos 10 will have different settings than these.

Figure 10 Server Preferences page showing server and logs directory
Figure 10 Server Preferences page showing server and logs directory

Visualization Preferences

The Visualization preference page is a page for selecting how much information to show in each node. By default the name and tooltip are shown. Turning off the name reduces the size of the nodes and removing the tooltip stops hover tooltips which you may want to do as all of the information is available in the properties view.

The screenshot below shows the Visualization Preference Page with the default (most verbose) settings.

Figure 11 Visualization preferences page displaying the node content preferences
Figure 11 Visualization preferences page displaying the node content preferences

Node filtering Preferences

The node filtering preference page is a page for selecting which nodes in the graph are shown. An IBM Cognos Dynamic Query Mode graph can be quite verbose as there are many nodes that are data and some others do not generally affect performance. Many of these nodes are filtered out by default in order to make a smaller graph but they can be shown by selecting the nodes to show in this page.

The screenshot below shows the node filtering preference page with the default settings selected.

Figure 12 Node filtering page displaying the currently visible nodes
Figure 12 Node filtering page displaying the currently visible nodes

Administrators Notes

IBM Cognos 10 does not expose the logs directory to the user in the portal currently. The logs directory is the <platform>/logs/XQE directory in your IBM Cognos 10 install. If the user has physical access to the machine the file protocol can be used as well. If you wish your users to access this directory you need to set up an HTTP server to access them using something like a Tomcat server. To test if your setup will work in IBM Cognos Dynamic Query Analyzer paste the URL you are going to use into a web browser. If you get a directory and file listing you are ready.


Accessibility

Graph traversal using the keyboard: If you prefer to work without a mouse you can traverse the graph using the arrow keys on your keyboard. When you want to switch graphs you can do so using Ctrl+F6 which will give you a list of graphs. If you wish to switch into the supporting views you can do so using Ctrl+F7.

Maximizing/Restoring Views: If some of your text has scrolled off of the horizontal edge you may want to maximize the view or editor. You can do this in the view or editor menu. This menu can be found using the keyboard hotkey Alt - as well.

Note There is currently a limitation where moving into the view will not give keyboard focus if the editor is active. You can work around this by selecting Alt+G which will give focus to the tab rather than the graph before you traverse out of them.

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=726928
ArticleTitle=IBM Cognos Proven Practices: IBM Cognos 10 Dynamic Query Analyzer User Guide
publish-date=07202011