Cognos Dynamic Query Analyzer User Guide Update 10.2.1 FP3

Content

What's new?

This technote contains a list of new and changed features for this release of IBM® Cognos® Dynamic Query Analyzer. It helps you plan your upgrade and application deployment strategies and the training requirements for your users.

The following features are new or have been changed in this release of Cognos Dynamic Query Analyzer:

• Aggregate Advisor results are now stored on the Cognos Business Intelligence server
• Aggregate Advisor in-memory recommendations from different runs can be merged
• Logging out from and in to the Cognos Business Intelligence server
• In-database aggregate SQL can now be based on columns from the sample SQL of other in-database aggregates

Aggregate Advisor results are now stored on the Cognos Business Intelligence server

Advisor results are now stored on the IBM Cognos Business Intelligence server instead of on the workstation that is running IBM Cognos Dynamic Query Analyzer.

If you are upgrading from a previous version of Cognos Dynamic Query Analyzer, you are prompted, when opening the application for the first time after upgrading, whether to move Aggregate Advisor results stored on the workstation to the Cognos Business Intelligence server. All results stored on the workstation are moved to the server, including results of runs that used a different Cognos Business Intelligence server.

Aggregate Advisor results stored on the Cognos Business Intelligence server can be viewed in the Advisor Results view after Cognos Dynamic Query Analyzer has connected to the Cognos Business Intelligence server.

You do not need to keep Cognos Dynamic Query Analyzer open after starting an Aggregate Advisor run. You can start the Aggregate Advisor run, shut down Cognos Dynamic Query Analyzer, and restart Cognos Dynamic Query Analyzer later to see the results of the Aggregate Advisor run.

To refresh the list of Aggregate Advisor results, click Refresh Aggregate Advisor results .

Applying Aggregate Advisor results to a different server

To apply in-memory aggregate recommendations to a different server you can either create a deployment that contains the model, including in-memory aggregate definitions, and deploy it to the new server or you can use Cognos Dynamic Query Analyzer. You can still use a deployment as before but if you want to use Cognos Dynamic Query Analyzer then the procedure is different.

Previously, you could run Cognos Dynamic Query Analyzer against a server, the results would be stored locally, and then you could connect Cognos Dynamic Query Analyzer to a different server and apply the results to the new server.

Now that the Aggregate Advisor results are stored on the Cognos Business Intelligence server where they were generated, you must copy the results file to the new server where you want to apply them. Aggregate Advisor results are stored in the folder <BI_server_installation_location>/logs/XQE/ROLAPCubes/<cube_name>/advisor/recommendations. If you need to copy Aggregate Advisor results from one server to a different server, copy the results .xml file to the equivalent folder on the destination server. You can then connect Cognos Dynamic Query Analyzer to the new server and apply the results.

Aggregate Advisor in-memory recommendations from different runs can be merged

When applying Aggregate Advisor results, you can merge in-memory recommendations from a different Aggregate Advisor run with the results that you are currently applying.

Important: Usually, you will apply the recommendations from a single Aggregate Advisor run. However, if you have found that a specific in-memory aggregate has been useful previously and is not included among the recommendations in the current result, you can include it with recommendations from the current run. If the added in-memory aggregate was covered by an in-database aggregate from the other Aggregate Advisor run, you should ensure that it is still covered by an in-database aggregate in the current set of in-database aggregates, so that the added in-memory aggregate will continue to load at comparable speeds.

To merge results from a different run with the results you are processing, click File, Apply Selected In-Memory Recommendations. Click Add, and then select a stored result from the drop-down list. The in-memory recommendations from that run are added to the list of recommendations currently displayed but are not selected. Check the check-box next to the Name of any recommendations you wish to add.

Logging out from and in to the Cognos Business Intelligence server

You can now log out from within IBM Cognos Dynamic Query Analyzer and also log back in without closing and restarting Cognos Dynamic Query Analyzer.

Your log-in name is displayed next to the Log Out button at the bottom of the Cognos Dynamic Query Analyzer user interface.

Click Log Out to log out from the IBM Cognos Business Intelligence server. After you log out, the Log In button is displayed.

Click Log In to log in after you have logged out, or if your session with the Cognos Business Intelligence server is interrupted.

In-database aggregate SQL can now be based on columns from the sample SQL of other in-database aggregates

Aggregate Advisor may recommend in-database aggregates that can be derived from either the fact table or other in-database aggregates. If an in-database aggregate is derivable from another in-database aggregate, the sample SQL will refer to columns from the sample SQL of the second in-database aggregate. If a derived in-database aggregate can be derived from multiple in-database aggregates, the sample SQL will be generated from the in-database aggregate that provides the optimal solution.

The following figure illustrates the changes in Aggregate Advisor in this release. Diagram A shows in-memory and in-database aggregate recommendations based on the previous release. The in-memory aggregate recommendations are based on in-database aggregate recommendations (A1 and A2) that are built from the fact table. Diagram B shows aggregate recommendations based on the latest release. This has in-database aggregate recommendations (B1 and B2) derived from the fact table but there are also in-database aggregate recommendations (B3 and B4) built on other in-database aggregates (B1 and B2).

Figure 1. Aggregate changes in Dynamic Query Feature Bundle

Deriving in-database aggregates from other in-database aggregates provides these benefits:

• Improved loading of in-memory aggregates by populating them from relatively small in-database aggregates.
• Improved refresh of in-database aggregates by populating them from other in-database aggregates instead of the fact table.

An extract from an Aggregate Advisor in-database recommendation is shown here. This recommendation has two in-database aggregates, one of which is derived from the fact table and the other derived from the first in-database aggregate. The following points should be noted about the extract:

• The prolog indicates how many in-database aggregates are recommended including the number derived from the fact table and other in-database aggregates.
• The prolog has instructions about creating in-database aggregates that are derivable from other in-database aggregates.
• The first recommended in-database aggregate is derived from the fact table. The SQL selects from the fact and dimension tables.
• The second recommended in-database aggregate is derived from the first recommended in-database aggregate. The SQL is much simpler since it only queries the aggregate table and does not need to join to other tables. The name chosen when creating the first in-database aggregate should be specified here.