Hyperion Essbase and JAPI - Custom Applications using JDeveloper

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.

         

         

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

         

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

Creating a Hyperion Essbase(Analytic Server) Cube

In this example, I would be using Sales Warehouse data that comes in Oracle SH schema. It would make a lot of sense if one creates a cube out of transactional data. But for the sake of simplicity I would be focussing on the cube creation process with a simple warehouse schema.

Before you start, ensure that you have all the components (Analytic Server and Analytic Integration Services up and running)

Open up the Analytic Integration Services Console. As soon as you start, it would prompt you to create an OLAP catalog.

 

Enter connection details for a schema that you generally use for storing metadata (recommended to create a separate schema in order to store the metadata). Once that is done it would automatically take us to the following screen

 

 

On to the left is the OLAP metadata connection. Enter the details that you entered in the previous screen. On to the right is the Analytic Administration Services connection. Enter those details here. The next step is to create an OLAP Model.

 

Next is the screen to establish a connection to the data source. As I said, I would be connecting to the Sales Warehouse schema.

 

 

 

There are two types of files that we would be dealing here. They are

ü      Olap Model

ü      MetaOutline

In Olap Model one can establish all the joins of the tables that they want in their schema. It requires a fact table and their corresponding dimensions. Hierarchies should be specified here.

 

 

Metaoutline is a file type wherein one can create the dimensions and specify the measures. Once the Olap model is created the next step is to design the Metaoutline.

 

 

The next step is to load the data.

Then one can use the excel add in to view the data.

This is one of the ways to create a cube. One can also create the cube directly from the Analytic Administration Services. Also, I have not gone into lots of other features that would make essbase stand part. This was just to show an overview for people would like to start with Essbase but do not know where to begin from.

Hyperion Essbase Analytic Server - First Impressions

Hyperion Essbase (as it was formerly called) is one of the market leaders in the MOLAP space. Prior to the acquisition there have been many cases wherein Oracle competed head on head with Hyperion Essbase using Oracle OLAP. Both of these products have their own advantages. In this article let’s try to understand the working of Hyperion Essbase vis-à-vis Oracle OLAP.

Hyperion System 9 BI was the latest and last offering from Hyperion before the acquisition. There have been quite a few name changes for many of the well-known products like Essbase, Brio etc. Also, there were quite a few changes in the architecture front. System 9 is based on a 3-tier architecture. In System 9 Hyperion Essbase has been re-branded as the analytic server. There are 4 components that form the entire MOLAP engine. These components are as follows

1. Analytic Services
2. Analytic Administration Services
3. Analytic Integration Services
4. Analytic Provider Services

One must realize that even though Essbase (I will stick with this name to refer to all the above 4) is a MOLAP engine, it’s considered as a database in itself. It’s more like a multi-dimensional database. It is one of the fundamental components of Hyperion Planning and Financial Management.

Following would be installation sequence that one would have to follow to get this up and running.

1.                  Install License Server

2.                  Install Shared Services

3.                  Install Analytic Services

4.                  Install Analytic Administration Services

5.                  Install Analytic Integration Services

6.                  Install Analytic Provider Services

7.                  Install the Excel Add in which is under BI + Analytic Services Client

After the installation one would have to startup the analytic server and the integration server. Following is the architecture diagram for the analytic server

 

Let us try to understand the 4 components in detail.

Analytic Server:        

This is where the MOLAP cube is stored. The server acts as a shared resource handling all data storage, calculatioo, sorting etc. It also contains the Outlines (just assume that outline is a file that stores the dimension and measure specifications), rules (one can define rules for data load) etc.

            Analytic server has 2 kinds of storage

ü      Block Storage (Easy to use and implement but does not scale)

ü      Aggregate Storage (Has certain limitations but can scale)

 Analytic Administration Services:     

            Analytic Administration Services—the database and system administrators’ interface to Analytic Services—provides a single-point-of-access console to multiple Analytic Servers. Using Analytic Administration Services you can design, develop, maintain, and manage multiple Analytic Servers, applications, and databases. You can preview data from within the console, without having to open a client application such as Spreadsheet Add-in. You can also use custom Java plug-ins to leverage and extend key functionality.

 Analytic Integration Services: 

            This is a very important component wherein one designs the dimensions and fact tables if one wants to leverage different data sources like Oracle, DB2, SQL Server etc. It basically uses ODBC to connect to different data sources. It also provides a drill through feature wherein one can drill down from the database (MDB) to relational database.

 Analytic Provider Services:

            This is for enabling clustering of the database (essbase cube) across multiple machines.