One of our large financial customers is using a novel and surprisingly-flexible OLAP dashboard technique to allow their board to make strategic business decisions. It also allows ad hoc changes and special request analyses with fast turnaround. So how do they do this, and how can it still be meaningfully-perused by non-OLAP executives?
This Microsoft web page points out that converting an Excel PivotTable to “cube formulas” frees dashboard designers from the layout of a traditional cross-tab PivotTable. Decision makers then can see what they want, and only what they want, so more astute business decisions can be made. If you convert an OLAP-connected PivotTable to cube formulas, each spreadsheet cell becomes its own re-freshable OLAP query. You can then freely re-arrange the location of cells, and delete unwanted and distracting cells. You can add rows or columns in the middle of the PivotTable. Your report doesn’t even have to be a rectangular! It can have non-uniform OLAP row or column lengths, even containing data from disparate OLAP sources, as appropriate to your vertical industry. And with a technical hint below, you can make this table still perusable by executives.
To allow user or general input to tailor your table, you can within a cube formula also use an Excel reference to another cell. Here’s a tip: our customer doesn’t use references like $B$21 to other cells. Instead, they use the name of an Excel “named range” within the cube formula. That named range refers to a single cell elsewhere, perhaps on a different worksheet. This way, board members can click on each cell in a sophisticated report and by the range ‘name’ understand what this cell is showing.
Interestingly, sometimes this financial institution now doesn’t even start with PivotTables to author a new report. They are so familiar with the few, simple “cube formula” syntaxes that they just start wonderful reports for their vertical as necessary cell by cell.
Note that the financial institution mentioned above uses Simba’s MDX Provider for Oracle OLAP product for the glue between Excel and Oracle Database OLAP, but the Excel OLAP techniques I’ve described apply equally well to other OLAP sources.