Skip to Content
SBOP SDK

Changing Dataproviders for a Webi Report using RESTful Web Services

Tags:

Description

The sample below was written in Java and demonstrates how to use the Restful SDK to change the datasource provider (Universe) for a webi report.

Special Note:

-There is a known issue where using this method will cause any filters and input controls to be removed from the report.  It has been tracked as ADAPT01704491 and is currently targeted to be fixed in BI 4.1 SP3.

Restful call sequence

This section will list the restful calls made by this sample in case you want to translate it into another language.

Logon to Enterprise

Type of call: POST
URL: http://localhost:6405/biprws/logon/long

Headers:
Accept: application/xml

Payload:
<attrs>
     <attr name="userName" type="string" >Administrator</attr>
     <attr name="password" type="string" >MyPassword</attr>
     <attr name="auth" type="string" possibilities="secEnterprise,secLDAP,secWinAD,secSAPR3">secEnterprise</attr>
</attrs>

Expected Response:

<entry>
     <author>
          <name>@MyServer:6400</name>
     </author>
     <id>tag:sap.com,2010:bip-rs/logon/long</id>
     <title type="text">Logon Result</title>
     <updated>2013-09-03T21:46:47.360Z</updated>
     <content type="application/xml">
          <attrs>
               <attr name="logonToken" type="string">MyServer:6400@{3&2=10209,U3&2v=MyServer:6400,UP&66=60,U3&68=secEnterprise:Administrator,UP&S9=12,U3&qe=100,U3&vz=yCFAVGKDkzcAIefcqibDE9m8WGhlPyIyPjzijsiChX8,UP}

               </attr>
          </attrs>
     </content>
</entry>

Add LogonToken

Take the Resulting logontoken and add it to the header as

X-SAP-LogonToken: "MyServer:6400@{3&2=10209,U3&2v=MyServer:6400,UP&66=60,U3&68=secEnterprise:Administrator,UP&S9=12,U3&qe=100,U3&vz=yCFAVGKDkzcAIefcqibDE9m8WGhlPyIyPjzijsiChX8,UP}"

Retrieve the dataprovider collection for the report

Note: the SI_ID of the report is 12345


Type of Call: GET


URL: http://localhost:6405/biprws/raylight/v1/documents/12345/dataproviders

Headers:
Accept: application/xml
X-SAP-LogonToken: "MyServer:6400@{3&2=10209,U3&2v=MyServer:6400,UP&66=60,U3&68=secEnterprise:Administrator,UP&S9=12,U3&qe=100,U3&vz=yCFAVGKDkzcAIefcqibDE9m8WGhlPyIyPjzijsiChX8,UP}"

Payload:
None - it's a GET request

Expected Response:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<dataproviders>
     <dataprovider>
          <id>DP0</id>
          <name>Query 1</name>
          <updated>2013-08-28T18:59:52Z</updated>
     </dataprovider>
</dataproviders>

Verify what universe that dataprovider is based off of

Type of Call: GET


URL: http://localhost:6405/biprws/raylight/v1/documents/12345/dataproviders/DP0

Headers:
Accept: application/xml
X-SAP-LogonToken: "MyServer:6400@{3&2=10209,U3&2v=MyServer:6400,UP&66=60,U3&68=secEnterprise:Administrator,UP&S9=12,U3&qe=100,U3&vz=yCFAVGKDkzcAIefcqibDE9m8WGhlPyIyPjzijsiChX8,UP}"

Payload:
None - it's a GET request

Expected Response:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<dataprovider>
     <id>DP0</id>
     <name>Query 1</name>
     <dataSourceId>5379</dataSourceId>
     <updated>2013-08-28T11:59:52.000-07:00</updated>
     <duration>2</duration>
     <isPartial>false</isPartial>
     <rowCount>13</rowCount>
     <flowCount>1</flowCount>
     <dictionary>
          <expression qualification="Dimension" dataType="String">
               <id>DP0.DOa6</id>
                <name>City</name>
               <description>City located.</description>
                <dataSourceObjectId>DS0.DOa6</dataSourceObjectId>
          </expression>
          <expression qualification="Dimension" dataType="String">
               <id>DP0.DOda</id>
               <name>State</name>
               <description>State located.</description>
               <dataSourceObjectId>DS0.DOda</dataSourceObjectId>
          </expression>
     </dictionary>
     <query>SELECT Outlet_Lookup.State, Outlet_Lookup.City FROM Outlet_Lookup</query>
</dataprovider>

From that result - we can see that the dataprovider is based off of the universe with SI_ID 5379

Get the default mappings for changing to a different universe

Note: 5387 is the SI_ID of the new universe

Type of Call: GET


URL: http://localhost:6405/biprws/raylight/v1/documents/12345/dataproviders/mappings?originDataproviderIds=DP0&targetDatasourceId=5387

Headers:
Accept: application/xml
X-SAP-LogonToken: "MyServer:6400@{3&2=10209,U3&2v=MyServer:6400,UP&66=60,U3&68=secEnterprise:Administrator,UP&S9=12,U3&qe=100,U3&vz=yCFAVGKDkzcAIefcqibDE9m8WGhlPyIyPjzijsiChX8,UP}"

Payload:
None - it's a GET request

Expected Response:
<?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>

Commit Changes

Note:  We are assuming that the default mappings are OK.  If you wanted to make changes to the mappings - you would do so here


Type of Call: POST


URL: http://localhost:6405/biprws/raylight/v1/documents/12345/dataproviders/mappings?originDataproviderIds=DP0&targetDatasourceId=5387

Headers:
Accept: application/xml
X-SAP-LogonToken: "MyServer:6400@{3&2=10209,U3&2v=MyServer:6400,UP&66=60,U3&68=secEnterprise:Administrator,UP&S9=12,U3&qe=100,U3&vz=yCFAVGKDkzcAIefcqibDE9m8WGhlPyIyPjzijsiChX8,UP}"

Payload:
<?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>

Expected Response:
<?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>

There is no need to explicitly save the webi report as calling the POST statement in step 6 also saves the modified report.

Instructions

To run this sample on a BI 4.1 system:

  1. Stop Tomcat
  2. Copy XercesImpl.jar from C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\BOE\WEB-INF\eclipse\plugins\webpath.PlatformServices\web\WEB-INF\lib to C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\AdminTools\WEB-INF\lib
  3. Go to http://hc.apache.org/downloads.cgi and download one of the binary zip files (This sample was original coded with version 4.2.5).  Then extract and add the jar files in it to the folder C:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\AdminTools\WEB-INF\lib
  4. Start Tomcat
  5. Browse to http://localhost:8080/AdminTools/YourApp.jsp

Notes:

Possible Issues

  • "org.apache.http.client.HttpResponseException: Unauthorized"
    This error can happen if you forget to include the "Accept" or "Content-Type" headers for your get / post requests
    It can also happen if you pass in the wrong Enterprise Password.
  • Notice that there is no CMS name variable - that is because it is assumed that you are connecting to the system who's restful services you are using

Source Code

Source code for Remap Webi Report

<%// Created by Shawn Penner 2013 %>

<% // These imports are for the Apache HttpComponents %>
<%@ page import = "org.apache.http.client.ResponseHandler"%>
<%@ page import = "org.apache.http.client.HttpClient"%>
<%@ page import = "org.apache.http.client.methods.HttpGet"%>
<%@ page import = "org.apache.http.client.methods.HttpPost"%>
<%@ page import = "org.apache.http.impl.client.BasicResponseHandler"%>
<%@ page import = "org.apache.http.impl.client.DefaultHttpClient"%>
<%@ page import = "org.apache.http.entity.StringEntity"%>
<%@ page import = "org.apache.http.client.HttpResponseException"%>
<%@ page import = "org.apache.http.HttpResponse"%>
<%@ page import = "org.apache.http.HttpEntity"%>
<%@ page import = "org.apache.http.client.methods.HttpPut"%>
<%@ page import = "org.apache.http.util.EntityUtils"%>


<% // These imports are for the XML DOM Parser %>
<%@ page import = "javax.xml.parsers.DocumentBuilderFactory"%>
<%@ page import = "javax.xml.parsers.DocumentBuilder"%>
<%@ page import = "org.w3c.dom.*"%>
<%@ page import = "org.w3c.dom.Node.*"%>
<%@ page import = "org.w3c.dom.Element"%>
<%@ page import = "com.sun.org.apache.xerces.internal.parsers.*"%>
<%@ page import = "org.xml.sax.InputSource"%>

<% // These imports are for transforming the DOM Parser back into a string %>
<%@ page import = "javax.xml.transform.Transformer"%>
<%@ page import = "javax.xml.transform.TransformerFactory"%>
<%@ page import = "javax.xml.transform.OutputKeys"%>
<%@ page import = "javax.xml.transform.dom.DOMSource"%>
<%@ page import = "javax.xml.transform.stream.StreamResult"%>

<% // Imports for URL Encoding %>
<%@ page import = "org.apache.http.client.entity.UrlEncodedFormEntity"%>
<%@ page import = "org.apache.http.client.utils.URLEncodedUtils"%>
<%@ page import = "org.apache.http.message.BasicNameValuePair"%>
<%@ page import = "org.apache.http.NameValuePair"%>

<% // Fiddler Trace Params %>
<%@ page import = "org.apache.http.HttpHost"%>
<%@ page import = "org.apache.http.conn.params.*"%>

<% // Generic Java Imports %>
<%@ page import = "java.io.*"%>
<%@ page import = "java.util.List"%>
<%@ page import = "java.util.ArrayList"%>

<%
// Enterprise Authentication Credentials
String boUsername = "Administrator";
String boPassword = "Password";
String boAuthType = "secEnterprise";

// Enable Fiddler Trace.  This causes the requests to go through a proxy on port 8888 which fiddler listens on
boolean enableFiddler = false;

// Sample Variables
String reportID = "12345";
String sourceUniverseID = "5379"; // Only change dataproviders based off of this universe
String destUniverseID = "5387";  // Change those dataproviders to use this Universe

// Restful URL's
final String baseURL = "http://MyServer:6405/biprws";
final String logonURL = baseURL + "/logon/long";
final String logoffURL = baseURL + "/logoff";
final String baseWebiURL = baseURL + "/raylight/v1/documents";

try {

String xmlString = "";
String documentID = "";
String logonToken = "";

// -----------------------

// Logon to Enterprise

// -----------------------

// First check to see if we have a logonToken stored in session.  If so, use it.
if (session.getAttribute("logonToken") != null) {
  out.println("LogonToken found </br>");
  logonToken = (String)session.getAttribute("logonToken");
} else {

  // No logontoken detected - so create one
  out.println("No LogonToken found - Creating one </br>");
  xmlString = "<attrs><attr name=\"userName\" type=\"string\" >" + boUsername + "</attr><attr name=\"password\" type=\"string\" >" + boPassword + "</attr><attr name=\"auth\" type=\"string\" possibilities=\"secEnterprise,secLDAP,secWinAD,secSAPR3\">" + boAuthType + "</attr></attrs>";
  String logonXML = restPost(enableFiddler, logonURL, xmlString, "","","","");
 
  // The quotes are added because the webi URL require quotes around the token
  logonToken = "\"" + getLogonTokenFromXML(logonXML) + "\"";

  // Now that we have a logonToken - it must be included in the header for a future RestFul calls
  session.setAttribute("logonToken", logonToken);
}

// ---------------------------------------------------

// Retrieve the dataprovider collection for the report

// ---------------------------------------------------

out.println("Looking at report " + reportID + " for a dataprovider based off of universe with ID " + sourceUniverseID + "</br>");
// First retrieve the webi report dataproviders so we can iterate through them
xmlString = restGet(enableFiddler, baseWebiURL + "/" + reportID + "/dataproviders", "X-SAP-LogonToken",logonToken, "","");

// Now iterate through the result
Document myDoc = convertStringToDom(xmlString);

// Now Loop through all the dataprovider nodes that were returned
// It is assumed that each dataprovider node will only have one ID tag
NodeList dpNodes = myDoc.getElementsByTagName("id");


for (int j = 0; j < dpNodes.getLength(); j++) {
  Element dpElement = (Element) dpNodes.item(j);
 
  // Get the string value
  String nodeID = (String)dpElement.getTextContent();
 
  out.println("Looking at DataProvider: " + nodeID + "</br>");

// ------------------------------------------------------

// Verify what universe that dataprovider is based off of

// ------------------------------------------------------


  // Now we need to check what universe this dataprovider is based off of.  To do that, we need to do a second query
  String xmlString2 = restGet(enableFiddler, baseWebiURL + "/" + reportID + "/dataproviders/" + nodeID, "X-SAP-LogonToken",logonToken, "","");
 
  // Now iterate through the result
  Document myDoc2 = convertStringToDom(xmlString2);

  // There should only be 1 result with the tag dataSourceID
  NodeList UnivNodes = myDoc2.getElementsByTagName("dataSourceId");
  Element univNode = (Element)UnivNodes.item(0);
 
  // Retrieve the value of the node
  String univValue = (String)univNode.getTextContent();
 
  out.println("Universe for " + nodeID + " is " + univValue + "</br>");
  // Now compare the universe value to verify if we should change this dataprovider
  if (sourceUniverseID.equals(univValue)) {
   out.println("Source Universe to look for is: " + sourceUniverseID + " - match found - changing universe </br>");
 
   // Confirmed - now change universe

// ------------------------------------------------------

// Get the default mappings for changing to a different universe

// ------------------------------------------------------


   // First get the default mappings
   String changeUnivUrl = baseWebiURL + "/" + reportID + "/dataproviders/mappings?originDataproviderIds=" + nodeID + "&targetDatasourceId=" + destUniverseID;
   String xmlString3 = restGet(enableFiddler, changeUnivUrl, "X-SAP-LogonToken",logonToken, "","");

// -----------------

// Commit Changes

// -----------------


   // Now do the actual change
   String xmlString4 = restPost(enableFiddler, changeUnivUrl, xmlString3, "X-SAP-LogonToken",logonToken,"","");
   out.println("Result of call: " + xmlString4 + "</br>");
  
   // There is no need to save the report after making this call - it is automatically saved.
  }
}

// Now log off
xmlString = restPost(enableFiddler, logoffURL, "", "X-SAP-LogonToken",logonToken,"","");
session.removeAttribute("logonToken");

out.println("Logged off - done");

} catch (IOException eIO) {
    out.println("IO Exception: " + eIO);
} catch (Exception ex) {
out.println("Exception: " + ex);
}

%>


<%!

// This is for debugging purposes.  The xmp tag tells the browser you want to see the XML on screen
public void printXML(JspWriter out, String msg) throws Exception {
out.println("<xmp>" + msg.replaceAll("><", "></xmp><xmp><") + "</xmp>");
}

public Document convertStringToDom(String domXMLSTring) throws Exception {
DOMParser parser = new DOMParser();
parser.parse(new InputSource(new java.io.StringReader(domXMLSTring)));
return (parser.getDocument());
}

public String convertDomToString(Document doc) throws Exception {
// Now convert the document back to a string
TransformerFactory tf = TransformerFactory.newInstance();
Transformer transformer = tf.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "no");
StringWriter writer = new StringWriter();
transformer.transform(new DOMSource(doc), new StreamResult(writer));
String output = writer.getBuffer().toString().replaceAll("\n|\r", "");
return(output);
}

public String buildEncodedURLString(String basePage, String url, String urlTitle) throws Exception {
String returnURL = "";

if (url.equals("")) {
  returnURL = urlTitle;
} else {
  List< NameValuePair > qparams = new ArrayList< NameValuePair >();
  qparams.add( new BasicNameValuePair( "queryURL", url ) );
  returnURL = "<a href=\"" + basePage + "?" + URLEncodedUtils.format(qparams, "UTF-8") + "\">" + urlTitle + "</a>";
}
return(returnURL);
}

public String getLogonTokenFromXML(String xmlString) throws Exception {
Document doc = convertStringToDom(xmlString);
NodeList nodes = doc.getElementsByTagName("attr");

for (int i = 0; i < nodes.getLength(); i++) {
  Element element = (Element) nodes.item(i);
 
  // Is this the correct XML token
  if (element.getAttribute("name").equals("logonToken")) {
   return(element.getTextContent());
  }
}
return("");
}

public static String restGet(Boolean enableFiddler, String urlStr, String param1Name, String param1Value, String param2Name, String param2Value ) throws Exception {

HttpClient httpclient = new DefaultHttpClient();

try {
  if (enableFiddler) {
   HttpHost proxy = new HttpHost("127.0.0.1", 8888, "http");
   httpclient.getParams().setParameter(ConnRoutePNames.DEFAULT_PROXY, proxy);
  }

  HttpGet httpget = new HttpGet(urlStr);
 
  httpget.addHeader("Accept", "application/xml");
  httpget.addHeader("Content-Type", "application/xml");
 
  if (!param1Name.equals("")) {
   httpget.addHeader(param1Name, param1Value);
  }
  if (!param2Name.equals("")) {
   httpget.addHeader(param2Name, param2Value);
  }
 
  HttpResponse response = httpclient.execute(httpget);
  int statusCode = response.getStatusLine().getStatusCode();
  HttpEntity entity = response.getEntity();
  String responseBody = (String)EntityUtils.toString(entity);
  return(responseBody);
} finally {
  // When HttpClient instance is no longer needed,
        // shut down the connection manager to ensure
        // immediate deallocation of all system resources
        httpclient.getConnectionManager().shutdown();
}
}

// Allow for two parameters to be passed to the post request along with the XML string.
// The most common one will be the "X-SAP-LogonToken" parameter
public static String restPost(Boolean enableFiddler, String urlStr, String XMLString, String param1Name, String param1Value, String param2Name, String param2Value ) throws Exception {

HttpClient httpclient = new DefaultHttpClient();
try {
 
  if (enableFiddler) {
   HttpHost proxy = new HttpHost("127.0.0.1", 8888, "http");
   httpclient.getParams().setParameter(ConnRoutePNames.DEFAULT_PROXY, proxy);
  }

  HttpPost httpPost = new HttpPost(urlStr);
  httpPost.addHeader("Accept", "application/xml");
  httpPost.addHeader("Content-Type", "application/xml");
 
  if (!param1Name.equals("")) {
   httpPost.addHeader(param1Name, param1Value);
  }
  if (!param2Name.equals("")) {
   httpPost.addHeader(param2Name, param2Value);
  }
 
  httpPost.setEntity(new StringEntity(XMLString));
  
  HttpResponse response = httpclient.execute(httpPost);
  int statusCode = response.getStatusLine().getStatusCode();
  HttpEntity entity = response.getEntity();
  String responseBody = (String)EntityUtils.toString(entity);
  return(responseBody);
} finally {
  // When HttpClient instance is no longer needed,
        // shut down the connection manager to ensure
        // immediate deallocation of all system resources
        httpclient.getConnectionManager().shutdown();
}
}

// Allow for two parameters to be passed to the post request along with the XML string.
// The most common one will be the "X-SAP-LogonToken" parameter
public static String restPut(Boolean enableFiddler, String urlStr, String XMLString, String param1Name, String param1Value, String param2Name, String param2Value ) throws Exception {

HttpClient httpclient = new DefaultHttpClient();
try {
 
  if (enableFiddler) {
   HttpHost proxy = new HttpHost("127.0.0.1", 8888, "http");
   httpclient.getParams().setParameter(ConnRoutePNames.DEFAULT_PROXY, proxy);
  }

  HttpPut httpPut = new HttpPut(urlStr);
  httpPut.addHeader("Accept", "application/xml");
  httpPut.addHeader("Content-Type", "application/xml");
 
  if (!param1Name.equals("")) {
   httpPut.addHeader(param1Name, param1Value);
  }
  if (!param2Name.equals("")) {
   httpPut.addHeader(param2Name, param2Value);
  }
 
  httpPut.setEntity(new StringEntity(XMLString));
  
  HttpResponse response = httpclient.execute(httpPut);
  int statusCode = response.getStatusLine().getStatusCode();
  HttpEntity entity = response.getEntity();
  String responseBody = (String)EntityUtils.toString(entity);
  return(responseBody);
} finally {
  // When HttpClient instance is no longer needed,
        // shut down the connection manager to ensure
        // immediate deallocation of all system resources
        httpclient.getConnectionManager().shutdown();
}
}

%>