One of the most common reader questions I receive is, "How can I use the PivotTable and PivotChart components in Microsoft Excel?" These components, which enable simple analysis in Microsoft Excel or a custom application, work with either OLAP data sources or relational data sources. When you use it with an OLAP data source, the PivotTable component provides basic data-navigation (OLAP) capabilities. Because PivotTable's OLAP capabilities are available in Microsoft Office XP or Office 2000, which most people have, developers commonly use PivotTable in simple analytic applications that they create for analyzing their organizations' business data. However, many people who start using PivotTable in an analytic application quickly discover the component's limitations: PivotTable doesn't support drillthrough or writeback.
The Microsoft article "Extending Excel OLAP Functionality" (http://msdn.microsoft.com/library/default.asp ?url=/library/en-us/dnexcl2k2/html/odc_xlextendolap.asp) includes examples of how to work around these problems. However, the article doesn't fully explain how drillthrough works or enough about the example source code to use it in your application. Let's look at an expanded explanation of the Microsoft article's drill-through example.
How Drillthrough Works
Drillthrough is important in analytic applications because it lets users view individual, detailed transactions that contribute to the summarized information that an OLAP cube typically contains. For example, say you're viewing a cube that contains sales for each of your products and you notice that last week, one of your stores had unusually high sales of a particular product. Naturally, you want to see the individual transactions for that product, time period, and store. Viewing the transactions might help you determine the data's validity and identify possible causes of data errors (e.g., a transaction that was entered into the system multiple times). If the data is valid, you'll further examine the transaction details to better understand the sales and determine whether the spike is an anomaly or the start of a trend.
An analytic application performs a drillthrough operation by sending a special DRILLTHROUGH command to Analysis Services through ADO MD or OLE DB for OLAP (the Analysis Services application programming interfaces—APIs). The DRILLTHROUGH command consists of an MDX query that references a cell you want to see in the cube. Analysis Services uses that MDX query to determine which fact-table records correspond to the selected cube cell, package the records in an ADO or OLE DB recordset, and return the records to the client application. Before the DRILLTHROUGH command can execute, the cube administrator must use Analysis Manager to enable drillthrough capabilities for the cube and decide which of the columns to return to the client application when the client application issues the command. The returned columns can come from the fact table or from another table in the same data source. Note that if the columns come from a different table, you must use a foreign key to join that table to the fact table.
Examining the Microsoft Example
Let's look at a slightly modified version of Microsoft's example of how to add drillthrough capabilities to Excel's PivotTable. You can download the modified drillthrough example at http://www.sqlmag.com. Type 40382 in the InstantDoc ID text box, and click Download the Code. The download includes an Excel spreadsheet called PivotTable Drillthrough.xls (which contains a PivotTable connected to FoodMart 2000) and a Visual Basic for Applications (VBA) macro. The macro adds to the PivotTable's context (or right-click) menu an option called Drill to details. When you select this menu option, a VBA macro that's called Drillthrough() creates an MDX DRILLTHROUGH statement, sends the statement to ADO MD, and displays the resulting recordset in an Excel worksheet.
Before trying the example, be sure to enable drillthrough on your FoodMart 2000 Sales cube. You can enable drillthrough in Analysis Manager's cube editor by selecting Drillthrough Options from the Tools pulldown menu. Also, the PivotTable in the example spreadsheet that you downloaded is connected to the LOCALHOST server. That connection will work if Analysis Services is installed on your local machine; otherwise, you need to reconnect the PivotTable to the server that contains FoodMart 2000. This example also requires that you have Analysis Services' client components installed on your PC. If you've used OLAP PivotTables in Excel before, your system already has the required components. If you haven't used OLAP PivotTables before, you need to install the client components from your SQL Server installation CD-ROM or Office XP CD-ROM.
After you open the PivotTable Drill-through.xls file in Excel, open the Visual Basic Editor by selecting Tools, Macro, Visual Basic Editor. When you open the .xls file, Excel automatically calls the first VBA function you see, Workbook_Open(). The VBA code in the Workbook_Open() function adds the drillthrough option to the PivotTable context menu. The second function is CreateDrillMdx(), which I describe in a moment. Third, you'll see the ValidDrillCell() function, which determines whether the currently active cell is valid for a drillthrough operation; to be valid, the cell must be in the data area of an OLAP PivotTable. ValidDrillCell() displays error messages if the cell doesn't meet this condition. Finally, you'll see the Drillthrough() function, which uses ValidDrillCell() and CreateDrillMdx() to perform the drillthrough operation, including creating a new Excel worksheet and displaying the result.
Prev. page
[1]
2
next page 
