Denodo Custom Wrapper for IBM Cognos TM1 - User Manual

Introduction

IBM Cognos TM1 is an enterprise-planning software platform. Data is stored and represented as multidimensional OLAP cubes.

TM1 REST API

One of the ways that TM1 offers access to its data is its TM1 REST API, this API provides an Open Data Protocol (OData) Version 4 interface to access data and metadata. And it allows clients to query and update data using a REST interface.

Denodo Custom Wrapper for IBM Cognos TM1

This Denodo wrapper allows you to access IBM Cognos TM1 using the TM1 REST API. You can access data executing predefined views or executing MDX queries. MDX (MultiDimensional eXpressions) is a query language for online analytical processing (OLAP).

Usage

Import the Custom Wrapper

To import the custom wrapper, follow these steps:

1. In the VDP Administration Tool, go to:

 

• From Denodo 7.0: File → Extension management
• From Denodo 6.0: File → Jar management

2. Click on “Create” button and select the “denodo-cognos-tm1-customwrapper-{denodoversion}-{version}-jar-with-dependencies.jar” file, located in the dist folder of the Denodo Wrapper for IBM Cognos TM1 distribution, downloaded from the Denodo Support Site.

Create the IBM Cognos TM1 data source

To create a new IBM Cognos TM1 custom data source:

1. In the VDP Administration Tool, go to: File → New… → Data source → Custom

2. In the “Create a New Custom Data Source” window, do the following:

• Set a name for the new IBM Cognos TM1 data source in the “Name” field.

• Click on “Select Jars” and select the file imported in the previous section.

• The “Class name” field must be filled with:

com.denodo.connect.cognos.tm1.wrapper.CognosTM1CustomWrapper

3. [Denodo 7.0] Click to refresh the input parameters of the data source.

4. [Denodo 7.0] Configure the parameters:

• URL: It is a mandatory parameter. It is the service root URL of the TM1 REST API.  http://server:port/api/v1/.
• Authorization type: You can use Basic Authentication or CAM Authentication.
• Namespace: It is an optional parameter. It should be used when the TM1 server uses Cognos Authentication Manager (CAM).
• Login:   Login to connect with the Cognos TM1 server.
• Password: password to connect with the Cognos TM1 server.

5. Click on “Save” button.

Create the base view

To create a new base view using the IBM Cognos TM1 data source:

1. Double-click on the IBM Cognos TM1 data source and then click on “Create base view”.
   

2. Configure Base View:
   
   Note that in Denodo 6.0 both the parameters explained below and the ones in the previous section for the definition of the Data Source will be configured at this level.

You can access the data of the cubes of Cognos TM1 server, in two ways: executing an MDX query or accessing to a view. These are mutually exclusive. So, you have to introduce only one of the following parameters.

• MDX Query: this query will be executed in the request to the server. You have to escape the symbols '{','}' with a '\'.

        

• View query: this is the part of the URL that references the view that you want to import.

Authentication

The wrapper supports two security modes of the TM1 REST API:

1. HTTP Basic: To use this security mode, choose Basic in the parameter Authentication Type.  The user name and password are passed on to the TM1 Server, with this security mode Login and Password parameters are mandatory.

2. CAM Authentication: If you want to connect to the Cognos TM1 server using CAM authentication, you have to choose CAM in the parameter Authentication Type. CAM credentials (a namespace, user name and password pair) are passed on to the TM1 server, so Namespace, Login and Password parameters are mandatory

Use Case

In the first place, you have to configure the parameters of the data source.

Example of configuration of data source.

Example with MDX Query

In this example we want to import the result of the execution of the following MDX query (note the backslash \ symbols are there in order to escape braces as required by VDP’s administration tool):

\{"MDX":"SELECT [product].[Product ALL] on ROWS, \{[Version].[Version 1],[Version].[Actual],[Version].[Rate Variance],[Version].[Volume Variance],[Version].[Explanation]\}  on COLUMNS  FROM [Revenue Reporting]  WHERE [organization].[101],[Channel].[10],[Revenue].[Gross Revenue],[Month].[Jun],[Year].[Y2])"\}

The full query for this case would be:
http://192.168.157.4:8014/api/v1/ExecuteMDX?$top=2&$skip=2&$expand=Axes($expand=Tuples($expand=Members($select=*))),Cells

Example of MDX Query

The schema and data of the base view can be seen in the images below.

MDX Query Base View

MDX Query base view execution

Example with View query

In this example, we want to import the following view of Cognos TM1: Cubes('Revenue')/Views('Default').  

The full query of this example would be:

http://server:port/api/v1/Cubes('Revenue')/Views('Default')/tm1.Execute?$expand=Axes($expand=Tuples($expand=Members($select=*))),Cells

View Query Base View

View Query Base View Execution

Limitations

This wrapper only supports executing queries (view or MDX) that return a cell set that contains the cells and values, in these queries the HTTP method POST is used. The rest of the uses of the TM1 REST API are not supported by this wrapper: CREATE, READ using GET, DELETE; UPDATE OR PATCH. Access to the metadata of the cubes is not supported either.