Ephemeral Port Issue with Essbase Has Been Fixed!

The issue that has plagued a number of Essbase customers over the years related to running out of available ports has finally been fixed!

This issue, which often manifested itself with errors in the Essbase error 10420xx range, was caused by how the Essbase Java API communicated with the server. In essence, whenever a piece of information was needed, the Essbase Java API grabbed a port from the pool of available ports, did its business, and then released the port back to the pool. That doesn’t sound bad, but the problem occurs due to how Windows handles this pool of ports. Windows will put the port into a timeout status for a period of time before it makes the port available for reuse and the default timeout in Windows is 4 minutes! Further, the size of the available pool of ports is only about 16,000 ports in the later versions of Windows. That may sound like a lot of ports, but the speed of modern computers makes it possible, and even likely, that certain operations, such as the outline APIs, that call Essbase many, many times to get information would be subject to this issue. Frankly, we see this issue quite often with both VB and the Java Essbase Outline Extractors.

We brought this issue to the attention of the Java API team and assisted them by testing a prerelease version of the Java API jars. I am happy to report the fix was released with Essbase 11.1.2.3.502. In addition, there is a new essbase.properties setting that allows you to turn the optimization on or off:

olap.system.socketoptimization=false

It is our understanding that this optimization is turned on by default. I also checked the default essbase.properties files shipped with both Essbase 11.1.2.3.502 and 11.1.2.4 and did not see that setting in those files. It may be one of those settings that is there in case it messes something else up. The work of our own Jay Zuercher in our labs and searching Oracle Support seems to have confirmed that thought. There is apparently an issue where EIS drill-through reports don't work in Smart View if socket optimization is turned on. It is documented in Oracle Support Doc ID 1959533.1.

There is also another undocumented essbase.properties setting:

olap.server.socketIdleTime

According to Oracle development, this value defaults to 300 ms but there should be little need to ever change it. The only reason it is there is to tune socket optimization in case more than 2 sockets are used per Java API session.

Jay also tested the 11.1.2.4 version in our labs with the Next Generation Outline Extractor. With the default settings, one large test outline we have, "BigBad", with about 120,000 members in it, extracted in 1 minute and 50 seconds.  With socket optimization turned off, the same outline was only about 25% complete after 2 hours.   In summary, this fix will be very useful for a lot of Oracle customers.

Strange New(?) Error Message in Essbase API 11.1.2.1

I have been working with Essbase 11.1.2.1 and am seeing an error message that I don't remember seeing in previous Essbase versions.  The new error message is:

Unknown Error: Not a valid entry

I saw this message a couple of times over the past couple of days when working with Essbase members.  On the first occasion, I was calling the IEssCubeOutline.getDimensions() method and saw this error:

Cannot get child member names. Essbase Error(1013383): Unknown Error: Not a valid entry

I traced this issue to code that inadvertently called IEssCube.clearActive() before calling the IEssCubeOutline.getDimensions() method.  The second instance happened when I called IEssMember.getRelatedMemberNames() on an IEssMember object that was obtained from an IEssMemberSelection object.  In this case, the error number was slightly different:

Cannot get related member names. Essbase Error(1013384): Unknown Error: Not a valid entry

I expected the second exception to occur.  The getRelatedMemberNames() method, which returns an array containing the parent, sibling and first child information, is not available unless you obtain the IEssMember object by querying an IEssCubeOutline object.  I was simply surprised that the error message was the same.

Of course, it is confusing that all member objects are not created equal in Essbase.  It will be a great day when Essbase returns a full IEssMember object regardless of the method used to obtain it.  That being said, I am not holding my breath.

Using the Java API to Logout Users From a Server

There was a question on the Network54 board today regarding the Java API and logging off all users from a given application.   I quickly wrote up a quick (but untested) bit of Java API code as an example, but decided to post it here as Network54 mangles the formatting of code examples.   Without further ado, here is the code in a more readable format:

void disconnectUsersOfApplication(IEssOlapServer server, String applicationName) throws EssException {
    // get the connections to the server
    IEssIterator connections = server.getConnections();

    // loop the connections
    for (int i = 0; i < connections.getCount(); i++) {
        // cast to a connection info object
        IEssOlapServer.IEssOlapConnectionInfo connection = 
         (IEssOlapServer.IEssOlapConnectionInfo)connections
         .getAt(i);

        // if the connection is to the target app
        if (connection.getConnectedApplicationName()
         .equalsIgnoreCase(applicationName)) {
            // log them off
            connection.logoffUser();
        }
    }
}

Kscope11: Java API Tips/Tricks Slides

I have been quite busy working on getting Dodeca version 6.0 ready to ship, thus the long delay in getting these slides posted.  I wanted to get these posted, however, as they are unfinished business before I can really start writing about Dodeca 6.  So, without further delay, here are the slides..

BTW, the slides contain the abbreviation 'WWEAD'..   This stands for 'What Would the Essbase Addin Do?'

I also have uploaded some sample code that shows two different variations of signing on to the server, several different ways of getting member information and some examples of grid operations.  The member information example was very interesting as I added a speed test that shows how one particular method is much faster than another.   The code is available for download here.

Essbase Java API - Group Names When Using Shared Services

One of our Dodeca customers had a question about support for Essbase group names when using Shared Services security.  We did a bit of testing and found some interesting results to share.

One of the configuration settings in Dodeca allows Dodeca administrators to limit the sets of views/reports a user can see based on their assigned roles.   The roles can be sourced from a number of places including the Essbase group names.  That being said, with the advent of Shared Services, there is some confusion with the availability, to the Essbase Java API, of certain pieces of security information.  Dodeca uses the following Essbase Java API code to get the group names:

// get the olap user object
IEssOlapUser user = olapServer.getOlapUser(username);


// get the groups for the user
IEssIterator groups = user.getGroups();


// loop the groups
for (int i = 0; i < groups.getCount(); i++) {
  // get the group
  IEssOlapGroup group = (IEssOlapGroup)groups.getAt(i);


  // serialization code removed...
}

In testing this code in 11.1.2, we found that the group names are returned, but also have an '@' sign and the directory appended as well.  Of course, Dodeca communicates via web services, so the XML stream we saw coming out of Dodeca looked like this:

So, the information is available to the Essbase Java API with the caveat that the group name is postpended with the directory (which makes sense).

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.

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

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.

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.

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.