Self-service BI is a great idea, with the Microsoft suite of Power tools going a long way to delivering this idea. For sophisticated users that can build models and analyse data using pivot tables, this is a great and effective solution. But what about the rest, those who just need results that they can use in their day job, the managers who just need the figures to help them make decisions?
Dashboards with good charts and graphics go some ways to delivering information for such users, but often a good table of results will give the correct amount of detail to gain the necessary insights, and is easy to read. With the aptly named CUBE formulas, Excel has the capability to read data directly from a data cube in the spreadsheet. This is a great way to build structured output in Excel, in the form of a simple table. The problem with this approach is in managing the data changes, changes in the underlying data can mean more or fewer rows of data to show in your table.
This session is not intending to explain how to use those CUBE formulae, although using them in the presentation will show their usage, as it is assumed that the audience at least knows of them and what they each do. Instead, the emphasis is on showing how to use other Excel formulas to manage the output, building a truly dynamic display that caters for changes in the size of the underlying data, whilst also handling those ugly #REF errors that can occur when users insert or delete rows above the output table.
Given time, we will add a little MDX to the formulas to show further dynamic capability.
Speaker: