Hyperion Essbase Knowledgebase

Essbase and VBA

User Rating:  / 0
PoorBest 

To start off with what is VBA?

VBA is the (free) scripting language for Excel and other Microsoft Office applications. Entire corporate reporting systems have been written in VBA that combine data from various systems, be they relational databases, olap databases, or even spreadsheets.

The easiest way to understand VBA is that it is the Excel Macro language.  Macros are typically recorded key strokes that can save time.  You record the macro once, then you can run the macro and watch it perform all the steps you recorded.

While this is one use, VBA is really much more powerful.  VBA is the programming language for the entire Microsoft Office Suite.  The code you learn by learning Excel VBA can be directly transferred into programming other applications like Word, Access, PowerPoint, and even Outlook.

Business Analyst: Why do I care about VBA?

VBA can do many things to make your job easier.  When you combine VBA with Essbase, suddenly you have a very powerful and flexible reporting system. 

Examples where VBA helps out:

-creation of a reporting system that cranks out hundreds of pages of reports that get published to the web or file share and even emails them, at the touch of a button.

-creation of controlled templates to send out to the field for data entry.  Users can email the workbooks back to corporate or place them on a file share.  In either case you need only perform a sannity check on the books then initiate an automated lock and send routine that sends the data to the essbase server.

-the creation of a template system where the field actually inputs data into an excel workbook that then sends the numbers to Essbase and initiates a calc script via VBA automations resulting in consolidated financials.

-the creation of automated emails from Excel is just 4 commands away.

Information system developer: Why do I care about VBA?

-by basing your development environment on Excel, you development task can be much faster.  Much of the functionality desired by end users is already built into Excel. You have a head start of designing an information system through the use of Excel and VBA.

Tradeoffs:

Advantages: no compiling of code.  VBA is an interpretive language.  All code is contained in the .xls, the Excel workbook.  Users must have the office application to run the VBA.  Since we're talking mostly about Excel, users must have excel to run the VBA.  This is good and bad.  You can be sure that when you send a .xls to a user, as long as they have the same version (most things are cross compatible, within a version or 2 is usually ok), you can be sure the code will run.  Contrast this with VB, where the code itself is relatively tiny, but the distribution can be quite large.  You also have to worry about versions of dll's and the like.

CIO: Why do I care about VBA?

Through implementation of VBA you can lower your total cost of ownership.

VBA developers are really cheap.  You can train them yourself, just start out with a VB developer.  VBA is a skill that has been taught in schools for the past 10 years.  It is a subset of Visual Basic.  This means that your VB developers would need only a week or so to become proficient in the develoment and maintenance of a VBA reporting system.  The only development environment you need for VBA is a desktop with the appropriate version of the Office application on it.

While VBA itself is not web centric, some of this web stuff is becoming overrated.  I have worked on a handful of large projects where it was thought users were going to enter their budgets entirely in a web interface.  When the usability tests were performed, if given a choice between a spreadsheet based template system and a web form where everything has to be retyped, 100% of the users kick and scream that they will not use the web forms.  In some cases, Smartview can be used to help alleviate users concerns.

Director of Finance:Why do I care about VBA?

VBA can make your business analysts much more effective.  Much time is spent compiling data that not much time is left over for analysis.  While implementing Essbase is a great start, there are a bunch of Essbase reporting systems out there that result in manual compilation of of reports in Excel.  Proper use of VBA can reduce your report generation time by hours and days.

Some pluses:

VBA skillset is not that pricey.  Most every graduate has seen it. The beauty is that it is easy to learn, but it can get as complex as you want it to. The reason VBA is discussed here is that Hyperion has provided a very powerful spreadsheet toolkit for automating Essbase / Excel retrieval tasks.  You can even build 1 workbook that generates your entire financial book at the touch of a button.  By using Excel as the reporting tool, you have the greatest of flexibility in terms of generating reports and making dashboards.

If you have a set of repetitive keystrokes you enter often, you can record the key strokes then replay them by assigning a hot key or running the macro.  From this simplistic example to full blown programming that interfaces with databases and the internet, VBA has many uses.

If you've ever recorded a macro in Excel then you have seen and used VBA.  Another great thing about VBA (for Excel) is that it is stored in the workbook itself which makes distributing your code very easy.

Additional information