#jdev

I know it has been while since the show but I wanted to make sure this got posted.  The plan for this lab was to build on S655 and S654 and for the end result to be a useable Essbase utility written with the Java API in JDeveloper. 

The lab was focused on the Outline API and was centered around the idea of creating an Outline Extractor.  The lab can be downloaded here.  This lab will get you started but here is a more complete code sample with XML output.

package com.oracle.essbase.japi;

  import com.essbase.api.base.EssException;

import com.essbase.api.base.IEssIterator;

import com.essbase.api.datasource.IEssCube;

import com.essbase.api.datasource.IEssOlapServer;

import com.essbase.api.domain.IEssDomain;

import com.essbase.api.metadata.IEssCubeOutline;

import com.essbase.api.metadata.IEssDimension;

import com.essbase.api.metadata.IEssGeneration;

import com.essbase.api.metadata.IEssLevel;

import com.essbase.api.metadata.IEssMember;

import com.essbase.api.session.IEssbase;

 import java.util.ResourceBundle;

 import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

 import javax.xml.parsers.ParserConfigurationException;

 import javax.xml.transform.OutputKeys;

import javax.xml.transform.Transformer;

import javax.xml.transform.TransformerException;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

 import javax.xml.transform.stream.StreamResult;

 import org.w3c.dom.DOMImplementation;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

 public class OutlineExtractor {

private static String s_userName = "";

private static String s_password = "";

private static String s_olapSvrName = "";

private static String s_provider = "";

private IEssOlapServer olapSvr = null;

private IEssbase ess = null;

private Document doc;

public OutlineExtractor() {

olapSvr = Connect();

}

  public static void main(String[] args) {

OutlineExtractor outlineExtractor = new OutlineExtractor();

outlineExtractor.ReadOutline("Sample","Basic", true);

outlineExtractor.Disconnect();

}

private IEssOlapServer Connect() {

readProperties();

try {

// Create JAPI instance.

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

// Sign On to the Provider

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

IEssOlapServer olapSvr = dom.getOlapServer(s_olapSvrName);

olapSvr.connect();

return olapSvr;

}

catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

Disconnect();

return null;

}

}

private void Disconnect(){

//Try to sign off

try {

if (olapSvr != null && olapSvr.isConnected() == true){

olapSvr.disconnect();

}

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

try {

if (ess != null && ess.isSignedOn() == true){

ess.signOff();

}

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

}

private static void readProperties() {

ResourceBundle settings = ResourceBundle.getBundle("Essbase");

  s_userName=settings.getString("Common.UserName");

s_password=settings.getString("Common.Password");

s_olapSvrName = settings.getString("Common.ServerName");

s_provider = settings.getString("Common.Provider");

}

public void ReadOutline(String ApplicationName, String DatabaseName, Boolean IncludeAttributes){

IEssCube cube;

try {

cube = olapSvr.getApplication(ApplicationName).getCube(DatabaseName);

readOutline(cube,DatabaseName,IncludeAttributes);

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

}

private void readOutline(IEssCube cube, String DatabaseName, Boolean IncludeAttributes) throws EssException {

IEssCubeOutline otl = null;

try {

otl = cube.openOutline();

IEssIterator dims = otl.getDimensions();

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder();

DOMImplementation impl = builder.getDOMImplementation();

  doc = impl.createDocument(null,null,null);

Element elementCube = doc.createElement("Cube");

elementCube.setAttribute("ApplicationName", cube.getApplicationName());

elementCube.setAttribute("DatabaseName", DatabaseName);

elementCube.setAttribute("CubeType", cube.getCubeType().stringValue());

doc.appendChild(elementCube);

for (int i = 0; i < dims.getCount(); i++) {

IEssDimension dim = (IEssDimension)dims.getAt(i);

if (IncludeAttributes) {

Element elementDim = doc.createElement("Dimension");

elementDim.setAttribute("DimensionName", dim.getName());

elementDim.setAttribute("Category", dim.getCategory().stringValue());

elementDim.setAttribute("StorageType", dim.getStorageType().stringValue());

elementCube.appendChild(elementDim);

getMembersIterator(dim.getDimensionRootMember(),elementDim);

}

else{

if(dim.getCategory() != IEssDimension.EEssDimensionCategory.ATTRIBUTE && dim.getCategory() != IEssDimension.EEssDimensionCategory.ATTRIBUTE_CALC){

Element elementDim = doc.createElement("Dimension");

elementDim.setAttribute("DimensionName", dim.getName());

elementDim.setAttribute("Category", dim.getCategory().stringValue());

elementDim.setAttribute("StorageType", dim.getStorageType().stringValue());

elementCube.appendChild(elementDim);

getMembersIterator(dim.getDimensionRootMember(),elementDim);

}

}

}

otl.close();

otl = null;

// transform the Document into a String

DOMSource domSource = new DOMSource(doc);

TransformerFactory tf = TransformerFactory.newInstance();

Transformer transformer = tf.newTransformer();

//transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");

transformer.setOutputProperty(OutputKeys.METHOD, "xml");

transformer.setOutputProperty(OutputKeys.ENCODING,"ISO-8859-1");

transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");

transformer.setOutputProperty(OutputKeys.INDENT, "yes");

java.io.StringWriter sw = new java.io.StringWriter();

StreamResult sr = new StreamResult(sw);

transformer.transform(domSource, sr);

String xml = sw.toString();

System.out.println(xml);

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

catch (ParserConfigurationException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

catch (TransformerException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

finally {

if (otl != null) {

try {

otl.close();

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

}

}

}

  private void getMembersIterator(IEssMember mbr,Element ParentElement) throws EssException{

Element elementMember = doc.createElement("Member");

elementMember.setAttribute("MemberName", mbr.getName());

elementMember.setAttribute("UniqueName", mbr.getUniqueName());

elementMember.setAttribute("MemberFormula", mbr.getFormula());

elementMember.setAttribute("LevelNumber", String.valueOf(mbr.getLevelNumber()));

elementMember.setAttribute("GenerationNumber", String.valueOf(mbr.getGenerationNumber()));

elementMember.setAttribute("ChildCount", String.valueOf(mbr.getChildCount()));

ParentElement.appendChild(elementMember);

IEssIterator mbrs = mbr.getChildMembers();

for (int i = 0; i < mbrs.getCount(); i++) {

getMembersIterator((IEssMember)mbrs.getAt(i),elementMember);

}

}

 }

Here is what my properties file looks like.

Common.UserName=admin Common.Password=password Common.ServerName=localhost Common.Provider=Embedded

The goal of this lab was to introduce the to Essbase Java API and teach the basics.  It walked through the following:

Setting up J-Developer to work with the Essbase Java API (check out my earlier blog on this topic)

Connecting to Essbase

Creating a grid view (grid API, similar to what the classic add-in uses)

Filling in and querying the grid view

Basic output of the results

The Essbase Java API objects covered in this lab:

IEssbase - The Essbase API instance object.

IEssOlapServer - The connection to the Essbase server.

IEssCube - The Essbase cube object.

IEssCubeView & IEssGridView - The Essbase grid API.

If you did not get a chance to sign up for this hands on lab or just want to try it you can get the lab here.

Please note that this lab was based on a pre-built image so some of the directories, URL's, username,etc. will vary.

Here is the complete code for the lab:

package myproject; import com.essbase.api.base.*; import com.essbase.api.dataquery.*; import com.essbase.api.session.*; import com.essbase.api.datasource.*; public class Essbase { private static String username = "admin"; private static String password = "password"; private static String essbaseServer = "localhost"; private static String provider = "http://localhost:13080/aps/JAPI"; private static String application = "Sample"; private static String database = "Basic"; public Essbase() { } public static void main(String[] args) { IEssbase essbase = null; try { essbase = IEssbase.Home.create(IEssbase.JAPI_VERSION); IEssOlapServer olapServer = essbase.signOn(username,password, false,null,provider,essbaseServer); IEssCube cube = olapServer.getApplication(application).getCube(database); IEssCubeView cubeView = cube.openCubeView("default"); cubeView.setAliasNames(true); cubeView.updatePropertyValues(); IEssGridView gridView = cubeView.getGridView(); gridView.setSize(3,5); gridView.setValue(0,2,"Product"); gridView.setValue(0,3,"Market"); gridView.setValue(1, 2, "Jan"); gridView.setValue(1, 3, "Feb"); gridView.setValue(1, 4, "Mar"); gridView.setValue(2, 0, "Actual"); gridView.setValue(2, 1, "Sales"); IEssOperation essOp = cubeView.createIEssOpRetrieve(); cubeView.performOperation(essOp); int rowCount = gridView.getCountRows(); int colCount = gridView.getCountColumns(); for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { System.out.print(gridView.getStringValue(i, j) + "\t"); } System.out.print("\n"); } } catch(EssException e) { System.out.println("Error: " + e.getMessage()); } finally { try { if (essbase != null && essbase.isSignedOn() == true) essbase.signOff(); } catch (EssException e1) { System.err.println("Error: " + e1.getMessage()); } } } }

Check out my sessions at Kaleidoscope 2009:

http://www.odtugkaleidoscope.com/abstracts.html#Milella

Top Five Essbase CDF's In this session we will demonstrate how to install and use a CDF (Custom Defined Function) in Essbase. We will also walk through some of the most popular CDF categories including an overview of J-Export. CDF categories include; exporting data (J-Export), importing data, accessing Web content (stock quotes, other Web services), outline changes with the Essbase JAPI, and general utility functions for tuning and debugging Essbase calculations.

Bonus CDF - Integrate Essbase with Twitter http://essbaselabs.blogspot.com/2009/05/ashton-kutcher-can-now-get-my-essbase.html

  Hands on Labs

I will also be joining Tim Tow in some hands on labs:

Introducing JDeveloper In this session, you will learn how to download, install and perform the basic configuration, with a focus on the Essbase Java API, of the Oracle JDeveloper Java Integrated Development Environment.   You will also learn basic Java concepts including how to create a new Java application and project and how to create a new Java class by creating the classic Java ‘Hello World’ application.

Java API: The Basics In this session, you will learn the basic structure of the Essbase Java API, how to use it to connect to a given Essbase server, application and database, how to layout a data retrieval grid and how to retrieve data into the grid.

Not sure if this is covered in any document or on any other blog but I could not find it so I thought I would share the steps I took to do this.

Pre Requites:

Essbase is installed and working and is accessible from the machine where you plan to do your development work.

APS (Analytic Provider Services) is installed, this does not have to be running to use the API but you will need files from this installation.

JDeveloper is installed.  I am using JDeveloper 11.1.1.0.1 but I am sure earlier versions will also work.

  In JDeveloper create a new 'Generic Application' by selecting File New...

  Give your application a name (i.e. Essbase Java API) and click finish.

With the new application selected create a new 'Generic Project' by selecting the File New menu or right clicking on the application in the application navigator.

Give the new project a name and select Java for it's technology.

Click Next and enter a default package name (i.e. com.oracle.essbase.japi) and click finish.

On the server where you have installed APS and Essbase copy the following files and put them in a directory that will be accessible from the JDeveloper project:

%HYPERION_HOME%\products\Essbase\aps\lib\cpld14.jar

%HYPERION_HOME%\products\Essbase\aps\lib\ess_es_server.jar

%HYPERION_HOME%\products\Essbase\aps\lib\ess_japi.jar

%HYPERION_HOME%\common\loggers\Log4j\1.2.8\lib\log4j-1.2.8.jar

%HYPERION_HOME%\products\Essbase\aps\bin\essbase.properties

When copying these files to your machine make sure you have following directory structure:

Local Folder\lib - This folder has all the jar files.

Local Folder\bin - This folder has properties file.

Local Folder\data - This folder is empty and when you run your first application a file will be created there for you called domain.db.

In JDeveloper right click on the project created above and select 'Project Properties'.

Select the section named 'Libraries and Classpath' and add a reference to the 4 jar files from above.

Select the section ' Run/Debug/Profile' and edit the default profile.

In the Java Options for the client JVM add the following parameter:

-DESS_ES_HOME= (i.e. -DESS_ES_HOME=C:\JDeveloper\mywork\EssbaseJAPI\japi)

Now you are ready to program using the JAPI to do this you will need a Java class.  Right click on the project and select New...

Select New Java Class and click OK.

Give the class a name and select any of the options you want.

So for an upcoming ODTUG presentation I needed to create some basic CDF's to show as examples; so I decide I had to do something more fun than just logging data or running an executable.  As I was updating my Twitter status I realized there may be an actual business case for having a Twitter account and my CDF example was born... The use case is to follow an Essbase server on Twitter and get updates as calculations are run on the server.

Here is what I did:

Set up a new Twitter account (did not want to mess with my own account) http://twitter.com/essbaseserver

Using JDeveloper I created a new CDF that sends string updates to twitter.  I did this using JTwitter an open source Java library for access to twitter.

Registered the CDF and created a calculation script to run while sending the Twitter account status updates.

RUNJAVA com.oracle.essbase.cdf.EssbaseTweet "Running a Calculaiton on the Cube Sample.Basic";

Calc ALL;

fix("Market","Sales","Jan",@LEVMBRS ("Product", 0))

"Actual"(

@EssbaseTweet (

@CONCATENATE (

@CONCATENATE (

@CONCATENATE ("Sales for ", @AlIAS(@CURRMBR("Product")))

, " are "), @JgetStringFromDouble (@CURRMBR ("Product"),@_false,@_false)));

)

endfix

 RUNJAVA com.oracle.essbase.cdf.EssbaseTweet "Calculation Complete on the Cube Sample.Basic";

• to you, the music I love •

« the music that has always resounded with me - and art as well - is when it feels a little bit like it’s coming from a person, and it’s coming as a visceral place. a place that is maybe trying to explain something that isn’t explained yet. and I guess that’s what I was trying to do, and by trying to write songs in a subconscious way, I’ve ended up with something I’m pretty proud of that I didn’t know I was capable of doing. the songs on the new record, they all mean something to me .»

it’s after midnight in Wisconsin so basically you’ve just turned 34 and I am supposed to be able to tell you something remarkable but words are just lacking. the point is that you’ll never ever read anything of this and you will never ever know how much your music means to me and how insanely it has affected my life and how pure is the warmth I feel inside of my chest when I hear you singing. it’s all about your sincerity;

it’s all about the fact that you put your true self in what you create. It’s all about the trembling of your breathing while you’re playing a song that touches you, the shaking of your voice while you sing re:stacks, the one that makes you cry every time you listen to it

. I love you because I know that if I listen to the music you make I listen to your heart and it’s so rare to find someone purely true! God, you are a macrocosm, you are a never ending cycle of emotions and a unique inspiration, you are a deep breath of fresh air. you are a world that’s huge, that’s too complex and too perfect to be even real. I keep on discovering new features of you and I’d say that «you keep ending up in my shaking hands» and I like the way you do it.

thank you genius.

be happy today.