Tableau On BO – Setting up live connection from SAP Webi report to Tableau

I recently received several questions about connecting Tableau to SAP BI (Business Objects) as a data source. Tableau already has direct connectivity to HANA, however many users out there are looking for ways to connect their existing webi (web intelligence) reports to Tableau. So, I set to look for some solutions to this problem and was able to develop two good solutions that are automated and allow users to leverage the investment they already made in setting up webi reports through Tableau in an automated fashion.
I started out by examining the data connections available in Tableau and looking for possible candidates I can use to connect to SAP BI and webi. Two options caught my attention immediately as viable connectivity options, since they are relatively open: the “Other Databases (ODBC)” connection and the “OData” connection.
My first course of investigation was around the ODBC option. I know that Crystal Reports for example has an ODBC driver that can connect to an XML feed. So, in theory, I should be able to create an XML feed off a BI service published from a webi report, and connect using this driver.
Trying to use the Crystal Reports driver was a bust. Technically, this would have worked, but the driver, produced by DataDirect is licensed for use with Crystal only and trying to use it with Tableau (or any other client) produce an error message stating the driver can only be used for Crystal. If you happen to have access to a DataDirect or other XML ODBC driver, you can convert the BI Service SOAP response into an XML feed (see jsp code below), and use that to build a DSN you can connect Tableau to. In my searches, I was only able to find commercially licensed XML ODBC drivers, so my second approach, writing my own OData producer became more relevant.
OData is a relatively new, but very popular, internet data exchange protocol and it defines ways to send and request information from a service. There are several implementations for it, and I ended up choosing the java odata4j framework for my experiment.
I started out by setting up the odata4j project in Eclipse and getting two examples I was interested in working: the XmlDataProducerExample which describes how to read an XML feed and expose it as an OData producer, and the ExampleProducerFactory example which demonstrates how to expose the OData producer in Tomcat. I ended up using the Jersey based XML example in my working prototype, but I would most certainly look to host this in Tomcat directly in a real world situation.
So, using the odata4j examples, I created a java application and added a class to read my own XML feed instead of the provided one. I also removed the portions of the OData framework that would allow users to interact with the feed in terms of making changes to the data (not needed in our scenario, which is reporting only). So, my main class connecting the XML feed looks like so:
package org.odata4j.examples.producer;
import static org.odata4j.examples.JaxRsImplementation.JERSEY;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.events.XMLEvent;
import org.odata4j.core.OEntities;
import org.odata4j.core.OEntity;
import org.odata4j.core.OEntityId;
import org.odata4j.core.OEntityKey;
import org.odata4j.core.OExtension;
import org.odata4j.core.OFunctionParameter;
import org.odata4j.core.OProperties;
import org.odata4j.core.OProperty;
import org.odata4j.core.Throwables;
import org.odata4j.edm.EdmDataServices;
import org.odata4j.edm.EdmEntityContainer;
import org.odata4j.edm.EdmEntitySet;
import org.odata4j.edm.EdmEntityType;
import org.odata4j.edm.EdmFunctionImport;
import org.odata4j.edm.EdmProperty;
import org.odata4j.edm.EdmSchema;
import org.odata4j.edm.EdmSimpleType;
import org.odata4j.examples.AbstractExample;
import org.odata4j.examples.ODataServerFactory;
import org.odata4j.examples.producer.jpa.northwind.Customers;
import org.odata4j.exceptions.NotImplementedException;
import org.odata4j.producer.BaseResponse;
import org.odata4j.producer.CountResponse;
import org.odata4j.producer.EntitiesResponse;
import org.odata4j.producer.EntityIdResponse;
import org.odata4j.producer.EntityQueryInfo;
import org.odata4j.producer.EntityResponse;
import org.odata4j.producer.ODataProducer;
import org.odata4j.producer.QueryInfo;
import org.odata4j.producer.Responses;
import org.odata4j.producer.edm.MetadataProducer;
import org.odata4j.producer.resources.DefaultODataProducerProvider;
/**
* This example shows how to expose xml data as an atom feed.
*/
public class XmlDataProducerExampleRon2 extends AbstractExample {
public static final String endpointUri = "http://localhost:8010/XmlDataProducerExampleRon2.svc";
public static void main(String[] args) {
XmlDataProducerExampleRon2 example = new XmlDataProducerExampleRon2();
example.run(args);
}
private void run(String[] args) {
System.out.println("Please direct your browser to " + endpointUri + "/Customers");
// register the producer as the static instance, then launch the http server
DefaultODataProducerProvider.setInstance(new XmlDataProducer());
new ODataServerFactory(JERSEY).hostODataServer(endpointUri);
}
@XmlRootElement
public class CustomersList {
@XmlElement
Customers[] customers;
}
/**
* Sample ODataProducer for providing xml data as an atom feed.
*/
public class XmlDataProducer implements ODataProducer {
private final EdmDataServices metadata;
private XMLInputFactory xmlInputFactory;
public XmlDataProducer() {
// build the metadata here hardcoded as example
// one would probably generate it from xsd schema or something else
String namespace = "XmlExample";
Listproperties = new ArrayList();
properties.add(EdmProperty.newBuilder("state").setType(EdmSimpleType.STRING));
properties.add(EdmProperty.newBuilder("lines").setType(EdmSimpleType.STRING));
properties.add(EdmProperty.newBuilder("manager").setType(EdmSimpleType.STRING));
properties.add(EdmProperty.newBuilder("owned").setType(EdmSimpleType.STRING));
properties.add(EdmProperty.newBuilder("revenue").setType(EdmSimpleType.DOUBLE));
properties.add(EdmProperty.newBuilder("margin").setType(EdmSimpleType.DOUBLE));
properties.add(EdmProperty.newBuilder("recordID").setType(EdmSimpleType.DOUBLE));
ListentityTypes = new ArrayList();
EdmEntityType.Builder type = EdmEntityType.newBuilder().setNamespace(namespace).setName("Customers").addKeys("recordID").addProperties(properties);
entityTypes.add(type);
ListentitySets = new ArrayList();
entitySets.add(EdmEntitySet.newBuilder().setName("Customers").setEntityType(type));
EdmEntityContainer.Builder container = EdmEntityContainer.newBuilder().setName(namespace + "Entities").setIsDefault(true).addEntitySets(entitySets);
EdmSchema.Builder modelSchema = EdmSchema.newBuilder().setNamespace(namespace + "Model").addEntityTypes(entityTypes);
EdmSchema.Builder containerSchema = EdmSchema.newBuilder().setNamespace(namespace + "Container").addEntityContainers(container);
metadata = EdmDataServices.newBuilder().addSchemas(containerSchema, modelSchema).build();
xmlInputFactory = XMLInputFactory.newInstance();
}
@Override
public EdmDataServices getMetadata() {
return this.metadata;
}
/**
* Returns OEntities build from xml data. In the real world the xml data
* could be filtered using the provided queryInfo.filter.
* The real implementation should also respect
* queryInfo.top and queryInfo.skip.
*/
@Override
public EntitiesResponse getEntities(String entitySetName, QueryInfo queryInfo) {
EdmEntitySet ees = getMetadata().getEdmEntitySet(entitySetName);
URL url =null;
URLConnection urlConnection = null;
InputStream is=null;
try {
url = new URL("http://myserver.com/mycontext/getSource.jsp");
urlConnection = url.openConnection();
is = new BufferedInputStream(urlConnection.getInputStream());
} catch (MalformedURLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
XMLEventReader reader = null;
try {
// transform the xml to OEntities with OProperties.
// links are omitted for simplicity
reader = xmlInputFactory.createXMLEventReader(is);
Listentities = new ArrayList();
List> properties = new ArrayList>();
boolean inCustomer = false;
String id = null;
String data = null;
while (reader.hasNext()) {
XMLEvent event = reader.nextEvent();
if (event.isStartElement()) {
if ("customers".equals(event.asStartElement().getName().getLocalPart())) {
inCustomer = true;
}
} else if (event.isEndElement()) {
String name = event.asEndElement().getName().getLocalPart();
if ("customers".equals(name)) {
entities.add(OEntities.create(ees, OEntityKey.create(id), properties, null));
properties = new ArrayList>();
inCustomer = false;
} else if (inCustomer) {
if ("recordID".equals(name)) {
id = data;
}
properties.add(OProperties.string(name, data));
}
} else if (event.isCharacters()) {
data = event.asCharacters().getData();
}
}
return Responses.entities(entities, ees, null, null);
} catch (XMLStreamException ex) {
throw Throwables.propagate(ex);
} finally {
try {
if (reader != null) reader.close();
} catch (XMLStreamException ignore) {}
try {
is.close();
} catch (IOException ignore) {}
}
}
@Override
public CountResponse getEntitiesCount(String entitySetName, QueryInfo queryInfo) {
throw new NotImplementedException();
}
@Override
public EntitiesResponse getNavProperty(String entitySetName, OEntityKey entityKey, String navProp, QueryInfo queryInfo) {
throw new NotImplementedException();
}
@Override
public CountResponse getNavPropertyCount(String entitySetName, OEntityKey entityKey, String navProp, QueryInfo queryInfo) {
throw new NotImplementedException();
}
@Override
public void close() {}
@Override
public EntityResponse createEntity(String entitySetName, OEntity entity) {
throw new NotImplementedException();
}
@Override
public EntityResponse createEntity(String entitySetName, OEntityKey entityKey, String navProp, OEntity entity) {
throw new NotImplementedException();
}
@Override
public void deleteEntity(String entitySetName, OEntityKey entityKey) {
throw new NotImplementedException();
}
@Override
public void mergeEntity(String entitySetName, OEntity entity) {
throw new NotImplementedException();
}
@Override
public void updateEntity(String entitySetName, OEntity entity) {
throw new NotImplementedException();
}
@Override
public EntityResponse getEntity(String entitySetName, OEntityKey entityKey, EntityQueryInfo queryInfo) {
throw new NotImplementedException();
}
@Override
public EntityIdResponse getLinks(OEntityId sourceEntity, String targetNavProp) {
throw new NotImplementedException();
}
@Override
public void createLink(OEntityId sourceEntity, String targetNavProp, OEntityId targetEntity) {
throw new NotImplementedException();
}
@Override
public void updateLink(OEntityId sourceEntity, String targetNavProp, OEntityKey oldTargetEntityKey, OEntityId newTargetEntity) {
throw new NotImplementedException();
}
@Override
public void deleteLink(OEntityId sourceEntity, String targetNavProp, OEntityKey targetEntityKey) {
throw new NotImplementedException();
}
@Override
public BaseResponse callFunction(EdmFunctionImport name, Map params, QueryInfo queryInfo) {
throw new NotImplementedException();
}
@Override
public MetadataProducer getMetadataProducer() {
return null;
}
@Override
public > TExtension findExtension(Class clazz) {
return null;
}
}
}

Notice the line url = new URL(“http://myserver.com/mycontext/getSource.jsp”);

This is the .jsp (can be a servlet, or our Bogoboards Gateway) that converts a BI Service into a simple XML feed. This .jsp looks like so:

<%@ page language="java" contentType="text/xml; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="com.crystaldecisions.sdk.framework.IEnterpriseSession,
com.crystaldecisions.sdk.framework.CrystalEnterprise,
com.crystaldecisions.sdk.exception.SDKException, com.crystaldecisions.sdk.framework.ISessionMgr,
com.crystaldecisions.sdk.occa.security.ILogonTokenMgr, com.businessobjects.bcm.*,tableau_source_rk_pkg.*,
javax.xml.rpc.Service,java.net.URL, java.util.*, java.text.NumberFormat,
java.math.RoundingMode" %>
<%@ page trimDirectiveWhitespaces="true"%>
<%
try{
//in this example, hard coded BO account, in real life will integrate auth or obtain from form
String username = "myusername";
String password = "mypwd";
String token = "";
//Authenticate user and get BO session
IEnterpriseSession enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, "cmsname:6400", "secEnterprise");
String serSess = enterpriseSession.getSerializedSession();
ILogonTokenMgr tokenMgr = enterpriseSession.getLogonTokenMgr();
token = tokenMgr.getDefaultToken();
session.setAttribute("serSess",serSess);
session.setAttribute("EnterpriseSession",enterpriseSession);
session.setAttribute("token",token);
}
catch(Exception e){
System.out.println(new java.util.Date()+": error on .jsp: "+e);
response.sendRedirect("out.html");
}
%>
<%
String str = "";
String rowStr = null;
NumberFormat formatter = NumberFormat.getCurrencyInstance(java.util.Locale.US);
formatter.setRoundingMode(RoundingMode.HALF_EVEN);
formatter.setMaximumFractionDigits(0);
URL endpoint = new URL("http://server.com/dswsbobje/qaawsservices/queryasaservice/biws?cuid=SOMEBISERVICECUID&authType=secEnterprise&locale=en_US&timeout=60&ConvertAnyType=true");
BIServicesSoapStub stub = new BIServicesSoapStub(endpoint,null);
try{
GetReportBlock_tableau_source_rk parameters = newGetReportBlock_tableau_source_rk();
parameters.setGetFromLatestDocumentInstance(true);
//In this example, reading report cache data, can set setRefresh to true to hit the DB each time
//parameters.setRefresh(true);
parameters.setResetState(true);
QaaWSHeader request_header = new QaaWSHeader();
request_header.setSerializedSession((String)session.getAttribute("serSess"));
//Call the soap service and get the response
GetReportBlock_tableau_source_rkResponse res = stub.getReportBlock_tableau_source_rk(parameters, request_header);
//Iterate through the response and parse it out to xml format
//In this example, hard coded parsing, can use the header to parse out dynamically
java.lang.Object[][] table = res.getTable();
for (int i=0;i<table.length;i++) {
for (int x=0;x<table[i].length;x++) {
if(x==table[i].length-1) {
rowStr = rowStr+"<customers><state>"+(String)table[i][1]+"</state><lines>"+
(String)table[i][2]+"</lines><manager>"+(String)table[i][3]+"</manager><owned>"+(String)table[i][4]+"</owned><revenue>"+
(Double)table[i][5]+"</revenue><margin>"+(Double)table[i][6]+"</margin><recordID>"+(Double) table[i][0]+"</recordID></customers>";}
}
}
}
catch(Exception e) {
System.out.println(new java.util.Date()+": Error in get and display data: " + e);
}
str = "<?xml version='1.0' encoding='utf-8'?><customersList>"+
rowStr+
"</customersList>";
out.println(str); //output the xml
%>

These are the two main components of the solution. The rest is more setup, less code. So now, my java app calls a BI Service that is being converted to XML and exposes it as an OData producer, so ANY client that understands OData can read it. If you like to try out this OData service, you can point your Tableau to: http://bogoboards.com/gmap/jerseyproxy.jsp

The OData producer for this example is exposed through a proxy on Tomcat. The data might look familiar, it’s a webi report based on eFashion universe, limited to a couple of states and one line of clothing, to keep things light and simple for this example…

So, armed with this URL, I was now able to connect Tableau to my report based OData producer, make changes in webi to modify the data, and refresh the Tableau dashboard to automatically update the dashboard! Here are some screen shots of the process I used to test out the live refresh functionality:

1. Connect to the Odata Producer from Tableau

2

2. Data from OData producer via webi is available in Tableau to work with now

3. Setup a simple chart

4. Now, go to the webi report that is exposed via the OData producer

5. Open the webi report

6. Modify the query to add more data

7. Save the modified webi

8. Now, switch back to Tableau and refresh the OData producer data source

9. Observe the new data flows through

 

 

This entry was posted in BI At Large, BusinessObjects 4.0, Data visualization, Web Intelligence and tagged , , , , . Bookmark the permalink.

11 Responses to Tableau On BO – Setting up live connection from SAP Webi report to Tableau

  1. SK says:

    Hi,

    Is that possible to connect Business Objects UNV through Tableau Odata Connector?

  2. Kiran says:

    Hi , I would like to see the graphs in Tableau in SAP BI WEBI reports. Is there any possible way we can so that?

    • biha8964 says:

      That is an odd use case, but possible. You can set a webi cell to be html format, and then use the Tableau dashboard embed code in it. You will need to handle authentication as well somehow, so might need to embed Tableau in a different webpage to do some custom handling and then embed that page in your webi. There are quite a few examples on this blog that talk about embedding external html in webi (mashups), like google maps etc.

  3. Jake Tully says:

    Hey Ron,

    I know I’m a little late to the party, but great article. I’m trying to recreate the results on my end, and I’m just running into a few questions. I’ve created a similar XmlDataProducerExample java file as you had posted above, but when I try to use the servlet-api.jar file to compile it into a servlet, I’m getting a few syntax errors.

    However, compiling the odata4j example, I get over 100 errors! So I’m not sure if I’m missing a step, or if I just need to clean up a few of the lines in your code posted above. I was wondering if you still had your Java file, and if you could link it to this post? If not, maybe you could spare some time to help me better understand how you’re hosting this java file in Tomcat. You had mentioned something about using a servlet or a gateway, I was trying the servlet route.

    Thanks in advance, and again, very impressive and interesting work!

    • biha8964 says:

      Jake, since you are getting so many errors, i would guess you are missing some dependencies in your project. Youcan look into the specific errors (i assume you are using eclipse), and find out what classes are missing, this should indicate which jars you need to add to your build path. If you are looking for more help in the way of professional services, please feel free to email me and we can discuss further.. There might be other approaches that would work for what you are looking to accomplish in a simpler fashion. While i published this post as a working example of a concept, real world applications can merit different approaches as well.. Thanks – Ron

  4. Brenda says:

    Would using Alteryx be a middle man solution to getting data from SAP BO or Webi?

    • Ron Keler says:

      Hi Brenda, the concept would be similar, in the sense that you will need to write an alteryx function to connect to the BO soap service and then store the data, so you can connect to it from Tableau. You could probably write a proxy program to handle the authentication pece for BO and then parse the xml to whatever format would be easi to get into Tableau via alteryx. In this scenario, you could skip the need to write your own odata provider and use alterix as the source once you got through getting the data into it from BO..

  5. howard morgenstern says:

    another option is to use the TDE API. If your code can convert the BI Service SOAP response into an XML feed then you could write a script that parses the xml into a tableau extract.

    • Ron Keler says:

      Thank you Howard, this sounds interesting.. it would be much simpler to spit out an xml output from the BI service then converting it to oData… Can you elaborate..?

  6. Ryan Goodman says:

    Did you figure out the security for this one? Those BI services expose username and password as clear text which had always been one of the reasons we couldn’t use BI web services for other third party tools in production.

    This is clever work! Thanks for sharing with community.

    • Ron Keler says:

      Thanks Ryan, yes the security is handled via a token. So to invoke the BI service i first had to obtain a token. You can obtain a BO session token in a number of different ways, in my example, i simply use a back office enterprise account. So, no user info is ever being sent here, it’s basically using the same authentication/authorization models already in place in BO.

Comments are closed.