TM1 REST API

Post Reply
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

TM1 REST API

Post by Wim Gielis »

Hello all,

I was wondering where I could find example code to use the TM1 REST API using PowerShell ?

I did my own research first. I can use Postman to execute statements.
Postman can deliver the code that you can use in other programming languages, but PowerShell is not part of the supported languages.
Using general REST API codes in PowerShell I was able to at least do something, also using this link:
https://www.ibm.com/developerworks/comm ... bb14&ps=50 (see the code by Bob Milli)

but in general I still can't get it working. Does anyone have example code, for example to get a list of cubes ?
Authentication is Basic, just TM1 authentication.

For example, this sort of works (no errors in PowerShell ISE):

Code: Select all

$AdminHost= "AEX199"
$httpport = "8001"
$TM1User = "wim"
$userresponse = Read-host "Input the password?" -AsSecureString
$TM1Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($userresponse))

$headers = @{"Authorization" = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("$($TM1User):$($TM1Password)"));}
$session = New-Object Microsoft.PowerShell.Commands.WebRequestSession

$TM1_Instance_url = 'http://' + $Adminhost + ':' + $httpport + '/api/v1/Cubes'
$TM1cubes = Invoke-RestMethod -Method Get -uri $TM1_Instance_url -WebSession $session -Headers $headers

$TM1cubes | Out-File c:\debug.txt
https does not work (needed for UseSSL=T in the TM1s.cfg file)

The output in the text file is only very little:

Code: Select all

@odata.context  value                                                                                                                                                                                                                                            
--------------  -----                                                                                                                                                                                                                                            
$metadata#Cubes {@{@odata.etag=W/"8c2097d4083b864dcfec672c9536c919e660a898"; Name=Aexis_Planning; Rules=; DrillthroughRules=[] = S: 'Z_Drill detail (Aexis_Planning)';...                                                                                        
Thanks in advance,

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
bgregs
Posts: 77
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 REST API

Post by bgregs »

Hi Wim,

Unfortunately my PS version is too outdated to run any fancy Rest calls :( , but from what I can tell your output looks like a typical return except for the fact that it is getting truncated for some reason. I'm not sure if this is an issue with writing to a .txt, but could you maybe pass it into a .json file or something similar? If that still doesn't work, does it display correctly in the PS console if you don't export to a file?

Last but not least, if you only want the cube name (and not the rules and all the other junk associated with it), try just pulling out the "Name" object from the request. I found a SO thread about how to do this in PS:

https://stackoverflow.com/questions/165 ... ield-value

Sorry I couldn't be of much help :(
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: TM1 REST API

Post by macsir »

1. Try to add -ContentType 'application/json' into your Invoke-RestMethod command
2. Your output is basically right but because the returned json response might be too big for Invoke-RestMethod to deserialize. You need to change to JavaScriptSerializer instead
3. For https to work, you need to refer to https://social.technet.microsoft.com/Fo ... powershell

To be honest, if you are doing rest api, you'd better to have some experience with programming and http knowledge. Otherwise, it would be hard to testing and debugging.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis »

Hello macsir,

Thanks ! By continuing to work on this and by looking up information on the internet, I am nearly reaching my end goal:
creating a custom drill process by having TI export TI code and REST API commands, and PowerShell code to launch it.

As we speak I am running the final tests.
You are right that this kind of knowledge and experience is beneficial, though I have enough knowledge and programming experience to be able to reach this point. I don't intend going much further with the REST API. 'Only" creating or updating or executing processes, looping over TM1 objects, and so on. No extensive programming, I'll keep that for TI :lol:

UseSSL = F for the moment, I'll look at the link you provided.

Many thanks, also to bgregs.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis »

Wim Gielis wrote: Wed Oct 24, 2018 11:40 pmBy continuing to work on this and by looking up information on the internet, I am nearly reaching my end goal:
creating a custom drill process by having TI export TI code and REST API commands, and PowerShell code to launch it.

As we speak I am running the final tests.
Confirmation that all is running fine, TI and PowerShell and the REST API do what I ask them to do for my drills :D
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: TM1 REST API

Post by macsir »

glad it works
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis »

macsir wrote: Wed Oct 24, 2018 9:15 pm 1. Try to add -ContentType 'application/json' into your Invoke-RestMethod command
2. Your output is basically right but because the returned json response might be too big for Invoke-RestMethod to deserialize. You need to change to JavaScriptSerializer instead
3. For https to work, you need to refer to https://social.technet.microsoft.com/Fo ... powershell

To be honest, if you are doing rest api, you'd better to have some experience with programming and http knowledge. Otherwise, it would be hard to testing and debugging.
Update: using the link above (point 3) I was able to use the REST API where UseSSL = T in the TM1s.cfg file. Thanks for providing the link macsir.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: TM1 REST API

Post by macsir »

No worries, mate. Glad to hear. ;)
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Post Reply