Big Data Analytics with IBM Cognos BI Dynamic Query

IBM® 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. With intuitive self-service interfaces, you can 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.

This IBM Redbooks® Solution Guide introduces IBM Cognos Dynamic Query and has an overview of its features, benefits, and architecture. This information is intended for Cognos BI administrators, authors, modelers, and power users who need to understand the business value of the dynamic query layer.

Figure 1 illustrates how the Cognos Dynamic Query layer is integrated into the Cognos BI stack.


Figure 1. IBM Cognos Dynamic Query integrated into the Cognos BI stack


Did you know?

• Over 2.5 quintillion bytes of data are created daily.
• The architects working on the design of the Cognos query engine literally have multiple decades (over 25 years) of experience in business intelligence.
• The lessons learned over decades of leading the BI industry culminated in the design of this next generation query engine.

Business value

The success of your business depends on your ability to analyze information, quickly find the right answers, and make timely responses. To meet this challenge, IBM® Cognos® Business Intelligence provides an enhanced Java-based query execution mode.

The heart of the Cognos platform is its service-oriented architecture (SOA). It is built on a true set of peer-to-peer services that are interoperable and extensible through standard interfaces and protocols. This feature enables organizations to consume their big data analytics through the Web, mobile devices, or via e-mail right to the user's in-box. A low footprint, highly automated solution that can distribute the content mindful of security however your users want to consume it; whether it be through a browser or mobile device.

Table 1 outlines benefits of the dynamic query mode for common business problems organizations are facing today.

Table 1. How the dynamic query mode helps your organization

Business problem	Solution
Your organization has or is moving to Java-based architectures and you require JDBC or Java connectivity to data sources.	The dynamic query mode provides expanded data reach to support connectivity to various data sources. New data sources are frequently added.
More database vendors exclusively support 64-bit platforms. You want your applications to take advantage of your investment in 64-bit technology.	The IBM Cognos Business Intelligence query platform supports 64-bit environments, which provide better memory management and improved scalability and performance.
Business users want to quickly find answers when looking at information. The IT organization wants to provide this capability while maintaining the security of the applications.	With the dynamic query mode, business users get the correct information quickly without compromising security. In-memory caching stores both the query plan and the query results while maintaining the security permissions for each user.
Your organization invested in OLAP and relational technologies and you want to optimize and gain the maximum value from your investment.	The dynamic query mode provides optimized access to OLAP data sources. It provides customized and enhanced MDX for the specific source and version of your OLAP technology, and it harnesses the intelligence of the OLAP data source. The dynamic query mode provides SQL generation optimizations for relational data sources.
It is a challenge to easily troubleshoot what happens when a query is planned and executed.	The dynamic query mode provides detailed logging and query visualization. IBM Cognos Dynamic Query Analyzer enables you to visualize the logs and helps you troubleshoot.

Solution overview

IBM Cognos Business Intelligence provides reports, analysis, dashboards, and scorecards to help support the way people think and work when they are trying to understand business performance. You can freely explore information, analyze key facts and quickly collaborate to align decisions with key stakeholders.

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 a number of 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. 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.

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

• Open access to the most popular RDBMS, OLAP, CRM, and ERP data sources
• Query optimization techniques to address complex analytical requirements, large and growing data volumes, and expectations for timeliness
• Enterprise-level scalability and stability
• Intelligent combinations of local and remote processing
• Federation of multiple heterogeneous data sources
• OLAP functionality for relational data sources when using a dimensionally modeled relational (DMR) package or Cognos Dynamic Cubes
• Security-aware caching
• 64-bit processing
• JDBC connectivity to relational data sources
• Query visualizations for ease of maintenance

New projects in IBM Cognos Business Intelligence version 10.2.1 and later are set to 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 IBM Cognos BI.


Solution architecture

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

• Transformation engine and transformation libraries
• Query execution engine
• Metadata cache
• Data cache
• RDBMS and OLAP adapters

Figure 2. Internal architecture of the query service

The transformation engine does not implement any query planning logic by itself. 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 IBM 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; the OLAP adapters supply data to the MDX engine.


Usage scenarios

IBM Cognos offers several query technologies to address your analytical needs. This section provides guidance that will lead you to the best solution for your requirements.
Table 2 summarizes the guidance regarding when to employ pure relational, IBM Cognos TM1®, Cognos Dynamic Cubes, or DMR analytics for different application objectives.

Table 2. Summary of analytic technology selection guidance

Application objective	Preferred technology
Reporting on leaf-level records Static reports (no user requirements for navigating through business hierarchies) Simple list reports	Pure relational
Users writing back to the same data source being analyzed What-if analysis Volatile data due to planning and budgeting applications	IBM Cognos TM1
Self-service interactive analysis High performance on large and growing data volumes Data warehouse structured in star or snowflake schema	IBM Cognos Dynamic Cubes
Interactive analysis on operational or transactional database Tight control over caching Tight control over security	Dimensionally modeled relational (DMR)

Integration

IBM Cognos Dynamic Query is tightly integrated into the Cognos Business Intelligence stack, and its data can be surfaced through any of the Cognos interfaces. With this method, existing customers can integrate this technology into their application environment without affecting existing users. Such users are already familiar with interfaces such as Report Studio, Business Workspace, and Business Workspace Advanced (previously named Business Insight and Business Insight Advanced).

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 3 presents the workflow of requests and responses between these components.


Figure 3. Query service request and response workflow


Supported platforms

For information about software environments that are supported in IBM Cognos Business Intelligence V10.2.1, see the IBM Cognos Business Intelligence 10.2.1 Supported Software Environments web page:
http://www.ibm.com/support/docview.wss?uid=swg27037784


Ordering information

Ordering information is listed in Table 3.

Table 3. Ordering program numbers and program names

Program number	Program name
5724-W12	IBM Cognos Business Intelligence V10.2.1
5724-W68	IBM Cognos Business Intelligence PowerPlay® V10.2.0
5724-W19	IBM Cognos Business Intelligence Reporting V10.2.0
5724-W13	IBM Cognos Data Manager V10.2.0
5724-W20	IBM Cognos Mobile V10.1.0

Related information

For more information, see the following documents:

• IBM Cognos Dynamic Query, SG24-8121
  http://www.redbooks.ibm.com/abstracts/sg248121.html
  
• IBM Cognos Dynamic Cubes, SG24-8064-00
  http://www.redbooks.ibm.com/abstracts/sg248064.html
  
• Big Data Analytics with IBM Cognos Dynamic Cubes, TIPS0942
  http://www.redbooks.ibm.com/abstracts/tips0942.html?Open

• IBM Cognos Business Intelligence V10.1 Handbook, SG24-7912
  http://www.redbooks.ibm.com/abstracts/sg247912.html?Open
  
• IBM Cognos Business Intelligence V10.1: Intelligence Unleashed, REDP-4693
  http://www.redbooks.ibm.com/abstracts/redp4693.html?Open
  
• Cognos Business Intelligence 10.2.1 documentation
  http://www.ibm.com/support/docview.wss?uid=swg27037021
  
• IBM Cognos Business Intelligence 10.2.1 information center
  http://pic.dhe.ibm.com/infocenter/cbi/v10r2m1/index.jsp

• Cognos software product page
  http://ibm.com/software/analytics/cognos/

• IBM Offering Information page (announcement letters and sales manuals)
  http://www.ibm.com/common/ssi/index.wss?request_locale=en
  
  On this page, enter IBM Cognos Business Intelligence, select the information type, and then click Search. On the next page, narrow your search results by geography and language.