Introducing Data Detective

Introduction

Sometimes working on this blog is a nightmare, other times it’s a joy, and when I’m really lucky, someone else does all the work; this is one of those times. 

Several (well, like two) people have told me that they can't find the link to this tool.  Never let it be said that I stand in the way of disseminating cool stuff.  Download Data Detective RIGHT HERE and enjoy Dave’s generosity.

No test like production

What do you do when you change a formula, calculation, data source, etc. in your superduper Essbase database?  Never check for correctness and pray for the best?  Create a copy of the app and make the changes there (there is this concept called development, quality, and production) and then spend endless hours recursion testing?

I’m guessing you do the latter as my readers are smart, intelligent, and continuously employed.  And when you do that testing, I’m going to bet that you compare the existing database against the new one.  I do this all the time, setting up retrieve sheets for the original and the new databases and then creating a variance sheet.  This is an acceptable approach, but every time I find a difference, I invariably drill into it, and then have to replicate the modified retrieve in the other database and then manually rewrite the variance formulas, ad infinitum.  Heretofore, this has been the only way to do this kind of compare, but it is an inefficient and error prone process.

Dave Farnsworth has a solution to this problem with one of his great utilities – this man knows Excel.

In his own words

Data Detective is one of those programming ideas that hang around in my head. I intended to write the utility if I ever had the time and motivation. Finally after many years I’ve put pen to paper and this utility is the result.

Commonly consultants and Essbase administrators engage themselves in the tedious task of comparing two Essbase databases checking that the values are equivalent. After too many years of retrieving from two databases, creating a delta sheet and then pivoting the original report and doing it all again I said enough! Delta Detective is my new favorite tool.

Very simply, Delta Detective is an Excel based macro that uses the classic Essbase add-in tool kit. As the user, you open Delta Detective.xlsm, connect to Essbase using the normal Add-in dialog, Format your first query. Next, start the macro using ctrl+q which brings up a dialog where you enter the connection information for both database that you want to compare. Hit the run button and the macro creates exact duplicates of your original sheet, retrieves data from the second database, computes the delta’s and colorizes the mismatching cells. Returning to the original report sheet you can drill, pivot, or whatever using the classic menu and then rerun the delta report. Excel formats from the original report carry over to the others and you can always be sure that the sheets are connected to the proper databases.

Please look at the ReadMe particularly if you are using an Office version earlier than 2007.

A couple of points

The classic add-in, not SmartView

Another quote from Dave, this time from his documentation (yes, he’s that good), “Since this utility is freeware, the idea was to make it simple, so that it didn’t consume Excel resources when not in use. Additionally it needed to be relatively version independent with regards to Essbase and Excel. For these reasons it requires the traditional Essbase add-in and not SmartView.”

Why not an add-in?

Dave wrote this as .xlsm file instead of a xll because of the loss of menus in Excel 2007/no ribbon bars in Excel 2003.  One day we’ll all be in the ribbon-driven world, but that is definitely not true today.  Until then, this approach removes the UI from the equation.

If you’re using Excel 2003

To allow you to open Excel 2007’s Macro-Enabled Workbook format you must first have Excel 2003 SP3 (I tried skipping SP3 – nope, it doesn’t work) and then you must install Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 and later File Formats (Compatibility Pack).  Read all about it here.

Macro security

This workbook isn’t signed, so in both Excel 2003 and 2007 (sorry, this hasn’t been tested on 2010 but go ahead and leave a comment telling us), you are going to need to allow Excel to run the macro. 

2003

Just follow the instructions and set the security level to medium.

2007

You’ll see the warning message right up front when you open the file.  Click on the Options button, and enable the content.

Tested variants

Essbase version	Excel version
9.3.1	2007
11.1.1.3	2003
11.1.1.3	2007
11.1.2	2003

Has every release under the moon been tested?  No, but I’m going to guess that since there was zero problem in getting this to work in such a wide span of Essbase releases, there isn’t going to be any problem in your world, either.

Documentation

In addition to the utility itself, Data Detective comes with its very own tutorial. 

Conclusion

I’ve used Data Detective in beta on a project as I tweaked and optimized complex financial allocations.  Data Detective is an easy to use tool to generate the deltas between the databases and helped me quickly hone in on problematic calculations, all without me laboriously creating dual retrieves and variance sheets.  Very, very nice.  Download Data Detective RIGHT HERE and enjoy Dave’s generosity.