Recently we decided to upgrade one of our cubes by adding some new dimensions. To do this we needed to update our history to include these new fields via a set of SQL update queries to our source table. We had the the correct data in a separate set of PeopleSoft source tables. We needed to ensure that our table updates populated the correct data. We are talking more than 5 million rows of data so testing in Excel is not exactly a viable solution. So what I did instead was load my data from my PeopleSoft tables into a copy of my cube into a scenario called "Actual." Next I took my SQL updated table and and loaded it into the cube into a scenario called "Test." I then had a dynamic scenario called "Difference" which did a simple calculation of "Test" - "Actual." Once each of the data sets was loaded, I was able to set up a high level retrieve sheet for each of the drilled out new dimensions for the "Difference" scenario. Anywhere, where I saw something other than zero was something that needed to be researched, as it was a variance between my two data sets. I was also able to further drill down on my other dimensions to determine exactly where the variance was occurring. I was able to completely validate my data in hours rather than the days or weeks it may have taken me if I had been forced to use a set of queries and Excel.
This was just the immediate use I found for an Essbase difference cube. Taking just a few minutes, I can think of various other situations where this type of cube might come in handy. Just a couple of examples would be that it might also be useful in:
- Verifying a new calculation for a cube
- Comparing a back up of an initial cube load to a new cube load
- Validating a new cube to an existing similar cube
As a note, if it were so necessary, you can also keep a copy of your difference cube as documentation of your validation for audit or SOX requests.