Cognos Dynamic Query Analyzer User Guide Update 10.2.1.1 IF3

Fix readme


Abstract

The IBM Cognos Dynamic Query Analyzer has been updated in the IBM Cognos Business Intelligence Version 10.2.1.1 Interim Fix 3 (IF3). This document describes the new and changed features of the Cognos Dynamic Query Analyzer. Before using this technote, you should be familiar with the IBM Cognos Dynamic Query Analyzer User Guide.

Content

Open and Close navigation paneOpen and Close navigation paneOpen and Close navigation paneOpen and Close navigation pane
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
Important: Both the IBM Cognos Business Intelligence server and installations of IBM Cognos Dynamic Query Analyzer on workstations must be updated with IBM Cognos Business Intelligence Version 10.2.1.1 Interim Fix 3 (IF3).

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 Refresh Aggregate advisor results icon.

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 Log Out iconbutton at the bottom of the Cognos Dynamic Query Analyzer user interface.

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

Click Log In Log Out iconto 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.

The database administrator creates derived in-database aggregates and applies them to the database after creating and applying the base in-database aggregates.

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.
Important: If the database administrator creates the in-database aggregate using different column names from what is in the sample SQL, the SQL for any derived in-database aggregates will need to be modified to match the column names used.
/*******************************************************************************
* Dynamic Cubes Aggregate Advisor
* In-Database Recommendations
*
* Cube: GoSales_AllEmp_en-us
* Start time: 2013-11-19 15:12:09.761 EST
*
* General options:
* - Do not use query workload logs
* - In-memory aggregates maximum size = 1000000 (bytes)
* - In-database aggregates maximum size = 10000000 (bytes)
* - Advisor run time limit: 60 (minutes)
*
* Summary:
* - Number of new database aggregates recommended: 2. Details are in the next section.
*   Number of new database aggregates derivable from the fact table: 1.
*   Number of new database aggregates derivable from recommended database aggregates: 1.
*******************************************************************************/


/*******************************************************************************
* Advisor recommended aggregates for the database. The recommendations are listed
in the following sections.
*
* The DBA must create the appropriate aggregate tables.
* The modeler must define an aggregate cube for each created aggregate table.
*
...
*
* Following the textual description is an example of the SQL that can aggregate
the data.
* For aggregates derivable from recommended database aggregates, the example SQL
is constructed in the context of the optimal underlying aggregate table and uses
a placeholder name for the table name.
*
...
*******************************************************************************/


/*******************************************************************************
* Aggregate: Month - Product type
*
* Dimension hierarchies with level they are aggregated at:
*
* Dimension              Hierarchy               Level              
* ---------------        ---------------         ---------------    
* Time                   Time                    Month              
* Products               Product                 Product type      
* Retailer               Retailer (by Region)    [All]              
*
* Measures:
* ---------------
* Quantity
*
* Column               Data Type                Cube Model Relationship
* ---------------      ---------------          ------------------------------
* CURRENT_YEAR         SMALLINT                    
* QUARTER_KEY          INTEGER                      
* MONTH_KEY            INTEGER                  Time -> Time -> Month
* PRODUCT_LINE_CODE    INTEGER                      
* PRODUCT_TYPE_KEY     INTEGER                  Products -> Product -> Product type
* Quantity             BIGINT                   measure: Quantity
*
* This aggregate cannot be derived from any other recommended aggregates for the database.
*
* Number of recommended in-memory aggregates that this aggregate can cover: 4
*
* Estimated row count of this aggregate: 21500
*
*******************************************************************************/

SELECT
   "GO_TIME_DIM2"."CURRENT_YEAR" AS "CURRENT_YEAR",
   "GO_TIME_DIM2"."QUARTER_KEY" AS "QUARTER_KEY",
   "GO_TIME_DIM2"."MONTH_KEY" AS "MONTH_KEY",
   "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE" AS "PRODUCT_LINE_CODE",
   "SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY" AS "PRODUCT_TYPE_KEY",
   SUM("SLS_SALES_CLEAN_FACT"."QUANTITY") AS "Quantity"
FROM
   "GOSALESDW"."GO_TIME_DIM" "GO_TIME_DIM2"
       INNER JOIN "GOSALESDW"."SLS_SALES_CLEAN_FACT" "SLS_SALES_CLEAN_FACT"
       ON "GO_TIME_DIM2"."DAY_KEY" = "SLS_SALES_CLEAN_FACT"."ORDER_DAY_KEY"
           INNER JOIN "GOSALESDW"."SLS_PRODUCT_DIM" "SLS_PRODUCT_DIM"
           ON "SLS_PRODUCT_DIM"."PRODUCT_KEY" = "SLS_SALES_CLEAN_FACT"."PRODUCT_KEY"
GROUP BY
   "GO_TIME_DIM2"."CURRENT_YEAR",
   "GO_TIME_DIM2"."QUARTER_KEY",
   "GO_TIME_DIM2"."MONTH_KEY",
   "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE",
   "SLS_PRODUCT_DIM"."PRODUCT_TYPE_KEY"


/*******************************************************************************
* Aggregate: Quarter - Product line
*
* Dimension hierarchies with level they are aggregated at:
*
* Dimension              Hierarchy               Level              
* ---------------        ---------------         ---------------    
* Time                   Time                    Quarter            
* Products               Product                 Product line      
* Retailer               Retailer (by Region)    [All]              
*
* Measures:
* ---------------
* Quantity
*
* Column               Data Type                Cube Model Relationship
* ---------------      ---------------          ------------------------------
* CURRENT_YEAR         SMALLINT                    
* QUARTER_KEY          INTEGER                  Time -> Time -> Quarter
* PRODUCT_LINE_CODE    INTEGER                  Products -> Product -> Product line
* Quantity             BIGINT                   measure: Quantity
*
* This aggregate can be derived from any of the following other aggregates for the database:
* Month - Product type
*
* Number of recommended in-memory aggregates that this aggregate can cover: 2
*
* Estimated row count of this aggregate: 4000
*
*******************************************************************************/

SELECT
   "CURRENT_YEAR",
   "QUARTER_KEY",
   "PRODUCT_LINE_CODE",
   SUM("Quantity") AS "Quantity"
FROM
   [Month - Product type]
GROUP BY
   "CURRENT_YEAR",
   "QUARTER_KEY",
   "PRODUCT_LINE_CODE"

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Cognos Business Intelligence
Dynamic Query Analyzer

Software version:

10.2.1

Operating system(s):

AIX, HP Itanium, HP-UX, Linux, Solaris, Windows

Reference #:

1659240

Modified date:

2014-01-06

Translate my page

Machine Translation

Content navigation