TM1 REST API ( Any early adopters )

nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

TM1 REST API ( Any early adopters )

Post by nick_leeson »

Hi All,

I have been trying to the get the TM1 REST API up and working for a POC while I appreciate its a new but I have following the instructions but it ain't working for me. No Error as well.

1) Added the following line to the tm1s.cfg file in the PlanSamp directory.
HTTPPortNumber=8000
2) Step 2
<crn:parameter name="tm1AdminHTTPSPortNumber">
<crn:value xsi:type="xsd:unsignedShort">8000</crn:value>
</crn:parameter>
3) Verified that the port number settings by specifying http://localhost:5895/api/v1/Servers in a local browser instance. I got a list of all servers that I was expecting so Application Server is all but working.

4) Typed the http://localhost:8000/api/vi/$metadata URL in my browser and its just showing as "working" and hangs there.

I was hoping to use this with D3 for some awesome visualization but I am coming unstuck here.


Any ideas ?

Cheers
Nick ..
TableManagerOne
Posts: 42
Joined: Fri Apr 19, 2013 7:07 pm
OLAP Product: TM1
Version: 10.1 RP1 FP1
Excel Version: 2003 SP3

Re: TM1 REST API ( Any early adopters )

Post by TableManagerOne »

I think step 2 is only necessary if you need to use a custom port number for your admin host. In which case, you'd want to set that configuration to the admin host port, not the tm1 server port.
In step 4, you have "vi", but should have "v1". Though, I imagine this is just a typo in your forum post.
nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

Re: TM1 REST API ( Any early adopters )

Post by nick_leeson »

Thanks , I will give this a bash and let you know.

Much appreciate your help.
nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

Re: TM1 REST API ( Any early adopters )

Post by nick_leeson »

Thanks that worked.
I
{"@odata.context":"$metadata#Servers","value":[{"Name":"proven_techniques","IPAddress":"192.168.0.28","IPv6Address":"","PortNumber":17797,"ClientMessagePortNumber":0,"HTTPPortNumber":0,"UsingSSL":true,"AcceptingClients":true},{"Name":"Planning Sample","IPAddress":"192.168.0.28","IPv6Address":"","PortNumber":12345,"ClientMessagePortNumber":0,"HTTPPortNumber":0,"UsingSSL":true,"AcceptingClients":true},{"Name":"GO_Scorecards","IPAddress":"192.168.0.28","IPv6Address":"","PortNumber":54321,"ClientMessagePortNumber":0,"HTTPPortNumber":0,"UsingSSL":true,"AcceptingClients":true}]}


I get then used http://localhost:12345/api/v1/Cubes?$select=Name and I got this.

HTTP/1.1 504 Fiddler - Receive Failure
Date: Thu, 05 Jun 2014 06:43:07 GMT
Content-Type: text/html; charset=UTF-8
Connection: close
Cache-Control: no-cache, must-revalidate
Timestamp: 23:43:07.965


Sorry is there a step by step guide because I could not find this in the documentation and its very frustrating.

Cheers
VuPham
Posts: 1
Joined: Wed Jun 04, 2014 1:53 pm
OLAP Product: TM1, MSAS
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by VuPham »

Hi,

Could you check for the correct HTTP port? From the response of the Admin Server, none of your TM1 Servers support HTTP. If you have not already, you should create an IBM developerWorks account. There is a moderated community site there, where you can find out more about documentation and support. More information here:

http://www.ibm.com/developerworks/

Search for "IBM Cognos TM1 SDK"

Regards,

VP
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

Bit of a noob question, but how do you get the REST API to perform a POST action?

From the IBM Developers Forum I can get the following to work:

Code: Select all

/api/v1/Cubes('GL_Cube')/Views('Default')?$expand=tm1.NativeView/Rows/Subset($expand=Elements($select=Name))
This is a GET request and gives me the expected result.

How do I action the below?

Code: Select all

POST /api/v1/Cubes('GL_Cube')/Views('Default')/tm1.Execute?$expand=Cells
I'm assuming via a HTML file?
I've had a look at the Odata reference but it all seems to assume you have knowledge of how all this stuff works (which I'm trying to do)

This is what I was looking at:
Cellsets
A cellset is the result of an execution of a view or an MDX expression, and represents a snapshot of your data at a certain point in time. You can use the cellset ID within a session instead of running a view or MDX expression multiple times.

When a view is executed, a snapshot of the data in the cube at a certain point in time is displayed, based on the dimensions specified for the view. Use the $expand query option to return all of the cell values in the cellset. Columns and rows are complex types, and are addressed in previous sections.
POST /api/v1/Cubes('Metrics cube-Sales')/Views('Metrics cube-Sales')/tm1.Execute?$expand=CellsReturns the cell values, starting at ordinal 0, for the view specified.

{
"@odata.context": "../../$metadata#Cellsets(Cells)/$entity",
"ID": "kyMtu_8DAIB7AQAg",
"Cells": [
{
"Ordinal": 0,
"Value": -1,
"FormattedValue": "($1.00)"
},
{
"Ordinal": 1,
"Value": -1,
"FormattedValue": "($1.00)"
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

Found my answer last night...

One of the easiest ways to view what your POST action is doing is get one of the Chrome Plugins (Postman, Simple REST Client,Advanced REST Client etc.)
These allow you to plonk in what you need & see what is returned from the API.

Unfortunately these are all restricted in our company so am looking for alternatives.
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1 REST API ( Any early adopters )

Post by blackhawk »

RJ!

Postman is restricted? It is a pretty sublime add-in. Is that just a business practice thing or is it actually preventing it.

The reason I ask is because there are some other methods, but it sounds like that anything you try might be restricted. Most tend to get worse than Postman in terms of installation footprint and such.

You could write your own JavaScript. JQuery is pretty easy to do that.

What language are you using to connect to the API, and what are you trying to create?
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

The technology office is trying to clamp down on "cottage industries" in the business making their own little apps that no-one can support (which is fair enough).

All I'm looking to do is learn how this API works & work with the examples that are on the IBM Developers Forum. Ultimately I'd like to use TM1/TM1Web/HTML to be able to publish Dashboards for our Managers to be able to use via GOOD on their mobile devices. We don't have Cognos BI so using that is out of the question so was looking at other options. Another option is to migrate more of our business databases that are in MS Access over to TM1 and service them with a proper Enterprise solution.

Besides, I thought HTML/CSS/JavaScript + REST + JAVA would be good things to learn for my career progression...

As for connecting to the API, I've just been inserting the URL's in Chrome & viewing the results. Crude but works I guess. Will start to look at the JavaScript side of things & see what I can do there.

Seems the hard part is getting access to the Developer tools that I need to perform certain functions.
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1 REST API ( Any early adopters )

Post by blackhawk »

RJ!

Well, if you want to put up some web applications quickly that use TM1, you might want to look at Enterprise Services http://www.carpedatuminc.com/index.php/ ... e-services (shameless plug...I work for CarpeDatum). Also check out these videos on how ESFramework lets you build TM1 apps in JavaScript/HTML/CSS etc. quickly. http://www.carpedatuminc.com/index.php/ ... plication/

With the REST API, you are going to have to do a tremendous amount of work after you get the data because odata, though very rich in being able to return metadata and data using a url query, it has some issues which are troublesome. Check out this article https://github.com/ServiceStack/Service ... -not-odata. I am not saying that it is bad, just you have to be aware that headaches may be in your future, especially as the API matures. If you are doing it to learn, well, it may be worth it.

With regard to JavaScript, can you use jQuery? If so, then all you need to do is this:

Code: Select all

$.ajax({
    url: "/api/v1/Cubes('GL_Cube')/Views('Default')/tm1.Execute",
    method: 'POST',
    data: { '$expand': 'cells' },
    success: function (data) {
        console.log(data);
    }),
    error: function () {
        alert("Uh oh, something went wrong")
    }
});
Doing this from memory...so there may be errors.

[EDIT:]
You may have to do this on the server because you could be getting into a cross-domain issue if you specify the URL with a different machine name. I haven't tried it out in this way yet.
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

I would not be surprised for a minute if jQuery was also restricted...

Thanks for giving me some pointers, I'll have a play around with this again and see how I go.
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1 REST API ( Any early adopters )

Post by blackhawk »

RJ!

Wow, if they restrict jQuery, then I am surprised they even allow TM1...it is, after all, an add-in to Excel :shock:
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

Shhh.... TM1 isn't even supposed to exist here either...

I had a quick look at your links, the Enterprise Services looks pretty schmick, alas, we have no budget for such things.

Back on topic, I'll try and see if I can get jQuery working. I'm assuming that will be another plugin to be added to Eclipse?
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1 REST API ( Any early adopters )

Post by blackhawk »

HA! That is funny. :lol:

You only need a plug in if you want code completion in Eclipse. As far as using it, though, you just need to put this in your HTML:

<script src="lib/jquery.js"></script>

Of course, you do have to download jQuery though (if they let you). ;)
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 REST API ( Any early adopters )

Post by tomok »

blackhawk wrote:HA! That is funny. :lol:

You only need a plug in if you want code completion in Eclipse. As far as using it, though, you just need to put this in your HTML:

<script src="lib/jquery.js"></script>

Of course, you do have to download jQuery though (if they let you). ;)
Actually, you don't have to download JQuery. You can reference a public version in your <script src> tag and it will work just like if you had the actual code on your web server. Google, among others, has a public version.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

jQuery is allowed luckily, so will get it installed on the server. In the mean time I'll be learning jQuery I guess...

Might have to have a closer look at that Service Stack per the link below too.

Thanks for the help guys!
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

Ok, so I've drafted my jQuery similar to your suggestion above:
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>jQuery Demo</title>

<script src="js/jquery-1.11.1.min.js"></script>

</head>
<body>

<script>

function testAjax() {
return $.ajax({
url: "http://localhost:port/api/v1/Cubes('GL')/Views('Default')/tm1.Execute",
method: 'POST',
data: { '$expand': 'cells' }
crossDomain: true,
beforeSend: function(xhr) {
xhr.setRequestHeader('Authorization', 'Negotiate');
}

});
}

var promise = testAjax();

</script>

promise.success(function (data) {
alert(data);
});

</body>
All seems good, seems to be connecting to the TM1 Server and now wants Authorisation...
I've been looking up what you need to add to do that part, being enterprise, the "WWW-Authenticate: Negotiate" will give our IT guys less heart attacks I'm assuming.

Is there any documentation that is a little more "dumbed" down for novices to learn how to get the Authentification working?
kangkc
Community Contributor
Posts: 206
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1, PA , TMVGate
Version: 2.x
Excel Version: 36x
Location: Singapore
Contact:

Re: TM1 REST API ( Any early adopters )

Post by kangkc »

If you are using Chrome, just install Postman extension. It is a REST Client and works with the REST API. Tested myself.
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: TM1 REST API ( Any early adopters )

Post by RJ! »

As noted previously, Postman and other plugins are restricted in our environment. It's why I've tried to do the jQuery instead.
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: TM1 REST API ( Any early adopters )

Post by blackhawk »

RJ!,

The problem you are running into at this point is that you are trying to perform a cross-domain POST. This is an issue because the TM1SessionId cookie is lost between servers and so it does not retain the session ID, per design by browsers. Unfortunately the only way to solve this is to either put that code on the same server and port or to write a proxy. I have looked for a login method where you can get a session token and pass that in, but unfortunately, I don't see such a beast in the API.

If you write an application that uses the API directly or create a server side app; you should be fine.

I think I might have a CS proxy that will do this for you. Let me see if I can locate it. Are you running IIS where your jquery app is?
Post Reply