BI4 RESTful SDK / Excel : WebIntelligence Documents : Change dataproviders

[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: List documents using an universe

Howto change universe in dataproviders on a lot of webo documents ? With RESTful SDK and Excel !
We got the list of documents in the previous chapter, we can select the ones we want to change.

For more information you can see another SAP community's blog. The Shawn Penner's script  inspired me, a nice suggestion to read !

Workflow

The workflow is :

1. logon, get the token


2. get source and target universe's id from excel worksheet


3. for each checked document in the excel worksheet,


4. call the url to get all dataproviders of the document


5. for each dataprovider of the document


6. if the universe is the same as the source universe,


7. call another url to get default mapping


8. call another url to set default mapping and commit changes


9. call another url to save document


10. logoff

Of course we have to parse XML of requests, but if you followed the blog it's the same as in previous posts ...

It seems that we don't have to save the document, but it didn't work for me on 4.1 SP7, so I think it's better to force to save the document.

Level 1 : logon -> for each checked document in the excel worksheet, logoff
It's Excel VBA, I wrote a simple procedure for this :

Public Sub changeUniverseAll()

    Dim boDocId, boUnivSrcId, boUnivTgtId, rownum As Integer

    

    Call logon

    

    boUnivSrcId = Sheets("config").Cells(6, 2).value

    boUnivTgtId = Sheets("config").Cells(7, 2).value

    

    rownum = 2

    While Sheets("liste docs").Cells(rownum, 1) <> ""

        If Sheets("liste docs").Cells(rownum, 5) = "x" Then

            boDocId = Sheets("liste docs").Cells(rownum, 1)

            Call changeUniverseDoc(boDocId, boUnivSrcId, boUnivTgtId)

        End If

        rownum = rownum + 1

    Wend



    Call logoff

End Sub

 

Level 2 : Call the url to get all dataproviders of the document, for each dataprovider ...

The url is like GET <servername>:6405/biprws/sl/v1/documents/<docId>/dataproviders where <docId> is the document ID. We used it in the previous post.

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 verify if a dataprovider has a dataSourceId on the universe. Then we go to level 3 ...

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 ?
To be sure, you can verify all dataproviders one by one calling another url (like http://<servername>:6405/biprws/raylight/v1/documents/12345/dataproviders/DP0 )

The procedure is :

Private Sub changeUniverseDoc(ByVal idDoc As Integer, ByVal idUnvSrc As Integer, ByVal idUnvTgt 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, dpSrcId, idUnv 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

        Call afficheErrorREST(objHTTP, "getNumberDPLinkUnv", "Error gettin number of DP for document " & idDoc)

        Call logoff

        End

    End If

        

    objXML.LoadXML (objHTTP.ResponseText)

    Debug.Print objHTTP.ResponseText

    n = 0

    On Error Resume Next ' car des fois le XML est mal formulé ...

    For Each oNodeXML In objXML.SelectNodes("/dataproviders/dataprovider")

        dpType = oNodeXML.SelectSingleNode("dataSourceType").Text

        dpSrcId = oNodeXML.SelectSingleNode("dataSourceId").Text

        dpId = oNodeXML.SelectSingleNode("id").Text

        Debug.Print "Check DP " & dpId

        If dpSrcId = idUnvSrc And dpType = "unv" Then

            n = n + 1

            Debug.Print "Change DP " & dpId

            Call changeUniverseDocDP(idDoc, idUnvSrc, idUnvTgt, dpId)

            'If dpId = "" Then Debug.Print "pas de dataSourceId pour le doc " & idDoc & " (" & dpType & ")"

        End If

    Next

    On Error GoTo 0

    

    If n = 0 Then

        Call MsgBox("No change for doc " & idDoc, vbCritical)

    Else

        Call MsgBox("Doc " & idDoc & " changed", vbOKOnly)

    End If

        

    Set objXML = Nothing

    Set objHTTP = Nothing



End Sub

Level 3 : Change dataprovider ... call another url to get default mapping -> save

First, we have to get & verify status of default mapping. We call an url like GET <servername>:6405/biprws/sl/v1/documents/12345/dataproviders/mappings?originDataproviderIds=DP0&targetDatasourceId=5387 where 12345 is the document ID, DP0 the dataprovider ID and targetDatasourceId the universe ID we want to have in target.

We can change multiple dataproviders at the same time. For example we can call the url POST /documents/12345/dataproviders/mappings?originDataproviderIds=DP0,DP1&amp;targetDatasourceId=5387

The response expected is like :

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<mappings>

	<content>

		<mapping status="Ok">

			<source>

				<id>DS0.DOa6</id>

			</source>

			<target>

				<id>DS1.DOa6</id>

			</target>

		</mapping>

		<mapping status="Ok">

			<source>

				<id>DS0.DOda</id>

			</source>

			<target>

				<id>DS1.DOda</id>

			</target>

		</mapping>

	</content>

</mappings>

We have to verify all status attributes are "Ok" in mapping nodes. Next, we commit the changes. It's easy, we call the same url in POST (not GET) with this response xml as request body. We have to get a response body like :

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<success>

	<message>The resource of type 'Document' with identifier '12345' has been successfully updated.</message>

	<id>10113</id>

</success>

We verify if we have a success node. Finally we save the document, we call the url PUT <servername>:6405/biprws/sl/v1/documents/<docId>.

The final VBA procedure is :

Private Sub changeUniverseDocDP(idDoc As Integer, idUnvSrc As Integer, idUnvTgt As Integer, ByVal idDP As String)

    Dim objHTTP As WinHttp.WinHttpRequest

    Dim objXML As MSXML2.DOMDocument

    Dim oNodeXML, oSubNodeXML As MSXML2.IXMLDOMNode

    Dim n As Integer

    Dim mappingstatus, idSource As String

       

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

    Set objXML = CreateObject("Microsoft.XMLDOM")

    

    ' get & verify status of default mapping

    url = boUrlRL & "/documents/" & idDoc & "/dataproviders/mappings?originDataproviderIds=" & idDP & "&targetDatasourceId=" & idUnvTgt

    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

        Call afficheErrorREST(objHTTP, "changeUniverseDocDP", "Error gettin default mapping for document " & idDoc & " & DP " & idDP)

        Call logoff

        End

    End If

        

    'verify default mapping

    objXML.LoadXML (objHTTP.ResponseText)

    n = 0

    For Each oNodeXML In objXML.SelectNodes("/mappings/content/mapping")

        mappingstatus = oNodeXML.Attributes.getNamedItem("status").Text

        If mappingstatus <> "Ok" Then

            n = n + 1

            idSource = oNodeXML.SelectSingleNode("source/id").Text

            Call MsgBox("Can't change mapping for Doc " & idDoc & ", DP " & idDP & ", column " & idSource & ", status=" & mappingstatus, vbCritical)

        End If

    Next

    

    If n > 0 Then

        Call MsgBox("Stop, can't change mapping")

        Call logoff

        End

    End If

    

    'commit

    url = boUrlRL & "/documents/" & idDoc & "/dataproviders/mappings?originDataproviderIds=" & idDP & "&targetDatasourceId=" & idUnvTgt

    objHTTP.Open "POST", url, False

    objHTTP.SetRequestHeader "Content-type", "application/xml"

    objHTTP.SetRequestHeader "Accept", "application/xml"

    objHTTP.SetRequestHeader "X-SAP-LogonToken", boToken

    objHTTP.Send objXML.XML

        

    If objHTTP.Status <> "200" Then

        Call afficheErrorREST(objHTTP, "changeUniverseDocDP", "Error commit mapping for document " & idDoc & " & DP " & idDP)

        Call logoff

        End

    End If

        

    objXML.LoadXML (objHTTP.ResponseText)

    n = 0

    For Each oNodeXML In objXML.SelectNodes("/success")

        n = n + 1

    Next

    If n = 0 Then

        Call MsgBox("Error on change mapping, no success ?")

    End If

        

    'save

    url = boUrlRL & "/documents/" & idDoc

    objHTTP.Open "PUT", 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

        Call afficheErrorREST(objHTTP, "changeUniverseDocDP", "Error saving document " & idDoc)

        Call logoff

        End

    End If

        

    Set objXML = Nothing

    Set objHTTP = Nothing

End Sub

Howto test :

• Open the document, set the bi4 servername, login, password in the first sheet, and click the button refresh list


• Select source and target universes in sheet liste univers, note the ID (first column).


• Enter these IDs in sheet config and
  
  
  
  • (RESTful method) click the button list documents (REST)
  
  
  • (CMS method) click the button list documents (BO dll)
  
  
  
  


• Check the documents to be altered in liste docs sheet (set column E to "x")


• Click button change universe

Warning : The documents are altered, there's no rollback !!!

TODO List :

• Make a backup of document before commit


• Verify all dataproviders one by one


• Save document AFTER all dataproviders have been changed


• Change all dataproviders in only one request

Attached file : change_universe_1.0.xlsm.zip