Debugging Essbase Add-in Issues

Sorry I haven't posted in a while; I am still trying to recover from ODTUG (and am preparing to fly to Seattle to speak at the Oracle Days conference (http://www.oracledays.com/) next week. I have been trying to hit the forums and have seen a common theme that deserved a blog post to explain.

I have seen a number of posts relating to the Essbase Excel add-in that claim "XYZ functionality doesn't work in this machine but it works on every other machine we try it on..". You can sometimes debug those things until you are blue in the face and still not find the answer. That being said, these things are just computers and they do what they are programmed to do unless, of course, you believe in gremlins. What that means is that there is a cause to every issue and there is an answer.

The two most common things that could happen in this circumstance are that the files being loaded on not the right files or the configuration is not correct. In the first case, the most common thing is the client side files are not in synch with the Essbase server version and, of course, the fix is to upgrade the add-in. Another issue I have seen frequently is that the files are of *mismatched* versions which can cause some really serious and hard to debug issues. On the configuration side, I haven't seen issues that frequently and, when I have, a simple deletion of the ini file (for earlier versions of the add-in) or the related registry entries (for later versions) fixed those issues. I do remember seeing Excel crash due to ini file issues back in the version 5 or 6 timeframe; if I remember right, it was because I changed the Essbase options string stored in the ini file to contain 'bad' values (accidentally, of course!)

On to the impetus for this post... Whenever I have hear of people who are having problems debugging issues in the Excel add-in like this, of course I first tell them about our Dodeca product which doesn't have any of the numerous issues associated with the Excel add-in. Although most users who see Dodeca want it, they normally aren't in a position to purchase a replacement product no matter how good it is (or they probably wouldn't be the ones trying to debug these issues in the first place). The next steps in debugging the most difficult Excel add-in issues is to examine the files in memory on the 'bad' machine and compare them to the files in memory on a 'good' machine in their organization. This process is more involved than just comparing the files in a directory but, fortunately, there is a tool that can help.

I use the Process Explorer tool from SysInternals for this type of work. SysInternals is a company that has some very cool freeware utilities and they really know Windows inside/out. In fact, they know Windows so well that Microsoft purchased the company a couple of years ago. You can get the Process Explorer at http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx. Here is a screenshot of the Process Explorer running on my machine with the Excel add-in (click on the picture to see a larger version):



Note: the View menu has 'Show Lower Pane' checked and the Lower Pane View is set to DLLs. Once you see the lower pane, you may have to click on the column headers to add the 'Version' and 'Path' columns.

The first thing I normally do is sort by the 'Path' column, scroll down and look at the versions of the files in the Hyperion directories. If the versions differ between the different Hyperion files, that itself may be the issue causing the problem.

I also sort by the 'Company Name' column and look for Hyperion files. I have seen many instances where some Essbase files are getting loaded from one place in the PATH and others are loaded from another place. This can happen when a developer that is not very familiar with Essbase incorrectly installs Essbase DLLs in Windows\System32 or some other common directory in the path. Windows searches directories in the PATH environment variable when it needs to load a file and then loads the first one it finds; Windows\System32 is commonly very early in the PATH so any Essbase files installed there would most likely get loaded before the version in your ARBORPATH\bin directory.

While you are reviewing files, also look at what other add-ins may be loaded in Excel. Occasionally other add-in's interfere with the workings of the Essbase add-in.

Finally, you can save this information to a file. Do the same thing for both the 'good' and 'bad' machines, compare the differences and you may find what is causing you user issues. If you can't find a solution, give me a call and I will show you how our Dodeca product can resolve these types of headaches for you!