Business Intelligence – Oracle

Hyperion Essbase and JAPI – Custom Applications using JDeveloper

Posted by Venkatakrishnan J on September 3, 2007

I had a question from one of the Hyperion users yesterday with regard to the open APIs of Essbase. The user wanted to know whether a Hyperion cube can be exposed to custom applications. In short, the customer wanted to know whether there is any Hyperion equivalent of Oracle OLAP API. I did some digging around and found that Hyperion Essbase has 3 different types of APIs. They are

1.   C API
2.   Microsoft Visual Basic API
3.   JAPI for Java

In this article, i would be showing how to use the JAPI from a client machine to access the analytic server data. To do this, i created a sample cube on the Essbase server. If you are new to Hyperion, you can get a short writeup of how to go about creating a cube in one of my previous blog entries here. My sample cube consisted of 2 dimensions. They are

1.   Times
         Levels:   Year->Quarter->Month->Day

2.   Products
         Levels:   Product Name

The OLAP Model and the Meta Outline are given below in the pictures.

         olapmodel1.jpg

         olapmetaoutline2.jpg

In order to use the JAPI in a client machine, one would have to download and install the analytic server runtime component (analytic server client). This client would give the necessary jars which we can include in our project. The entire JAPI of Hyperion Essbase is split into 6 main packages. They are

1.   com.essbase.api.base
2.   com.essbase.api.metadata
3.   com.essbase.api.dataquery
4.   com.essbase.api.datasource
5.   com.essbase.api.domain
6.   com.essbase.api.session

As the name suggests, each of these packages have their own importance. For example, com.essbase.api.session and com.essbase.api.base help in creating a session. These package consists of methods that expose the entire functionality of Essbase to end users. In our example, we would be using all these packages to get a value from one of the cells in the Essbase. To do this, i will give an overview of the steps that we would have to take.

1.   Establish an Essbase session by calling the signOn() method.

2.   Specify the Olap Server that you want to connect by using the getOlapServer() method.

3.   Connect to the Olap Server using the connect() method.

4.   Open the Cube (database in Essbase terminology) by specifying the application name and the database (use the openCubeView method)

5.   Perform member selection. Select the members that you would like to display.

6.   Get the cell value from the cube for the specified member(s).

The code to do this would look like this.

package essbasetest;

import java.lang.Object;
import com.essbase.api.base.*;
import com.essbase.api.datasource.*;
import com.essbase.api.dataquery.*;
import com.essbase.api.metadata.*;
import com.essbase.api.domain.*;
import com.essbase.api.session.IEssbase;

public class test1
{
public test1()
{

String olapServerName;
String userName = “analyticservices”;
String password = “welcome1”;
String providerURL = “http://localhost:13080/aps/JAPI”;
String analyticServerName = “incq065bb”;
String appName = “LevelTi”;
String dbName = “LevelTi”;

IEssbase ess = null;
IEssOlapServer olapSvr = null;
try
{
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom;
dom = ess.signOn(userName, password, false, null, providerURL);
//System.out.println(dom.PROP_COUNT_OLAP_SERVERS) ;
olapSvr = (IEssOlapServer)dom.getOlapServer(analyticServerName);
olapSvr.connect();
IEssCube cube = olapSvr.getApplication(appName).getCube(dbName);
System.out.println(cube.getApplicationName());
//getMemberFromCube(cube);

IEssCubeView cv = null;
cv = dom.openCubeView(“Cube Query”, analyticServerName, appName, dbName);

performMemberSelection(ess, cv);
performCubeViewOperation(ess, cv, “pivot”);

} catch (EssException ex){
System.err.println(“Error: ” + ex.getMessage());
}
}

static void performCubeViewOperation(IEssbase ess, IEssCubeView cv,
String opStr) throws EssException
{
IEssGridView grid = cv.getGridView();
grid.setSize(3, 5);
grid.setValue(0, 2, “Product”);
grid.setValue(0, 3, “Market”);
grid.setValue(1, 2, “Jan”); ;
grid.setValue(1, 3, “Feb”);
grid.setValue(1, 4, “Mar”);
grid.setValue(2, 0, “Actual”);
grid.setValue(2, 1, “Sales”);
IEssOperation op = null;

op = cv.createIEssOpPivot();
((IEssOpPivot)op).set(0, 3);

cv.performOperation(op);

int cntRows = grid.getCountRows(), cntCols = grid.getCountColumns();
System.out.print(“Query Results for the Operation: ” + opStr + “\n” +
“—————————————————–\n”);
for (int i = 0; i < cntRows; i++)
{
for (int j = 0; j < cntCols; j++)
System.out.print(grid.getValue(i, j) + “\t”);
System.out.println();
}
System.out.println(“\n”);
}

static void performMemberSelection(IEssbase ess, IEssCubeView cv)
throws EssException {
String fldSel = “<OutputType Binary <SelectMbrInfo (MemberName, MemberLevel, MemberGen, Consolidation, MemberFormula, MemberAlias, DimensionName, Expense, MemberNumber, DimensionNumber, ChildMemberName, ParentMemberName, PreviousMemberName, NextMemberName)”,
mbrSel = “@ichild(PRODUCTS), @ichild(TIMES)”;
IEssMember[] mbrs = cv.memberSelection(mbrSel, fldSel);
for (int i = 0; i < mbrs.length; i++) {
IEssMember mbr = mbrs[i];
System.out.println(“Name: ” + mbr.getName());
System.out.println(“Level: ” + mbr.getLevelNumber());
System.out.println(“Generation: ” + mbr.getGenerationNumber());
System.out.println(“Consolidation: ” + mbr.getConsolidationType());
System.out.println(“Formula: ” + mbr.getFormula());
System.out.println(“Dimension name: ” + mbr.getDimensionName());
System.out.println(“Child count: ” + mbr.getChildCount());
System.out.println(“Parent name: ” + mbr.getParentMemberName());
System.out.println(“Member number: ” + mbr.getMemberNumber());
System.out.println(“Dimension number: ” + mbr.getDimensionNumber());
}

mbrs = cv.memberSelection(“1999”, IEssMemberSelection.QUERY_TYPE_CHILDREN,
IEssMemberSelection.QUERY_OPTION_MEMBERSONLY, “1999”, “”, “”);
for (int i = 0; i < mbrs.length; i++) {
IEssMember mbr = mbrs[i];
System.out.println(“Name: ” + mbr.getName() +
“, Desc: ” + mbr.getDescription() +
“, Level Num: ” + mbr.getLevelNumber() +
“, Gen Num: ” + mbr.getGenerationNumber() +
“, Child count: ” + mbr.getChildCount() +
“, Dim Name: ” + mbr.getDimensionName() +
“, Dim Category: ” + mbr.getDimensionCategory().stringValue());
}
}

static void getMemberFromCube(IEssCube cube) throws EssException {
System.out.println
(“\nGetting a Member from Cube (Year): \n” +
“———————————“);
IEssMember mbr = cube.getMember(“1998”);
displayMemberProperties(mbr);

System.out.println
(“\nGetting a Member from Cube (Year): \n” +
“———————————“);
IEssDimension dim = cube.getDimension(“1998”);
IEssMember rootMbr = dim.getDimensionRootMember();
displayDimensionProperties(dim);
displayMemberProperties(rootMbr);

System.out.println
(“\nGetting a Dimension from Cube (Year): \n” +
“———————————“);
dim = cube.getDimension(“1999”);
displayDimensionProperties(dim);
}

static void displayMemberProperties(IEssMember mbr) throws EssException {
System.out.println(“\nDisplaying member properties…\n”);
System.out.println(“Name: ” + mbr.getName());
System.out.println(“Dimension Root Member: ” + mbr.isDimensionRootMember());
System.out.println(“Level Number: ” + mbr.getLevelNumber());
System.out.println(“Generation Number: ” + mbr.getGenerationNumber());
System.out.println(“Unary consolidation type: ” + mbr.getConsolidationType());
System.out.println(“Dimension Name: ” + mbr.getDimensionName());
System.out.println(“Parent member name: ” + mbr.getParentMemberName());
System.out.println(“Member number: ” + mbr.getMemberNumber());
System.out.println(“Dimension number: ” + mbr.getDimensionNumber());
System.out.println(“Next Sibling Member Name: ” + mbr.getNextSiblingMemberName());
System.out.println(“First Child Member Name: ” + mbr.getFirstChildMemberName());
System.out.println(“Previous Sibling Member Name: ” + mbr.getPreviousSiblingMemberName());

}

static void displayDimensionProperties(IEssDimension dim)
throws EssException {
System.out.println(“\nDisplaying dimension properties…\n”);
System.out.println(“Name: ” + dim.getName());
System.out.println(“Dimension number: ” + dim.getDimensionNumber());
System.out.println(“Dimension storage type: ” + dim.getStorageType());
System.out.println(“Dimension tag: ” + dim.getTag());
System.out.println(“Declared size: ” + dim.getDeclaredSize());
System.out.println(“Actual size: ” + dim.getActualSize());
System.out.println(“Attribute dimension data type: ” +
dim.getAttributeDimensionDataType());
}

public static void main(String[] args)
{
test1 a= new test1();
}
}

The output after executing the above package is shown below

         codeoutput.jpg

I have generated the above code based on an example that gets bundled along with the client. I would recommend everyone to go through that since it has a lot of examples and covers a lot of features. The above is just to give you an example and to show you the capabilities of Hyperion Essbase API. One can leverage these java classes in any application. It would be very interesting to see how Essbase gets integrated into Oracle BI stack and i believe it should not be much of a problem. Interesting times ahead indeed!!!

Advertisements

4 Responses to “Hyperion Essbase and JAPI – Custom Applications using JDeveloper”

  1. Swapan said

    Is there something similar to SQL access to Oracle OLAP cubes in Essbase?

  2. Venkatakrishnan J said

    I am afraid there is none so far for Essbase yet. But i believe the integration between OBI EE Server and Essbase is of highest priority and would be done some time in the near future.

  3. Valeriy V. Garnaga said

    Nice article 🙂
    Are you can to send the cell value to the cube for the specified member?

  4. Phaneendra A. said

    Nice one…
    can you please comment more on providerURL

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

 
%d bloggers like this: