Starting with the REST API - Examples using Excel

[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]

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.

[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]

Very good article George. Looking forward to see more and contribute as well.
I am undertaking a similar exercise.

[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]

Many thanks George. Again very readable and good material you add.

[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