CUBE Functions 4 Deconstructing the PivotTable

Actually, before I talk about data connections, I want to cover one other way that users can author OLAP formulas ... users can create a PivotTable bound to an OLAP cube and then use the Field List to quickly add all the fields that they care about to thier PivotTable. Then, with a single command, they can convert that PivotTable to a set of formulas, where each formula uses one of the new CUBE functions. Fast and easy.Once the Pivot Table has been converted to formulas, users can insert rows and columns, add their own calcs, and modify their work in all sorts of ways, using the converted PivotTable as a convenient starting point. Note that conversion only works in one direction. While you can convert a PivotTable to Formulas, you cannot convert a set of formulas to a PivotTable.Imagine a report that shows several thousand members of a dimension, each member on one row of the report. (As an example, this might be a report that has data for each of several thousand products, or for each of several thousand customers.) Instead of authoring thousands of CUBE functions, it can be much easier and faster to create a PivotTable that contains the data you want in the report. By bringing a single field into the report (such as Product or Customer) many thousands of values can also appear in that report. The command to “Convert to Formulas”, can be found on “Options” Tab under “PivotTable Tools”. It is located under the command labeled “OLAP tools” as illustrated in the screenshot below (these are beta 1 visuals – not final design).
(Click to enlarge)For a PivotTable that uses a ReportFilter, There are two options when this command is invoked. By default, the row labels, column labels and data area of a PivotTable will be converted to Excel formulas that use CUBE functions. If there was a ReportFilter, that filter will remain as it was and the CUBE functions will refer to it. This permits the filter to be changed and all the formula values will be adjusted accordingly. The other option is to convert the ReportFilter too, so that nothing of the original PivotTable remains.Here’s the dialog that comes up to give you this choice:
To illustrate this capability, here is a PivotTable report where the Report Filter has not been converted along with the rest of the report. In this example, users may still dropdown the filter in cell B1 and change its value. When this cell is changed, the rest of the numbers will change with it.
(Click to enlarge)Here is the same report, but in this case, everything was converted, including the ReportFilter. There is no longer a dropdown in cell B1, but if the CUBEMEMBER function in that cell were changed, the rest of the report would reflect that change.
(Click to enlarge)That wraps up OLAP formulas. With this series of blog postings, I hope that I’ve illustrated how Excel 12 has added an important new feature that make it an incredibly powerful tool for analysis of OLAP data. With improved PivotTables, new CUBE functions and the ability to convert from a PivotTable to formulas, there are lots of ways to build sophisticated reports based on the analytic data that is more and more commonly stored in multi-dimensional databases such as SQL Server 2005 Analysis Services.
<div