Analysis Services Drillthrough

Dig deeper with this new feature of SQL Server 2000

One of the greatest benefits of multidimensional databases is their speed in answering queries that request highly aggregated information—for example, "What were the sales during the past three quarters for all products in Europe?" A traditional online transaction processing (OLTP) database might have to sum tens of thousands of individual transactions to answer this query. Multidimensional databases can respond quickly because they store or cache aggregated information about individual transactions, thus minimizing the amount of work necessary to respond to a query.

The storage structure of multidimensional databases is both a strength and a weakness. The structure is a strength because it helps answer queries quickly; it's a weakness because most multidimensional databases store only aggregated information, so an OLAP database user might be able to view only summarized information. For some users, that limitation might be acceptable, but in some situations, it isn't. For example, imagine a marketing manager trying to learn why a certain product isn't profitable. Using an analysis tool to browse an OLAP cube, the manager narrows the profitability problem down to the most detailed information in the cube. Then, the manager determines that during one specific month, the product's profitability was significantly low in the West Coast region. The next logical step for this decision maker is to drill through to the individual transactions in the OLTP database to see which customers and sales representatives were involved in the transactions during this period. For anyone who's ever faced such a situation, here's good news: SQL Server 2000 Analysis Services (formerly OLAP Services) natively supports drillthrough. Let's look at how to enable and configure drillthrough for your OLAP cubes and examine how to take advantage of drillthrough in your own Visual Basic (VB) client applications.

How Drillthrough Works

When you're viewing information from an OLAP cube, you can identify the values you see as tuples. A tuple is a set of dimension members where each member is from a different dimension. Think of a tuple as the coordinates that identify a cell location in n dimensions. This concept is similar to the way X,Y coordinates identify a location in a two-dimensional graph—in a cube, a tuple might be (1997, USA, Product B). A tuple not only identifies a cell value in the cube, but it also identifies a set of transactions in the source data that Analysis Services has aggregated to determine the cell value.

In Analysis Services drillthrough, the client software specifies a tuple from a cube in a special drillthrough MDX statement, and the server returns a row set with the drillthrough transactions. Here's an example of a drillthrough MDX statement:

DRILLTHROUGH MAXROWS 100    SELECT {[1997]} ON ROWS,    {([USA], [Product B])} ON COLUMNS    FROM [My Cube]

Typically, when you build a cube in Analysis Services, you first move the data from your OLTP database into an intermediate relational database that is structured in a star schema. (For more information about creating a star schema, see Bob Pfeiff, "Relational to Dimensional," page 39.) How does Analysis Services know how to get to your OLTP data source to return the underlying transactions when you drill through? It doesn't—it returns records from the intermediate star schema that was used to build the cube. However, if you know about this limitation when you build the star schema, you can include the extra tables or columns in your star schema that you want a drillthrough operation to display.

To demonstrate drillthrough, let's use the Sales cube of the new FoodMart 2000 database. First, run the Analysis Manager program and expand the treeview in the left pane until you see the Sales cube. Right-click the Sales cube, and select Edit to display the Cube Editor. Then, from the Tools drop-down menu, select Drillthrough Options to display the Drillthrough Options window, which Screen 1, page 58, shows. Now, select the check box to enable drillthrough.

In the columns list, select unit_sales, the_date, and SKU. Now, to demonstrate how you can add information to the drillthrough records, click the Add Table button. In the Select table window, scroll down and select region to view details for each sales region. Click OK in the Select table window, then scroll to the bottom of the columns list and select the sales_region column of the region table. Now click OK in the Drillthrough Options window. Click the Save button on the Cube Editor's toolbar, then close the Cube Editor.

Next, enable drillthrough in the cube-level security. In the Analysis Manager treeview, right-click the Sales cube and select Manage Roles. The Cube Role Manager window will display the security roles that apply to the Sales cube. The sixth column for each role shows whether drillthrough is enabled. For the All Users role, click on the ellipsis (...) next to the Drillthrough column to edit the cube's role. In the Edit a Cube Role window, select the Enable drillthrough check box, then click OK. Finally, click the Close button in the Cube Role Manager window. You're now ready to perform a drillthrough.

To drill through, right-click the Sales cube in the Analysis Manager treeview and select Browse Data. When the Cube Browser window appears, drag time onto the columns, then drill down on time (by double-clicking 1997) to see the months in the first quarter (Q1) of 1997. Also, drill down on customers into California cities. If you attempt to drill through before drilling down into the cube, your drillthrough query might access a large number of records. Analysis Manager limits the returned set of records to 1000, but displaying a large set of records can take a long time. After you drill down, right-click a cell that displays a number and select Drill Through, as Screen 2 shows. A set of drillthrough records like the set in Screen 3 will display. Notice that the sales_region column displays as the fourth column in the record set because Analysis Services used the region_id key to join the sales_region table with the store table.

Some Limitations

In experimenting with and reading about drillthrough in Analysis Services, I've found a couple of limitations, the first of which is limited security. The built-in security in the Analysis Manager only lets you enable or disable drillthrough; you can't control which drillthrough columns are available to certain user roles. For example, SQL Server 2000 Books Online (BOL) warns of a potential security breach in drillthrough configuration. You could use dimension- or cell-level security to prevent certain users from viewing a measure called Salary, but if the drillthrough configuration includes the Salary column of the employee dimension table, anyone with drillthrough access can see salary information.

Another drillthrough limitation is that you can't control the column order in the resulting drillthrough records. If you use the Analysis Manager to configure drillthrough, you're confined to the order in which the columns appear in the Drillthrough Options window. Although I haven't tried this solution yet, the Microsoft Analysis Services development team says that you can change the column order by developing a program that directly accesses the Decision Support Objects (DSO) API. You can circumvent some of Analysis Manager's limitations by using DSO applications instead of Analysis Manager.

Drillthrough with ADO MD

Now that you've seen drillthrough in action, let's go a step further and demonstrate how to use drillthrough from a VB application. If you're going to create a VB program that uses ActiveX Data Objects, Multi-Dimensional (ADO MD), you must first set up your project references. If you download the program's source files, the project references are set up for you already. To set up the references yourself, start VB and select References from the Project drop-down menu. Then check Microsoft ActiveX Data Objects (Multi-Dimensional) and Microsoft ActiveX Data Objects 2.6 Library, as Screen 4 shows. You can then type into the VB editor the code from Listing 1 or download it at the link to this article at http://www.sqlmag.com.

The VB program in Listing 1 is simple. First, the program establishes an ADO MD connection to the OLAP Server on your local machine and sets the initial database to FoodMart 2000. If you don't have the server portion of Analysis Services running on your local machine, you need to change LocalHost to the name of the server you want to connect to. After establishing the connection, the program creates a new Recordset object and executes the drillthrough statement. Notice that this object is a Recordset object, not a Cellset object. When you execute MDX queries, you usually retrieve the result in a Cellset object because the Cellset object supports n-dimensional results. In a drillthrough operation, the result is always a set of records, as is the result of a SQL query.

The syntax of a drillthrough statement is an extension of the standard MDX query. Note that you can select only one cell. You still need to use the curly brackets in the ON COLUMNS and ON ROWS list, but only one item can be in each set of brackets. In a drillthrough statement, which member is on the columns or rows isn't important; only the combined set of members (the tuple) is important.

In this example program, I used the MAXROWS keyword in the drillthrough statement to limit the number of returned rows to three. Setting a limit on the number of rows you retrieve is important because you won't always know how many records will match the drillthrough operation. A cube might contain millions of fact table records, and a drillthrough on the root of several dimensions could return most, if not all, of the fact table records.

After the drillthrough statement executes, the VB program loops through the records and outputs a list of each field name and value. The program generates the output by using Debug.Print; Table 1 shows the output for this program, which you can view in the VB Immediate window. The final step in the VB program releases the Recordset and Connection objects.

In any Analysis Services deployment, seriously consider support for drillthrough. Without drillthrough, you keep the analysis user one level away from the transactional data. Connection to the transaction detail, which drillthrough provides, can allay users' doubts about the aggregated information's authenticity. Transaction detail can also be vital in uncovering hidden business problems. Without Analysis Services and SQL Server 2000, implementing drillthrough requires either a custom DSO server application or a special client application that understands the mapping between the cube information and the transaction database. With SQL Server 2000, drillthrough is just another MDX statement. (For answers to the May MDX Puzzle, see "May MDX Puzzle Solution Revealed," above. For the June puzzle, see "MDX Puzzle.")