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 :
- logon, get the token
- get source and target universe’s id from excel worksheet
- for each checked document in the excel worksheet,
- call the url to get all dataproviders of the document
- for each dataprovider of the document
- if the universe is the same as the source universe,
- call another url to get default mapping
- call another url to set default mapping and commit changes
- call another url to save document
- 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&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
downloaded and tested all the resftul utilities, working great except change data provider. thanks for sharing.
while trying to change the data providers, it throwing error.
Hi Santhosh,
Do you have a message for this error ?
Thanks for the response Stephan, here is the error message..