Getting webservices as data source working in the Oracle BI Publisher
Facts - Other software technologies
Sunday, 30 May 2010 20:59

This article applies to the BI Publisher version 10.1.3.4 patch 9546699 installed on Tomcat 5.5. See this article for installation tips for the BI Publisher.

Oracle BI Publisher is a product which offers great flexibility in creating documents from a template. It uses reports defined as templates combined with so called data sources and arguments for calling these data sources. The templates define how response data from the data source is displayed in any generated documents. Oracle BI Publisher supports many data sources, such as databases and webservices. using the BI Publisher with the webservice data source offers great flexibility, is not difficult to set up and therefore is discussed here.

Test with public web service. If you want to use a web service as data source for your reports then you may first want to test with a public webservice instead of with your own webservice.

Proxy setting in application server. If you are behind a proxy and you want to test with a public webservice then you need to add proxy settings within your application server. For Apache Tomcat a proxy can be set using -Dhttp.proxyHost=yourproxyhost and -Dhttp.proxyPort=yourproxyport in the Java options in the Java tab of the Tomcat Properties Manager.

Return type web service data source. You can set the type of your webservice data source to "complex" or to "simple". According to the documentation this setting refers to the structure of the response of the web service. If you select "complex" and specify the wsdl url then the BI Publisher will present you a list of webservices and after selecting the webservice it will present a list of operations. After selecting the operations you can enter parameters. Be sure to enclose your parameters in ant-style brackets: ${yourparameter}. If you forget this the BI Publisher will pass the literal value of what you specify in the parameter list.

If you select "Complex type = false" then the response record consists of a simple string field containing an xml string. You will gain some flexibility if you set up your web service such that you can set the web service data source to "Complex type = true" in the BI Publisher. The advantage of using a simple type return is that you don't need to change the webservice definition every time you want to add fields. Instead you can add as many fields to the xml string response as you want.

A fair question is how to get the data for such a simple type web service. I worked at a city council department dealing with requests for building permits. And each request came from the central government as an xml. What we did is storing these xml's as XMLType fields in an oracle database. So our web service data source had the request id as input and the request xml string as simple type response.

The following wsdl is for a web service with a simple response type.

<wsdl:definitions
    name="simpleRequestOperations"
    targetNamespace="services/data/simpleRequestOperations"
    xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
    xmlns:tns="services/data/simpleRequestOperations"
    xmlns:http="http://schemas.xmlsoap.org/wsdl/http/"
    xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
    >
  <wsdl:types>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" 
                targetNamespace="services/data/simpleRequestOperations">
      <xsd:element name="RequestSimple">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element minOccurs="0" maxOccurs="1" name="RequestSimple" type="xsd:string"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:element name="RequestSimpleResponse">
        <xsd:complexType>
          <xsd:sequence>
            <!-- this field contains an xml string -->
            <xsd:element minOccurs="0" maxOccurs="1" name="RequestSimpleResult" type="xsd:string"/>
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
    </xsd:schema>
  </wsdl:types>
  <wsdl:message name="RequestSimpleSoapIn">
    <wsdl:part name="parameters" element="tns:RequestSimple"/>
  </wsdl:message>
  <wsdl:message name="RequestSimpleSoapOut">
    <wsdl:part name="parameters" element="tns:RequestSimpleResponse"/>
  </wsdl:message>
  <wsdl:portType name="RequestOperationsPort">
    <wsdl:operation name="RequestSimple">
      <wsdl:input message="tns:RequestSimpleSoapIn"/>
      <wsdl:output message="tns:RequestSimpleSoapOut"/>
    </wsdl:operation>
  </wsdl:portType>
  <wsdl:binding name="RequestOperationsBinding" type="tns:RequestOperationsPort">
    <soap:binding transport="http://schemas.xmlsoap.org/soap/http"/>
    <wsdl:operation name="RequestSimple">
      <soap:operation style="document" soapAction="/RequestSimple"/>
      <wsdl:input>
        <soap:body use="literal"/>
      </wsdl:input>
      <wsdl:output>
        <soap:body use="literal"/>
      </wsdl:output>
    </wsdl:operation>
  </wsdl:binding>
  <wsdl:service name="RequestOperationsService">
    <wsdl:port name="RequestOperationsSOAPEventSource" binding="tns:RequestOperationsBinding">
      <soap:address location="http://calculateonline.org:7000/RequestOperationsPortImplService"/>
    </wsdl:port>
  </wsdl:service>
</wsdl:definitions>

Java proxy for calling the BI Publisher as a web service. Writing a java proxy generating reports by calling the BI Publisher webservice is fairly simple: there are several how-to postings on the internet. The location of the report to be supplied to the web service call is worth mentioning here. Use java method ReportRequest.setReportAbsolutePath(..) to specify such the report location within the BI Publisher. The method argument is relative to the directory of your BI Publisher installation, see the file webaps/xmlpserver/WEB-INF/xmlpserver-config.xml in your Tomcat installation directory. If you installed the example reports then /Sales Manager/World Sales/World Sales.xdo is a valid path. On your file system this is the file Reports/Sales Manager/World Sales/World Sales.xdo. If you want to use a report named test from the administrator user in the Users directory then a valid path is /~administrator/test/test.xdo. On your file system this is Users/~administrator/test.

Templates: simplified XSLT. The BI Publisher Word templates consist of text segments mixed with simplified xslt statements. This syntax may be too technical for your business users.

Namespaces. If the fields of your web service response contain namespace then you need to add them to your template. Otherwise you won't see the response data of the webservice data source in your generated documents. Add the following to your template in order to add a namespace: <?namespace:ns=your.name.space?>
And add the prefix to the fields inside your template: <?ns:yourfield?>
The namespace declaration won't appear in the generated documents, since it is just a processing instruction.

Removing namespaces. If you have a java web service data source with "Complex type = false" you can also choose to remove the namespaces from the xml response string. In that case you can apply the following xml stylesheet to the response string and then return the transformed response string:

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
  <xsl:template match="/|comment()|processing-instruction()">
    <xsl:copy>
      <!-- go process children (applies to root node only) -->
      <xsl:apply-templates/>
    </xsl:copy>
  </xsl:template>
 
  <xsl:template match="*">
    <xsl:element name="{local-name()}">
      <!-- go process attributes and children -->
      <xsl:apply-templates select="@*|node()"/>
    </xsl:element>
  </xsl:template>
 
  <xsl:template match="@*">
    <xsl:attribute name="{local-name()}">
      <xsl:value-of select="."/>
    </xsl:attribute>
  </xsl:template>
 
</xsl:stylesheet>

You can extend this xml stylesheet to add extra fields in the xml. In our case this was the easiest way to add fields to the xml datasource output. Originally we got the data from an other webservice that was used by other components as well. By using this xsl transform we did not need to change this "upstream" webservice and neither the components accessing it.

If you have a java webservice data source then you can call the stylesheet using the following java function:

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.xml.sax.SAXException;
import org.w3c.dom.Document;
 
String RemoveNamespaces(String xmlinp) 
  throws ParserConfigurationException, SAXException, IOException,
           TransformerConfigurationException, TransformerException
{
  Document document;
  String stylesheet = ".. your stylesheet ...";
  DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
  factory.setNamespaceAware(true);
 
  DocumentBuilder builder = factory.newDocumentBuilder();
  StringReader xmlSR = new StringReader(xmlinp);
  InputSource xmlIS = new InputSource(xmlSR);
  document = builder.parse(xmlIS);
 
  // Use a Transformer for output
  TransformerFactory tFactory = TransformerFactory.newInstance();
  StringReader stylesheetSR = new StringReader(stylesheet);
  StreamSource stylesource = new StreamSource(stylesheetSR);
  Transformer transformer = tFactory.newTransformer(stylesource);
 
  DOMSource source = new DOMSource(document);
  StringWriter outputSW = new StringWriter();
  StreamResult result = new StreamResult(outputSW);
  transformer.transform(source, result);
 
  return result.getWriter().toString();
 
} // end of String RemoveNamespaces(String xmlinp)

Or if you include the stylesheet as a separate file in a war file and read the stylesheet file from your java code:

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.xml.sax.SAXException;
import org.w3c.dom.Document;
 
String RemoveNamespaces(String xmlinp) 
  throws ParserConfigurationException, SAXException, IOException,
           TransformerConfigurationException, TransformerException
{
  Document document;
  final String xslpath = "META-INF/transformer.xsl";
  InputStream xslstylesheet = 
    Thread.currentThread().getContextClassLoader().getResourceAsStream(xslpath);
  if (xslstylesheet==null) 
    myLogger.error("RemoveNamespaces: could not find stylesheet, with name= " +xslpath);
 
  DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
  factory.setNamespaceAware(true);
 
  DocumentBuilder builder = factory.newDocumentBuilder();
  StringReader xmlSR = new StringReader(xmlinp);
  InputSource xmlIS = new InputSource(xmlSR);
  document = builder.parse(xmlIS);
 
  // Use a Transformer for output
  TransformerFactory tFactory = TransformerFactory.newInstance();
  StreamSource stylesource = new StreamSource(xslstylesheet);
  Transformer transformer = tFactory.newTransformer(stylesource);
 
  DOMSource source = new DOMSource(document);
  StringWriter outputSW = new StringWriter();
  StreamResult result = new StreamResult(outputSW);
  transformer.transform(source, result);
 
  return result.getWriter().toString();
 
} // end of String RemoveNamespaces(String xmlinp)