Goal

IBM Cognos Analytics is a web-based, integrated business intelligence suite by IBM. It provides a toolset for reporting, analysis, scorecarding, and monitoring of events and metrics. It helps to understand the organization’s data and make effective business decisions.

This document describes how a Denodo Virtual DataPort (VDP) database can be accessed from IBM Cognos and how data from VDP views can be used from the Analytics Tool.

Cognos Analytics Introduction

IBM Cognos (IBM Cognos Analytics since version 11) offers a web administration portal to manage the server configuration, create and publish the reports/dashboards provided by the tool. IBM Cognos also includes a desktop modelling tool named IBM Cognos Framework Manager that can be installed either on the same computer as other Cognos components or on a different computer.

In version 11, IBM deprecated some components from previous versions (Cognos Query Studio, Cognos Analysis Studio and Cognos Workspace) integrating its functionalities into the web based portal.

Prerequisites

IBM Cognos 11.0.3 or later version is needed to be able to connect to Denodo VDP using the JDBC connector.

Adding the Denodo JDBC driver

The Denodo JDBC driver needs to be copied into the Cognos server files in order to be able to connect to a Denodo server. In order to do this, the following steps can be performed:

1. Get the Denodo JDBC driver located in the following path of the Denodo Platform installation:

<DENODO_HOME>/tools/client-drivers/jdbc/denodo-vdp-jdbcdriver.jar

2. Copy it to the following location in the Cognos server (for Cognos 11.0.x) :

<COGNOS_HOME>/drivers

3. Restart the Cognos server in order to be able to use the Denodo JDBC driver.

Adding a Denodo Data Server

1. Open the IBM Cognos Analytics Web Tool

2. In the left menu go to Manage > Data Servers and click on + button to add a new Data Server

3. Select Denodo as Data Server Type, a form will be opened to enter the connection details.

4. Fill the connection details and enter the VDP credentials. Check if the connection is correctly configured using the Test button.

Using the Data server to create Cognos Reports

As an example, let’s see how to create a Report with the data coming from a Denodo VDP database:

Creating a Data Module

To be able to create a report one or more data modules should be created with the desired views from the source.

1. Click on + > Data module.

2. Select Data servers, select the VDP server previously created and the schema where the desired views are created. Click Start.

3. Select the views that will be used in the Data module by dragging & dropping the views. Click on Save.

Creating a Report

1. Once the Data module has been created, can create a new Report Clicking on + > Report. You can select the desired theme and template for the Report.

2. On the Report creation screen, select the Source tab and click on the + button. A new dialog will be opened to select the previously created Data Module.

3. The views of the data module will appear on the left side of the screen. On the right side it is possible to design the report with the fields and the desired data. As an example, drag & drop a view from the left side to the right side to display the data in a table.

4. Click on the Run button to run the report in the desired format and the data from the VDP views will be shown.

Note: In some scenarios it was detected that the reports run quite slow. Setting or adjusting the Cognos-specific property ibmcognos.fetchBufferSize may help to improve the performance. This connection property needs to be specified by the Cognos administrator. For more information about this property, refer to the Cognos documentation.

Enabling the SSL protocol between IBM Cognos and Denodo VDP Server

It is possible to configure IBM Cognos to use the SSL protocol for the connections between IBM Cognos and the Denodo Virtual DataPort Server. In order to configure this, the following steps must be performed:

1. Enable SSL connections in the Denodo VDP Server as explained in the Enabling SSL/TLS in Denodo Platform Servers section of the Denodo Platform Installation Guide. This will generate a certificate (eg: selfcert.cer)
2. Add the certificate to the trustStore of the JRE used in the IBM Cognos server. In case the default IBM Cognos JRE is used, follow these steps:

1. Copy the server certificate to <COGNOS_HOME>/jre/lib/security
2. Execute “<COGNOS_HOME>/jre/bin/keytool” -importcert -file selfcert.cer -keystore <COGNOS_HOME>/jre/lib/security/cacerts -alias selfcert

3. Update the JDBC Data Server Connection to include the JDBC properties ssl=true and sslTrustServerCertificate=true.  For example: jdbc:vdb://<hostname>:9999/<database>?ssl=true&sslTrustServerCertificate=true

4. Restart the IBM Cognos Server.
5. Add the Denodo Data Server as explained before.

References

IBM Cognos 11.0 Installation and Configuration guide

ibmcognos.fetchBufferSize