Using the Excel CUBE Functions
Time: 2:30 – 3.30 in Auditorium Two
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 way 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, providing structured output 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 simply to explain how to use CUBE functions, but rather showing how to use other Excel functions in conjunction with the CUBE functions to build formulas to manage the output. We will be creating a 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 capability.
Bob Phillips is an independent consultant based in the UK specializing in all things Excel, and first came to appreciate the power and versatility of Excel as an enterprise tool in the mid-90s when managing the delivery of a data warehouse project.
Now a freelance consultant, he specializes in Excel automation and BI delivered via Excel. An 11 year veteran of Microsoft’s MVP program, he is on an impossible mission in trying to convince and persuade small and medium corporates that data analysis and BI is important to them too