REST API

What is the TM1 REST API?

The TM1 REST API is a new way of accessing data and everything else in TM1. Rather than being a proprietary API like old TM1 interfaces it is based on web standards making it accessible to a wide range of developers.  

REST stands for REpresentational State Transfer and is an architectural style (not a standard) that has become the common way of designing API for applications on the internet. The TM1 REST API does however support the OData standard (v4) which provides a common way for accessing data through queries and also updating data. OData is used by multiple vendors and is supported by companies like Microsoft, SAP and of course IBM

If you want to become a true TM1 REST API guru you should read up the documentation on the odata.org website. TM1 supports v4 of the standard, you may find examples out on the web that are an earlier version of the standard that won't work with TM1.

An important distinction to make about REST and OData is that neither of these are programming languages. The REST API is language agnostic, you can use the language of your choice: Java, JavaScript, Python, C#, Visual Basic, etc. All of these languages have HTTP libraries that will allow you to access the REST API.

What is great about the REST API?

  • It is fast! The REST API is built in to the TM1 server, there is no external web servers or parts you need to install. Because of this the REST API has direct access to all of that data stored in memory in your TM1 server.
  • It is fast over slow networks! The REST API is optimized for access over the internet, it takes advantage of all of the goodness that is built into the HTTP protocol such as compression, streaming, etc.
  • Did I mention it is fast? In our applications, we are noticing no discernible difference in speed when accessing applications from a local server to one that is hosted on the other side of the world. Our application framework Canvas optimizes this further by batching requests so they are returned in milliseconds rather than seconds.
  • You can use any programming language. Whatever your favorite programming language is the chances are you can use it with the REST API. As long as there is a HTTP library for your language, you can use it. Examples of these are:
    • Java: Apache HTTP Client, okHttp, etc.
    • C# and VB.NET: Built into the .NET Framework: System.Net.WebClient and System.Net.HttpWebRequest
    • JavaScript: jquery, Angular $http.
    • Python: httpie.
    • Others: Search github.
  • It is very easy to get most information out of TM1, there is even a built-in document that describes the API called $metadata (more on that later).
  • It is cloud and big data enabled! I just added that to make the article sounds cooler.

What ARE the hard bits ABOUT the REST API?

If we are going to make an objective assessment of the REST API we also need to understand that it isn't all smooth sailing with the REST API. In fact, because of the points below Cubewise decided to build a framework on top of the REST API called Canvas to help TM1 developers build beautiful user interfaces without all of the scary bits.

  • It is low level. The REST API is built to access TM1 objects directly, this is easy for many queries but is more difficult for updating data, accessing application entries and a number of other things.
  • Difficult for the traditional Accountant who recently became a TM1 Developer. As stated before, the REST API can be complicated for people without an IT background.
  • Little cross-over from existing TM1 concepts and terminology. If you are a long time TM1 person you will be very familiar with functions such as DBRW and SUBNM as well as Active Forms and Slices. These concepts don't exist in the REST API but do in Canvas.

What can you use the REST API for?

Anything, the REST API is NOT a user interface however, it is a way to access objects/data in TM1. It could be used for pretty much anything:

  • As an ETL tool to move data into and out of TM1.
  • As a data source for a user interface.
  • To sync data between TM1 servers.
  • To backup data to another source or file. 
  • Create, update and delete TM1 objects.

Where to next?

Over the coming weeks we will be posting regular articles on how to use the REST API including examples. We will also provide a comparison and reasons why we developed Canvas to leverage the goodness of the REST API and provide something that is easy to work with for regular TM1 developers. 

Stay tuned!

Enabling the TM1 REST API

The TM1 REST API is not enabled by default, you need to update your tm1s.cfg on your TM1 server with the following parameter:

  • HttpPortNumber=8881

The port number can be anything but not be used by any other application. You must make sure you use a different port number for each TM1 instance (server). 

You now need to restart the instance of TM1 for it to take effect using the Windows Services panel:

To see if the REST API is working open your favourite browser (my recommendation is Chrome) and enter the following URL (replace the port number with your one). It is important that you enter the URL exactly as it is below. Note the https as the protocol, TM1 uses SSL by default.

https://localhost:8881/api/v1/$metadata 

Because the SSL certificate used by TM1 is for tm1server rather than localhost you will receive an error about your connection not being private (example of Chrome below). It is safe to ignore the error (in development only) so click on ADVANCED and then Process to localhost (unsafe)

You will now see the content below, which is the TM1 REST API $metadata page that describes all of the objects / actions you can take against the REST API.

Retrieving Information from the REST API (The GET Request)

It is very simple to test out the functionality of the REST API using your browser. In the previous article I discussed how you both enable and connect to the REST API. Here I am are going to describe how you query the REST API to retrieve information about your TM1 model.

Before we get into the detail we need to understand a couple concepts about REST APIs in general. In REST terminology TM1 has a number of resources that can be retrieved, created, updated and deleted. From a TM1 perspective these are just objects: cubes, dimensions, processes, chores, cells, etc. 

Each resource has a unique URL that identifies what information is to be retrieved from TM1. For example if we want to retrieve information about a cube called General Ledger we would issue a GET request using the URL:

https://localhost:8881/api/v1/Cubes('General Ledger')

A GET request is called a HTTP method and TM1 REST API supports 4 different HTTP methods:

  • GET: Retrieves information about the resource specified in the URL.
  • POST: Creates a resource in the TM1 server.
  • PATCH: Updates an existing resource in the TM1 server.
  • DELETE: Deletes an existing resource from the TM1 server.

The GET request is something that can be executed easily from your browser by typing the address into the Address Bar and pressing Enter. The other methods can only be executed in the browser with the assistance of an add-in like Postman (for Chrome), I will provide more information on how to do this in subsequent articles.

FIRST EXAMPLE - RETRIEVING A LIST

The first thing we are going to retrieve is a list of the cubes in our TM1 model. Go to your browser and type in the below URL and press ENTER:

https://localhost:8881/api/v1/Cubes

NOTE: If your TM1 model has SSL enabled (the default) you will receive a warning about it being an untrusted or insecure connection. It is ok to ignore this message and proceed. 

You will be greeted by a pop-up dialog like the image below that will ask you for your TM1 credentials. This dialog uses what is called BASIC authentication and it is only supported when using traditional TM1 security, i.e. IntegratedSecurityMode 1 or 2. If you are using CAM security you should revert back to mode 1 or 2 on a dev/test environment so you can follow these examples.

Enter your regular TM1 credentials in this box and press Log In, i.e. if you are using the Planning Sample model for testing it would be: admin and apple. You will then be presented with a full list of every cube in your model including the rules. It will also look like a jumbled mess like the screenshot below:

This data is the JSON response from TM1, JSON is a text based format that is human readable, supported by nearly every programming language and is natively supported in web browsers. To help you understand what this information is I recommend installing a Chrome add-in called JSON Formatter, it will turn that previously ugly mess into this:

SECOND EXAMPLE - RETRIEVING A SINGLE OBJECT

We can see from the screenshot above that TM1 is returning a list of cubes in the TM1 model. In this next example we are going to retrieve just a single object, in this case the General Ledger cube:

https://localhost:8881/api/v1/Cubes('General Ledger')

Enter the above request into the browser address by appending ('General Ledger') and press Enter. Make sure you use single quotes, it is best to type these directly into the browser or use a text editor like Notepad or Notepad++. If you type the same single quotes into Microsoft Word or Outlook it will convert them to Smart Quotes which aren't supported.

You will also note that any spaces are automatically converted by the browser to %20. This is called encoding the URL, for a full list how URLs should be encoded see this article.

The response you retrieve should look like this:

BREAKING DOWN THE URL

Before we move on to the next article Using $expand and $select with the TM1 REST API we should review the parts of the URLs above and describe what they mean. Example:

https://localhost:8881/api/v1/Cubes('General Ledger')

Parts:

  • https: This the scheme to be used, in this case https, which means use the HTTP protocol using secure sockets. If we were to disable SSL in the tm1s.cfg (UseSSL=F) we would use http instead.
  • localhost: The location as a server name, IP address or domain name to access the REST API.
  • 8881: The port number the TM1 REST API is running on, this has to the same number that you have used for the HttpPortNumber setting in the tm1s.cfg file.
  • api/v1/: The version of the TM1 REST API that you are using, if for some reason breaking changes are made to the REST API the version will increment so no existing applications are broken.
  • Cubes: The collection of resources that we are querying, this can also be Dimensions, Processes, Chores, etc. For a full list see the $metadata document:

https://localhost:8881/api/v1/$metadata

A journey through the TM1 REST API

The TM1 REST API is a way of accessing data and everything else in TM1. Since its first introduction with TM1 10.2, a number of fix packs have been released with additional improvements and extensions. All new products released by IBM such as CAFE (PAX) and Planning Analytics Workspace (PAW) are using it. It is the THE API for the TM1 server going forward.
The TM1 REST API can be used to do pretty much anything you want with your TM1 server such as building cubes, dimensions, extracting or loading data...

The TM1 REST API journey for a web application

You don’t need any extra components to access your TM1 server with the TM1 REST API. You can follow the IBM official guide and try to tackle the TM1 REST API by yourself, but you need to understand that it isn't all smooth sailing:

  1. Access TM1 server. This is the challenging part, there are lots of things which can be difficult to set up and not well documented such as cellsets, cross domain scripting and SSL certificates.
  2. Request data. The TM1 REST API supports the OData standard (v4) which provides a common way for accessing data through queries and also updating data.
  3. Transform data. The MDX query will return you a set of cells that you’ll need to transform in a specific format to make it fit in your application.
  4. Visualization: Once your data is ready, you now need to build your web application using HTML.

On the one hand, some things are very simple to do with the TM1 REST API, such as getting the list of cubes and dimensions. On the other hand some things which are trivial to do in TM1, can be very hard to do with the TM1 REST API. For example: getting a cell or updating a value.

Because of these points Cubewise decided to build a development framework on top of the TM1 REST API called Canvas to help TM1 developers to build beautiful user interfaces without all of the scary bits.

TM1 REST API with Canvas

Canvas handles all the way from the server access to data transformation so you can focus on the visualization part. With a mix of HTML and Canvas directives you’ll be able to build a modern web planning and reporting application:

With Canvas, you don’t need to know JavaScript or MDX, the assumption is that if you know how to write a rule or a TM1 process, you’ll quickly learn how to build a TM1 application with HTML. However if you do know JavaScript, you'll be able to build a more sophisticated application.

Canvas is built with TM1 Developers in mind – it uses immediately recognizable TM1 concepts like DBRs, SUBNMs, Views, etc. and for the first time makes these easy to plug into any web application. DBR in Canvas looks like this:

<tm1-ui-dbr tm1-instance="dev" 
             tm1-cube="Regional Assumptions" 
             tm1-elements="Actual,2012,Jan,13,Super Rate" 
             >
 </tm1-ui-dbr>

With Canvas, the TM1 REST API is now available to anyone who does not want to rely on limited drag and drop tools to develop sophisticated applications.

Using $expand and $select with the TM1 REST API

In the previous article we went through some simple GET requests to retrieve a list of cubes and a single cube. We are now going to expand on those examples by using two OData query options: $expand and $select

HOW TO USE $EXPAND

The $expand query option is used to display related properties on a object when executing a GET request. For example if we look at the Cube entity type in the $metadata document (https://localhost:8881/api/v1/$metadata) we will see that each cube has:

  • A unique key (the Name).
  • 5 properties: <Property ....
  • 5 navigation properties: <NavigationProperty ....

It is the navigation properties that we can use the $expand query option for, in fact, the navigation properties will ONLY be returned when you use $expand. Let's try an example to "expand" the dimensions for a given cube:

https://localhost:8881/api/v1/Cubes('General%20Ledger')?$expand=Dimensions

This will return the Dimensions for the cube as well all of the other standard Properties:

You can also "expand" multiple Navigation Properties by separating them by a comma:

https://localhost:8881/api/v1/Cubes('General%20Ledger')?$expand=Dimensions,Views

The above query will result in BOTH the Dimensions and Views being returned for the cube:

HOW TO USE $SELECT

The next query option I am going to talk about is $select$select does the opposite of $expand, it reduces the content returned from TM1 by "selecting" the properties we want to return. For example, if we want to only return the name of our cube we would use $select this way:

https://localhost:8881/api/v1/Cubes('General%20Ledger')?$select=Name

NOTE: The $select option is important way of optimising your REST API queries. A smaller response results in fastest network transfer and less time parsing the result into something you can use.

Just like the $expand option you can also "select" multiple properties:

https://localhost:8881/api/v1/Cubes('General%20Ledger')?$select=Name,LastSchemaUpdate

HOW TO COMBINE $EXPAND AND $SELECT

You can combine the use of these two query options to return exactly what you need. For example, if we wanted to return the name of the General Ledger cube and its dimensions we would do the following:

https://localhost:8881/api/v1/Cubes('General%20Ledger')?$select=Name&$expand=Dimensions

You will notice that we get all of the properties of the dimension, you can use $select on Navigtation Properties as well. To get just the names of dimensions on the cube we would use this:

https://localhost:8881/api/v1/Cubes('General%20Ledger')?$select=Name&$expand=Dimensions($select=Name)

GETTING A LIST OF ALL CUBES AND THEIR DIMENSIONS

We can use these same query options to retrieve a full list of cubes with their dimensions. Just remove the ('General Ledger') part like so:

https://localhost:8881/api/v1/Cubes?$select=Name&$expand=Dimensions($select=Name)

Drill-Through is now available in the TM1 REST API

One of the best features of TM1 is its ability to drill through a third-party database (e.g. Oracle Financials, SQL and MS Dynamics) from TM1 cubes, TM1 web sheets or MS Excel workbooks. With TM1, you do not to need to know SQL to access data from these databases. From TM1 you can simply on a cell where the drill is set up and drill either to another cube or to an external database.

TM1 10.2.2 FP6 for the first time makes the drill through available in the TM1 REST API. This is the last main feature which was missing in the TM1 REST API.

If the drill is set up in TM1, it will natively appear in Canvas at the cell intersection where the drill through is configured: 

Once you click drill, depending on how the drill is set up in TM1, you'll see either a cube view:

Or the details from the database:

Since the first release of the TM1 REST API in v10.2, IBM kept adding fixes and new features in every fix packs. With FP6 the TM1 REST API is now very robust and includes all TM1 functionalities.