An homage to “Who moved my cheese?”

One of my colleagues had a client request to turn on data auditing in Hyperion Planning 11.1.2.3.

Seems they have multiple people forecasting the same cost centers which was causing some confusion.

The “my data is gone” is always an interesting conversation; the last client conundrum of missing data I recall where planning data went “missing” was a defense contractor client — that root cause was someone changed substitution variables and voilà! presto chango no data show on the forms; always a fun conversation when your manager goes so this data is gone and the client isn’t real happy.   My first reaction is data doesn’t go missing; then the next reaction is I wish we had Essbase ssaudit on and we would know who cleared it, who changed it, or where it was before.

The interesting thing with the audit features of planning is it logs this information to a relational table within the given planning application database (schema on Oracle) however there is no query mechanism.

There are also no tools which come with planning other than Web Analysis which can query and put it into a nice presentable format.

So I mess around with Web Analysis for a bit, creating a relational data source and then in the wizard get stuck at the area you define dimensions and measures.  I phone a friend (or two as it turns out) one Mr. Collins and one Mr. Nader.  Dave quickly relates this technology isn’t recommended and Oracle (and Hyperion) hasn’t touched it since Brio wrote it.  He relates you probably want the SQL Spreadsheet vs trying to do it another way; turns out it’s very easy with this widget.

So how does it work?

Web Analysis New Document

Start a new Web Analysis Docuent

SQL Spreadsheet Widget

Drag the SQL Spreadsheet Widget to your canvas.

Setup your connection and query

Next save it to a handy area in your worskpace structure.

NOTE: One caveat; if this app were used for salary planning you may want to restrict who can see these reports.

P.S. Web Analysis is going away.

P.P.S One would expect Oracle would need a way for Planning and Budget Cloud Services to see the audit information.


John A. Booth