Last modified on: 09 Jun 2020
IBM Cognos TM1 is an enterprise-planning software platform. Data is stored and represented as multidimensional OLAP cubes.
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.
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).
To import the custom wrapper, follow these steps:
To create a new IBM Cognos TM1 custom data source:
com.denodo.connect.cognos.tm1.wrapper.CognosTM1CustomWrapper
To create a new base view using the IBM Cognos TM1 data source:
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.
The wrapper supports two security modes of the TM1 REST API:
In the first place, you have to configure the parameters of the data source.
Example of configuration of data source
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
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
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:
Access to the metadata of the cubes is not supported either.