Scalability and the Essbase Java API

During my Dodeca webcast a couple of weeks ago, someone asked a question about the typical number of servers necessary for a Dodeca deployment. Dodeca is quite resource friendly on the server due to it's architecture and thus the answer is 'Less than you would expect'.

One reason is that we use the Essbase Java API on the server. The Java API was designed from the ground up to be a highly scalable and highly dependable API layer to be consumed by both Hyperion applications and by third party applications. We did extensive scalability testing of our own on our Essbase services. The most extreme test ran 25 concurrent threads constantly on an old underpowered server we had sitting around. The test was intended to simulate approximately 500 users assuming the usage pattern is that the user is querying the database 5% of the time and analyzing the results of the time in the application. We left this test running for 5 months in a single instance of Tomcat with the following results:

Number of requests serviced - 204,043,599
Hours of processor time - 2237:07:28
RAM used - 66.2 Mb

Nearly a quarter of a billion transactions in a single Tomcat instance.. I remember 15 years ago when the Excel add-in wouldn't do more than 50 or 60 retrieves before it would sometimes crash. Now that we have faster servers, I am thinking perhaps we should go for a billion transactions in a single instance!

All of this ties in very nicely with the functionality I am working on currently for Dodeca. In a near future version, we expect to ship functionality that will allow administrators to capture the queries, calcs, etc. that users are calling when they use the application and use that input to run their own stress testing on their server using their own data and usage patterns. Let me know if you think this functionality would be useful in your environment.

Essbase Java API bug: IEssCubeOutline.executeQuery()

I have been working on some cool new things in our Dodeca Essbase web services server using the Java API and have found a few interesting things that I will try to post over the next couple of weeks. 

I found this first item about a month ago when I was working on member information.  If you have read my blog for a while, you may remember my comments last summer on how getting all information about a member can be quite hard and that you have to really open the outline to get the information.  I wish it had only been that easy.

One of my thoughts was to use the executeQuery method on the IEssCubeOutline object to query the data.  Theoretically, the members that are returned from that call should be 'opened' from the outline and thus all of the information is available for the member.  I say 'theoretically' because I couldn't get it to work.  No matter how I tried, the method always throws an EssException with the following error message:

Cannot query members by name. Essbase Error(1060000): Invalid outline handle

I wrote some sample code, against Sample Basic in version 11.1.2, and sent it over to some friends in Oracle tech support and they confirmed it was a bug within a couple of days.   This won't help me though as we support all versions of Essbase back to 6.5.3.  Even if they get a fix into the upcoming 11.1.2.1 release, it will be 5 to 10 years before I could consider using it.

Here is the code I sent to Oracle:

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.metadata.*;

public class EssQueryOverOutline {
  private static String _username = "admin";
  private static String _password = "password";
  private static String _url = 
                 "http://localhost:13080/aps/JAPI";
  private static String _server = "localhost";

  public static void main(String[] args) {
   IEssbase ess = null;
   IEssOlapServer server = null;

   try {
    // Create API instance.
    ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

    // connect to the Essbase server
    server = ess.signOn(_username, _password, false,
                 null, _url, _server);

    // get the cube
    IEssCube cube = server.getApplication("sample")
                        .getCube("basic");

    // get the outline
    IEssCubeOutline outline = cube.openOutline();

    // Note: the next line throws the following 
    //exception:
    // com.essbase.api.base.EssException: Cannot query 
    // members by name. Essbase Error(1060000): 
    // Invalid outline handle
      
    // execute the query
    IEssIterator members = outline.executeQuery("Diet",
         IEssMemberSelection.QUERY_TYPE_DESCENDANTS,
         IEssMemberSelection.QUERY_OPTION_MEMBERSONLY,
         null, null, null);

    for (int i = 0; i < members.getCount(); i++) {
      // get the member
      IEssMember member = (IEssMember)members.getAt(i);

      // print some properties
      System.out.print("Member:");
      System.out.print(member.getName());
      System.out.print("; Parent:");
      System.out.print(member.getParentMemberName());
      System.out.print("; Is opened from outline:");
      System.out.print(member.getParent() instanceof 
                       IEssCubeOutline);
      System.out.print("; Parent from 
                       getRelatedMembers():");
      System.out.print(member.getRelatedMemberNames()[0]);
      System.out.print("\n");
    }
  } catch (EssException e) {
    e.printStackTrace();
  } finally {
    try {
      if (server != null && server.isConnected())
        server.disconnect();
    } catch (Exception e) {
      e.printStackTrace();
    }

    try {
      if (ess != null && ess.isSignedOn())
        ess.signOff();
      } catch (EssException e) {
        e.printStackTrace();
      }
   }
 }
}

And due to the wrapping problems, here is a jpg of the code from my Java dev environment, IntelliJ:

Oddly enough, the same week I was working on this, another Essbase Java API fan, and friend, Joe Aultman, gave me a call and asked me if I had ever successfully got executeQuery to work..  Boy, was I ever prepared for that question!

Kaleidoscope 2010 - Java API Session Slides / Source Posted

As promised, I have posted the slides and source code examples from my Introduction to Development with the Essbase Java API session at Kaleidoscope 2010.  There is a readme file that gives you the basics of how to run them from the command line.  If you are serious about learning the Essbase Java API, I strongly recommend you download one of the free Java IDE's available.  My favorites are:

• IntelliJ
• Oracle JDeveloper
• Eclipse

You can download the files from the Blog-Content section of the Applied OLAP website at http://www.appliedolap.com/downloads.

Essbase Outline Performance Testing - Do It Yourself Kit

I decided I would post my code for the Essbase Outline Performance Testing for a couple of reasons:

• So you can try it on your own outlines; and
• To make more Essbase Java API examples available online.

To use the code below, follow these steps:

• Make sure you have a Java JDK installed on your system and referenced with the JAVA_HOME environment variable.
• Make sure you have a backup of your Essbase outline (just in case).
• Create two text files; name on file 'EssOutlineOpenTimingsTest.cmd' and the other 'EssOutlineOpenTimingsTest.java'.
• Copy the following to code to the EssOutlineOpenTimingsTest.cmd (and I apologize in advance for the small size of the code; I had to shrink it for the blogger software to properly display all of the code):

@echo off

rem Change the directory below to point to your jar file 
set CLASSPATH=%CLASSPATH% ;C:\Hyperion\products\Essbase\aps\lib\ess_japi.jar;
echo Compiling ...

"%JAVA_HOME%\bin\javac" *.java -d .

echo Running test class ...
echo . 
"%JAVA_HOME%\bin\java" -ms128m -mx512m EssOutlineOpenTimingsTest

echo . 
echo . 
echo Done ... 
pause

• Copy the following code to the EssOutlineOpenTimingsTest.java file:

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;
import java.text.DecimalFormat;

public class EssOutlineOpenTimingsTest {
    // TODO: CHANGE THE VARIABLES BELOW TO USE YOUR INFORMATION
    private static String _user = "timt";
    private static String _password = "essbase";
    private static String _server = "mustang";
    private static String _url = "http://mustang:13080/aps/JAPI";

    public static void main(String[] args) {
        IEssbase ess = null;
        IEssOlapServer server = null;

        try {
            // create api instance
            ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

            // signon to the domain
            IEssDomain dom = 
                ess.signOn (_user, _password, false, null, _url);
            
            // connect to the server
            server = (IEssOlapServer)dom.getOlapServer(_server);
            server.connect();
 
            // print the column headers
            System.out.println(
        "Try #|Application|Cubename|Milliseconds|Filesize (Mb)|Members"
            );

            // TODO: CHANGE THE NUMBER OF LOOPS BELOW AS DESIRED
            // open each outline 3 times in a loop
            for (int i = 1; i <= 3; i++) {
                // TODO: CHANGE THE APPLICATIONS/DATABASES BELOW, 
                // AND ADD/DELETE OPENOUTLINE CALLS, AS DESIRED
                openOutline(i, server.getApplication("Sample")
                    .getCube("Basic"));
                openOutline(i, server.getApplication("ASOSamp")
                    .getCube("Sample"));
                openOutline(i, server.getApplication("Big1")
                    .getCube("Big1"));
                openOutline(i, server.getApplication("BigASO")
                    .getCube("BigASO"));
                openOutline(i, server.getApplication("BigASO_C")
                    .getCube("BigASO_C"));
                openOutline(i, server.getApplication("zzz")
                    .getCube("zzz"));
                openOutline(i, server.getApplication("zzz_C")
                    .getCube("zzz_C"));
            }
        } catch (EssException e) {
            System.out.println("Error: " + e.getMessage());
        } finally {
            try {
                if (server != null && server.isConnected() == true)
                    server.disconnect();
            } catch (EssException e) {
                System.out.println("Error: " + e.getMessage());
            }

            try {
                if (ess != null && ess.isSignedOn() == true)
                    ess.signOff();
            } catch (EssException e) {
                System.out.println("Error: " + e.getMessage());
            }
        }
    }

    static void openOutline(int tryNumber, IEssCube cube) 
        throws EssException {
        
        IEssCubeOutline outline = null;

        try {
            // stop/start the cubes to get a fair timing
            try {
                cube.getApplication().stop();
            } catch (EssException e) {
                // fails if not started, so just ignore
            }

            // start cube
            cube.start();

            // let the machine catch it's breath
            try {
                Thread.sleep(3000);
            } catch(InterruptedException e) {

            }

            // get the start time
            long startMillis = System.currentTimeMillis();

            // open the outline
            outline = cube.openOutline();

            // compute the time to open
            long totalMillis = System.currentTimeMillis() - startMillis;

            // declare variables for the output string
            int memberCount = 0;
            String filesize = "";

            if (tryNumber == 1) {
                // get the dimensions
                IEssIterator dims = outline.getDimensions();

                // loop the dimensions
                for (int i = 0; i < dims.getCount(); i++) {
                    // get the dimension
                    IEssDimension dim = (IEssDimension)dims.getAt(i);

                   // count the members
                    memberCount += dim.getDeclaredSize();
                }

                // get the size of the outline file
                byte[] bytes = cube.copyOlapFileObjectFromServer(
                    IEssOlapFileObject.TYPE_OUTLINE, 
                    cube.getName(),
                    false);

                // count the bytes
                filesize = new DecimalFormat("0.0")
                    .format(bytes.length / (1024 * 1024));
            }

            // print the result
            System.out.println(tryNumber + "|" + 
                               cube.getApplication().getName() +
                               "|" + cube.getName() + "|" + 
                               totalMillis + "|" + filesize + "|" + 
                               memberCount);
        } finally {
            // cleanup
            if (outline != null && outline.isOpen())
                outline.close();
        }
    }
}

• Modify the parameters in the Java code were noted.  These parameters will set the code to user your server, username, password and databases.
• Save both files, then double click the cmd file to run.

Remember to backup your Essbase outline file before you start as, by using this code, you acknowledge that you are responsible for the result and agree to hold me and my company harmless for any use of the code, in whole or in part.

Let me know your results!

Essbase JAPI 11.1.2.1 - Location of samples directory has changed

I noticed when we were doing our initial work on our Dodeca servlet that the Essbase JAPI samples directory was not where it used to be.  In 11.1.2, it was located at:

C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\aps\samples

I am setting up my new 11.1.2.1 laptop today and found the samples directories, along with some of the other directories, have changed locations to live in the equivalent of the old HYPERION_HOME\common directory:

C:\Oracle\Middleware\EPMSystem11R1\common\EssbaseJavaAPI\11.1.2.0\samples

By the way, that is not a typo; the directory name does reference 11.1.2.0 although the accompanying lib\ess_japi.jar manifest.mf file does properly state the version as 11.1.2.1.

How to configure Tomcat, as delivered with Analytic Provider Services, to capture stdout

By default, the Analytic Provider Services (“APS”) service does not log standard out. Unfortunately, Java developers frequently use standard out to log information (particularly error information). If this information is critical to you, the logs need to be configured.

After doing a bit of digging, I was able to determine that logging is enabled by configuration parameters in the Windows registry. Specifically, the following parameters need to be added to the registry to configure the service:

• StdError


• StdOutput

The following screenshot shows how I configured these two settings to enable the standard out logging in APS 9.3.0. Remember that you must restart the service for these settings to take effect.


(Click image to enlarge)

When I installed System 9.3.1, standard out logging again stopped working. Upon further investigation, I found the names and location of the settings had changed. On the plus side, the settings actually did already exist but were set to ‘nul’ by default.

I modified the settings and added a path to the SysErrFile and SysOutFile parameters as shown below.

(Click image to enlarge)

If you find any other strange things with APS service logging, please email me at timtow@appliedolap.com and I will add the information to this document.

I have made this content downloadable as a pdf document here.

Essbase Outline Performance Testing

I posted a blog entry last week about getting member information in the Essbase API and made a comment about how opening an Essbase outline can be slow.  We have seen anecdotal evidence over the years that outlines created in EIS/Essbase Studio seem to open more slowly which, incidentally, led us to write metadata caching into our Dodeca-Essbase service years ago.  If I remember correctly, the Java API developers told me back then that opening the outline copies the outline file to the client machine, so some of the performance problem may be due to the file size that must be passed across the network; this is the same with the C and VB APIs.  Based on these things, I decided to do some testing to try and get to the bottom of it (and perhaps help our friends at Oracle understand how the APIs are used out here 'in the wild' so they can better optimize the operations).

For my test, I wrote a Java method to open an outline and output the time it takes to complete the action.  I then wrote code to call the method 5 times for each of four cubes/databases to make sure I was getting consistent timings.  The testing was done completely on my laptop with the Java code, Essbase 11.1.1.0 and APS 11.1.1.0 all running on the same machine.  I picked these four cubes for different reasons.  The four cubes are:

• Sample.Basic.  I picked this cube as everyone has it and it can provide a comparison baseline.  The filesize for the test was 9.1 Mb.

• ASOSamp.Sample.  I picked this cube as it gave me an ASO comparison baseline with 17,711 members in 14 dimensions.  The filesize for the test was 5.2 Mb.

• Big1.Big1.  I picked this (renamed) customer cube as it is a very large BSO outline, built with build rules, with 337,272 members in 6 dimensions including 45,985 Accounts and 331,226 entities.  The filesize for the test was 64.1 Mb.

• zzz.zzz.  I picked this (renamed) customer cube as it is an average ASO cube built by Essbase Studio with 55,284 members in 11 dimensions.  The filesize for the test was 133 Mb.

Here are the results of my test summarized in a pivot table; click on the graphic to view the entire sheet.

I found the zzz.zzz outline was, by far, the slowest to open.  When compared to the ASOSamp baseline outline, it took approximately 9 times longer to open zzz.zzz despite the fact that it has only 3 times more members.  Big1.Big1, which has 6 times more members than zzz.zzz, opened in just over 50% of the time.  Based on my tests, it appears the filesize is a major factor in the performance and that the outline built with Essbase Studio is significantly larger than the outline built with build rules.

So, how does outline performance affect you?  Other than the obvious wait times in EAS, there may be some things that are not as obvious. The two most glaring examples are the inability to get all of the available information about associated attributes and the inability to get member comments.  In any case, wouldn't it be great if all member queries were equal and outlines opened really fast?

Random thoughts on differences in APS 11.1.1

I have come along on my Essbase 11.1.1 install and plan to start a series on the installation but, at this point, I started looking at Analytic Provider Services ("APS") to setup my development environment. Our Dodeca product talks to Essbase through APS and, although I have compiled our Essbase service against 11.1.1, I am starting work to support the new features of Essbase. These are just some random notes I had while reviewing APS.

The directory structures for the Essbase product appear to have been reverted back to the pre-System 9 naming conventions which is great. I never liked the 'Analytic Administration Services' subdirectory name and highly prefer 'eas'.

The essbase.properties file is much more organized than in 9.3.1 but has significantly fewer configurations that in the previous version.

Tomcat is still the 'batteries-included' application server for now. I wonder how long it will be until some version of WebLogic is delivered 'out-of-the-box'. The version of Tomcat that is shipped has changed from 5.0.28 to 5.5.17. Further, it appears that support for running Tomcat in a Window is no longer delivered. This last item is going to have a significant effect on me as I normally run Tomcat in a window during development so I can see my web services data flowing in/out of the server. Tomorrow, I will have to look at how 5.0.28 was configured in 9.3.1 and emulate that in this version. Ironically enough, the infrastructure for 5.0.28 is still delivered in the aps subdirectory structure but it looks like it is not used anywhere.

The APS command line console is available and works but it looks like the default username/password has changed. It used to be that you had to login with the system/password combination, add a username that matched a username in your Essbase system, disconnected and logged back in using the new username. In 11.1.1, I logged in using admin/password. I don't know, however, if that is the default username or if it is the username/password combination I used during my setup/configuration.

Numerous times during my work so far, I have seen numerous references to the previous product numbering system. For example, in the APS\bin directory there is a file named css-0_9_5.dll which, of course, refers to the fact that EPM 11.1.1 originated as System 9.5.0. I have seen a number of people say they would wait on installing 11.1.1 as they wouldn't install a initial release; I wonder if their opinion would be different if it were still named 9.5.0?

There are a number of new external libraries shipped with APS now. The jar files that look new to me include ldapbp.jar, commons-codec-1.3.jar, commons-httpclient-3.0.jar, jakarta-regexp-1.3.jar, jakarta-slide-webdavlib.jar and (maybe) jdom.jar. Most of these are from the Apache project including the commons and jakarta jars.

My first compile of our Essbase servlet with the 11.1.1 client jars worked without a hitch. In the next day or so, I will be updating our build scripts for building a production version of our servlet with 11.1.1 support. As the code hasn't changed, I don't expect any issues during our QA testing cycles.