Hyperion Essbase and JAPI – Custom Applications using JDeveloper

I found an interesting article on usage of JAVA APIs of Essbase. The following articles shows how to expose the Hyperion Essbase cubes to custom applications.Basically, 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. 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.

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 < j =" 0;">

static void performMemberSelection(IEssbase ess, IEssCubeView cv)
throws EssException {
String fldSel = “< mbr =" mbrs[i];">

mbrs = cv.memberSelection(”1999″, IEssMemberSelection.QUERY_TYPE_CHILDREN,
IEssMemberSelection.QUERY_OPTION_MEMBERSONLY, “1999″, “”, “”);
for (int i = 0; i < mbr =" mbrs[i];">

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

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!!!