IBM Cognos TM1 is an enterprise-planning software platform. Data is stored and represented as multidimensional OLAP cubes.
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.
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 to 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 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
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: CREATE, READ using GET, DELETE; UPDATE OR PATCH. Access to the metadata of the cubes is not supported either.