Page 1 of 1
Starting with the REST API - Examples using Excel
Posted: Mon Apr 25, 2022 5:18 pm
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.
Re: Starting with the REST API - Examples using Excel
Posted: Tue Apr 26, 2022 3:59 pm
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.
Re: Starting with the REST API - Examples using Excel
Posted: Tue Apr 26, 2022 6:54 pm
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.
Re: Starting with the REST API - Examples using Excel
Posted: Thu Apr 28, 2022 9:54 am
by Wim Gielis
Very good article George. Looking forward to see more and contribute as well.
I am undertaking a similar exercise.
Re: Starting with the REST API - Examples using Excel
Posted: Tue Aug 09, 2022 7:03 pm
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.
Re: Starting with the REST API - Examples using Excel
Posted: Thu Aug 11, 2022 12:57 am
by Wim Gielis
Many thanks George. Again very readable and good material you add.
Re: Starting with the REST API - Examples using Excel
Posted: Wed Dec 14, 2022 7:10 pm
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