arrow-icon-size3 Contact Us

REST API - Examples using Excel - Part 1

1. Introduction

This blog post aims to get you started using the Planning Analytics REST API. Excel will be used as the user interface with VBA to drive the queries handle the results.

Excel is not the ideal interface to work with JSON but through the use of 3rd party modules, JSON can be parsed into objects that can be dealt with in Excel VBA.

In this post we look at building processes to retrieve Sessions and associated Threads as well as a request to cancel a Thread.

There are multiple methods of interacting with the REST API:

  • A service using MSXML2.XMLHTTP60
  • Via Cognos Office Automation Object using the Reporting object

 

This post will look at using the MSXML2.XMLHTTP60 option where the Planning Analytics for Excel add-in is not required. The main difference that I have noticed when working with Planning Analytics on the Cloud (PAOC) is that you can only access the REST API through the provided automation user (non-interactive account). Using the Reporting object, you can interact with the REST API through the authenticated user.

For my examples, I will be connecting to PAOC through the automation user. You could change the endpoint for your local server e.g. http://tm1server:<HTTPPortNumber>/api/v1/

2. Connection Settings

On our first sheet in the workbook we will create the server access details.

We will make use of the following parameters as a start:

Connection: A name to identify the connection

Server: Endpoint of my server

Database: TM1 database name

Username: The automation user

Password: Password

CAM Namespace: For PAOC this is linked to LDAP

 

Name your sheet as Config and add each of the parameters to sheet starting in cell B3.

For each parameter, add a named range scoped to the workbook as follows:

pServer, pDatabase, pUsername, pPassword, pCAMNamespace

You should have a sheet that looks something like the below:

On my sheet I have multiple connections and have linked my connection to a list of servers to select and switch as required. You could add this to your solution later to extend it further.


3. Testing the connection

We will need 4 components before we can test the connection;

  • Base64Encoder to encode our credentials
  • JSON Converter to parse JSON into VBA objects we can deal with
  • A function to perform the query and return the parsed JSON object
  • A process to test our connection to the server

 

3.1. Base 64 Encoder

There are various functions available on the web but I used the following:

				
					Option Explicit
Function Base64Encode(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As Variant
  Dim objNode As Variant

  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  Base64Encode = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

Add a new module to your VBA project and call it modRESTAPI or similar. Paste the above function into the module. I like to add Option Explicit to ensure the variables are correctly defined and we don’t encounter surprises.

3.2. JSON Converter

Download Tim Hall’s module from https://github.com/VBA-tools/VBA-JSON.

Import the module into your VBA project to make it available to our calls.

This makes use of Dictionary data types and requires the Microsoft Scripting Library reference to be selected in Tools, References.

3.3. Function to Query the REST API

Copy the function below and add to your VBA project in the module you previously created.

				
					Function ExecuteQuery(pAction As String, pQueryString As String, Optional strPayload As String) As Object

    Dim TM1Service As New MSXML2.XMLHTTP60
    
    Dim pServer As String
    Dim pDatabase As String
    Dim pUsername As String
    Dim pPassword As String
    Dim pCAMNamespace As String
    
    Dim sBase64Credentials As String
    
    Dim sQueryString As String
    Dim sQueryResult As String
    
    Dim bAsynch As Boolean
    
    Sheets("Config").Calculate
    
    pServer = ThisWorkbook.Names("pServer").RefersToRange
    pDatabase = ThisWorkbook.Names("pDatabase").RefersToRange
    pUsername = ThisWorkbook.Names("pUsername").RefersToRange
    pPassword = ThisWorkbook.Names("pPassword").RefersToRange
    pCAMNamespace = ThisWorkbook.Names("pCAMNamespace").RefersToRange
       
    sBase64Credentials = Base64Encode(pUsername & ":" & pPassword & ":" & pCAMNamespace)

    With TM1Service
            'Query design for PAOC but could add in some code to look for ibmcloud.com and use the below or a local endpoint
            sQueryString = pServer & "tm1/api/" & pDatabase & "/api/v1/" + pQueryString
             
            If UCase(pAction) = "POST" Then
               bAsynch = True
            Else
               bAsynch = False
            End If
             
            .Open pAction, sQueryString, bAsynch
             
            .setRequestHeader "Content-Type", "application/json"
            .setRequestHeader "Accept", "application/json;odata.metadata=none"
            .setRequestHeader "TM1-SessionContext", "TM1 REST API tool"
            .setRequestHeader "Authorization", "CAMNamespace " & sBase64Credentials
           
            .Send strPayload
             
            While .readyState <> 4
                DoEvents
            Wend
            
            If .Status >= 400 And .Status <= 599 Then
                sQueryResult = CStr(.Status) + " - " + .statusText
                If .responseText <> "" Then
                    sQueryResult = sQueryResult + vbCrLf & .responseText
                End If
                MsgBox "Error " + sQueryResult, vbCritical, "Connection"
                GoTo Cleanup
            End If
        
        sQueryResult = .responseText
        
        'This outputs the JSON to the Immediate window.
        'You can copy this to a viewer like http://jsonviewer.stack.hu/ to interrogate the JSON
        Debug.Print sQueryResult
        
    End With
    
    If sQueryResult <> "" Then
        Set ExecuteQuery = JsonConverter.ParseJson(sQueryResult)
    Else
        Set ExecuteQuery = Nothing
    End If
    
Cleanup:
        
End Function

Be sure to go to Tools, References and tick Microsoft XML, v6.0 from available references as the TM1Service leverages this.

A walk-through of what the function does:

All the server parameters required are retrieved

Username, password and CAMNamespace are concatenated then Base64 encoded for authentication.

 

The query string is built based on the parameters passed from the underlying caller process.

 

Next we need to set the Request Headers for the call we are about to make. This sets the expected content type to JSON and some other settings required.

Note that I am setting odata.metadata=none as I do not need any additional information like the odata.etags. This could also be set to minimal to or removed depending on what you would like to see returned.

I have also set TM1-SessionContext so that I can easily see which are calls from my testing versus other sessions.

 

We then send the request and get a response. If the response is not ready, we do events then check again.

 

Our response is then checked to see if we have success or errors. See https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html for more on return codes.

 

Assuming we have a success code and some JSON returned from the query, we parse the JSON using the JSONConverter and return the result back to the calling process.

If an error was encountered, we show the error and associated text to assist in troubleshooting.


3.4. Test Function

Our test function is simply going to request the server information and return it to a message box if successfully connected.

				
					Sub GetServer()
    Dim oJSON As Variant
    Dim oKey As Variant
    Dim pQueryString As String
    Dim sResult As String
    
    pQueryString = "Server"
    
    Set oJSON = ExecuteQuery("Get", pQueryString)
    sResult = ""
    
    If Not oJSON Is Nothing Then
        For Each oKey In oJSON
            sResult = sResult & oKey & " - " & oJSON(oKey) & vbCrLf
        Next
        MsgBox sResult, vbInformation, "Server Details"
    End If

End Sub

You should be able to run the code to test it or step through it line by line to check what each step is doing.

The JSON returned is returned to an object which in this case is a dictionary object i.e. key and value pairs.

The code loops through each key and returns the associated value to a string then pops the result up in a message box.

Now that the connection has been successfully tested we can move on to retrieving Sessions and Threads.

 

4. Retrieving Sessions and Threads

We want to retrieve both Sessions and Threads in one query. Threads are linked to a Session which means that we can expand Sessions to include the Threads using a query like:

Sessions?$expand=Threads

 

A session in the resultant JSON should look something like the below:

				
					{
            "ID": 5707,
            "Context": "",
            "Active": true,
            "Threads": [
                {
                    "ID": 13016,
                    "Type": "User",
                    "Name": "LDAP/xxx_tm1_automation ccc_user CAMID(\"LDAP:u:uid=xxx_tm1_automation,ou=people\")",
                    "Context": "",
                    "State": "Run",
                    "Function": "GET /api/v1/Sessions",
                    "ObjectType": "",
                    "ObjectName": "",
                    "RLocks": 2841,
                    "IXLocks": 0,
                    "WLocks": 0,
                    "ElapsedTime": "P0DT00H00M00S",
                    "WaitTime": "P0DT00H00M00S",
                    "Info": ""
                }
            ]
        },...

Threads could contain multiple records for the same Session e.g. where multi-threading is occurring. Note that Threads has square then each Thread has braces. The square brackets are seen as an array whereas the braces are interpreted as a dictionary.

 

Add a new sheet to the Excel book to return the Session and Threads results.

In my sheet I have a header row on row 7 and return the results to row 8 onwards. Above row 7 I have some space to add command buttons.

Cell A7 contains ID, B7, Context then Active, Thread ID etc. per above JSON.

For Cell A8 I added a named range, scoped to the sheet, called Sessions and added in the text “Start”. This indicates the start of my result range but importantly also updates the last cell used for the clear function.

I also added a button to call my macro to make running the process easier.

 

Add the below code to your module. This will execute the query and return the results to the sheet.

				
					Sub GetActiveSessions()
Dim oJSON As Variant
Dim oSession As Variant
Dim oThreads As Variant
Dim oThread As Variant
Dim oThreadDetail As Variant
Dim pQueryString As String
Dim sResult As String
Dim iRow As Integer
Dim iCol As Integer
Dim sResultRange As String

Application.EnableEvents = False
Application.ScreenUpdating = False


'Clear old rows
sResultRange = "Sessions"
Range(Range(sResultRange), Range(sResultRange).SpecialCells(xlLastCell)).EntireRow.Clear

pQueryString = "Sessions?$expand=Threads"
Range("qryREST").Value2 = pQueryString

Set oJSON = ExecuteQuery("Get", pQueryString)
sResult = ""

If Not oJSON Is Nothing Then
    iRow = 0
    For Each oSession In oJSON("value")
        iCol = 0
        For Each oThreads In oSession
            If oThreads <> "Threads" Then
                Range(sResultRange).Offset(iRow, iCol).Value2 = oSession(oThreads)
                iCol = iCol + 1
            Else
                'Threads - defined as oJSON("value")(<item>)("Threads")(<threaditem>)("ID")
                If oSession("Threads").Count > 0 Then
                    For Each oThread In oSession("Threads")
                        iCol = 3
                        For Each oThreadDetail In oThread
                            Range(sResultRange).Offset(iRow, iCol).Value2 = oThread(oThreadDetail)
                            iCol = iCol + 1
                        Next
                        
                        If oThread("ID") <> oSession("Threads")(oSession("Threads").Count)("ID") Then
                            iRow = iRow + 1
                        End If
                    Next
                End If
            End If
        Next
        iRow = iRow + 1
    Next
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

With the above code, it may be useful to add some watches to better understand what each object contains.

Per the JSON snippet above, there are Session related properties and then a container of zero of more Threads.

You can see the Session detail with the Thread detail alongside. Where there are multiple Threads, these would be iterated starting in column D of subsequent rows.

5. Cancelling a Thread

Essentially, we want to select a Thread ID and pass this to a REST API action which will request the cancellation.

Add the following code to your module:

				
					Sub ThreadCancel()

    Dim oJSON As Variant
    Dim pQueryString As String
    
    Dim sThread As String
   
    'Thread ID is in column D
    sThread = ActiveCell.EntireRow.Columns(4).Value2
    If sThread = "" Then GoTo Cleanup:
    
    pQueryString = "Threads('" & sThread & "')/tm1.CancelOperation"
    
    Set oJSON = ExecuteQuery("Post", pQueryString)
    
Cleanup:

End Sub

The process will read the Thread ID from column D for the row of the ActiveCell. This value is then sent to the CancelOperation.

You can add another button to your sheet to cancel the Thread by assigning the ThreadCancel process to it.

To test this function, you will need to run a process from PAW or possibly create a simple process with a Sleep function to keep the process running for a few seconds.

 

The screenshot below shows the Session and the active Threads along with the process being run.

To cancel the Thread, select a cell in the relevant row then click the Cancel Thread button or run your process.

PAW should then show you a message box that the process has been cancelled (may not show to non-admin users):

6. Summary

You now have a way to check active Sessions and Threads running on a specified server.

You also have a way to cancel a Thread should there be an issue with a process.

You could extend this example by adding to your configuration sheet, catering to multiple connections. You could also change your Cancel Thread process to cater for multiple selected Threads.

These examples have laid the foundation for many more REST API query projects and will allow you to easily extend to querying cubes, dimensions, subsets etc.

By George Tonkin, Business Partner at MCi.

 

Further Reading

Tagged