Skip to Content

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.

Workflow

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

API

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 :

<documents>
	<document>
		<id>6804</id>
		<cuid>AbrVz6E95lNMtjZk4KLzPuM</cuid>
		<name>BOF-ROLE_AA_ActiveHierOnCtry - Levels 02 and 03</name>
		<folderId>7611</folderId>
		<state>Original</state>
	</document>
	<document>
		<id>6861</id>
		<cuid>AUwfvPdEG3xOoLNoNEzDquQ</cuid>
		<name>BOF-QRY_SIMPLE - Filter not in Result</name>
		<description>This is a sample for BOF-QRY_SIMPLE - Filter not in Result</description>
		<folderId>7611</folderId>
		<state>Unused</state>
	</document>
...
</documents>

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
        Next
    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 :

<dataproviders>
	<dataprovider>
		<id>DP0</id>
		<name>Query 1</name>
		<dataSourceId>6187</dataSourceId>
		<dataSourceType>unv</dataSourceType>
		<updated>2006-09-20Z</updated>
	</dataprovider>
	<dataprovider>
		<id>DP1</id>
		<name>Query 2</name>
		<dataSourceId>6191</dataSourceId>
		<dataSourceType>unx</dataSourceType>
		<updated>2014-04-23T09:08:20.000+02:00</updated>
	</dataprovider>
	<dataprovider>
		<id>DP2</id>
		<name>Query 3</name>
		<updated>2014-04-23T09:08:20.000+02:00</updated>
	</dataprovider>
</dataproviders>

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 oNodeXML, oSubNodeXML As MSXML2.IXMLDOMNode
    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
    Next
    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 !

Solution

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

SELECT TOP 100000 SI_ID, SI_NAME, SI_CUID, SI_PARENTID, SI_WEBI, SI_OWNER, SI_WEBI_DOC_PROPERTIES FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where PARENTS ("SI_NAME = 'Webi-Universe'", "SI_NAME = 'efashion'")

 

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 lies_1.0.xls.zip

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply