Essbase Date/Text Measures In Detail

I have been working on the Dodeca data audit log to make sure changes to date and text measures are properly logged and, as a result, got some interesting insights into how date and text measures work at the API level.  I thought I would share my thoughts with you.  In my work, I created a very simple example for tracking satisfaction of marketing programs including the start date and end date of the promotion.  This may or may not be a realistic usage of date and text measures, but I am interested to look at how it works under the covers, so it is good enough for my purposes.

For my test environment, I made a copy of the Sample Basic database and called it SampleEx Basic.  In this Essbase outline, I made a couple of changes.  First, I added two date measures, Start Date and End Date, and a text measure, Satisfaction.   I also added a dimension called Marketing Campaign.

In the Member Properties dialog for the members Start Date and End Date, I changed the Type property to Date.   When I pressed the OK button, the Consolidation property was set to Never automatically.

The date format used by Essbase is determined by the Date Format String property of the outline.  Here is where I set the date format that will be recognized by Essbase.  This is important not only for display of dates, but also for the input of dates as well.  I will show this in an example a bit later in this post.

Next, I added a Text List named Satisfaction Level defined with High, Moderate and Low values represented by the numbers 1, 2 and 3.  These numbers are the actual values stored in Essbase.

I selected the member Satisfaction and opened the Member Properties dialog, then changed the Type property to Text and selected the text list Satisfaction Level.   As with the date measures, Consolidation property was automatically set to Never by EAS.

I then created a view in Dodeca based on an Excel spreadsheet.  This view is really a template targeted for input these date and text measures.

As I mentioned earlier, Essbase expects dates to be input in the format specified for the outline.  It isn’t good enough, however, to use an Excel date format on the cell.  So, what happens if you us Excel dates?  Let’s take a look.  Here is the spreadsheet with an Excel date input.

After the data is sent to Essbase, then re-retrieved, the date appears to have changed.

So, what happened?  To find out, I stepped the Essbase Java API code that is running inside the Dodeca server.  Once I stepped into the server, I can see the value passed to Essbase is 41142 which is the numeric date format used by Excel.  The Excel date format represents the number of days since January 1, 1900.  I couldn’t find official boundaries for date measures in the Essbase DBAG, however, I did see references to date attributes are valid for dates between 1/1/1970 and 1/1/2038.  Apparently, the way that Excel format dates differs from the way Essbase server formats dates.  I experimented a bit more to determine how it works.

Next, I re-retrieved the data from Essbase so I could check the API level detail.  I found that the IEssDataCell.getDoubleValue() call for this cell returns 41142.0.  In other words, Essbase properly returned the same number that was sent to that cell.  The Essbase API also has an option to return a formatted string; the formatted string for this cell, returned via IEssDataCell.getValue(), returns “01-01-70”.  That is the value that I am seeing in the spreadsheet.  Hmmm...

What the...?  What is going on?  Well, here is the answer: Essbase expects the date to be submitted to the server in string format.  After all, it is a “date and text” measure, right?  So, let’s give that a try by putting the date in the string format expected by Essbase and, as with Excel, I will prepend the date with a single quote to force the value to be a string.

Once again, I sent the data to Essbase, then re-retrieved the data into the spreadsheet.  This time, I got different results.  The formatted string returned by IEssDataCell.getValue() returns “08-21-12” and the numeric value returned by IEssDataCell.getDoubleValue() returns 1,345,507,200.  What the heck is that number?  Believe it or not, it is the number of seconds since 1/1/1970.  Let’s calculate it in Excel:

Now we also know why the first attempt to send data to our date measure returned the date 1/1/1970.   The number that was actually sent to the database, 41,142, is less than the number of seconds in one day.  As the date measure is the number of seconds since midnight on 1/1/1970 and the number sent to Essbase doesn’t represent even one entire day’s worth of seconds, it was correct that it returned 1/1/1970.

I also tested input of date measures in Dodeca using the numeric values representing a date.  For this test, I entered 1,345,999,999 and sent it to Essbase.  When I subsequently retrieved the data value, it properly returned 1,345,999,999 as the numeric value and 08-26-12 as the formatted date.  In other words, sending the number of seconds to Essbase appears to work properly.

One final thought on date measures.  As we saw earlier in this post, when you setup an Essbase outline for date measures, you must choose a date format string.  We also saw that Essbase stores date measures with a data granularity to the second.  My question is this: Why are the allowed date format strings only granular to the day?

With that, we have learned a bunch about how Essbase handles date measures, so let’s turn our attention to a (much shorter) discussion of text measures.  In my example, we have the member ‘Satisfaction’ as a text measure and valid values are 1 – High, 2 – Moderate, and 3 – Low.  To enter the data into these cells, simply type the text value and sent it:

I did find, however, that I was unable to update the cell by entering the numeric values (even in Smart View or the classic add-in).  I thought this was a bit strange and may take a look at it in more detail in the future.

On the topic of text measures, I have heard discussion by a lot of people who believe that text measures may be a way for end users to enter free-form text into Essbase intersections.  That is not the purpose of text measures and neither can you enter free-form text.  Instead, the values that can be entered must be defined by an Essbase administrator in the Text List Manager for the outline.  They are much more akin to Smart Lists in Hyperion Planning.  In fact, under the covers in the Essbase API, they are known as Smart Lists.  Free form text is available in Essbase in the form of Linked Reporting Objects, or LROs.  LROs are problematic, however, as they are stored in the Essbase database index and must be subjected to special backup/restoration routines when you rebuild your cubes.  LROs are also not available in ASO databases.  If you need this capability, I recommend Dodeca, which features the ability to store textual information related to an Essbase data cell in the Dodeca relational server.

So, now you know more than you thought you may have ever wanted to know about date and text measures.  Perhaps your sole remaining question relates to my example.  You probably want to ask me, “Tim, why does the start date of your marketing campaign for the July 4th holiday start on August 21?”  OK, you caught me.  I used that date because I am writing this in August and August 21st happens to be my Dad’s birthday.  So, now you know..

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.

OLAPUnderground Outline Extractor on 11.1.2.1 - Updated Path Requirements

We are starting to get OlapUnderground users who have installed 11.1.2.1 and are seeing problems with the Outline Extractor.  We also had issues with this version of the Outline Extractor.  Some of the typical symptoms were that Essbase dialogs, such as the login dialog, did not work.  In fact, I saw this same behavior in the classic Excel add-in on my machine; it did not work.  My QA team went to work and found a couple of interesting things about Essbase API applications in 11.1.2.1.

First, there is a new requirement for another directory in the path statement.  After a bit of searching, our QA guys found the following article on Oracle's MySupport site (Document ID 1322496.1):

Problem : Running a 32-bit VB API on a 64-bit Essbase Server Fails With "ERROR: Essbase Initialization Failed, StatusCode = 1030803

This document explains that the "environment has to use the bin-32 directory to get 32-bit VB API programs to run on the 64-bit server."

There is a new directory that must be in the PATH environment variable!  As I am running the 64-bit Essbase server on my machine, I didn't want to change the PATH of the operating system on the chance it would break the Essbase server.  Instead, I created a cmd file to set the appropriate environment variables and run the Outline Extractor.  Here is the complete command file:

@rem This command file uses the 8.3 filename format.  
@rem The 8.3 filename may vary between machines, so 
@rem be sure to check the paths carefully.  Use dir /x
@rem to find the filenames in the 8.3 format.

set EPMHOME=C:\Oracle\MIDDLE~1\EPMSYS~1

set ARBORPATH=%EPMHOME%\common\ESSBAS~2\1112~1.0
set ESSBASEPATH=%ARBORPATH%
set ESSLANG=English_UnitedStates.Latin1@Binary

set PATH=%ARBORPATH%\bin;%EPMHOME%\bin-32;%PATH%

start C:\PROGRA~2\OLAPUN~1\ESSBAS~1.2\EXPORT~1.EXE 

Note that I used 8.3 filename format.  I quickly tried using double-quotes around long filenames to these directories in an attempt to make it run without the 8.3 filenames.  I didn't get it right the first time and didn't put any more time into it; the script with 8.3 filenames worked the first time.

A second thing we noticed about the Essbase 11.1.2.1 VB API is that the VB supporting files, most notably esb32.bas declarations file that defines the functions in the dll's, do not appear to be in the distribution.  We did find esb32.bas in the samples, but they were from Essbase 9.3

Finally, due to the missing esb32.bas, we have not yet shipped an official version of the Essbase Outline Extractor for 11.1.2.1.  The likely case is that no declarations changed and we can just use the 11.1.2.0 version with an updated ESB_API_VERSION variable.  Until we do that, when you run the Essbase Outline Extractor with Essbase 11.1.2.1, you will see a dialog that warns you are using the incorrect version of Essbase.

If you are using the Essbase Outline Extractor for 11.1.2.0, you can ignore this warning and the Extractor should still run successfully.

Note: I will follow up with a similar post on how I got the classic Excel add-in working on my machine.

Strange ASO Update Behavior

We recently had a customer report an issue when writing back to an ASO database through Dodeca.  The issue involved writing back #Missing to cells that previously had a value.  Whenever the data was retrieved, the cell did not have a missing value, but rather the cell returned 0.   They also reported that updating the same cells using the classic Excel add-in behaved exactly the same way.   As it misbehaved in the classic add-in as well, we recommended to the customer that they file a service request with Oracle.

Today we heard the result from Oracle Tech Support:

“This is the expected behavior when you load #Missing cells through Excel Add-In or SmartView into an ASO application. The load operations through Excel Add-In loads data into an incremental slice using override mode. In this mode when you load #Missing values into cells that already have a non-missing value, they will be replaced with 0 values.“

Not many companies are writing back to ASO at this time and I wasn't yet aware of this behavior, but now we now another one of the limitations of ASO writeback.

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 API - Ephemeral Port Issues/Resources

One of the most popular posts on my blog has been the post where I showed how to open up the number of ports available to Essbase. This is necessary as the Essbase API is quite chatty and, when combined with the small number of available dynamic ports and the long default timeout on these ports in Windows, running out of ports has become an issue.  Most users see this when running many, many repetitive API calls against Essbase. 

One example is when users run the Essbase Outline Extractor against a large database.   Back in the old days when the Essbase API was designed, it probably wasn’t much of a consideration.  These days, however, with million member outlines and really fast computers, it is very easy to run out of ports.

We are currently testing some new Dodeca functionality we plan to introduce this summer that optionally performs some intensive caching of the Essbase outline.  Of course, we ran into the port issue very quickly and, in the course of looking at how the ports have changed in the new versions of Windows, found a couple of interesting links to pass along.

First, I found a nice link that explains how dynamic ports work.  Better yet, this link covers dynamic ports on non-Windows operating systems.  You can see this link at http://www.ncftp.com/ncftpd/doc/misc/ephemeral_ports.html.   This link also talks about the new, larger default dynamic port range in Windows Vista, Windows 7 and Windows Server 2008 which now feature a default range of 16,384 ports.

I also found a link to a Microsoft KnowledgeBase article that talks about the new defaults and shows ways to both display, and dynamically increase, the range of ports.  The KnowledgeBase article is located at http://support.microsoft.com/kb/929851/.   I ran the commands listed there on my Windows 7 laptop:

C:\Users\timt>netsh int ipv4 show dynamicport tcp
Protocol tcp Dynamic Port Range
---------------------------------
Start Port      : 49152
Number of Ports : 16384

I then increased the number of ports dynamically:

C:\Users\timt>netsh int ipv4 set dynamic tcp start=5000 num=60536
Ok.

C:\Users\timt>netsh int ipv4 show dynamicport tcp
Protocol tcp Dynamic Port Range
---------------------------------
Start Port      : 5000
Number of Ports : 60536

The changes appear to permanent.  I rebooted my machine and found the new configuration remained.

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.

Kaleidoscope 2010 Review

Kaleidoscope 2010 wrapped up a week ago Thursday and, not surprisingly, I haven’t had time to write about it until now.  Here is a summary of some of the highlights.

Community Service

The Community Service project this year provided labor to the Ronald H Brown Middle School in northeast DC.  The school is was named for a US Secretary of Commerce who was killed in a plane crash in 1996 in Croatia.  The ODTUG crew worked on refurbishing a playground, did landscaping and sorted books in the library.   Joe Aultman from AutoTrader, Jeff Sims from Tethsys and I worked on the basketball courts which literally had no lines or layout when we started.  Cameron Lackpour and my son Billy worked in the library and Whitney, my daughter, worked on the hopscotch and four-square areas with an EPM consultant I had never met before, Jessica Cordova.  Here is a picture (thanks to Larissa Stamey from ODTUG and Wayne Van Sluys for the picture).

For what it is worth, I placed the basketball in the center of the circle for this picture (and laid out the lines and painted much of the blue court.  I am standing on left end just in front of my daughter, Whitney.  My son, Billy, who labored for weeks on the Windows 7 installation blog, is standing just in front of the guy holding the letter 'B'.

Sunday Symposium

The Sunday EPM Symposium is one of the unique things about Kaleidoscope as it is the only forum where customers and consultants can meet the Hyperion development teams face-to-face.  There was some great exchanges of information but, of course, as Oracle was talking about futures, everything presented came with heavy disclaimers.

During the Symposium last year, the product manager from Smart View held an impromptu ‘tell me why you like the classic add-in better than Smart View’ session that lasted nearly his entire allotted time.  From his presentation this year, it is very apparent that he listened to the feedback.  The Smart View team is working hard on classic add-in parity and are they are starting to get Smart View much closer to the point where it will gain more widespread acceptance.

Keynote / General Session

The General Session had a huge announcement for us Hyperion people as one of our own, Edward Roske from interRel, is the Conference Chair for Kaleidoscope 2011 in Long Beach.  Edward has worked very hard on the previous conferences and has thus earned his way to a very tough job.  For us Hyperion people, however, having a Hyperion person as the Conference Chair shows the importance of Hyperion in the ODTUG world.   I expect that next years conference will have even more Hyperion content and will be even more of a can’t miss event.

The keynote was by Lee Rainie, Director of the Pew Research Center's Internet & American Life Project.  Lee discussed some very interesting facts gathered from their research.  One interesting trend he discussed is ‘lifelogging’ where people document everything they do on-line.  I instantly recognized my daughter follows this tendency and, if fact, we always kid her about her natural ability to take pictures of herself with her iPhone for her Facebook page.  I encourage you to read the findings at http://www.pewinternet.org/Presentations/2010/Jun/The-Future-of-the-Internet--National-Geographic.aspx. This presentation features the same content contained in the keynote

Sessions
I normally don’t get the opportunity to go to many sessions other than my own and this year was no exception.  I did get to attend the Smart View sessions on Thursday and they were all very good and thought provoking.

I gave three presentations in DC as well.  The first session was a vendor session which highlighted the features and benefits of Dodeca.  It was well attended and we gave away a bunch of Dodeca T-Shirts at the end.

The second session was a new presentation intended to educate Oracle database developers about Essbase and where it fits into the enterprise.  This was a fun topic and I think it is becoming more relevant with each passing day as Essbase gets integrated deeper into the Oracle stack.

The last session was a fill-in session on the Java API.  My friend Jason Jones was scheduled to do the session but, at the last minute, was unable to attend.  This session was the most fun for me as I really dig into Essbase at the API level and foster new understanding of how Essbase works ‘under the covers’ to even experienced Essbase people.   A number of people of have asked for the sample code from this session; I will make it available on our website soon.

Overall / The Future

This Kaleidoscope was the best one yet but I still expect it to be bigger and better next year, but there is still a lot of work to do.  Many people, including me, still long for the days of the Hyperion Solutions conference.  The Solutions conference had the advantage of being run by the vendor, so the marketing department could devote many dollars to make sure it was ‘the place to be’ for Hyperion people.

In the Oracle world, the comparable conference is Oracle Open World but, as Hyperion is a small percentage of Oracle as a whole, Open World will never have the Hyperion focus that Solutions had or that Kaleidoscope has today.  Kaleidoscope, on the other hand, is run by the Oracle Development Tools User Group which means that it is completely controlled, and funded, by the users.  That means you can make difference on the direction of this conference.  To help make Kaleidoscope even better, I plan to run for the ODTUG Board of Directors in order to provide Hyperion users a voice on the governing body.  Elections are coming up in the fall and paid members are eligible to vote.  I encourage everyone to join, participate and please vote for me for the Board of Directors!

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?

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.

MethodName	Cube	Member Selection	Outline
getAggregationLevelUsage	x	x	x
getAssociatedAttributes			x
getAttributeAssocLevel	x	x	x
getAttributeMemberDataType	x	x	x
getAttributeValue	x	x	x
getChildCount		x	x
getChildMembers			x
getConsolidationType	x	x	x
getCountChildMembers	x	x	x
getCountOfDupMemberNameInDim*
getCurrencyCategoryOrName		x	x
getCurrencyConversionType		x	x
getDescription	x	x	x
getDimensionCategory		x	x
getDimensionName	x	x	x
getDimensionNumber	x	x	x
getDimensionSolveOrder	x	x	x
getDimensionStorageCategory		x	x
getDimensionStorageType		x	x
getFirstChildMemberName	x	x	x
getFormatString	x	x
getFormula	x	x	x
getGenerationNumber	x	x	x
getHierarchyType*		x	x
getLastFormula	x	x	x
getLevelNumber	x	x	x
getLinkedAttributeAttachLevel	x	x	x
getMemberComment			x
getMemberId	x	x	x
getMemberNumber	x	x	x
getMemberType*
getName	x	x	x
getNextSiblingMemberName	x	x	x
getOriginalMemberName	x	x	x
getParentMemberName	x	x	x
getPreviousSiblingMemberName	x	x	x
getPropertyDataTypes	x	x	x
getPropertyModes	x	x	x
getPropertyNames	x	x	x
getRelatedMemberNames			x
getRoot	x	x	x
getShareOption		x	x
getSmartList*
getSolveOrder	x	x	x
getTimeBalanceOption		x	x
getTimeBalanceSkipOption		x	x
getUDAs		x	x
getUniqueName		x	x
isAttributesAssociated	x	x	x
isClientCachingEnabled	x	x	x
isDimensionRootMember	x	x	x
isExpenseMember		x	x
isFlowType	x	x	x
isIndependentDim	x	x	x
isInitialized	x	x	x
isMemberNameUniqueWithinDim*
isNameUnique		x	x
isRelationalDescendantPresent	x	x	x
isRelationalPartitionEnabled	x	x	x
isTwoPassCalculationMember		x	x

* 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.

Essbase 11 Formatted Values, Text and Date Measures

There was a question on the Network54 recently about formatted values in Essbase 11, so I thought I would post a little info from 'under the covers' with formatted values and how they relate to Text Measures and Dates.

First, Essbase does support formatted values in Essbase 11. You can see in this screenshot from EAS, the format string is a property of the member.

The format string is in MDX format and there seems to be no valid examples in the docs of how to actually do a format.  Thanks to the beauty of the internet and the hard work of Venkat at RittmanMead Consulting, I found an example on their blog.  In the screenshot below, you will see the result of the formatted value:

Under the covers, there is a setting at the API level that determines if the formatted values are retrieved from the database.  Uses of Smart View control this setting using the 'Format String' option in the Options dialog.

The same setting also governs whether Date and Text Measures are returned as either the formatted values or as numbers (which are what are stored in the database).   Here is a screenshot of how the Java API returns formatted values with both the formatted values returned and the the formatted values not returned:








The value circled in yellow is a formatted value; note that the number below is the numeric value (which can also be returned in the same retrieve at the API level).  The value circled in green is a Date measure; I don't think my date values are valid though.  It seems limited that the earliest date supported is January 1, 1970 (and I think I used Excel dates to fill the cell which starts at January 1, 1900.

The values circled in red and blue are Text Measures.  Text Measures get their values from Smart Lists.  Here is the EAS editor showing my simple Smart List:















OK, so it says 'Text Lists' in the dialog, but if you look at the API level, you see Smart Lists:

Now you probably know more about Formatted Values, Text and Date Measures than you ever cared to know.

The Essbase API Can Sometimes Be Very Touchy

I am working on something this morning with the Java API and found something to be a bit touchy and, of course, thought I would share it in case someone else runs into something similar.  Specifically, I was using the IEssCube.queryMembers() method which very quickly returns member information.  This function is the Java version of the VB API EsbQueryDatabaseMembers and C API EssQueryDatabaseMembers functions.

Those API calls use a query language that is similar to, but different than, Essbase report scripting language.  In this scripting language, spacing matters.  Here is a screenshot of something that didn't work:

Note there is no space between the FORMAT and {MBRNAMES} clauses in the query.  I added a space and here is the result:

In this case, the API correctly returned the ancestors of Connecticut.

Lesson: Be aware that spaces can be important, in some places, when working with Essbase.

One Good Reason to Stick with Supported Versions

I have been using Essbase for over 14 years now and have got it to run just about anywhere. Sometimes it took a few tweaks and sometimes, such as in getting it to run on Windows 7, is quite a bit more involved. Still, I got it working.

Recently, there was a post on the Network 54 board about a problem getting EAS 11.1.1.3 running on Windows 2000 Advanced Server:

http://www.network54.com/Forum/58296/message/1253831592/Essbase+11.1.1.3+configuration+issue

Typically, I wouldn't expect this to be a problem but it was. We did a bit of research and found that one of the dlls, jmaxl.dll, called a function in the operating system that apparently did not exist in Windows 2000. When you see this, there is basically nothing you can do.

Last week, we saw a similar issue with 11.1.1.3 while doing QA on the Olap Underground utilities. We found the Essbase VB API for 11.1.1.3 does not appear to initialize properly on Windows 2000. What that means to Olap Underground users is that those products will no longer work on Windows 2000 if they upgrade to 11.1.1.3.

Little Nitpick on VBA - Continued

Here is a comment on was posted on my 'nitpick' blog post by 'Jared'; I thought it would be better as a separate post:

Speaking of lost productivity...one thing that has thrown me off in VBA (with Essbase or not) is the syntax for calling functions and procedures--when to use parentheses or not. For example, every Essbase VBA programmer has used EssVRetrieve, probably in the format:

dim sts as long

'Retrieve data from the current sheet
sts=EssVRetrieve(Null, Null, 1)

The function runs and its return value is assigned to the variable sts.

Now, in some cases, I want to call functions and I don't care about a return code. Or, I want to call a procedure (which you cannot assign to value, of course). But if you do this:

EssVRetrieve(Null,Null,1)

...or this, for example:

CheckCellValue(Activesheet.Name,
5, FALSE)

...you'll get a syntax error. And since function syntax in most documentation includes the parentheses, you may have a hard time figuring out the error. Even the auto-complete in the VBA editor seems to indicate that the parentheses are OK: type a left paren after the
function/sub, and it will list the arguments for you, as if everything is just fine.

Unfortunately, it isn't. :) So the rule is this: only use parens with a function call if you are assigning a value to the function; and if it's a sub procedure, you never use parens. So you would have:

EssVRetrieve Null,Null,1 'Retrieve data, don't worry about return code

...or:

CheckCellValue Activesheet.Name, 5, FALSE 'Call "CheckCellValue" proc, passing parms

Hope this isn't too much of a derail, but it just came to mind...

Not too much of a derail at all Jared as I think it will be useful information for a number of blog readers.