A closer look at a Microsoft example lets you create drillthrough for Excel
| Downloads |
|---|
| 40382.zip |
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.
Figure 1 shows the drillthrough operation in action. In this PivotTable, I included the Cities level of the Customers dimension on the rows and the Year level (from the Time dimension) and the Product Family level (from the Product dimension) on the columns. I've selected the C7 cell, and I'm about to select the Drill to details menu option (which the VBA macro added) to perform a drillthrough. When I select the drillthrough option, the VBA macro will generate the query that Listing 1 shows.
You might notice two anomalies in Listing 1. First, the query has the numbers 0, 1, and 2 for axis names instead of the names COLUMNS and ROWS. The numbers are a shorthand notation for the axis names and are easier to generate programmatically. Second, Berkeley, 1997, and Drink are all represented in the drillthrough query, but they don't appear on the same axes as they do in the PivotTable. For example, the PivotTable shows Berkeley on the rows, but the drillthrough query lists Berkeley on columns (axis 0). This switch is OK because the axis layout of the drillthrough query doesn't affect the rowset result. Only the list of dimension members determines what rows the DRILLTHROUGH command returns.
Listing 2 shows the VBA code you down-loaded that creates the drillthrough query. The CreateDrillMdx() function uses a Pivot-Table data cell (oPTCell) to determine which dimension members you need to create the DRILLTHROUGH statement. You can identify the dimension members by stepping through the row and column headers that match oPTCell. The drillthrough query doesn't use all the header cells; it uses only one member from each dimension. The dimension member that the query uses is the lowest member (hierarchically) in that dimension. Cell A7, which references California (CA), is an example of an unused header cell. The query doesn't reference CA because Berkeley is from the same dimension and is lower in the dimension hierarchy than CA. I included CA only to give context to Berkeley, and the numbers in the data area correspond to Berkeley, not CA.
To determine the row and column headers, the VBA code uses the RowItems and ColumnItems properties of the PivotCell object in Listing 2. These properties are collections of header items in order from the outside edge of the PivotTable toward the data area. Although Listing 2 doesn't show it, the code determines the current PivotCell object by using the global expression ActiveCell.PivotCell. The inner FOR loop, which contains the loop variable i, steps through the header cells from the outside edge in. For each cell, the code records the dimension name and the dimension member name and checks two conditions before adding the member name to the drillthrough query. First, the code checks whether the previous header cell corresponded to a different dimension than the current cell. If so, the code needs to add the member from the previous cell because it's the lowest-level member from the previous dimension. The second condition checks whether the FOR loop is in its last iteration; in other words, is the current header cell adjacent to the data area of the PivotTable? If so, this cell must be the lowest-level member from the current dimension because no more space exists in the header for a lower member.
After the VBA code traverses the row and column headers, it continues by checking the page fields. The page fields are dimension-member selections for the PivotTable that don't appear on either rows or columns. Every page field corresponds to a different dimension, so the code doesn't require any additional logic to eliminate extraneous dimension members (as it would in the rows and columns).
The final step in the CreateDrillMdx() function is to add the cube name to the drillthrough query. Note that I've included hard brackets that act as quotes to surround the cube name in case the cube name includes spaces.
This drillthrough example is simple, but it's useful for anyone who's trying to use Excel XP to access OLAP data. As I mentioned, the biggest drawback of using Excel's OLAP capabilities is that Excel doesn't support drillthrough and writeback. In "Extending Excel OLAP Functionality," Microsoft includes examples of how to use VBA to circumvent both these limitations. Once you understand them, these examples are useful for more than just Excel-based analytic applications. You can use them to add drillthrough and writeback functions to any analytic application because they show how to access these important features through ADO MD.
