This chapter provides an overview of the dynamic query layer of IBM Cognos Business Intelligence (BI) software. The chapter includes an introduction to and history of the dynamic query layer, descriptions of the architecture, and high-level guidance for determining which technology within the dynamic query layer can best satisfy your business requirements.

The chapter contains the following sections:

Cognos Business Intelligence (BI) helps you make better and smarter business decisions faster. Advanced visualization capabilities bring your data to life, and you can consume your Cognos BI reports, scorecards, and dashboards through Internet browsers and mobile devices or have them sent to your email inbox. In addition, intuitive self-service interfaces allow you to explore your data and collaborate over the insights you uncover.

The Cognos BI server interprets user gestures and report specifications and translates them into data-retrieval queries that are tailored to the most popular RDBMS, OLAP, CRM, and ERP sources. The term dynamic query refers to the planning and execution of queries using the Java-based extensible query engine in the Cognos platform. Leading practices, learned over decades of BI software development, were applied to its design. Dynamic query retains result sets and metadata captured from optimized queries to data sources in a 64-bit, in-memory cache. It can reuse these result sets and metadata to minimize the wait times for future requests.

The dynamic query layer was developed to meet requirements for interactive reporting and ad hoc analysis. It employs sophisticated, multiphase query optimization techniques and can dynamically alternate between SQL and MDX processing, depending on what best suits the scenario.

Dynamic query has several advantages. Advanced in-memory caching and aggregation can reduce data warehouse workload. Users are provided with a consistent experience, no matter what the data source is. In addition, simple cache administration and query visualization tools help reduce total cost of ownership. IT organizations can also take advantage of improved query performance thanks to the reduction in query planning and execution, along with lighter database server workloads.

This book explains how Cognos BI administrators, authors, modelers and power users can use the dynamic query layer effectively.

The dynamic query layer refers to the query service of Cognos 10, which is powered by an extensible query engine written in Java. The query layer offers the following key capabilities:

New projects in Cognos BI version 10.2.1 are set to the dynamic query mode, which is powered by an eXtensible Query Engine (XQE) written in Java. XQE, the engine behind the Cognos BI query service, embraces the principles of abstraction and extensibility, allowing it to evolve into a more efficient query planner, processor, and executor with every new version of Cognos BI.

All techniques and product behaviors that are described in this book assume that the project is using the dynamic query mode. However, existing packages can be set to the compatible query mode, which, for reasons of compatibility with previous versions, maintains query behaviors consistent with version 8.4.1 of Cognos BI.

The C++ query engine of version 8.4.1 addressed the analytical challenges of its day, but it was limited in two fundamental ways. The first is that it is 32 bit, which is a problem because effective caching of enterprise data requires 64-bit addressable memory. The second is that its architecture cannot easily adapt to the new trends emerging in the big data era.

XQE was developed to address these limitations and accelerate the improvements to query performance that is delivered in new versions of Cognos BI.

Some of the content of this section was previously published in IBM developerWorks®¹.

The query service accepts data and metadata requests (through the report service component) from authoring interfaces such as IBM Cognos Report Studio, IBM Cognos Report Viewer, and other clients. It returns the requested data or messages in a structured response to the report service component that formats the result for the client. Figure 1-1 on page 4 presents the workflow of requests and responses between these components.

Figure 1-2 shows the internal architecture of the query service, which consists of the following major components:

The transformation engine does not implement any query planning logic alone. Instead, it provides an execution environment for query transformations in the transformation libraries, thus separating planning logic from the engine. The transformations implement query planning logic for all supported query types and functionality. When there are no more transformations to be applied, query planning is complete and the transformation engine passes the resulting run tree to the query execution engine.

The query execution engine can execute any query request, independent of the type of query and target data source. The engine represents all query results in memory in a single format that encompasses both dimensional style (with axes, dimensions, and cells) and relational style (with a tabular format of rows and columns). This allows the engine to combine SQL and MDX queries in a single run tree, thus enabling simplicity of representation, flexibility in post-processing, and streamlined query performance. To process the two types of queries, the query execution engine contains both SQL and MDX engines.

The SQL engine obtains data directly from the RDBMS adapter. The query execution engine updates the secure data cache with dimensional data for future reuse. The MDX engine obtains dimensional data either directly from the OLAP adapters or from the data cache. It also updates and reuses dimensional metadata in the secure member cache. The cache security features ensure that, by default, no sharing of secured data ever occurs between users with different security profiles.

The RDBMS and OLAP adapters translate Cognos SQL and MDX queries to a query dialect suitable and optimized for each data provider. The adapters send the query and fetch results through the provider’s proprietary interface or a supported standard interface such as JDBC. There is only one RDBMS adapter, which uses a JDBC interface, because all supported relational providers are accessible through JDBC. The RDBMS adapter supplies data to the SQL engine in the query execution engine while the OLAP adapters supply data to the MDX engine.

Two major components are involved when the query service processes requests: the transformation engine and the query execution engine. Both engines share a common environment and operate on the same query structures: the plan tree and the run tree.

An XML parser converts an incoming report request into an initial plan tree, including any embedded SQL, HiveQL or MDX queries. The tree has two main branches: the query, describing what the user wants to see, and the QueryResultSet, describing how the user wants to see the results (such as in a list or crosstab format).

With the tree in place, the planning process can begin. The transformation engine checks each node in the plan tree to see which query transformations apply to that node. The query transformations implement the logic that transforms a Cognos query into one or more SQL, HiveQL or MDX queries that the one or more target data sources can understand. The transformations also add nodes representing any data manipulation and local processing operations that might be required to produce the requested result.

The transformations occur in several passes, with potentially several iterations per pass, until all possible transformations have been applied. During this process, the transformation engine connects to the IBM Cognos 10 Content Manager to look up model information that applies to the query being processed. When all transformations have been applied, the plan tree has morphed into a run tree and is ready for execution.

The run tree is at the heart of query execution. Results flow from the leaf nodes of the run tree to the root node, where the result is represented in a format suitable for the report service to render the report output. A run tree consists of various types of nodes, each representing a different function:

In the simplest form of a dimensional style query, MDX execution nodes cause the MDX engine to pull data from the data cache (if the cache is not available, the engine sends an MDX query to an OLAP data source). The results are stored in the data cache and go through some data manipulation nodes in the run tree, which might alter the shape of the results. After that, local processing nodes flatten the multidimensional result and sort the data before returning the requested results to the report service.

In a more complex query, such as one against a DMR package, the report request is dimensional in nature, but the data source is relational. This means the query generated for the report is MDX, but the data source only understands SQL. Thus the run tree consists of a mixture of all four types of execution nodes. In this event, the execution engine first sends SQL queries to the relational data source. Local processing nodes then reshape the results into dimensional form for storage in the data cache, from which MDX nodes query data just as they would from a dimensional data provider. Subsequent execution proceeds as it would for a dimensional query against an OLAP data source.

Cognos offers several query technologies to address your analytical needs. This section provides guidance that can lead you to the best solution for your requirements.

Table 1-1 summarizes the guidance regarding when to employ pure relational, IBM Cognos TM1®, IBM Cognos Dynamic Cubes, or DMR analytics for different application objectives. The remainder of this section gives details about the optimal scenarios for using each of these analytic technologies.

A pure relational package is one created in IBM Cognos Framework Manager over a relational database without modeling any dimensional context. For many applications, there is no need for OLAP functionality, such as when the application is primarily for ad hoc queries or pre-authored reports with no requirement for drilling up and down. In these cases, you might choose to publish packages based on query subjects alone (with no dimensions defined). A pure relational approach is best for scheduled reports run against a transactional or operational database. It works well for simple list reports that aggregate tens of millions of records. It is common for the target report in a drill-through definition to be purely relational and contain leaf-level records in a tabular format.

OLAP technologies work best for interactive analysis involving frequent examination of cross sections of your business. OLAP allows users to drill up and down business hierarchies and interactively create new views of data for specific business contexts. Cognos BI supports several proprietary and third-party OLAP solutions, including Microsoft Analysis Services and Oracle Essbase. In addition, it offers intuitive, web-based user interfaces such as Cognos Workspace Advanced and Analysis Studio that are conducive to navigating your business hierarchies.

IBM Cognos Dynamic Cubes is the preferred OLAP data source for performance reasons and is recommended for use unless a specific constraint prevents you from doing so. This section contains further guidance regarding OLAP technologies in Cognos BI.

Cognos TM1 is primarily positioned for financial planning applications where users are submitting planned budgets, contributions, and resources to a centralized server; everyone is working with the same basic facts.

The powerful OLAP server of TM1 scales writers to the thousands. Its configurable model, rules, and user interface layer are used to satisfy a broad range of planning applications (financial, resource, projects, demand, merchandise, customer churn, customer and product profitability, and so on).

When TM1 is employed to satisfy write-back requirements of users, it is preferred that those same TM1 cubes be used for business intelligence reporting and analysis. However, if no such write requirements exist, an alternative OLAP solution is likely best. This is because on-demand aggregations and calculations significantly affect first execution (non-cached) query performance for TM1 cubes when they are loaded with data volumes exceeding 10 GB of disk data or 10 million records. The nature of a MOLAP² (self-contained cube) architecture, such as that of TM1, limits data volumes to what can efficiently be contained in memory.

TM1 is designed for volatility in order to support large numbers of users writing back to the system. For read-only reporting and analysis requirements, Cognos Dynamic Cubes can perform better and satisfy a greater number of users.

Cognos Dynamic Cubes is an extension of the dynamic query layer that uses substantial in-memory data assets and aggregate awareness to achieve high-performance, interactive analysis and reporting over terabytes of warehouse data. Cognos Dynamic Cubes requires a data warehouse that is structured in a star or snowflake schema to maximize the performance characteristics of the solution.

Cognos Dynamic Cubes is the most scalable, high-performing OLAP solution offered by Cognos BI. Cognos Dynamic Cubes is the preferred solution for customers who have a star- or snowflake-structured data warehouse and want to enable users to perform self-service analysis and reporting on large and growing data volumes.

A DMR package enables an OLAP experience on top of a relational data source. The dimensional layer is defined in Cognos Framework Manager.

DMR analytics give you great control over latency. In Report Studio, you can specify at the query level which queries within a single report can benefit from caching versus which ones should retrieve live data for every request.

DMR analytics also give you great control over security. You can leverage the security defined in your database by associating Cognos users, groups, and roles with the corresponding users, groups, and roles in the RDBMS. In Framework Manager, you can define data-level security (user-specific filters) or object security (to prevent metadata objects from being accessible to certain users). The flexibility with respect to security requirements comes at the expense of less cache sharing. Unlike Cognos Dynamic Cubes, where security is applied on top of the same cache so that all security profiles are engaging the same cache, with DMR, there are separate caches for each security profile.

DMR analytics can have a low total cost of ownership. Aside from the administrative overhead associated with clearing and priming the cache, this approach enables you to essentially publish and forget, because you do not need to worry about starting or building cubes.

Consider DMR analytics when particular requirements cannot be satisfied by Cognos Dynamic Cubes. The following most common requirements necessitate DMR analytics instead of Cognos Dynamic Cubes:

Using DMR instead of Cognos Dynamic Cubes is preferred in the following scenarios because of the smaller memory footprint and lower administrative overhead:

Figure 1-3 highlights the difference between a DMR package and Cognos Dynamic Cubes with respect to security applied to the corresponding in-memory cubes. A DMR package filters the data used to build a cube based on security filters that are expressed in relational terms. In contrast, Cognos Dynamic Cubes reads the data as a super user with the highest authorization available and then applies security filters expressed in dimensional terms to the same cube.