BI4 RESTful SDK / Excel : List Universes Sample

Synopsis: In this series of Blogs, I’ll explore how we can use RESTful Raylight Web Services in Excel with VBA.

Previous Entry: logon/logoff sample

Ok, we can logon/logoff, now let’s try to get the list of the universes …

Workflow

The workflow is :

1. logon, get the token
2. create an object for HTTP request
3. define the URL, set parameters of the HTTP request
4. put the token in header of the http request
5. call the URL
6. check return code, stop and logoff it an error occured
7. create an object to analyze the XML of the response
8. load XML of the response
9. find the wanted elements (nodes)
10. get values
11. paste these in the worksheet
12. logoff

Code

I improved the previous code, I wrote some functions and procedures for logon, logoff and for error management.
I used global variables for sharing servername, RESTful base url and the token.

We call the REST url to get list of universes, the url is like GET <servername>:6405/biprws/sl/v1/universes?offset=&limit=50, where x is the position in the list, from which universes are returned and limit is the number of universes that we can get in one call – 50 is the max.

the API is only available for 4.1 SP2 and above
You can get too the list of universes via Web Intelligence REST API. The base url is not the same, it’s <servername>:6405/biprws/raylight/v1

Because we can’t get all the universes in one request, we need to have loop control statements to call url with incremental offset. We stop when return code is “WSR 00400” (offset is too high) or when we have no data in response body.

The XML of response body is like :

<universes>
	<universe>
		<id>6773</id>
		<cuid>AXyRzvmRrJxLqUm6_Jbf7lE</cuid>
		<name>efashion.unx</name>
		<type>unx</type>
		<folderId>6771</folderId>
	</universe>
	<universe>
		<id>5612</id>
		<cuid>AYCKrid6ngFGvrKlwVfZKj4</cuid>
		<name>Salary.unv</name>
		<type>unv</type>
		<folderId>509</folderId>
	</universe>
...
</universes>

We load XML of the response into a XML object, and we search all universe node (universes/universe in xml path). For each node we copy values of child nodes (id, name, type …) in the worksheet.

Public Sub refreshListUnivers()
    Dim objHTTP As WinHttp.WinHttpRequest
    Dim objXML As MSXML2.DOMDocument
    Dim oNodeXML, oSubNodeXML As MSXML2.IXMLDOMNode
    Dim folderId, errorCodeREST, t As String
    Dim i, l As Integer
        
    Call logon
    
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set objXML = CreateObject("Microsoft.XMLDOM")
    
    'clear worksheet
    Sheets("liste univers").Range("A2:Z65000").ClearContents
	
    l = 0
    Do
        url = boUrlSL & "/universes?offset=" & l & "&limit=50"
        Debug.Print url
        objHTTP.Open "GET", url, False
        objHTTP.SetRequestHeader "Content-type", "application/xml"
        objHTTP.SetRequestHeader "Accept", "application/xml"
        objHTTP.SetRequestHeader "X-SAP-LogonToken", boToken
        objHTTP.Send ""
        
        errorCodeREST = getErrorCodeREST(objHTTP)
        If errorCodeREST <> "" And errorCodeREST <> "WSR 00400" Then
            Call afficheErrorREST(objHTTP, "RefreshlistUnivers", "Error getting list of universes")
            Call logoff
            End
        End If
        
        objXML.LoadXML (objHTTP.ResponseText)
        'Debug.Print objHTTP.ResponseText
        i = 0
        For Each oNodeXML In objXML.SelectNodes("/universes/universe")
            t = "/universes/universe[" & i & "]"
            Sheets("liste univers").Cells(l + 2, 1) = objXML.SelectSingleNode(t & "/id").Text
            Sheets("liste univers").Cells(l + 2, 2) = objXML.SelectSingleNode(t & "/cuid").Text
            Sheets("liste univers").Cells(l + 2, 4) = objXML.SelectSingleNode(t & "/type").Text
            folderId = objXML.SelectSingleNode(t & "/folderId").Text
            Sheets("liste univers").Cells(l + 2, 3) = getFolder(folderId)
            Sheets("liste univers").Cells(l + 2, 5) = Decode_UTF8(objXML.SelectSingleNode(t & "/name").Text)
            
            l = l + 1
            i = i + 1
        Next
    Loop While i > 0

logoff:
    Call logoff
End Sub

The folder

For the folder of the universe, we have just a folder ID. We need to call another REST url, /infostore/ to get name from this ID. The function getFolder does it. The workflow is the same : call REST url, check return code, load XML, find the good node and get value.

For example, we call <servername>:6405/biprws/infostore/99 and we get :

<entry xmlns="http://www.w3.org/2005/Atom"> <author><name>System Account</name></author> <id>tag:sap.com,2010:bip-rs/AWItAeqx.FpBgqTpFH8LqwE</id>
Business Intelligence Platform RESTful Web Service Developer Guide
API reference © 2014 SAP SE or an SAP affiliate company. All rights reserved. 93
<title type="text">Root Folder 99</title> <updated>2011-04-14T10:27:50.969Z</updated> <link href="http://localhost:6405/biprws/infostore/99/children" rel="http://www.sap.com/rws/bip#children"></link> <link href="http://localhost:6405/biprws/infostore/Application%20Folder" rel="up"></link> <content type="application/xml"> <attrs xmlns="http://www.sap.com/rws/bip"> <attr name="id" type="int32">99</attr> <attr name="description" type="string" null="true"></attr> <attr name="cuid" type="string">AWItAeqx.FpBgqTpFH8LqwE</attr><attr name="description" type="string" null="true"></attr> <attr name="name" type="string">Root Folder 99</attr> <attr name="type" type="string">Folder</attr> </attrs> </content></entry>

Well, for the name we need to find attributes, like for logon (see previous post) … Instead of using positions (not tidy), let’s write a function to find the good attribute …

Private Function getAttribute(o As WinHttp.WinHttpRequest, name As String)
    Dim tmpXML As MSXML2.DOMDocument
    Dim node As MSXML2.IXMLDOMNode
    
    Set tmpXML = CreateObject("Microsoft.XMLDOM")
    tmpXML.LoadXML (o.ResponseText)
    
    'recup attribut
    For Each node In tmpXML.SelectNodes("//attrs/attr")
        If node.Attributes.getNamedItem("name").Text = name Then getAttribute = node.Text
    Next
End Function

Private Function getFolder(id)
    Dim tmpHTTP As WinHttp.WinHttpRequest
    Dim tmpXML As MSXML2.DOMDocument
    Dim s As String

    url = boUrl & "/infostore/" & id
    Debug.Print url
    
    Set tmpHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    tmpHTTP.Open "GET", url, False
    tmpHTTP.SetRequestHeader "Accept", "application/xml"
    tmpHTTP.SetRequestHeader "X-SAP-LogonToken", boToken
    tmpHTTP.Send ""
        
    If tmpHTTP.Status <> "200" Then
        Call afficheErrorREST(tmpHTTP, "getFolder", "Error gettin folder " & id)
    Else
        s = getAttribute(tmpHTTP, "name")
        getFolder = Decode_UTF8(s)
    End If
End Function

The name of universe is in UTF-8, and you have to decode it if you have special characters like accents. I found functions on the web , I copy/paste them. Thanks to Cyberpat92.

Howto test :
open the document, set the bi4 servername, login, password in the first sheet, and click the button or call the macro refreshListUnivers().

TODO List :

• Get the full path of universe, not only one level …
• Use while instead of for loop in function getAttribute

Next step : Get content of an universe

Attached file : liste univers.zip