TM1 REST API

Post Reply
Wim Gielis
MVP
Posts: 1984
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

TM1 REST API

Post by Wim Gielis » Wed Oct 24, 2018 9:05 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

bgregs
Posts: 57
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 » Wed Oct 24, 2018 12:05 pm

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
Community Contributor
Posts: 578
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: TM1 REST API

Post by macsir » 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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Wim Gielis
MVP
Posts: 1984
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Wed Oct 24, 2018 11:40 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

Wim Gielis
MVP
Posts: 1984
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Thu Oct 25, 2018 8:02 am

Wim Gielis wrote:
Wed Oct 24, 2018 11:40 pm
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.
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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

User avatar
macsir
Community Contributor
Posts: 578
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: TM1 REST API

Post by macsir » Thu Oct 25, 2018 9:01 pm

glad it works
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Wim Gielis
MVP
Posts: 1984
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: TM1 REST API

Post by Wim Gielis » Fri Jan 11, 2019 11:59 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 108 TM1 articles and a lot of custom code
Newest blog article: Access to cube cells in TM1

User avatar
macsir
Community Contributor
Posts: 578
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: TM1 REST API

Post by macsir » Tue Jan 29, 2019 11:20 pm

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