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!
Labels: ,