Starting with the REST API - Examples using Excel

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Starting with the REST API - Examples using Excel

Post by gtonkin »

For anyone keen on learning some of the REST API and linking it into Excel as the UI, I put together a starting point here.

Looking to add additional blogs over time to do some other useful examples to learn from like toggling chore active flag, read in views and show their dimensions, associate subsets, MDX, elements etc. etc.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Starting with the REST API - Examples using Excel

Post by declanr »

Hi George,

I have so far only had the chance to skim over it - but when time allows I will definitely have a proper look.
On a surface level, it looks like a very thorough explanation for a "starting point"!

Thanks for taking the time to contribute to this.
Declan Rodger
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Starting with the REST API - Examples using Excel

Post by gtonkin »

Thanks Declan.

Happy for any feedback, especially if there are errors that may need correction. There could be better ways of doing things to so happy to learn from feedback too.

Also, happy for future topic suggestions. Next part planned would is probably going to be Disabling/Enabling Chore Schedules. This seems to be something I have heard come up from time to time.
Wim Gielis
MVP
Posts: 3103
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: Starting with the REST API - Examples using Excel

Post by Wim Gielis »

Very good article George. Looking forward to see more and contribute as well.
I am undertaking a similar exercise.
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
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Starting with the REST API - Examples using Excel

Post by gtonkin »

There are now two addition parts added

Part 2 - Deals with chores - Listing and Activating/Deactivating them.
Part 3 - Hot Promote TI Processes between servers.

Both parts build on from the previous as there are some share components to transform the JSON.
Wim Gielis
MVP
Posts: 3103
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: Starting with the REST API - Examples using Excel

Post by Wim Gielis »

Many thanks George. Again very readable and good material you add.
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
WilliamSmith
Posts: 37
Joined: Tue Dec 13, 2022 8:54 pm
OLAP Product: TM1 / PA / PAx / PAW
Version: TM1 11
Excel Version: 365

Re: Starting with the REST API - Examples using Excel

Post by WilliamSmith »

Interested to see if you've been able to parse the JSON return of a cell set.

From what I've found, you can query for axes collection object, and cells collection object. Axes collection has the hierarchical header values for columns and rows, and cells collection has the cell ordinal # and value. FWIK, cells are outputted ordinally starting at upper left most cell, to end of first row, and then iterate down through rows.

Wondering if anyone has ideas or a good algo to parse any cell set, regardless of dimensionality.

Prototype to query a 2d cell set (easy to parse).

Code: Select all

Sub TM1_Query_View_Cellset()

    cubeName = "CubeName"
    'Simple view with two columns
    viewName = "2dCubeView"

    Dim x As MSXML2.ServerXMLHTTP60
    Set x = New MSXML2.ServerXMLHTTP60
        
    x.Open "POST", "https://tm1Server:tm1Port/api/v1/Cubes('" & cubeName & "')/Views('" & viewName & "')/tm1.Execute?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells", False, "username", "password"
    x.setRequestHeader "Content-Type", "application/json"
    x.setRequestHeader "WWW-Authenticate: Basic Realm", "TM1"
    x.SetOption 2, 13056
    
    x.send
    
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(x.responseText)
    
    Dim count As Long
    count = 1
    
    For Each Tuple In Json("Axes")(2)("Tuples")
    
        For Each Member In Tuple("Members")
                   
            Debug.Print Member("Name") & " : " & Json("Cells")(count)("Value")
                
            count = count + 1
                
        Next
    
    Next
    
End Sub
Post Reply