Essbase is often described as getting its start in finance departments. It was finance’s secret weapon. There are a number of ways that Essbase tends to have evolved organically from this foothold. One common evolution is for the tool to gain critical importance to a company, and need to be more robustly managed by the IT department (in theory, anyway, I’ll save my thoughts on this common tragedy for another time).

One of the other common developments for Essbase in the finance department is that certain books/sheets evolve more and more functionality over time. They get augmented with automation, macros, VBA code modules, and more. This is all often on top of complicated formula references across sheets, and sometimes even across different books.

For example, a pretty common scenario for a company is that there is an Excel reporting book that is used for daily/week/monthly/yearly/whatever reporting. So you see this a lot:

1. Open worksheet
2. Change Time/Year members on sheet(s)
3. Connect to Essbase
4. Retrieve sheets to update
5. Validate (you make sure it’s right, right?)

Over time there tend to be some extra one-off steps that are incorporated into this process, such as the need to:

• Copy/paste cells
• Incorporate data from additional sources/systems
• Sort/filter data

What happens is that many enterprising analysts and organizations find ways to automate this process. They combine the Hyperion VBA module, a lot of glue code, and generally a significant amount of development. The end result is often pretty slick – on the surface: a semi-automated reporting system that streamlines a manual, error-prone, and time-consuming process.

With Great Spreadsheets Comes Great Risk

People are usually pretty excited at this point: the reporting process is quicker and easier. The value of Essbase has been enhanced. There is more time for other important activities. The code is still fresh in mind.

Over time, things change. Requirements change. The code changes. People and departments change. Quite often, the person that wrote the original and highly complex automation code moves on.

“Well, Chuck originally wrote this book, he was a real wizard with all of this stuff, but he quit a couple of years ago so we just kind of tweak it here and there but we don’t really know how it works. If this ever breaks, we are screwed.”

Others try mightily to wade through the byzantine code and put some duct tape on. Very often in this scenario, a company also makes a transition from the classic Excel add-in to Smart View. Many companies are forced to try and upgrade the solution (which in many cases was never an “official” solution to begin with), to the completely different Smart View API. Many of these solutions don’t ever get migrated – they get thrown away since it is just too much effort and risk to maintain or convert.

The Best Code Is No Code

I talk to a lot of people that are looking for a way to extend the life of their in-house VBA reporting/analysis solutions. And sure, you can use the Dodeca Excel Add-In VBA translation module as a drop-in solution to do just that. This can be a good stopgap solution. But it can be better.

What I so often tell people in this situation is to try not think “How do I replace this code?” – and instead think more holistically about the solution. These solutions also tend to have a huge amount of the verbose, repetitive, low-value, confusing and undocumented code that does things like:

• Gather credentials
• Connect to Essbase
• Solicit member selections from the user
• Retrieve one or more ranges on one or more sheets
• Receive user input
• Run a calculation

As it turns out, these are all tasks that Dodeca handles out of the box – with no code. You can simply define a named range on an Excel template and have Dodeca handle all of the details of connecting and retrieving. You avoid tons of complex logic trying to get member selections from the user (or relying on them to key them in manually, correctly) with Dodeca’s flexible selectors, data input is handled just as easily as retrieves, and custom calculations are simple to run – all just by configuring a few options on a Dodeca Essbase Excel view, and again, with no code.

Perhaps more importantly, since Dodeca centralizes the spreadsheet templates and reports, you don’t have to worry about distributing Excel books all over the place such as with email, and you don’t have to worry about syncing up changes and making updates.

I love laying down some code as much as the next person, but perhaps more than that, I like developing effective solutions that are high quality and give my users what they want. And Dodeca’s Essbase functionality and spreadsheet paradigm delivers over creaky VBA solutions any day of the week. To borrow an analogy – don’t try and build a faster horse: get a car.