BI4 RESTful SDK / Excel : WebIntelligence Documents : Change dataproviders

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