Using the dynamic query mode

The dynamic query mode offers query and data source optimizations to address query complexity, large data volumes, and timeliness expectations with improved query execution techniques.

For relational data sources, the dynamic query mode offers: JDBC connectivity, 64-bit connectivity, and in-memory caching. For dimensionally modeled relational data sources, it offers: JDBC connectivity, 64-bit connectivity, in-memory caching, null suppression optimizations, and master-detail optimizations.

You can use the dynamic query mode with many relational and OLAP data sources. To view an up-to-date list of data sources that are supported by the dynamic query mode, see the Supported Software Environments ( website.

Tip: You cannot use XML data sources with the dynamic query mode.

The dynamic query mode is recommended for new applications of IBM® Cognos® Analytics. However, you can enable the dynamic query mode for existing IBM Cognos Framework Manager projects. Migrating existing applications to the dynamic query mode requires that you adjust your model and modify your reports to resolve differences in the migrated reports. Report differences occur because of changes in query behavior. For example, changes in sorting, aggregation, and suppression require that you modify your existing reports. Stronger enforcement of best practices and query syntax in the dynamic query mode may result in warnings and errors from migrated reports.

For more information about working with the dynamic query mode in IBM Cognos Analytics version 11.0.x and previous versions, see the IBM Redbooks® publication IBM Cognos Dynamic Query (

How the dynamic query mode helps your organization

The following table outlines the benefits of the dynamic query mode for different user roles within your organization.
Table 1. User roles and benefits of using the dynamic query mode


Business problem


IT Manager and Data Professional

You have or are moving to Java™-based architectures and you require JDBC or Java connectivity to data sources.

Expanded data reach by using the Java connectivity to OLAP data sources, XMLA connectivity, and JDBC connectivity to many relational data sources.

IT Manager and IT Administrator

More database vendors exclusively support 64-bit computers. You want your applications to take advantage of your investment in 64-bit technology.

Support for 64-bit environments that provide better memory management and improved scalability and performance.

Modeler and IT Manager

Your business users want to quickly find answers when they are looking at information. As an IT manager, you want to provide this ability while maintaining the security of your applications.

In-memory caching stores both the query plan and the query results while maintaining the security permissions for each user. As a result, business users get information quickly without compromising security.

Cognos Analytics Director and IT Manager

You invested in OLAP and relational technologies and you want to optimize and gain the maximum value from your investment.

Optimized access to OLAP data sources through customized and enhanced MDX for specific versions of OLAP technology.

SQL generation optimizations for relational data sources.

Cognos Analytics Administrator

You find it challenging to easily troubleshoot what happens when a query is planned and executed.

Detailed logging and query visualization by using Dynamic Query Analyzer to visualize the logs and to troubleshoot.