Tuesday, September 24, 2013

Essbase Aggregate Storage considerations

Essbase aggregate storage has been around for quite a few years now.  It's good at what it does, aggregates large amounts of data quickly.

If users want to get data out of an ASO application I used to agree with all the forum postings of "Where did you load the data from?"  Anyone in their right mind should be storing all the ASO source data somewhere, just in case.

This seemed to be an OK line of thought until write back was allowed to an ASO application via lock and send.  Now the rules of the game have changed.

If users can lock and send data to ASO, the system must be able to export that data nightly.  Sure, you can do an operating system level backup, or a native Essbase ASO data extract then import into an ASO app, but what good does that do you if you need to send the budget to outside systems, or you need to get to the data from a non Essbase tool?  The entire world does not revolve around Essbase despite what we might like to believe.

Real world example:
I was on a project that was live where the client decided to modify their production system by flipping the signs on revenue accounts so revenues were positive.  This was apparently an oversight on the first go live.  There were 2 databases that needed to be modified, 1 block storage and 1 ASO database.  The block storage changes were easy and the solution was implemented in one afternoon.  The ASO sign flip was a bit trickier and someone else got to take that one over.
The following were tried:
-an MDX script to multiply the revenue by -1.  This seemed like the correct tool to use but the system said the script was too big.  2 to the 52 power was too large for Essbase.  This was already only touching 1 of 10 entities for 1 scenario for 1 year.
-exporting the data in native then import to block storage then export in column format.  This took some time getting the outline to fit in block storage.  The data loaded without error but when the block storage was exported to column format it was missing 70% of the data.  
-the trusty old report script was then attempted.  Report scripts turned out to be the solution.  Over 100 report scripts were generated and run to get the data out of the ASO app.  This data then had the sign flip applied then reloaded.

It looks like in an export can be to relational.  I'll be excited once I get to test this on a populated ASO database.  While this is nice, I'd still like to see an option for a flat file extract.

If I'm overlooking anything here please let me know.