OLAP for Developers

SQL Server 2000 Analysis Services supports many new architectural, security, and administrative options for OLAP including linked cubes, calculated cells, named sets, and distributed partitioned cubes. Analysis Services also contains some new options for developers, such as the ability to see detail rows through drillthrough and the capacity to let users act on cells within a cube by using actions. In both cases, these features provide capabilities that were difficult if not impossible to implement previously. Russ Whitney discussed how to use these options for ADO MD in Mastering OLAP, "Analysis Services Drillthrough," June 2000, InstantDoc ID 85301, and "Analysis Services Actions," July 2000, InstantDoc ID 8758. In this article, I show you how developers can hook into Analysis Services for Web applications by adding drillthrough support and implementing actions.

Implementing Drillthrough
After Microsoft released SQL Server 7.0 OLAP Services, one of the most frequently requested features was the ability to retrieve the underlying data used to calculate a particular cell's value. This ability is useful if you need to know, for example, which customers or which products a particular cell includes. OLAP's ability to aggregate a fact table's rows—its key strength—by its very nature also abstracts the detail rows and thus can make finding them difficult. Before the release of Analysis Services, a developer faced with this challenge had to implement a metadata-mapping layer between cube members and the relational database (which contained the star or snowflake schema). When accessing a cell, the code had to read both the cube member information and any filter applied to the query. Then, the code had to generate a SQL query to join multiple tables from the relational database, execute the query, and return a result set. Writing the code to correctly access a cell in this way isn't trivial. With Analysis Services, you don't need to write or maintain this code at all.

Analysis Services contains the code to implement drillthrough; Figure 1, page 38, shows a high-level view of how Analysis Services accomplishes drillthrough. The client application executes an MDX statement that is passed from the local Pivot Table Service to the OLAP server. Keep in mind that the Pivot Table Service is the OLE DB provider that provides caching, local cube processing, and connections to an Analysis (OLAP) server. The OLAP server then uses the configuration options and security settings in the cube to create a SQL statement and send it to the relational database that holds the detail data. The SQL statement sends the returned OLE DB rowset back through the OLAP server and Pivot Table Services to the client application. Typically, the client accesses the data through an ADO Recordset object.

To implement the drillthrough feature, you must perform both administrative and application-specific tasks. From an administrator's perspective, the first task is to enable the cube for drillthrough. An administrator's ability to control enabling of the cube for drillthrough is crucial because cells often contain the data from thousands or even millions of rows, so letting all users query this data unrestricted can lead to network bottlenecks. To enable the cube for drillthrough, start the Cube Editor and select Tools, Drillthrough Options. In the resulting Cube Drillthrough Options dialog box, simply select the Enable drillthrough check box, as Figure 2 shows. Analysis Services supports drillthrough for regular, virtual, or linked cubes, but Analysis Services won't let you use drillthrough on a cell that has values based on calculated member cells or custom member formulas.

Note in Figure 2 that you must enable drillthrough and that the column list contains all the columns from the fact and dimension tables in the schema. In this example, the cube's name is Enrollment and the cube allows reporting against student enrollments in Quilogy's technical education courses. Depending on which columns you select in this dialog box, Analysis Services will join the appropriate tables when it queries the underlying data. The dialog box in Figure 2 also contains a Filter tab that can contain a WHERE clause to limit the number of rows in the result set. This WHERE clause is in addition to the WHERE clause that drillthrough generates dynamically.

In addition to enabling drillthrough at the cube level, you can also modify the options on each individual partition in cubes that contain more than one partition. You can access a dialog box analogous to the one in Figure 2 through the Partition Wizard—to access the Partition Wizard, right-click the partition and select Edit from the context menu—by clicking the Advanced Settings button and Drillthrough Options. The resulting Partition Drillthrough Options dialog box lets you change the columns and filter to use when creating drillthrough data from that partition. The ability to set drillthrough options on individual partitions means that if you execute a drillthrough operation on a cell that aggregates data from more than one partition, each partition returns its own result set and the columns in those result sets can differ from one another. Application developers need to be aware of this behavior so that they can display the results accordingly. The administrator's next task, configuring drillthrough security, requires that you use the Cube Role Manager dialog box and click the Drillthrough setting for the role you want to grant access to. Each role has a security setting, called Allow Drillthrough, that you can select to let that role execute the queries that perform a drillthrough operation.

Drillthrough for Developers
After you enable drillthrough and configure security, you implement drillthrough for applications. To demonstrate the steps needed to implement drillthrough, I added the drillthrough capability to an Active Server Pages (ASP) page based on the ASPADOComplex.asp page that ships with Analysis Services. This page executes a query that shows the Quilogy locations, the number of student days enrolled at each location, and the amount of revenue (fictional, of course) that courses at each location generated, as Figure 3 shows.