Overview of Dynamic Query Mode - IBM Cognos

In this section, we provide a basic overview of the high performance in memory query mode known as Dynamic Query Mode.

What is Dynamic Query Mode

Dynamic Query Mode is an enhanced Java-based query execution mode that addresses increasing query complexity and data volumes through key query optimizations and in- memory caching. These improvements benefit the query planning, query execution, and results that are returned while maintaining the security context.

Depending on the data source that you use, the Dynamic Query Mode offers different performance optimizations to improve the speed of data analysis. Table lists these optimizations.

Key optimizations

Key optimizations

In-memory caching

As users run and build interactive and batch reports on packages that are Dynamic Query Mode-enabled, a cache is built. Running and building reports requires several requests, such as metadata and data requests, to be made to the data source. As these requests return, the Dynamic Query Mode caches the results, whether those results are metadata or data, for future re-use. This caching results in fewer request sent to the underlying data source and, thus, provides better query performance.

This cache is data source and user specific and can be shared across report processes that are running on the same dispatcher.

Dynamic Query Mode cache: The Dynamic Query Mode cache is not maintained for packages that are based on IBM Cognos TM1 cubes, because IBM Cognos TM1 implements its own caching.

Enhanced null suppression

Deeply nested reports generate large amounts of cells. In addition, there is a higher probability that null values can occur where the relationship between the nested edges returns no data. These issues can make reports large and difficult to read. The higher the amount of cells, the longer it takes for the report to evaluate which rows and columns contain only null values.

IBM Cognos Query Studio, IBM Cognos Business Insight Advance, and IBM Cognos Report Studio include an enhanced suppression feature that can reduce the amount of cells that need to be evaluated to achieve the desired result.

Optimized master-detail relationships

A master-detail relationship links information and queries from the following types of data objects within a report:

  • A master data object
  • A detail data object

Using a master-detail relationship, you can use a single report to display information that normally would take multiple reports. You can, for example, create a list report that contains an Order Method and link a crosstab inside this list to display detailed information in the context of this particular Order Method.

In Compatible Query Mode, these master-detail relationships generate a separate query for every element in the master result set. Thus, the underlying data source has to endure high work loads, which can impact performance.

With the Dynamic Query Mode, the master query is pushed as a separate edge to the detail query, resulting in only one query sent to the data source for all output formats.

Query visualizations

To get the best performance possible from your IBM Cognos investment, it is important that you can troubleshoot unexpected results or slow run times easily. The Dynamic Query Analyzer allows for easy access and analysis of Dynamic Query Mode log files using a graphical user interface.

Why use Dynamic Query Mode

Using Dynamic Query Mode and its features offers some significant benefits to the IBM Cognos administrator. The new query mode can provide the following benefit to both the administrator and users:

  • Better query performance
  • Re-use of frequently used results
  • Easily identify issues

Enhancements in the inner workings of the query planning process have generated more optimized queries that are specific to the OLAP data source vendor and version. The query mode is also streamlined to take advantage of the metadata and query plan cache, which results in faster executing queries.

The implementation of various caching containers allows report authors and analysis users to take advantage of another performance optimization. The cache allows for report authors to make minor modifications, such as adding a calculation, to a report without this change resulting in another query to the underlying data source. In case the added calculation is based on measures already present in the initial report, the modification can be performed on the measures already in cache. The same is true for executing reports in different output formats. Changing a reports output format from, for example, CSV to PDF format does not trigger a new query to the data source because all that data is already in the cache.

Analysis users also benefit from caching but in a slightly different way. The nature of analyzing data requires a lot of metadata requests to present results to the user. These requests can create a high load on the underlying data source, affecting both speed and performance. With the introduction of Dynamic Query Mode, more types of metadata results can be cached, which results in faster navigation and exploration of the hierarchy.

Technical overview

To truly understand the nature of the features in Dynamic Query Mode, we need to take a quick look at how the query mode is built.

Dynamic Query Mode is a Java-based query mode that addresses the increasing query complexity and data volumes. Implementing the query mode in Java allows it to take advantage of a 64-bit enabled Java Virtual Machine (JVM).

For Dynamic Query Mode, running in a 64-bit JVM provides the advantage of an increased size of the address space. Thus, the 64-bit JVM is capable of addressing more virtual memory than its 32-bit variant. This increase in addressable memory allows Dynamic Query Mode to maintain more of the metadata and data cache in memory as long as needed.

To improve performance and reduce interference with the dispatcher or content manager, Dynamic Query mode is spawned in its own JVM instance as a child process of the dispatcher. No Java heap memory space is shared with the content manager or dispatcher, which enables the Dynamic Query Mode cache to use as many resources as possible. From a software architecture point of view, Dynamic Query Mode consists of the following main components:

  • The transformation layer
  • The execution layer

The transformation layer provides for a runtime environment where, during the planning phase, query transformations can be performed. The execution layer executes and processes the result from the planning phase during the execution phase.

When Dynamic Query Mode receives a query request, it converts this request into a tree structure called a plan tree. After this conversion is complete, the tree can be passed on to the transformation layer so that the query planning can begin. The transformation layer then goes through every node on this tree and checks for nodes that qualify for transformation. This process can take several iterations to finish. It is these transformations that allow the query mode to generate customized and enhanced MDX that is specific to your OLAP source. These transformations that implement the query planning logic are contained within separate transformation libraries and are called by the transformation layer to execute the node conversions.

When the planning iterations are complete, the plan tree is transformed in to a run tree and is now ready for execution by the execution layer.

Both layers have security-aware, self learning caching facilities for improved performance. Every time a query is executed, the results from the planning phase and execution phase are placed into the respective layer’s cache container. These layers reuse data from the cache only if the data that is needed exists and if the users security profile matches.

After the execution layer completes its process, the results are passed onto the report service to render the report.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics