TM1 REST API Project Structure and Pretty URLs

The great source of REST API best practices and development style offers free apigee collection of eBooks. 

URL Structure

Apigee folks suggest that URLs should exclusively consist of nouns declaring which object is requested and voluntary parameters for more precise specification. Examples include:

analyticshumanly.com/api/bears (get all data from Bears cube)

analyticshumanly.com/api/bears/Germany (get data from Bears cube about Germany)

However one TM1 server can include both Bears dimension and cube. In order to differentiate these object groups, I propose extending the URL with one of the possible category name: 

• dimension 
• subset 
• cube 
• view 

And again, some examples:

 analyticshumanly.com/api/cube/bears (get all data from Bears cube)

 analyticshumanly.com/api/dimension/bears (get all data from Bears dimension)

Methods handling

Now we have structure addressing every possible object with single specific URL. However, API needs to allow executing various operations (Select, Insert, Update, Delete) on every object. According Apigee, these methods should never be explicitly present in URL but rather provided in the HTTP method, such as:

GET analyticshumanly.com/api/cube/bears 

(get all data from Bears cube)

POST analyticshumanly.com/api/cube/bears/germany/2011/123 

(insert 123 units into Bears cube to Germany and 2011 intersection)

PUT  analyticshumanly.com/api/cube/bears/germany/2011/234

(update Bears at Germany and 2011 intersection to 234)

DELETE analyticshumanly.com/api/cube/bears

(delete cube Bears)

The code within the specific object such as bears.php in cube folder then needs to identify incoming method via $_SERVER[‘REQUEST_METHOD’] and implements own login for every operation. The code has been already presneted in the previous article and is repeated in the following snippet.

Project structure

Having in mind previous practices, I suggest using API project structure as shown at the following visualization. Yellow rectangles represent folders while blue are particular PHP files. Notice also position of the API library api.php.

TM1 API project structure

Pretty URLs

Secondly, notice that in all examples I excluded .php extension from final URLs. This can be obtained by URL rewriting in .HTACCESS file for Apache server. Rewrite rules heavily leverage regular expressions which can be little tricky at the beginning to crack, however powerful when master. Perfect beginners guide to regular expressions can be found at addedbytes.com.

In the following example, you can see how I rewrote api/cube/beras.php path to accept api/cube/bears URL with single line of code in .htaccess file placed in application’s root.

Calling TM1 Processes From PHP via TM1RunTI.exe

In the previous post I explained how to call TM1 processes from inside TM1RunTI.exe native utility. However, this function solitarily is still not useful and for purpose of universal API needs to be wrapped in some server side code. Today I will therefore focus on building general PHP functions executeProcess for invoking TM1 processes and subsequently echoing their outputs to the browser interface via getData.  Whereas both functions will be necessary for every class, they will be stored in api.php file in root folder of api project code. Code snippet is attached bellow.

executeProcess

PHP allows executing external programs via system function with two parameters (line 21). First parameter provides particular command similar to one issued directly tonative utility however properly formatted.  For the purpose of its universality I also replaced parameters with variables allowing function to be repeatedly re-used in different objects and for various processes. Second parameter message stores received response, it case of TM1RunTI.exe restricted to set of numerical codes (0-11,99). For their better readability, I encoded them into error array (line 4).   

getData

Function is supposed to be called immediately after executeProcess is finished when it reads exported file, completes JSON formatting and outputs data it to browser window. The required parameter is file location with path where data is stored. As you can see, the only modification done within the function is enveloping loaded string with angle brackets (line 37 & 41). Function returns JSON formatted output (line 42).

Imagine now, that you want to build function which returns data from Bears cube after invoking http://localhost:6600/analyticshumanly/api/cube/bears.php with GET method. The topic how to create pretty URLs, structure folders and handling naming conventions will be discussed in the following post. For now, it is sufficient to know that while the notion of the function is to get data from Bears cube, the code will be stored in api/cube/bears.php.

Requested cube Bears

Within the bears.php code, we have to firstly import out universal api.php library by calling require function (line 4). Further export file location, adminhost, server, user and password needs to be declared. Notice, that we haven’t determined particular process name yet. That is because the process called within Bears cube will depend on method which called the URL. 

Method can be identify by $_SERVER[‘REQUEST_METHOD’]. Invoked executeRequest function then switches through methods and calls particular processes. When message code is different to zero, proper warning from error array is issued (line 56), otherwise, data from the file are loaded and echoed to the browser (line 59). Prior to the request execution, we clear the content of the file by calling clearFile function (line 26). Code for bears.php is attached in the following snippet.



When you type http://localhost:6600/analyticshumanly/api/cube/bears.php to your borwser now, you can see that API responded with JSON formated array of nine entries from Bears cube.

JSON formatted responses with data from Bears cube

In the following article, I will explain how to invoke API, provide method and call specific URL from web browser.

TM1RunTI Guide

This article focuses on explaining how to call TM1 TurboIntegrator processes from outside the common TM1 environment. 

Since TM1 version 9.5.2 Hotfix 1, all developers have access to little handy utility they probably even don't know about called TM1RunTI.exe. TM1RunTI.exe allows you to execute TI processes from simple proprietary command line. 

The executable could be found in /bin or /bin64 folder of your TM1 installation. 

While there is an excellent and exhausting PDF dedicated to the utility, I will not repeat the theory and rather focus on the little detail they forgot - examples.

Screen shots guide and transcripts:

1. Run test process on demoserver as admin (pwd: apple) from utility's actual location /bin64

C:\Program Files\ibm\cognos\tm1\bin64>tm1runti.exe tm1runti -process test -adminhost localhost -server demoserver -user admin -pwd apple

1. Run test process on demoserver as admin from different location

"Program Files\ibm\cognos\tm1\bin64\tm1runti.exe" tm1runti -process test -adminhost localhost -server demoserver -user admin -pwd apple

1. Run test process with parameter param and value p1 on demoserver as admin

"Program Files\ibm\cognos\tm1\bin64\tm1runti.exe" tm1runti -process test param=p1 -adminhost localhost -server demoserver -user admin -pwd apple 

Run test process on demoserver as admin (pwd:apple) from utility's actual location /bin64

Run test process on demoserver as admin from different location

Run test process with parameter param and value p1 on demoserver as admin

Now you know how to call TI process from outside TM1. In the following article we will examine how to run TM1RunTI.exe and execute processes from server side scripting languages such as PHP.

JSON-like TM1 Text Output

JSON (Java-Script Object Notation) as defined by json.org is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate.

These properties make JSON perfect for facilitating when interchanging data between TM1, PHP and Java Script front-end interface. As discussed in the previous article, TM1 does not offer tools for encoding data directly to JSON and therefore some techniques have to be applied in order to obtain required outputs.

JSON basics

In short, JSON is an array of objects which are composed of pairs of names and values. Complicated? Imagine you want to encode single cell from bi-dimensional cube including Geo and Year dimensions such as 'Austria',''2012',1234.

JSON encoded: {"Geo":"Austria","Year":"2012","Value",1234}

In the previous example "Geo":"Austria" is the pair of name/value and the whole string within {} is JSON object. Also notice the double quotes! When more objects are joined together and separated by comma, the array of JSON object is created.

JSON with Text Output

Prior to any coding in Advanced tab of TI process, you should define the data source and identify all required variables as Other.

Secondly, in order to block TI from enveloping every variable into double quotes, you have to declare DatasourceASCIIQuoteCharacter local variable in the Prolog tab and assign it empty value!

DatasourceASCIIQuoteCharacter = '';

And the most important in Data tab:

TextOutput('C:\...\...\file.txt','{"Geo":"'|geo|'"','"Year":"'|year|'"','"Value":'|value|'}');

• Single quotes enclose whole expression and separate text from variables.
• Variables in green are enveloped with pink double quotes (except numerical value).
• Variable and double quote have to be separated with single quote and concatenated with pipe ( | ).
• Labels in blue are also enclosed with double quotes.

Despite it may seem extremely complicated at the beginning, you will later find out that when constructing the expression gradually, the quotes system actually make sense and the whole output is almost identical to real JSON format.  

TM1 API Response Options

Standard data formats for API responses are XML or JSON. However, how to get such structured data using current TM1 output options with no or minimal adjustments?

It took us few days of brainstorming and seemed to be little tricky first, but solutions are almost brilliant.

Firstly, why not leveraging XML-based Cognos Reports? With few simple integration steps, you can publish your cubes to Cognos Connections and subsequently design list objects exactly as output requires. Thanks to Cognos SDK, such reports can be later accessed from the custom application interface via REST or SOAP and processed anyhow you imagine.

Secondly, why not playing a little directly with TM1 Text Output and JSON-like format data before even storing them in temporary text file? Despite TM1 Text Output does not allow perfect results, the output is almost impeccable and requires only few adjustments before loading with server side script.

You can read more about JSON-like TM1 Text Output in the following article.

Believe it or not, all brilliant solutions also have few disadvantages.

XML-based Cognos Reports version

• Cannot output subset of data from the source cube
• Offers only XML-based structure (personal)
• Requires Cognos BI license

JSON-like Text Output

• Requires temporary storing possibly unsecured data in the text file
• Must be transferred to real JSON and  processes for possible errors

Although both options have their obstacles I decided to implement and further describe JSON version based on Text Output mainly because of the independence to Cognos BI software and possibility to define outputted subsets.

You can read more about securing text files and transferring JSON-like to JSON data in the following articles.

For the XML lovers I promise to come back to option leveraging XML-based reports and Cognos SDK later in the series.

TM1 API Architecture

There are two major problems which need to be solved generally when designing APIs :

• How can we "request" the provider for specific data output?

• How will the provider "respond"?

Making the request

The request can be interpreted as notification and command to return data. The most appropriate way how to execute commands in TM1 and transfer data on demand is TM1's internal TurboIntegrator process. However, how to call internal processes from outside the TM1 environment? Fortunately, in TM1 9.5.2 Hotfix 1, very handy utility for developers called TM1RunTI.exe has been introduced. TM1RunTI.exe simply allows running processes with set of parameters from proprietary command line. Therefore, to make the request, you only need to execute system calls pointing to TM1RunTI.exe with your favorite server-side scripting language such as PHP or even better asynchronously from front-end scripting languages like JavaScript.

The request is visualized in the upper part of the TM1 API call circle picture.

Providing the response

Basically, there are two options how to provide response using Cognos BI or temporary text file. You can read more about pros and cons of both approaches in the following article. While the second option seems to be easier and does not require Cognos BI, we will further leverage this approach. The incoming command from web browser (request) firstly calls TurboIntegrator process preparing data and exporting them to text file. Because TM1 TextOutput does not allow formatting data into XML or JSON standard, few adjustments have to be done to obtain desirable structure. The structured content of the file is subsequently read by PHP and outputted directly to the browser or processed by JavaScript.

The data flow is visualized in the bottom part of the TM1 API call circle picture.

TM1 API call circle

The visualization serves as top level overview of architecture and employed technologies.

You can read more about particular steps in following articles.  

Let's be in touch!

I am glad you have found Analytics Humanly interesting and you decided to contact me.

If you want to learn more about TM1 APIs or just need help, don't hesitate to contact me at peter.fedorocko@gmail.com or follow @fedorocko on Twitter.

In case you are experienced OLAP ( TM1 or any other software vendor) developer and you would like to spread the notion of beautiful visualizations with enterprise power, I will be very happy to welcome you onboard at Analytics Humanly.