Another Thanksgiving Email

Here is another email we got Wednesday from a Dodeca Excel Add-In for Essbase customer.  This customer has a lot of VBA macros running some automation with Essbase and asked for some assistance.  We told it was as easy as replacing they Essbase function declarations file with our Dodeca Add-In  function declarations file, and then setting the variables that contain the location for the Dodeca-Essbase server.  In other words, replace this file:

With this file:

Easy, right? Here is the email:

I hadn't had a chance to test this massive file out with the latest version of the add-in yet, but was more than pleasantly surprised when I replaced the add-in code for the Dodeca wrapper and the only thing I had to do was change the connection to our new server and the file was live! It's truly drop and go! Thanks so much!!!!

Essbase VB API is Officially Dead

It is with a sad heart that bring you the news that, as of Essbase 11.1.2.4, the Essbase VB API is officially dead.  I cut my teeth in Essbase working with that API way back in the mid-1990's, but the writing had been on the wall for some time.  Microsoft stopped supporting VB years ago, so it was only a matter of time before this time would come.  That being said, I haven't used the VB API for any new Essbase work since about 2001; the Essbase Java API is alive and growing so my efforts have been there.

Here is the official notification:

You can read the notification yourself in the Essbase 11.1.2.4 Readme.

VBA to Create Sample Budget Data

Yesterday I was working with one of our partners to create a proof of concept using Dodeca and one of the issues that came up was creating some sample budget data to go with the actual numbers obtained from the customer. The partner wanted the budget numbers to be within the range of actual +10%/-15%. Not a problem, so I dug deep into my Excel experience and came up with the following VBA algorithm in about 10 minutes.. I *think* it did what I wanted so I thought I would share it:

Option Explicit

Private Const MIN_VAR_PERCENT As Double = -0.15
Private Const MAX_VAR_PERCENT As Double = 0.1

Sub AdjustTargets()
    Dim oRange As Range
    Dim oCell As Range
    Dim vNumber As Variant
    Dim iPlaces As Integer

    ''' use the selected cells
    Set oRange = Selection

    ''' loop the selected cells
    For Each oCell In oRange
        ''' if the cell contains a number
        If IsNumeric(oCell.Formula) Then
            ''' grab the number
            vNumber = oCell.Formula

            ''' get the number of decimal places
            If InStr(CStr(vNumber), ".") > 0 Then
                iPlaces = Len(CStr(vNumber)) - InStr(CStr(vNumber), ".")
            Else
                iPlaces = 0
            End If

            ''' compute a random value between the bounds and
            oCell.Formula = Round((vNumber * (1 + MIN_VAR_PERCENT)) + (Rnd() * (MAX_VAR_PERCENT - MIN_VAR_PERCENT)) * vNumber, iPlaces)
        End If
    Next
End Sub

The HTML rendering on the blog is sometimes not very good, so here is a screenshot of the VBA window (click on the picture to see a full sized version).












To use the VBA, select a range in an Excel workbook and run the macro.

Little Nitpick on VBA - Continued

Here is a comment on was posted on my 'nitpick' blog post by 'Jared'; I thought it would be better as a separate post:

Speaking of lost productivity...one thing that has thrown me off in VBA (with Essbase or not) is the syntax for calling functions and procedures--when to use parentheses or not. For example, every Essbase VBA programmer has used EssVRetrieve, probably in the format:

dim sts as long

'Retrieve data from the current sheet
sts=EssVRetrieve(Null, Null, 1)

The function runs and its return value is assigned to the variable sts.

Now, in some cases, I want to call functions and I don't care about a return code. Or, I want to call a procedure (which you cannot assign to value, of course). But if you do this:

EssVRetrieve(Null,Null,1)

...or this, for example:

CheckCellValue(Activesheet.Name,
5, FALSE)

...you'll get a syntax error. And since function syntax in most documentation includes the parentheses, you may have a hard time figuring out the error. Even the auto-complete in the VBA editor seems to indicate that the parentheses are OK: type a left paren after the
function/sub, and it will list the arguments for you, as if everything is just fine.

Unfortunately, it isn't. :) So the rule is this: only use parens with a function call if you are assigning a value to the function; and if it's a sub procedure, you never use parens. So you would have:

EssVRetrieve Null,Null,1 'Retrieve data, don't worry about return code

...or:

CheckCellValue Activesheet.Name, 5, FALSE 'Call "CheckCellValue" proc, passing parms

Hope this isn't too much of a derail, but it just came to mind...

Not too much of a derail at all Jared as I think it will be useful information for a number of blog readers.

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?

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.

Nice Christmas Present

Christmas is over and I received a very nice Christmas present. I was doing a quick Google search to check on our website (after the recent outage) and found an interesting blog post on Dodeca. Jason Jones describes how they are using our Dodeca product to replace what they had previously done in Essbase with Excel VBA and instead create a cohesive work environment. Here is the link to the blog entry.

In the blog post, Jason describes one of their alternatives is to write 'a little home brew VBA action' to meet their needs. That statement reminded me of a conversation I had with one of Jason's coworkers a while back. He was showing me one of the reports they generated with Dodeca. Previous to Dodeca, this certain report was generated 'by hand' by someone working overtime *every* Saturday to have it ready for the executive team on Monday morning. Once the report was implemented in Dodeca, it is now generated in Dodeca in about 35 seconds. No more overtime!

"The report is pretty complex. It has 68 different retrieve ranges and pulls data from 3 different Essbase databases." stated the coworker.

"68 retrieve ranges???" I asked.

"Yes, and 60 of them are sorted after the retrieval." he replied. "That is nothing... We have one report with over 250 retrieve ranges."

What is the best feature of Dodeca used in this case? The report described here was created entirely with out-of-the-box functionality by the coworker who had just a few hours of on-line training in how to use Dodeca. You know, visioning the Dodeca product and leading the team that has built it is the real joy of being an entrepreneur.

The best part of the story is the value it provides to the company. Although the coworker could tell me about the mechanics of the report, he couldn't discuss the nature of the content due to the competitive advantage they enjoy. I am happy they allowed us to help.

Pushing Essbase Beyond the Limits of Excel webcast

I am doing webcast tomorrow with fellow Oracle ACE Glenn Schwartzberg. The webcast, "Pushing Essbase Beyond the Limits of Excel", is part of the interRel Consulting webcast series. You can signup for the webcast at the interRel website.

In the webcast, Glenn will show you how to setup a budget template in Excel including the necessary VBA code, dropdown controls, etc to login the user, allow them to make selections, send data to the database and run a specified calc. I will then build and deploy the same functionality in Dodeca from scratch in about 5 minutes. BTW, Glenn was impressed when I did the same in our planning for the webcast.

I hope you can join us tomorrow!