Tuesday, February 22, 2011

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!

5 comments:

Unknown said...

Hi,

executeQuery methods are good, we use it extensively.

Why don't you use it from a IEssMemberSelection object ?

Can you please remind what member properties cannot be retrieved when cube outline is not opened ?

I have developed an outline extractor, which uses only one executeQuery per dimension, which gets all Descendant. We get major informations. This is extremely fast, you can crawl a lot of outline within minutes.

Regards,
Sebastien

Tim Tow said...

I ended up using the IEssMemberSelection.executeQuery() overload that requires you to specify the properties I wanted. That, in combination with IEssOutline.findMember() allowed me to get everything. BTW, getting the 'real' parent, next sibling and prev sibling for shared members was the real challenge.

Tim

magrelo said...

In my case i´m using Hyperion 7.1.

Is the correct file to pass in url path ?
Hyperion\eas\console\lib\ess_japi.jar

If you have some examples... please

Gracias.

Tim Tow said...

With EDS 7.1.3, which you should be using for all versions of Essbase from 6.5.3 through 7.1.6, the Java API url is http://:/eds/EssbaseEnterprise.

Here is an example I found in some of my old code:

http://localhost:8080/eds/EssbaseEnterprise

Good Luck!

Tim

SickSkilz said...

FYI - They did not opt to fix this in 11.1.2.1, though they get a little more specific with the error message

Error: Cannot query members by specs. Essbase Error(1060000): Invalid outline handle passed to ESSOTL function EssOtlQueryMembersEx