Little Nitpick on essxlvba.txt

I was working on answering an OTN question today (and creating a related blog post) and I came upon a little annoying thing in the Extended Spreadsheet Toolkit declarations file, essxlvba.txt.

I imported the file into VBA like I *used* to do years ago when I made a living writing Essbase Excel VBA code (and before I was fed up with it and started my own company to do things better). I used one of the essxlvba.txt constants, EssBottomLevel, in the following line:

v = EssVGetMemberInfo(Null, "Year", EssBottomLevel, True)

As always, I was using Option Explicit at the top of my module and it caught that this line of code would not compile. Why? Because EssBottomLevel was not defined. Of course, it was in the imported module:

Const EssChildLevel = 1
Const EssDescendentLevel = 2
Const EssBottomLevel = 3
Const EssSiblingLevel = 4
Const EssSameLevel = 5
Const EssSameGenerationLevel = 6
Const EssCalculationLevel = 7
Const EssParentLevel = 8
Const EssDimensionLevel = 9

What was the problem? In VBA, the above declaration limits the scope of the variable to that module. As I was using Option Explicit, that didn't present a problem to me as VBA alerted me to the issue. What about the (majority of?) VBA programmers who don't use Option Explicit? They, of course, would have a bug in their code and would have to search for the reason they didn't get 'bottom level' members returned.

The easy solution would be for Oracle to simply change the declaration to expand the scope; you can do this in your essxlvba.txt file today:

Public Const EssChildLevel = 1
Public Const EssDescendentLevel = 2
Public Const EssBottomLevel = 3
Public Const EssSiblingLevel = 4
Public Const EssSameLevel = 5
Public Const EssSameGenerationLevel = 6
Public Const EssCalculationLevel = 7
Public Const EssParentLevel = 8
Public Const EssDimensionLevel = 9

I wonder how many thousands of hours of lost productivity by hapless programmers can be attributed to this oversight?

Essbase VBA toolkit: Getting different alias tables via EssVGetMemberInfo

There was a recent post on the OTN forum asking this simple question:

This code is getting a default alias:

at = EssVGetMemberInfo(Null, "Product", EssBottomLevel, True)

How do I get a 2nd aliase?

There is a pretty simple answer to this question. Essbase can only have one alias table 'active' at one time for a particular connection and the EssVGetMemberInfo function uses that alias table. Thus, to get the aliases from a different alias table, you need to set the alias table programmatically. Here is an example of how to do it:


Sub GetAliases()
Option Explicit

Sub GetAliases()
  Dim v As Variant
  Dim v2 As Variant

  ''' set alias table to default
  EssVSetSheetOption Null, 14, "Default"

  ''' get the aliases
  v = EssVGetMemberInfo(Null, "Product", _
      EssBottomLevel, True)
  v2 = EssVGetMemberInfo(Null, "Year", _
      EssBottomLevel, True)

  Stop ''' look at the arrays here; product has aliases

  ''' set alias table to default
  EssVSetSheetOption Null, 14, "Long Names"

  ''' get the aliases
  v = EssVGetMemberInfo(Null, "Product", _
      EssBottomLevel, True)
  v2 = EssVGetMemberInfo(Null, "Year", _
      EssBottomLevel, True)

  Stop ''' look at the arrays here; year has aliases
End Sub


When we engineered Dodeca, we looked at how people used different alias tables and included an alias table argument where appropriate in order to make it easier.

Scalability and the Essbase Java API

During my Dodeca webcast a couple of weeks ago, someone asked a question about the typical number of servers necessary for a Dodeca deployment. Dodeca is quite resource friendly on the server due to it's architecture and thus the answer is 'Less than you would expect'.

One reason is that we use the Essbase Java API on the server. The Java API was designed from the ground up to be a highly scalable and highly dependable API layer to be consumed by both Hyperion applications and by third party applications. We did extensive scalability testing of our own on our Essbase services. The most extreme test ran 25 concurrent threads constantly on an old underpowered server we had sitting around. The test was intended to simulate approximately 500 users assuming the usage pattern is that the user is querying the database 5% of the time and analyzing the results of the time in the application. We left this test running for 5 months in a single instance of Tomcat with the following results:

Number of requests serviced - 204,043,599
Hours of processor time - 2237:07:28
RAM used - 66.2 Mb

Nearly a quarter of a billion transactions in a single Tomcat instance.. I remember 15 years ago when the Excel add-in wouldn't do more than 50 or 60 retrieves before it would sometimes crash. Now that we have faster servers, I am thinking perhaps we should go for a billion transactions in a single instance!

All of this ties in very nicely with the functionality I am working on currently for Dodeca. In a near future version, we expect to ship functionality that will allow administrators to capture the queries, calcs, etc. that users are calling when they use the application and use that input to run their own stress testing on their server using their own data and usage patterns. Let me know if you think this functionality would be useful in your environment.

Dodeca Shines on the Clouds

This week was a big week for Dodeca on the cloud as there were three big cloud happening for us.

Thursday, Dodeca was featured in a public webcast hosted by EPMCloud.com and during the webcast, I demoed Dodeca running on an EPMCloud instance. We are planning to have this webcast on a regular basis. One unique thing about the cloud concept is that it also enables new and creative ways for interactive webcasts. Our friends at EPMCloud have asked if we would be interested in a Dodeca Hands-On Lab webcast where qualified participants would each get their own Dodeca cloud instance to work with. Would you be interested in participating in a Hands-On Lab? If so, contact EPMCloud and let them know! If you can't wait until then, you can request a EPMCloud Dodeca Sandbox on the website.

Another cloud partner, Full 360, also notified us that they are working on their first cloud-based Dodeca implementation. They share our vision of the availability of Dodeca on the cloud as an enabler for Essbase implementations at the tremendous amount of companies which were formerly considered too small for an Essbase application.

Finally, we worked with another traditional Hyperion partner this week to install Dodeca on one of their Amazon cloud instances for internal training purposes. I recently sat down for a couple of hours with some of their consultants and gave them a thorough demo of Dodeca's capabilities and, in particular, our workbook scripting functionality. Workbook script is essentially a codeless replacement for VBA. During that demo, I wowed them with the power of both workbook script and it's event model by showing them how they could implement a workbook script to dynamically format an Essbase adhoc report. Of course, that very exercise is impossible in Excel with either the classic Essbase Excel add-in or Smart View as they do not have, nor do I expect they will ever have, Essbase events. In workbook script, it was a five minute exercise. Look for an upcoming press release announcing this new partner along with a number of other partner announcements as well.

BI Challenge 2 Go Image Results

A few months back, I made a blog entry about the availability of the 'BI Challenge To Go', or BIC2G image that is available to Oracle partners from fellow Oracle partner Vlamis Software Solutions. We bought the 'Microsoft VHD Image with Evaluation License' edition of BIC2G and have been using it for several months. This edition of the BIC2G comes with a Windows evaluation license that expires after 30 days. My first concern was "What do I do after 3o days? Do I have to buy another image?". The answer to that is absolutely not. You can copy the evaluation version onto any number of computers and it doesn't violate the Vlamis, Oracle or Microsoft license agreements.

Our first use for BIC2G was to provide a base image for the Hyperion Hands-On Labs at Kaleidoscope 2009. This Using BIC2G, we were able to configure 25 laptops with the full Hyperion suite and sample applications in a matter of a few hours. Without the BIC2G image, it may have taken hundreds of hours to configure the laps for the class.

We are also now using the image internally to test our Dodeca product with the full Oracle suite of products. It has saved us, again, hundreds of hours of setup and configuration especially with the applications we don't use often.

Oracle is still restricting the BIC2G image to Oracle partners but I am continuing to lobby for this image to be made available to all customers.

Excel Add-in Question

I got this question from someone who saw my blog and thought it would be a good blog post:

"I hope this isn't a dumb question but it seems like if one user sends another user a spreadsheet with the Essbase add-in, it somehow installs itself. Then is a total mystery how to remove. It's not in Add-Remove programs or program files that I recall. Regedit? Any ideas?"

Due to the architecture of the Excel Essbase add-in, the add-in cannot possibly install itself from within an Excel workbook.. In fact, most people have trouble installing the add-in using the installer program as it requires admin privileges to install. That being said, one possibility is that the Excel add-in is already installed on your machine and the Excel workbook simply contains a Workbook_Open() macro that is setting the Excel AddIn.Installed property to true (to assure it is available).

If you have a Workbook_Open() macro, you used to be able to bypass it by holding down shift when you open the workbook. You could then go to the VBA editor (Alt-F11), choose the Workbook object and delete (or comment out) the code.

Kaleidoscope Sunday Symposium - Essbase Roadmap Part 2

Here is the remainder of my ODTUG Technical Journal article. As before, the standard Oracle disclaimer applies:

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Smart View Improvements

There are a number of improvements and, in fact, architecture upgrades coming for Smart View in the near future. The major improvements Oracle is publicly talking about for 11.1.2 include:

• An improved installation experience. The installation of Smart View will continue to be a standalone installer and, starting in 11.1.2, will not be packaged with the EPM installer. Further, satellite .dlls will be available for non-English language support.
• A plug-in architecture. The plug-in architecture eliminates the need for the Smart View team to create and control all new features put into Smart View. Further, the architecture is designed to ensure that new add-ins can peacefully co-exist and access Smart View’s features. Finally, in the future, Oracle plans to open up the plug-in architecture to partners, and I am sure that a number of partners will leverage this platform to create their own tailored functionality.
• An improved Connection Manager. The connection manager will talk to Shared Services, as well as explore all registered providers.
• Deeper integration of Office 2007 Ribbon technology. Oracle is planning to architect the Ribbon UI technology such that each provider will have its own context-sensitive Ribbon.
• Better EPM Application integration. Smart View will become even better integrated with both Hyperion Planning and Hyperion Financial Management. On the Hyperion Planning side, Smart View will reach ‘Web Parity,’ where all features available in Planning web forms will also be available in Smart View, including composite form support, task lists, and data validation. On the Hyperion Financial Management side, Smart View will add support for task lists, updates to the database, and XBRL (eXtensible Business Reporting Language).

These are all exciting improvements to Smart View that will certainly make it more popular with users.

The Future

I was surprised that Oracle actually discussed some of the things they are working on post-11.1.2 and the discussion emphasized the feature list is still to be determined, but they did talk about some directions that are very exciting. One overriding theme familiar to anyone following the Hyperion/Oracle is integration. Integration continues for both the OBIEE/Essbase integration and the integration of the Hyperion Applications and Essbase into the Fusion Middleware Stack. To accompany those integrations, Oracle expects to further evolve Essbase web services to make Essbase information more widely available as part of their SOA architecture.

One the pure Essbase side, the two most significant items include a thin client Essbase studio that is targeted to replace Essbase Administrative Services, Essbase Integration Services and the thick client version of Essbase Studio. Finally, IT departments will be happy that Oracle is targeting native failover for both the Essbase Agent and Essbase Server processes are part of their future plans.

I feel as though I have only scratched the surface when it comes to the amount of information presented at this year’s Kaleidoscope Hyperion Sunday Symposium and, as I think you can see, Essbase is not only alive at Oracle, but thriving. If you missed the Symposium this year, mark your calendars for late June next year, as we hope to make next year’s the best Symposium yet.