BI4 RESTful SDK / Excel : List documents using an universe

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