Business Intelligence – Oracle

Archive for January 12th, 2009

Hyperion Essbase – Using Java APIs – Part 1 – Viewing Outline Members

Posted by Venkatakrishnan J on January 12, 2009

In a prior entry here, i had briefly walked through my observations on the BI EE and Essbase Connectivity. There i had noted that BI EE uses the C-API of Essbase instead of XMLA API through the provider services. I wanted to further validate this by means of testing the APIs directly. To begin with, i would be starting with the Java APIs. The Java APIs can be executed in 2 modes, they are

1. Embedded Mode – In this mode, the API jar files & the client application should reside on the same machine as the Essbase Server.
2. Using Provider Services – In this mode, the API jar files can reside in any machine. The jar files can connect to the Essbase Server through the Provider Services. The client machine does not require provider services to be installed

In our testing, i would be using the Provider Services option. Also, we shall be using JDeveloper 11.1 (production release) to test the Java API. The entire Java API is divided into 6 main packages. They are

1. com.essbase.api.base – This package contains methods/classes/interfaces which form the fundamental core of the APIs. They provide the necessary object types to load/query data into/from essbase.
2. com.essbase.api.dataquery – This package contains methods/classes/interfaces which can be used for querying from Essbase.
3. com.essbase.api.datasource – This package contains methods/classes/interfaces which provide information on calculation scripts, security filters etc.
4. com.essbase.api.domain – This package contains information on the ESSBASE_SERVER, Users, Groups etc
5. com.essbase.api.metadata – This package can be used for querying/modifying the outline of any database within a specific Essbase server
6. com.essbase.api.session – This package is used for maintaining stateful and stateless transactions. Typically used for logging in and logging out of Essbase.

In our example today, we shall start with a simple Java Class which would be used for displaying all the Outline Members within the Sample->Basic cube. This is based on an sample class provided as an example when provider services is installed. The entire Java class file would be divided into 4 different methods.

1. Login Details Retrieval Method – This would contain the login credentials for the Essbase Server.
2. Member Retrieval Method – This would be used for retrieving all the members within the outline
3. Member Display Helper Method – This would be used primarily for display purposes (with proper indentation).
4. Main method – Used for execution

The entire sourcecode for this is provided below

package EssbaseJAPI;

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
//import com.essbase.api.dataquery.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;

public class OutlineListing {
private static String s_userName = “admin”;
private static String s_password = “password”;

private static String s_olapSvrName = “localhost”;
private static String s_provider = “http://localhost:13080/aps/JAPI”;

private static final int FAILURE_CODE = 1;

public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssOlapServer olapSvr = null;
try {

ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

IEssDomain dom
= ess.signOn(s_userName, s_password, false, null, s_provider);

olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
IEssCube cube = olapSvr.getApplication(“Sample”).getCube(“Basic”);

System.out.println(“\nOutline Viewing sample complete.”);
} catch (EssException x) {
System.err.println(“Error: ” + x.getMessage());
statusCode = FAILURE_CODE;
} finally {
try {
if (olapSvr != null && olapSvr.isConnected() == true)
} catch (EssException x) {
System.err.println(“Error: ” + x.getMessage());

try {
if (ess != null && ess.isSignedOn() == true)
} catch (EssException x) {
System.err.println(“Error: ” + x.getMessage());

if (statusCode == FAILURE_CODE) System.exit(FAILURE_CODE);

static void listOutlineMembers(IEssCube cube) throws EssException {
IEssCubeOutline otl = null;
try {
otl = cube.openOutline();
System.out.println(“Outline information\n——————-“);
System.out.println(“Count of enabled DTS members: ” + otl.getCountEnabledDTSMembers());

System.out.println(“\nListing all ouline members in cube ” +
cube.getApplication().getName() + “/” + cube.getName());
IEssIterator dims = otl.getDimensions();
for (int i = 0, cntTabs = 0; i < dims.getCount(); i++) { IEssDimension dim = (IEssDimension)dims.getAt(i); listOutlineMembers_helper(dim.getDimensionRootMember(), cntTabs); } otl.close(); otl = null; } catch (EssException x) { System.err.println("Error: " + x.getMessage()); } finally { if (otl != null) { try { otl.close(); } catch (EssException x) { System.err.println("Error: " + x.getMessage()); } } } } static void listOutlineMembers_helper(IEssMember mbr, int cntTabs) throws EssException { for (int i = 0; i < cntTabs; i++) System.out.print("\t"); System.out.println(mbr.getName()); boolean fetchAllProps = false; IEssIterator mbrs = mbr.getChildMembers(fetchAllProps); ++cntTabs; for (int i = 0; i < mbrs.getCount(); i++) listOutlineMembers_helper((IEssMember)mbrs.getAt(i), cntTabs); } static void acceptArgs(String[] args) throws EssException { s_userName = "admin"; s_password = "password"; s_olapSvrName = "localhost"; s_provider = "http://localhost:13080/aps/JAPI"; } } [/sourcecode] Now, lets execute the above method. This should give us all the outline members.

The above is pretty straightforward as all we are doing is extracing each member in the outline and displaying them. In the next blog entry we shall see, how i believe the C-API is being used by BI EE. I would demonstrate that through the Java API. We shall fire an MDX query through the Java API and compare the output of the query with the output of BI EE. This would hopefully throw more on the connectivity. I would also address how BI Publisher currently uses this Java API (yes BI Publisher uses Java API instead of the C-API like BI EE) to return the datasets.


Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 1 Comment »