Business Intelligence – Oracle

Oracle BI EE 10.1.3.4.1 – Running Hyperion Essbase Data Loads/Calculations from BI EE – Using JAPI and JSPs

Posted by Venkatakrishnan J on April 26, 2009

As consultants one of the major hurdles that we would face with customers on a daily basis is the fact that how much ever features are available in any BI tool, almost upto 10 to 30% of the requirements(especially on big implementations) require some kind of custom work(sometimes this might even go upto 60% depending on the project). For example, last week i had a customer who basically wanted to know of a method for kick starting essbase allocation/calculations/data loads directly from BI EE. Now that they are using BI EE/Essbase connectivity, they wanted to provide the capability to some users to kick start the calculations directly from BI EE. Unfortunately, currently there is no out of the box way of doing this. But with some amount of work, the integration can be achieved. Today, lets look at how to go about achieving this integration. This would serve as a pre-cursor to another blog series wherein i would walk you through some complex integration techniques between BI EE and Essbase.

The basic aim is to have a link in BI EE, clicking on which should run a calculation in Essbase and then refresh a report to show the report with the new calculated data. To do this, the first step is to create a simple JSP page which would be used as a placeholder for firing the Essbase calculation. This JSP page would in turn refer the JAPI of Essbase and fire the calculation. In my case, i have used JDeveloper 10.1.3.2 to create the JSP page.

In order to create the JSP page, create a sample project and package called AllocEssbase.

       

Create a custom JSP page within this application. Include the Essbase jar files Ess_japi.jar and Ess_es_server.jar (these are the 2 main jar files that contribute to Essbase JAPI) in the project classpath.

       

Now, create the JSP using the code below. The code basically executes a calculation script CalcAll.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"<a href="http://www.w3.org/TR/html4/loose.dtd">http://www.w3.org/TR/html4/loose.dtd</a>">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.Map.Entry" %>
<%@ page import="java.util.jar.Attributes" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="com.essbase.api.base.*" %>
<%@ page import="com.essbase.api.dataquery.*" %>
<%@ page import="com.essbase.api.session.*" %>
<%@ page import="com.essbase.api.datasource.*" %>
<%@ page import="com.essbase.api.domain.*" %>
<%@ page import="com.essbase.api.metadata.*" %>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
    <title>AllocEssbase</title>
  </head>
    <body>
  <font size="5"><%="Essbase Allocation Process Started" %></font>
<%
        String s_userName = "admin";
        String s_password = "password";
        String s_olapSvrName = "localhost";
        String s_provider = "<a href="http://localhost:13080/aps/JAPI">http://localhost:13080/aps/JAPI</a>";
       
        try
        {       
        IEssbase ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
        IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
        IEssOlapServer olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
        olapSvr.connect();
       
        IEssCube cube = olapSvr.getApplication("Global").getCube("Global");
        String maxLstat = "import database 'Global'.'Global' data connect as 'global' identified by 'global' using server rules_file 'UnitsLd' on error abort;";
        //cube.loadData(IEssOlapFileObject.TYPE_RULES,"UnitsLd",0,"SH",false,"global","global");
        cube.calculate(false,"CalcAll");
        //cube.beginDataload("UnitsLd",IEssOlapFileObject.TYPE_RULES,"global","global",0);
        //IEssMaxlSession maxLsess = (IEssMaxlSession)dom.getOlapServer(s_olapSvrName);
        //maxLsess.execute(maxLstat);
        }catch (EssException x){
            System.out.println("ERROR: " + x.getMessage());
        }
%>
               
  <font size="5"><%="Essbase Allocation Process Ended" %></font>
        </body>
</html>
<%
    response.sendRedirect("<a href="http://localhost:9704/analytics">http://localhost:9704/analytics</a>");
%>

       

As you see this is a very basic JSP which runs a calculation script. You can use the same for doing data loads or just about anything in Essbase. As a next step execute this JSP from JDeveloper itself to be sure that this JSP works.

       

This JSP would then have to deployed on the OC4J (or app server) of BI EE. Since this JSP uses a couple of external jar files, the only way to deploy this is by bundling it as a WAR file. In order to do that, create a new deployment profile and include both the jars

       

       

Deploy the war file in the OC4J of BI EE.

       

       

Now you should be able to access the JSP using the url given below


http://localhost:9704/AllocEssbase/AllocEssbase.jsp

Embed this URL in the BI EE dashboards.

       

This should get you started. Now there are lots of different applications that one can put this into use. Like Planning load start, ODI integration, BI Scheduler integration etc. I would cover all of these use cases in detail in the coming blog entries.

About these ads

One Response to “Oracle BI EE 10.1.3.4.1 – Running Hyperion Essbase Data Loads/Calculations from BI EE – Using JAPI and JSPs”

  1. [...] by Venkatakrishnan J on May 21, 2009 In the blog entry here, i had shown you how to run an essbase calculation script from the BI EE dashboards. To make it [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: