BI4 RESTful SDK / Excel : List Universes Sample

[former_member186753]

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