Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
0 Kudos
Synopsis: In this series of Blogs, I'll explore how we can use RESTful Raylight Web Services in Excel with VBA.

Previous Entry: Get content of an universe Sample

I didn't found a simple url to get the documents using a specific universe. We have to browse all documents, and see if they have dataproviders based on this universe.


The workflow is :

  1. get universe's id from excel worksheet

  2. logon, get the token

  3. create an object for HTTP request

  4. define the URL, set parameters of the HTTP request

  5. put the token in header of the http request

  6. call the URL to list all documents

  7. check return code, stop and logoff it an error occured

  8. create an object to analyze the XML of the response

  9. load XML of the response

  10. get data

  11. for each element (=document)

  12. call another URL to list all DataProviders from the document

  13. check return code, stop and logoff it an error occured

  14. create an object to analyze the XML of the response

  15. load XML of the response

  16. check for each DataProvider if it uses the specific universe

  17. if it's ok paste values of first XML (document) in excel sheet

  18. logoff


We call the REST url to list all documents, the url is like GET <servername>:6405/biprws/sl/v1/universes/documents?offset=xxx where xxx indicates the position in the list, from which documents are returned.

The XML of response body is like :
<name>BOF-ROLE_AA_ActiveHierOnCtry - Levels 02 and 03</name>
<name>BOF-QRY_SIMPLE - Filter not in Result</name>
<description>This is a sample for BOF-QRY_SIMPLE - Filter not in Result</description>

We parse all nodes from XML to get document IDs.
Public Sub refreshListDocs4Unv()
objXML.LoadXML (objHTTP.ResponseText)
'Debug.Print objHTTP.ResponseText
i = 0
For Each oNodeXML In objXML.SelectNodes("/documents/document")
boDocId = oNodeXML.SelectSingleNode("id").Text
If getNumberDPLinkUnv(boDocId, boUnivId) > 0 Then
f.Cells(l + 2, 1) = boDocId
f.Cells(l + 2, 2) = oNodeXML.SelectSingleNode("cuid").Text
f.Cells(l + 2, 4) = Decode_UTF8(oNodeXML.SelectSingleNode("name").Text)
folderId = oNodeXML.SelectSingleNode("folderId").Text
f.Cells(l + 2, 3) = folderId
'f.Cells(l + 2, 3) = getFolder(folderId)
l = l + 1
End If
i = i + 1
t = t + 1
Loop While i > 0 And t < 500
End Sub

For each document we see if it has dataprovider on the universe, with getNumberDPLinkUnv() function. Parameters are ID of the document and ID of the universe. This function calls another url to get the dataproviders, it's like GET <servername>:6405/biprws/sl/v1/documents/<docId>/dataproviders where <docId> is the document ID.

The XML in response is like :
<name>Query 1</name>
<name>Query 2</name>
<name>Query 3</name>

We browse all nodes and see if a dataprovider has a dataSourceId on the universe.

You notice that we don't have always the dataSourceId node. Why ? Not sure, but I found it in case of migrations, perhaps webi has lost universe id ?
Private Function getNumberDPLinkUnv(ByVal idDoc As Integer, ByVal idUnv As Integer)
Dim objHTTP As WinHttp.WinHttpRequest
Dim objXML As MSXML2.DOMDocument
Dim n As Integer
Dim dpType, dpId As String

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Set objXML = CreateObject("Microsoft.XMLDOM")

url = boUrlRL & "/documents/" & idDoc & "/dataproviders"
objHTTP.Open "GET", url, False
objHTTP.SetRequestHeader "Content-type", "application/xml"
objHTTP.SetRequestHeader "Accept", "application/xml"
objHTTP.SetRequestHeader "X-SAP-LogonToken", boToken
objHTTP.Send ""

If objHTTP.Status <> "200" Then
Debug.Print "Error gettin number of DP for document " & idDoc
End If

objXML.LoadXML (objHTTP.ResponseText)
n = 0
On Error Resume Next ' sometimes we don't have all the nodes ...
For Each oNodeXML In objXML.SelectNodes("/dataproviders/dataprovider")
dpType = oNodeXML.SelectSingleNode("dataSourceType").Text
dpId = oNodeXML.SelectSingleNode("dataSourceId").Text
If dpType = "unv" Or dpType = "unx" Then
If dpId = idUnv Then n = n + 1
If dpId = "" Then Debug.Print "pas de dataSourceId pour le doc " & idDoc & " (" & dpType & ")"
End If
On Error GoTo 0

Set objXML = Nothing
Set objHTTP = Nothing
getNumberDPLinkUnv = n
End Function

Limits of RESTful API

This script calls a lot of urls ... If we have 10000 documents, the script does 10001 calls ... And after a long long time I got an error :

 I give up to do it with RESTful, I'm not sure the script works !


Old school : a CMS request, with BI4 dlls installed within BI4 clients ...
The request is like


Howto test :

Open the document, set the bi4 servername, login, password in the first sheet, and click the button refresh list or call the macro refreshListUnivers().
Select the universe in sheet liste univers, note the ID (first column).
Enter this ID in sheet config and the name below and

  • (RESTful method) click the button list documents (REST) or call the macro refreshListDocs4Unv()

  • (CMS method) click the button list documents (BO dll) or call the macro refreshListDocs4UnvNotRESTFUL()

See the liste docs sheet.

TODO List :

  • find another way to get the documents in RESTful, any idea ?

Next step : WebIntelligence Documents : Change dataproviders
Attached file : liste univers documents
Labels in this area