Classic Excel Add-in on 64-bit Windows 7

As I mentioned in my earlier blog post about getting the OlapUnderground Outline Extractor running on Essbase 11.1.2.1, the classic Excel add-in did not work on my laptop.   In my case, when I attempted to login to Essbase, the Essbase Login dialog did not display.  As a result, I could not connect to Essbase.  My laptop behaved like this for a number of months and, frankly, I didn't miss the add-in that much.  Dodeca has an add-in mode where I can do everything the classic add-in does and more.  Still, I wanted the classic add-in to run. I often use the classic add-in to create report templates for deployment in Dodeca.

Recently, when my QA team was working on support for the Outline Extractor in version 11.1.2.1, they discovered a new requirement for the PATH environment variable.  There is a new directory, the bin-32 directory installed under the EPM home directory, that is now required for the Essbase client API's to work.  This directory appears to contain dll's related to Oracle security.  The solution to get the classic add-in running on my machine was to add this new directory to the PATH environment variable.

I have an additional complication on my laptop as it also runs my Essbase server.  Needless to say, I am very careful when considering changes to environment variables.  I decided to create a command file to temporarily set the environment variables and then launch Excel.  Now, when I launch Excel using the following command script, the Essbase add-in works correctly.

set EPMHOME=C:\Oracle\MIDDLE~1\EPMSYS~1
set ARBORPATH=%EPMHOME%\common\ESSBAS~2\1112~1.0
set ESSBASEPATH=%ARBORPATH%

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

set XLL_LOCATION=%EPMHOME%\products\EXCELA~1

start "C:\Progra~2\MIF5BA~1\Office11\EXCEL.EXE" %XLL_LOCATION%\bin\essexcln.xll

I find it more dependable to use the 8.3 filename format when creating command scripts.  Keep in mind that the 8.3 filenames may be different on your machine.

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.

We finally got out and did something here in the beautiful city of Sydney.  Yesterday, Darlene and I did the Sydney Harbour Bridge Climb with Mark Rittman and Stuart Bryson from Rittman Mead along with Stuart's wife Pamela.  Here is a shot from the top of the bridge.

After the climb, we met up with Ash Beauman from Rittman Mead Australia for a few beers and even some late night clubbing around The Rocks area of Sydney.  Of course, we had plenty of time to discuss OBIEE and how Essbase fits into the picture; I guess we are all true geeks.

Later, I pulled an all-nighter to go on-line and watch my Michigan Wolverines play Iowa; I didn't like the result.  Today, it is off to Manly Beach to explore another part of Sydney.

NZOUG / ODTUG SP Sydney Wrapup

It is Friday afternoon and my busy week of speaking is over.   It started out in Auckland, New Zealand at the NZOUG conference.  It was a great conference and the special event, a dinner with an interactive mystery game, was excellent.  

Wednesday, we flew to Sydney for the ODTUG SP Conference.  The flight was on Qantas and, due to the airline shutdown earlier in the week, we had to check-in for the flight 2 hours ahead of time or risk losing our seats.  Qantas also enforces a weight limit of about 16 pounds per carry-on bag.  We didn't know that until we tried to go through security and we were sent back to check-in to check one of our carry-on bags.  Once airborne, however, the flight was great and I relaxed, watched the movie and enjoyed the meal service.  They even brought ice cream bars for dessert!  As we approached Sydney airport in the plane, we had a great view of the harbor, the Sydney Harbour Bridge, and the famous Sydney Opera House.

The SP Conference was well attended in both the BI and the EPM tracks.  I participated in the keynote where I got to tell everyone about the new version of Smart View.  I also had the honor of introducing the Oracle Fusion Accounting Hub.  This new product shows Oracle's deep commitment to Essbase and I will be doing a blog post with more details in the near future.   

My first session in Sydney covered Essbase techniques in Excel.  This session first discussed the new version of Smart View and I threw in some live demos.  The new Smart View brings near-parity to the classic Excel add-in and thus makes available a number of techniques that did not work with the older Smart View versions.  I also covered how Essbase 'Query-by-Example' grid layouts work and, based on the applause at the end, the session was very well received (or the attendees where very polite).

My final session was this afternoon and focused on Essbase API tips and tricks.  The SP conference is now complete and it is time to relax for a while.  I have been in Australia for 48 hours and haven't seen the famous Sydney Opera House yet.  It is only 2 or 3 blocks from our hotel, but I haven't really been outside at all.  We are headed out for dinner tonight and will be staying in Australia until next Wednesday, so I am sure I will see it soon.  

Tomorrow morning, we are doing the Sydney Harbour Bridge Climb with Mark Rittman and Stewart Bryson from Rittman Mead.  We will certainly see it then.

Speaking of Mark Rittman, he tweeted about the dangerous animals featured on the wall displays leading to customs in Sydney Airport.  At lunch yesterday, he pulled up a 'Top 10' list of Australia's most deadly animals.  I guess there are some nasty creatures here; the Great White Shark is only the 9th most deadly animal on the list!   I will be sure not to let down my guard.

Outrageous..

New Zealand is a beautiful country and we had a wonderful time on the weekend visiting the Coromandel Peninsula.  I am now at the NZOUG conference, but I am outraged and need to let you know one thing in case you ever come here to visit.  Leave your internet devices at home and don't plan to communicate unless you are prepared to empty your pocket. 

I am staying at the conference hotel, The Pullman, where internet connectivity is $28.50/day/device.   I was told I could upgrade to have access to the 'The Club' level for $40/person/day and that I would have unlimited internet.  Given the fact that Darlene and I have 3 devices between us, and for the free snacks and drinks in the Club level, I decided I would upgrade us.  

When I connected, it seemed strange that I was being prompted to login and agree to the $28.50/day even though I had the 'Club' status, so I went to the front desk.   I was getting charged for both the club and the daily rate.  What?  Apparently, the clerk at check-in told me about the free internet in the 'Club' level but failed to mention that the internet was free only when you were on their computers in the 'Club'.  So, with my devices *and* the 'Club', I was being charged $165/day for internet access.  That is actually more than I paid for the room!  On top of that, I had paid an extra $300 this month just to have phone/3G service on my iPhone in New Zealand.   The hotel manager did agree to cancel my 'cCub' level access and remove those charges which was a nice gesture, the the idea that we are still paying nearly $90/day for internet access that drops when you move from room to room has left a bad taste in my mouth.

On a side note, on our first day in New Zealand, we stayed next door at The Quadrant hotel.   I liked the rooms better and they had free internet.  Next time, I will stay there the entire time. 

OK..  I feel better now..   Besides, Edward Roske is here with his wife; he told me they have 4 devices!

The Limits of SSPROCROWLIMIT are History

At Open World, in light of the announcement of Exalytics, I suggested during a panel discussion that the limit for SSROWPROCLIMIT be raised.  In case you are not familiar with the setting, let me tell you about it and its implications.   SSPROCROWLIMIT is an Essbase config file setting that controls the maximum number of spreadsheet rows Essbase processes on a Spreadsheet Add-in request. It is used only when suppress missing rows is turned on.  The rows subject to the limit are counted before suppression.

The problem with this setting is the small number of rows allowed, as the default value is 250,000 and the maximum value is 500,000.  That probably wasn't a bad limit when this setting was introduced in the very early days of Essbase.  At that time, I was running a fast Pentium machine with 1 Mb of RAM and 2 1Gb SCSI drives.  At that time, 500,000 rows was huge.   Now, with Exalytics and 1 TB of RAM which is, if I did the math correctly, more that a million times more memory than my old Pentium, the same 500,000 row limit seems quite confining.  At the same time, Essbase outlines keep growing.  I have a customer who has 380,000 members in one dimension in a BSO database!  If they tried to zoom in, suppress missing, on this and any other dimension, Essbase would need an SSPROCROWLIMIT of 760,000 rows if the second dimension had only 2 members.   Further, the row limit enforced by this setting considers the number of rows before suppression. This means that it doesn't matter whether the query actually returns more than 500,000 rows or just a single row after suppression, the limit is used internally during the suppress missing processing.

I sent a quick email to the Essbase program manager after Open World and, like magic, he let me know that the latest Essbase version, 11.1.2.1.102, has removed the upper limit.  Sure enough, I checked the readme.html for the new patch and here it is:

New Maximum for SSPROCROWLIMIT

(11730559)

The Essbase.cfg setting SSPROCROWLIMIT no longer has a maximum value of 500,000. The maximum number of spreadsheet rows Essbase processes on a request can now be set to 16,384 or higher. It is not recommended to use a limit higher than 500,000.

I haven't tried it yet, but I am sure I will in the near future.   I know a number of customers who will be excited about this change.