Connect Xcelsius Dashboard to OBIEE

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

This entry was posted in OBIEE, Xcelsius and tagged , , , . Bookmark the permalink.

6 Responses to Connect Xcelsius Dashboard to OBIEE

  1. Rishi says:

    Hi Ron,
    Thanks for the script and the elaborate steps.I have followed all the steps accordingly but getting error like
    1)org.apache.jasper.JasperException: Unable to compile class for JSP:
    2)An error occurred at line: 10 in the jsp file: /NEW.jsp
    3)The type javax.xml.rpc.ServiceException cannot be resolved. It is indirectly referenced from required .class files
    4)The type org.apache.axis.client.Service cannot be resolved. It is indirectly referenced from required .class files
    5)The method getSAWSessionServiceSoap(URL) from the type SAWSessionServiceLocator refers to the missing type ServiceException
    6)The method getXmlViewServiceSoap(URL) from the type XmlViewServiceLocator refers to the missing type ServiceException.

    • Ron Keler says:

      Hi Rishi, sounds like you are missing some jar files in your webapp… The errors you are getting relate to classes missing, either you are using older versions of the jars, or they are missing.. Thanks – Ron

  2. gokul says:

    We have both OBIEE and Business Objects.This post is indeed of great help to me as i need to post Xcelsius Dashboard to one of their home grown portals but problem is with refreshing data on load and that i am not able to do with xcelsius alone and with the help of OBIEE now i can create a report and then i can link it to xcelsius.Good way to start working with multiple apps….

  3. Santosh says:

    I dont understand, i never saw any client having xcelsuis and OBIEE, very peculiar combination,
    because OBIEE has its own dashboard module, why would some one implement this, unless its a weird situation.

    • Ron Keler says:

      Hi Santosh, first of all, thank you for your comment. I certainly understand your question. You would not see different enterprise BI platforms in a small or medium sized company. However, large companies with revenues in billions and tens or hundreds of thousands of employees have this situation often. Different departments use different BI platforms, applications are implemented with OEM BI products embedded in them, and so on and so forth. I have personally run into OBIEE and BusinessObjects implemented in the same company by different departments, as well as Cognos and BusinessObjects. So, if you do happen to run into this situation, some “cross pollination” between the platforms will often be useful. In addition, even if you are looking at a pure OBIEE implementation, I have run into clients requesting to create levels of design and refinements that will be very difficult and expensive to achieve with OBIEE dashboards. Xcelsius provide good control of components and a rich library of “gadgets” that can help improve the overall platform usability. A standalone Xcelsius designer license runs at about $2,500, and if you can satisfy some unique requirements for this kind of small change in a large scale OBIEE implementation, I think your clients will bi happy ;).

Comments are closed.