Thursday, June 3, 2010

When Is A Member Not A Member?

When is a member not a member? Sometimes in the Essbase Java API, that's when. Depending on how you obtained your IEssMember object instance, it may only partially describe the member. What I mean by that is that is that some properties are not available, and are thus either blank/null or throw an Exception when you try to access them. This is why it can be frustrating to work with the Essbase API. For what it is worth, this problem is not limited to the Essbase Java API but is also a present in the C and VB APIs as well. In this blog, I will focus on the Java API but for those of you using the C and VB APIs, see if you can spot the corresponding API calls in those languages.

To start, let's look at 3 common ways to get an IEssMember object in the Essbase Java API:
  • IEssCube.getMember()
  • IEssMemberSelection.executeQuery()
  • IEssCubeOutline.findMember()
Each of these methods have their advantages and their disadvantages. The IEssCube.getMember() method is fast but doesn't return all properties. IEssMemberSelection.queryMembers() allows you to find multiple members based on relationships or wildcard searches but, again, doesn't return all of the properties; it also will cause hanging Essbase connections if you don't properly close the IEssMemberSelection object. IEssCubeOutline.findMember() gives you basically all of the information but requires that you open the outline. Opening the outline can be terribly slow if you have a large outline or, in our experience, if the outline is built using EIS or Essbase Studio. The fact that there are multiple ways to get the member information can certainly be frustrating.

One example of frustration occurred this spring when we decided to add attribute information to the MemberTips we optionally display in a Dodeca member selector. We uncommented our server code that calls the IEssMember.getAssociatedAttributes() method and were not surprised that this method threw an Exception. We have gone to great lengths to make Dodeca performant and, for the most part, don't open the outline. As a test, however, we decided to open the outline only when the query was returning this information. As soon as we tested this on one of our larger customer outlines with 385,000 members in the Entities dimension, we knew this approach would be much too slow to put into production. Upon further testing, we found that we could get 2 of 4 attribute-related properties without opening the outline and that is what we have now implemented.

Dodeca MemberTips















This summer, I have my lucky intern working on a research project with Essbase member information with the Java API. The first thing I had him do was to write some Java code that used reflection to attempt to find which properties were available to each method of obtaining an IEssMember object. Here is a summary of that work using 11.1.1.3 run against Sample Basic using 100-10 as the target member. An 'x' in the cell indicates that the property value appears to have been returned properly; a blank indicates either an error occurred or, perhaps, that the method didn't work due to member 100-10 not being a dimension root member, etc.
MethodNameCubeMember
Selection
Outline
getAggregationLevelUsagexxx
getAssociatedAttributes

x
getAttributeAssocLevelxxx
getAttributeMemberDataTypexxx
getAttributeValuexxx
getChildCount
xx
getChildMembers

x
getConsolidationTypexxx
getCountChildMembersxxx
getCountOfDupMemberNameInDim*


getCurrencyCategoryOrName
xx
getCurrencyConversionType
xx
getDescriptionxxx
getDimensionCategory
xx
getDimensionNamexxx
getDimensionNumberxxx
getDimensionSolveOrderxxx
getDimensionStorageCategory
xx
getDimensionStorageType
xx
getFirstChildMemberNamexxx
getFormatStringxx
getFormulaxxx
getGenerationNumberxxx
getHierarchyType*
xx
getLastFormulaxxx
getLevelNumberxxx
getLinkedAttributeAttachLevelxxx
getMemberComment

x
getMemberIdxxx
getMemberNumberxxx
getMemberType*


getNamexxx
getNextSiblingMemberNamexxx
getOriginalMemberNamexxx
getParentMemberNamexxx
getPreviousSiblingMemberNamexxx
getPropertyDataTypesxxx
getPropertyModesxxx
getPropertyNamesxxx
getRelatedMemberNames

x
getRootxxx
getShareOption
xx
getSmartList*


getSolveOrderxxx
getTimeBalanceOption
xx
getTimeBalanceSkipOption
xx
getUDAs
xx
getUniqueName
xx
isAttributesAssociatedxxx
isClientCachingEnabledxxx
isDimensionRootMemberxxx
isExpenseMember
xx
isFlowTypexxx
isIndependentDimxxx
isInitializedxxx
isMemberNameUniqueWithinDim*


isNameUnique
xx
isRelationalDescendantPresentxxx
isRelationalPartitionEnabledxxx
isTwoPassCalculationMember
xx


* Indicated results may be due to the member tested, 100-10, instead of the availability of the information exposed by the IEssMember.

As it appears that basically every property value is available when the outline is open, my intern is now working on prototyping a faster methodology for getting member information that I engineered. I will discuss this methodology once we have it implemented and shipping in Dodeca.

Speaking of Dodeca, we have been hard at work on Dodeca 5.0 and are now at the beta 2 milestone (which explains the sparseness of my blogging). I plan to start blogging on the numerous new features of Dodeca 5.0, along with continuing the Dodeca architecture overview I started in the fall, within the next few days.

12 comments:

Anonymous said...

Tim,

How would you get the parent name of a shared member using the Java API - for example, "Diet": "200-20" in Sample.Basic? member.getParentMemberName() returns the name of the stored member's ("200-20") parent, or "200" in this case, rather than "Diet".

Thanks for your help.

Tim Tow said...

It really depends on how you get your member. If you are searching by name, you will probably end up with the 'real' member instead of the shared member. If, however, you are doing a query for descendants of Diet (or Product), you will get the IEssMember that has 'Diet' as the parent.

I really hate this behavior because it messes up the preorder walk algorithm I want to use to walk the tree.

Anonymous said...

When I query for the descendants, the parent comes after the child (Product?100 comes out below its children). This will obviously cause problems in a load file:

100?100-10?Cola?+??Caffeinated_True?Ounces_12?Can?Intro Date_03-25-1996?
100?100-20?Diet Cola?+??Caffeinated_True?Ounces_12?Can?Intro Date_04-01-1996?
100?100-30?Caffeine Free Cola?+??Caffeinated_False?Ounces_16?Bottle?Intro Date_04-01-1996?
Product?100?Colas?+??

The memberSelection I am using is below. Is there some option I can use to make it output in the same order as the outline?

IEssMemberSelection memberSelection = cube.openMemberSelection(dimName);
memberSelection.executeQuery("<OutputType Binary <SelectMbrInfo (MemberName, MemberFormula, MemberAliasName, MemberNumber, MemberLevel, MemberGeneration, ParentMemberName, Consolidation, UDAList)", "@descendants(\"" + dimName + "\")");

Thanks for your assistance, Tim.

Tim Tow said...

There is certainly nothing documented for that API call that indicates you can sort it. The syntax, however, looks very much like the syntax you would use for IEssCube.queryMembers (or EsbQueryDatabaseMembers) which has a sort command "<SORTNONE" that returns members in outline order. I would give that a try (and let me know if it works)..

Tim

Anonymous said...

Tim,

I get the following error when adding that sort command:

com.essbase.api.base.EssException: Cannot query members by specs. Essbase Error(1001001): Unknown Command [<SORTNONE] in Report

memberSelection.executeQuery("<OutputType (Binary) <SORTNONE <SelectMbrInfo (MemberName, MemberFormula, MemberAliasName, MemberNumber, MemberLevel, MemberGeneration, ParentMemberName, Consolidation, UDAList)", "@descendants(\"" + dimName + "\")");

Thanks!

Anonymous said...

Tim,

Any ideas on how to get the results of an IEssMemberSelection.executeQuery in outline order? I am open to outputting to a text file and manipulating the data there, but there must be a less kludgy way, right?

Thanks for all of your assistance!

Anonymous said...

Tim,

Just wanted to let you know that I am now able to return it in outline order.

Thanks for taking the time from your busy schedule to help!

Tim Tow said...

What was your solution?

Tim

Anonymous said...

Finally had to go with the following kludge:

1) get the members in outline order using - String sResults = cube.queryMembers("<NEWLINESEPARATED <SORTNONE <ALLINSAMEDIM \"" + dimName + "\"");

2) get the real members using - memberSelection.executeQuery("<OutputType Binary <SelectMbrInfo (MemberName, MemberFormula, MemberAliasName, MemberNumber, MemberLevel, MemberGeneration, ParentMemberName, Consolidation, UDAList)", "@descendants(\"" + dimName + "\")");

3) add the real members from the memberSelection to a hashMap

4) loop through the string from the queryMembers (which has the members in outline order) and query the hashMap to get the correct parent/child relationship (even for shared members)

I am very curious as to the algorithm that you are using. Would you mind sharing?

Thanks

Javanator said...

I was wondering if you might have some insight into wildcard member searches through the Java API. Specifically, I am trying to find members that match *_SOMESUFFIX, but the built-in wildcard search function only seems to work when the wildcard is at the end of my search patter (Example: SOMEPREFIX_*) I was also thinking i could do a search on * and return everything (one round-trip) but out-of-memory errors ensue.

I know I can always resort to manually traversing the outline and search iteratively, but my concern is with the number of round-trips and speed on larger outlines.

Thanks in advance!

Javanator said...

In the interest of sharing code and helping the community i would like to make a follow up post.

Earlier I posted a comment regarding finding members efficiently (fewer round trips) using a wildcard and/or regular expression. The built-in APIs only work if the wildcard is at the end of a search string. I used the below custom search method to find members using any regular expression. The number of round trips is directly proportional to the number of dimensions.

public List<String> findMembers(String applicationName, String cubeName, String regex) throws EssException {

List<String> memberNames = new ArrayList<String>();

IEssOlapApplication application = server.getApplication(applicationName);
IEssCube cube = application.getCube(cubeName);

IEssMemberSelection memberSelection = cube.openMemberSelection("my query");

memberSelection.executeQuery(null, IEssMemberSelection.QUERY_TYPE_CHILDREN, IEssMemberSelection.QUERY_OPTION_MEMBERSANDALIASES, null, null, null);
IEssIterator mbrs = memberSelection.getMembers();

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

// Iterating over dimension names. Perform report query for all
// members in a dimension
IEssMember dimension = (IEssMember) mbrs.getAt(i);

// Tab Delimited
String queryMembers = cube.queryMembers("<ALLINSAMEDIM " + dimension.getName());
StringTokenizer tokenizer = new StringTokenizer(queryMembers, "\t");

while (tokenizer.hasMoreTokens()) {

String token = tokenizer.nextToken();

if (token.matches(regex)) {
memberNames.add(token);
}
}
}

return memberNames;
}

Tim Tow said...

Interesting approach that certainly reduces trips the server..

One approach we have evaluated is to extract the outline members to a relational database, then do queries against relational to be able to do pattern matches easier. Our use case is such that relational would be a good approach; not all use cases share that attribute.

Tim