Denodo Custom Wrapper for IBM Cognos TM1 - User Manual

Last modified on: 25 Jun 2024

You can translate the document:

Introduction

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

TM1 REST API

IBM Cognos TM1 offers access to its data through 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:

In the VDP Administration Tool or in Denodo Design Studio, go to:

From Denodo 7.0: File → Extension management

From Denodo 6.0: File → Jar management

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:

In the VDP Administration Tool or in Denodo Design Studio, go to: File → New… → Data source → Custom

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

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

[From 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 Cognos Authentication Manager (CAM) Authentication.

Namespace: It is an optional parameter. It should be used when the TM1 server uses Cognos Authentication Manager (CAM).

Password: Password to connect with the Cognos TM1 server.

Click on the “Save” button.

Create the base view

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

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

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 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 '\'.

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

In addition to MDX query and View Query parameters, there is an additional parameter:

Retrieve non-formatted values: If unchecked, values will be retrieved formatted. If checked, values will be retrieved raw, without format, as stored in Cognos. The data type of a column will always be a string, regardless of this parameter.

Max processed elements: Maximum number of tuples per axis that can be stored in the memory during processing of the MDX queries. This parameter is important with queries or views over cubes with at least one high cardinality axis. Default is 10000000 and it should not need to be changed except for very specific query processing scenarios.

Max size disk cache(MB): Maximum size of the disk-based cache to be used for temporary processing of the MDX query results. The result processing mechanisms will need to overflow to disk if memory processing space is not enough, but this should be needed only in quite extreme cases. This overflow is performed in the Operating System’s temporary folder. Default value is empty, so disk cache is disabled. When this parameter is needed, Max size disk cache must have enough space to store at least Max processed elements (a typical value would be 3000).

If VDP throws the following error "The cache memory is not enough, you have to reconfigure the parameters: Max processed elements and Max size disk cache": You should increase Max Processed elements to process all in memory or increase Max size disk cache and use the disk to store the temporary values, but query processing might be slower.

If VDP throws the following error "java.lang.OutOfMemoryError: Java heap space": you must increase the Max size disk cache and slow down Max processed elements.

Authentication

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

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.

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, username 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 that the backslash \ symbols are there in order to escape braces as required by VDP 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

PATCH

Access to the metadata of the cubes is not supported either.

USER MANUALS