A detailed explanation of the dynamic query mode of Cognos

Development Background

We know that the overall response time of the front-end access backend system is an important indicator of the actual experience of the business user for BI (business intelligence, the same as the abbreviation for Commercial intelligence) system. If the response time is too long (more than 10 seconds in general), even if the graphical interface of the front-end is dazzling, it can also severely discourage end-user interest in using BI systems. Therefore, the poor customer experience of the BI system is often one of the important reasons why many of our analysis system projects fail to implement.

To address this challenge, IBM Cognos Bi has provided Java-enhanced query execution mode from version 10.1.0 (dynamic query mode, referred to as DQM, which is dynamically queried) while still retaining local access to the primary data source. With improved query execution, the dynamic query pattern provides the main query optimization to handle query complexity and data volume. It also provides enhanced query functionality, such as a memory cache, that can be targeted at the query plan while retaining user security privileges.

Introduction to IBM Cognos Dynamic Query mode

Dynamic query mode is an enhanced Java-based query pattern for IBM Cognos BI that provides the following key features:

• Query optimization to solve problems related to query complexity, data volume, and timeliness expectations through improved query execution language.
• The intelligent combination of local and remote processing and better MDX generation greatly improves complex OLAP queries.
• Security-aware cache.
• A new data interface that leverages 64-bit processing.
• Easy maintenance and query visualization.

Query optimization

Query optimization is implemented through advanced applications that strictly query planning rules. These planning rules incorporate the next generation planning approach, which is more streamlined and can improve quality and speed of query execution. The query planning process is also inherently optimized to better utilize metadata and expression caching, including planning caches that provide higher application throughput.

Performance gains through balanced local processing tools

Dynamic Query mode develops intelligent, rule-based, and system-load decisions, where some queries should be performed locally on the application server rather than remotely in the database server. This ensures that users have the highest level of functionality, regardless of whether the underlying data source supports the intent of the business Intelligence report. In addition, Dynamic Query mode contains a fine-grained metadata and a cell data cache (that is, a trickle fed) and a higher cache hit ratio than before. In addition, queries sent to a remote data source are further optimized by the execution layer based on cached content and advanced null-value suppression logic.

Security-Aware Cache

When connected to a secure metadata source, the caching logic provided under Dynamic Querymode can determine a secure metadata source when each user accesses the data source. This information is then used to optimize memory usage and to internally represent a secure view of the user's data source metadata. You can also set security so that you can share the entire OLAP dimension, providing cache reuse and performance benefits.

New data interface with 64-bit processing

Dynamic Query Mode is a fully supported 64-bit data access environment. It allows the use of 64-bit data source drivers, and can take advantage of the 64-bit space that satisfies query processing, metadata caching, and data caching.

Ease of maintenance and query visualization

Query visualization enables the system administrator to analyze the queries generated by Dynamic Querymode and visually see how they are handled. These visualizations include cost-based information from query execution. This information allows for fast identification of model and query optimizations, and the purpose of applying this optimization is to achieve better performance.

Technical architecture Analysis

The schema diagram for the traditional compatible query pattern is as follows:

As shown, the query pattern schema consists of multiple tiers. When from Querystudio, Report Studio, Analysisstudio, Cognos Workspace, one of Cognosworkspace advanced, Cognos Viewer or IBM Cognos When a client module such as the Software Development Kit (SDK) receives a report request, the request is first processed by the Query planning layer.

The upper part of the query planning layer is the query Framework (QFW). QFW checks the report request and determines whether it contains one or more requests, and the query should be processed here. OLAP queries for SAPBW, such as Oracle Essbase and ibmcubing Services-type data sources, go to OLAP query planner. Relational queries for IBM DB2, Oracle, Teradata class data sources go to the relational query planner. Dimensionally modeled Relational (DMR)-style queries go to the DMR query planner.

Each query planner generates the appropriate query language and sends the request to its respective query execution layer. For OLAP, the query planner generates an MDX (multidimensionalexpressions) query and sends it to the multidimensional data Service (MDDS), and for a relational query, it generates SQL and sends it to the universal Data Access (UDA) module. For DMR, a cube build request is processed first, which creates and loads a temporary cube, then generates a new OLAP query, sends the query, and processes the query against the temporary cube. The lowest layer of the schema represents a single data source type and data source supported by IBM Cognos BI. Queries processed by MDDS or UDA are converted into a query language idiom that the data source understands, and then sent to the data source using a direct private interface or through a common interface such as ODBC or XMLA.

The schema diagram for the latest dynamic query pattern is as follows:

From the view, while supporting very similar request flows, the schema of the dynamic query pattern is significantly different from the compatible query pattern. The top client layer displays a system management client that interacts with the query pattern to display metrics and statistics and change configuration settings, such as running the query log.

The planning layer is integrated into a layer called Transformationlayer. The transformation layer itself does not implement any query planning logic, it simply provides an execution environment for query transformations that are stored in a separate library called the Transformation Library. This is to separate the planning logic from the planning layer. The Transform library provides query planning logic for all supported OLAP queries and supports all the features provided by the query framework in compatible query mode.

In compatible query mode, the execution Layer provides similar query execution functionality to MDDS and UDA, but is also integrated into a single schema. It can execute any query request independently of the type of the query or target data source, and can execute SQL, MDX, and XML in a single environment. This is partially implemented in the mixed result set (HResult) format, which allows single query results in memory to be rendered simultaneously with OLAP (with axes, dimensions, and cells) and relational (tables, rows, and columns). With HResult, execution patterns can combine MDX and XML in a single run tree for greater flexibility and query performance.

Data sources supported by Dynamic query mode

You can use the dynamic Query method with several OLAP data sources, including the data sources in the following list:

• IBM Cognos TM1
• SAP Business Informationwarehouse (SAP BW)
• Oracle Essbase
• Microsoft SQL Server AnalysisServices

You can also use the dynamic Query method with several relational data sources, including the data sources in the following list:

• IBM DB2
• IBM DB2 for z/OS
• IBM Informix
• IBM Netezza
• Oracle
• Microsoft SQL Server
• SAP ECC
• Siebel
• Teradata

Take DB2, for example, to see if you are using dynamic query mode to make a connection to a data source.

1. The IBM DB2 client software needs to be installed on the BI server

2. Because DQM needs to access the data source through JDBC, the JDBC driver needs to be copied to the appropriate directory for Cognos.

a) in the IBM DB2 installation directory for the database to be used for the connection, locate: \sqlib\java directory.

b) Locate and copy the Db2jcc4.jar and Db2jcc_license_cu.jar files within the directory.

c) within the IBM Cognos installation directory, locate: \webapps\p2pd\web-inf\lib directory.

d) Paste the Db2jcc4.jar and Db2jcc_licencse_cu.jar files into the directory.

e) in order for IBM Cognos to accept IBM DB2 drivers, the IBM Cognos service needs to be stopped and started.

3. Create a data source.

a) Use the browser to open the Cognos home page of IBM Cognos Connection.

b) Click on the "Start" link in the top right corner and select Ibmcognos administration in the pop-up menu.

c) Click on the "Configure" tab, select "Data Source Connection" and click "New Data Source" button. As shown in.

d) After entering the name of the data source, click "Next" and select "IBM DB2" in the Type list. Note that you must select the check box for configure JDBC connection. As shown in the following:

e) After clicking "Next", enter the DB2 database name and login username password, the compatible query mode test pass.

f) then enter the JDBC Configuration interface and enter the relevant information into the Dynamic Query mode test pass.

In this way, we can use the Dynamic query mode to implement the report analysis of Cognos and the connection of the DB2 database.

Dynamic Query Mode's unique management features

With the introduction of Dynamic query Mode, IBM Cognos Administration provides new capabilities for configuring, tuning, and diagnosing new query services. The following sections identify new UI elements and briefly describe each feature.

DQM parameter settings

In IBM cognosadministration, you can see the QueryService service on the Scorecard tab under System, in the Settings-queryservice window, to the advanced settings, environment, log, and adjust settings. As shown in the following:

Click the "Set Properties" button in the upper right corner to make adjustments to the DQM related parameters in QueryService. As shown in the following:

DQM Use Maintenance

In IBM Cognos administration, you can see the button "New Query service Management task" in the interface content of the Content Management menu option under Configuration. The tasks available in this feature are the ability to maintain dynamic query caching for different data sources. As shown in the following:

For example: For DMR (dimensionally modeled relational) data sources, tasks that can be defined have cleanup Dynamic Query cache (with outdated data avoidance) or write cache state to (for cache usage analysis) IBM On a file on the Cognos server.

For the newly released dynamic Cubes after the 10.2.0 version, more dynamic query maintenance feature items are provided, as shown in:

Conclusion

To learn more about the features of Cognos, please click on the link below to discover the surprises!

Http://bigdata.evget.com/product/200.html

A detailed explanation of the dynamic query mode of Cognos