Business Intelligence Happiness

Oracle and SAP are two of the three leading BI vendors in the BI marketplace today. With Oracle just released OBIEE 11.g, and SAP is not far behind with BI 4.0 Both platforms are robust, enterprise grade, top of the line BI suites, and you could really not go wrong with either one. With that said, they have their strengths and weaknesses. OBIEE admin tool provides an exceptional way to manage semantic models and meta-data about your reporting environment. SAPs Xcelsius (SAP BusinessObjects Dashboards in 4.0) is an outstanding data visualization tool. Well, what if you could combine the power of OBIEE with the flexibility of the Xcelsius presentation…? Here’s how I did just that.
Xcelsius can connect to XML feeds very easily through Excel XML Maps. So all I needed to do was to figure out a way to get an OBIEE Answers report data as an XML stream. Fortunately, OBIEE has robust web services support that can be leveraged to accomplish exactly that.
First, I installed Tomcat on my OBIEE 10.1.3 environment. This version of OBIEE ships with OC4J, and it is running on my server, but I am not that familiar with it, and with deploying applications on it, so it was just as easy for me to install Apache Tomcat (I used Tomcat 6), which runs on port 8080 by default and used that for running my code.
Next, I spent most of the time figuring out which libraries I needed to add to Tomcat in order to instantiate and use the OBIEE web services classes. After a lot of experimentation, I found all I needed is packed into a single JAR file, sawsoapstubs.jar, this is of course not documented anywhere. Once I had a Tomcat server running with the correct lib, the rest was a piece of cake.
Of course, more can be done to integrate security and generalize the query access as needed, but the fundamentals are laid here.
So, here is what I did, and what you would need to do if you wanted to reproduce this in your OBIEE environment and produce stunning Xcelsius dashboards as part of your OBIEE implementation:
1.  Find sawsoapstubs.jar in your OBIEE installation (..OracleBIwebbinsawrepaj) and copy it to Tomcat lib directory

2.  Create a new web directory in Tomcat (I called mine xcelsius)

3.  Login to OBIEE and create an Answers report that contains all the data you want for your Xcelsius dashboard. Then, go to the report Advanced tab and copy the OBIEE SQL Issued

4.  Open the obiee_xlf_data_conn.jsp file (link to all example files is at the bottom of this post, also the obiee_xlf_data_conn.jsp code is below) and edit the file to reflect your host name, OBIEE username/pwd to use, and edit the query to be the one copied in the prior step. Access the edited .jsp file via a web browser (http://localhost:8080/xcelsius/obiee_xlf_data_conn1.jsp) and make sure you are getting results
obiee_xlf_data_conn.jsp:

<%@ page import=”java.net.URL, com.siebel.analytics.web.soap.v5.*, com.siebel.analytics.web.soap.*” %>
<%

try {

 SAWSessionServiceLocator awsessionservicelocator = new SAWSessionServiceLocator();
 XmlViewServiceLocator  xmlViewServiceLocator = new XmlViewServiceLocator();
 SAWSessionServiceSoap m_Session;
 URL url = new URL(“http://localhost:9704/analytics/saw.dll?SoapImpl=nQSessionService”);
 m_Session = awsessionservicelocator.getSAWSessionServiceSoap(url);

      XmlViewServiceSoap xmlService = xmlViewServiceLocator.getXmlViewServiceSoap(
      new URL(“http://localhost:9704/analytics/saw.dll?SoapImpl=xmlViewService”));
   String m_sessionID = m_Session.logon(“Administrator”, “Administrator”);
   String query = “SELECT Products.Brand saw_0, Periods.”Year” saw_1, “Sales Measures”.Dollars saw_2, “Sales Measures”.Units saw_3 FROM Paint ORDER BY saw_0, saw_1″;
   QueryResults results = xmlService.executeSQLQuery(query, XMLQueryOutputFormat.fromString(“SAWRowsetData”), new XMLQueryExecutionOptions(), m_sessionID);
   String xmlResult = results.getRowset();
   out.println(xmlResult);
   m_Session.logoff(m_sessionID);

  } catch (Exception e) {
   out.println(e);
   e.printStackTrace();

  } 

%>

5.  Now, open Excel and add a new XML map from the jsp url (make sure you refresh the data at least once from Excel. You can delete it after the refresh, to make sure you are getting the data from the server, but I had issues with excel 2003 maps not working unless I did this initial refresh inside excel). If you are running this on the obiee server and created an Xcelsius directory as did I in Tomcat, the XML Map location will be http://localhost:8080/xcelsius/obiee_xlf_data_conn.jsp

6.  Import the Excel file into excel, model as usual and add the xml map data connection

7.  Export your html/swf from Xceslius, and reference their location using an embedded content component in OBIEE Dashboards

And… voila. OBIEE Never looked better! You can download the sample files here