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.

Kaleidoscope Sunday Symposium - Essbase Roadmap Part 1

This is the first half of an article I wrote recently for ODTUG Technical Journal; I will post the remainder in the next day or so.

The Hyperion Symposium at Kaleidoscope 2009 was one of those sessions that every Hyperion professional needed to attend. The focus of the Symposium was the new and exciting features that Oracle/Hyperion development is working on for the future. Due to economic conditions, it was difficult for many members to attend Kaleidoscope, so this column will focus on the some of the new features expected both in the Essbase engine and in the further integration of Essbase deep into Oracle Applications. If there was one clear message at the Symposium, it was this: Essbase is a key strategic technology for Oracle Applications and Oracle is investing heavily in the technology.

As always, the Oracle slides were all marked with the following disclaimer, which basically means that the features I am discussing are subject to change before they ship:

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.

The roadmap, as presented, provided details for 3 upcoming versions of Essbase:

• 11.1.1.3
• 11.1.2
• 11.x.x.x (future)

Essbase 11.1.1.3 is considered a minor release, but it brings with it a significant advancement in Fusion integration. This version introduces the concept of built-in drill back to Fusion Financials via pre-mapped pathways, using Smart View. It is due to be released soon (although Oracle is quite firm in not stating ‘release dates’ until they actually happen).

Essbase 11.1.2, known internally as ‘Talleyrand,’ is a major release. This release boasts a large number of improvements coming to the Essbase engine and toward integration into the Fusion application stack. There are also a number of improvements in process for Smart View.

Essbase/Fusion Integration

It appears that the integration of Essbase into the Fusion application stack begins with the ability of the next version of Fusion Financials, i.e. Oracle Financials/E-Business Suite, to use Essbase for custom calculations and allocations. Thinking back to my days as an accountant, I can remember the tremendous complexity involved in some of our allocations; those allocations literally begged for a multidimensional solution. All Essbase users will benefit from this innovation, however, as the Essbase Aggregate Storage Option engine, known simply as ASO, will be getting outfitted with the first parts of a procedural calc engine. Procedural calcs are currently available only in the Essbase Block Storage Option, or BSO, engine. This new functionality will support specific calculation functions against ASO applications and will store results back in a slice of the cube. The calc functionality will be exposed in the C and Java APIs, as well as the MaxL scripting language.

Currently, most Oracle DBAs use Oracle Enterprise Manager to manage their databases. Starting in 11.1.2, many Essbase management functionalities will be incorporated into Enterprise Manager as well. Also Slated for inclusion are Oracle Single Sign-On and security integration, Oracle Diagnostic Logging, and the beginning of Oracle Process Manager and Notification Server (“OPMN”) integration. OPMN integration will add built-in, industrial strength failover to the Essbase stack that has been needed for many years, setting the stage for future innovations in Essbase reliability.

Another integration feature coming in Essbase 11.1.2 is the ability to access Essbase data via Oracle PL/SQL. For those of you from the Hyperion community, PL/SQL is Oracle’s proprietary procedural extension to SQL, targeted specifically at the Oracle database. This new feature will allow a whole new audience of developers to access Essbase data with the tools they are accustomed to using, promoting the use of Essbase to a wider audience. Further, Oracle JDeveloper developers will get a new Application Development Framework (“ADF”) control for Essbase that will make integrating Essbase content into web applications much easier.

Essbase Engine Improvements

Outside of Fusion integration, there are a number of other improvements coming in 11.1.2. One change that will help nearly every customer is the removal of the Shared Services-to-Essbase security sync. Currently, customers manage users in Shared Services and must either manually synchronize users from EAS to update the Essbase security (.sec) file, or they must setup an automatic sync process in the Essbase.cfg file. Many times these syncs cause performance issues, especially in large environments where auto-sync is configured to occur too frequently. Starting in 11.1.2, user information will automatically be retrieved from Shared Services at login. The only downside to this is that any changes to user provisioning in Shared Services will require a logout/login to be recognized by Essbase. The security file (.sec) file will not go away entirely, though, as Essbase filter security will still be stored in that file.

Essbase 11.1.2 will also add support for Oracle SQL*Net, which should increase both increase performance, as well as add support for Oracle Real Application Clusters (“RAC”), to the data load and dim build processes.

Essbase Studio gets a number of 11.1.2 improvements, including:

• The ability to move, copy, and backup the import/export catalog.
• Improved data source handling, including the ability to add or remove a table or column.
• Better support for ODBC DSN source, including the ability to set parameters in the DSN.
• Named level/ generations.
• Custom Text List mapping.
• Essbase Studio/OBIEE integration at the Business Model tier (in addition to the Presentation Layer as in earlier versions).
• Accessibility support (Section 508).
• Improved performance and usability.
• MaxL load scripts for batch load.

This last new feature, in particular, will make automation much easier, as it essentially records a cube deployment, much like the Macro Recorder in Excel records keyboard and menu actions into a VBA macro. I do suspect that the auto-creation of load scripts will “lower the bar,” when it comes to the skill-set necessary to automate Essbase administration tasks. But as with Excel’s Macro Recorder, I expect that the auto-creation of load scripts will serve as a starting point for further optimization by skilled Essbase developers, as well as an excellent learning tool for those new to Essbase administration.

Finally, my favorite new feature slated for 11.1.2 is the addition of Essbase web services. This is something that has been sorely missing from the Essbase stack, and I personally look forward to leveraging them in our products in the near future.