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.

In a Web-based application, the most natural way to provide drillthrough support in the UI is by putting a hyperlink on the cell and letting the user click the link to display detail data. Note that each cell in Figure 3 contains a hyperlink. To generate the hyperlink, you can modify the following statement to include or replace the ASPADOComplex.asp code that prints the cell value:

Response.Write " " _  & cst(k, j).FormattedValue & ""

Note that the hyperlink calls the Drillthrough.asp page and passes to that page through the query string the current column and row member names, the cube, and any WHERE clause that the MDX statement used to generate the cellset. The code in the ASPADOComplex.asp page stores the row and column names as arrays populated when it builds the table row and column headers in the ASP page. The code below extracts the WHERE clause from the MDX statement, then sends the values to the Drillthrough.asp page, which Listing 1 shows.

intPos = Instr(Session("MDXQuery"),"WHERE")If IntPos > 0 Then strWhere = Mid(Session("MDXQuery"),intPos)

Drillthrough.asp builds an MDX statement that includes the DRILLTHROUGH keyword to instruct the OLAP server to build and execute a query to return the requested detail data. You can prefix the DRILLTHROUGH keyword to an MDX statement, which can contain MAXROWS and FIRSTROWSET arguments to limit the number of rows returned to the client and determine which partition's data to return first if the cube contains multiple partitions. The OLAP server applies MAXROWS after applying the filter for the cube or partition. Note at callout A in Listing 1 that the page limits the number of rows to return to 5000. To build the MDX statement, the page then appends to the strSource variable the arguments passed in the query string (column name, row name, cube, and WHERE clause) and accessed through the ASP Request object. For example, if you click the Days column for Cincinnati, the page constructs the following MDX statement:

DRILLTHROUGH MAXROWS 5000SELECT {[Measures].[Days]} ON COLUMNS,{[Location].[All Location].[N].[Cincinnati]}   ON ROWSFROM Enrollment

To execute the MDX statement, you invoke a Connection object's Execute method. This example sets the ADOMD.Catalog object's ActiveConnection property to the server and catalog name that the user's session contained before it called Execute. In Web applications, the return value from the Execute method is an ADO Recordset, not a cellset as in ADO MD. Because a drillthrough in Web applications can return multiple recordsets, you should write the code in a loop to retrieve the results and format them into an HTML table, as callout B in Listing 1 shows.

The Recordset object's NextRecordset method returns the recordsets created for additional partitions, or it returns Nothing when no more recordsets exist. If you're sure the recordsets for the different partitions contain the same columns, you can merge the results by using ASP code. In this case, however, the PrintTable procedure just places an additional HTML table on the page returned to the user. Figure 4, page 40, shows the resulting detail data.

Implementing Actions

A second Analysis Services feature that developers can take advantage of is actions. Like drillthrough, actions are a bridge from the cube to additional data; unlike drillthrough, actions are extremely flexible and Analysis Services doesn't restrict them to linking a cell to the relational data that produced it. As the name implies, actions let users act on their analyses by giving them options for working with the data at the cube, dimension, level, member, or cell level. Basically, actions let a user initiate a process to solve a problem or to provide more information about data in a cube. For example, assume Quilogy's sales and marketing people have access to the Enrollment cube. As they browse the cube data through an ASP application, one possible action would be to click a hyperlink and view the syllabus for a particular course. The syllabus might give them a better idea of the course's content and how it relates to sales trends evident in the cube. You can implement this example by creating a URL action at the cube level to expose course information. Table 1 shows the types of actions available in Analysis Services and some possible uses for them. Like drillthrough, implementing actions is a two-step process. First, from the administrator's perspective, you must create the action; then, from the developer's point of view, you can modify the application to be "action aware."

To create actions, you use the Cube Editor. Right-clicking the Cube Editor's Actions folder invokes the Action Wizard. The wizard takes you through several steps, the first of which is to identify the action's target (i.e., the entire cube, a particular level, a dimension, individual cells, or named sets). If you choose a dimension or a particular level, you need to also identify the dimension or level and indicate whether the action should be available when the user clicks the heading or the actual dimension or level. In the example, the Quilogy Education cube contains a dimension called Course that contains Vendor Name, Product Name, and CourseNum levels. CourseNum identifies a course that Quilogy offers. For example, a Microsoft Official Curriculum (MOC) course has course number 2072. After selecting a level, the wizard presents a drop-down list of action types from which to select. In this case, you want to create a URL action.

The next wizard screen lets you define the action syntax to return to the client application and that's subsequently used to initiate the action. In this example, you need to format the action syntax as a URL. This wizard screen contains a button that invokes the MDX Builder so that you can incorporate data from the cube into the action syntax. Because this action is available when a user clicks a particular course number, the resulting URL must incorporate the course number to build the link to the course syllabus. Thus, you need to choose the dimension and use the CurrentMember function and the Name property to return the course number clicked.

"http://www.quilogy.com/courses/"   + [Course].CurrentMember.Name +   ".htm"

You can then give the action a name, and it becomes available in the Actions folder.

Actions for Developers

After adding the actions to the cube, the developer side of you has two primary tasks: First, you must build an interface so that a user can recognize when an action is available, and second, you must let the user invoke the action. For this example, I modified the earlier ASPADOComplex.asp page to be action aware. First, the ASP page must query the cube to determine whether a particular member has actions associated with it. You can use the ADO Connection object's OpenSchema method to perform this function. This method accepts a constant that determines the type of rowset to return and an array that specifies the restrictions the server uses to build the rowset. Analysis Services extends the OLE DB specification by adding the constant adSchemaActions to it to pass as the first argument to OpenSchema. The returned rowset (called an MDSCHEMA_ACTIONS rowset) contains one row for each action and includes the columns that Table 2 shows. The second argument to OpenSchema is an array that defines a set of restrictions to place on the columns in Table 2. Table 2 also shows the order of the restrictions array and whether the argument is optional.

Next, you need to create a UI cue so that the user can recognize that an action is available. To create the cue, I added the server-side DisplayURLAction procedure and associated constants to the ASP page, as Listing 2, page 42, shows. Note that the procedure first executes OpenSchema, as callout A in Listing 2 shows, using the Array function to pass the adSchemaActions constant and the set of restrictions. In this case, the array specifies the Enrollment cube (the third argument); only URL action types (fifth argument); the member to query for actions, as passed to the DisplayURLAction procedure (sixth argument); and the action's scope (seventh argument)—in this case, actions at the member level. Note that you can use an empty string (" ") to pass optional arguments.

If OpenSchema finds actions, the DisplayURLAction procedure relies on Dynamic HTML (DHTML) to build an image tag, as callout B in Listing 2 shows. The image tag displays a visual UI cue (an image) and a table (initially hidden) that contains one row for each URL action defined for the member, as callout C in Listing 2 shows. In addition, callout B in Listing 2 shows that the image tag's onClick attribute is set to invoke a client-side script procedure called ShowActions. ShowActions, which Listing 3, page 42, shows, is passed the table's element ID so that it can toggle the display attribute between block and none. Thus, when the user clicks the image, ShowActions displays or hides the member's list of actions, according to the setting. The class attribute of the table is set to clsActionMenu, which sets the cursor type to hand to provide a visual cue that users can click for actions as callout C in Listing 2 shows. Each row in the table contains a column that displays the URL action returned from the rowset, as callout C in Listing 2 shows.

The table's CONTENT column contains the URL that the administrator defined and that was dynamically built when the Cube Editor generated the action. The ACTION_NAME column contains the action's name, in this case Syllabus. The column's onClick attribute is set at callout C in Listing 2 to run the client-side InvokeURLAction procedure. The InvokeURLAction procedure uses the Document Object Model (DOM) window object's Open method to open the URL in a separate window when the user clicks the action.

The only remaining task is to position the call to DisplayURLAction. The ASPADOComplex.asp page contains loops that iterate the axes that the MDX query produced in the cellset. As the captions for the row and column headers are printed to the ASPADOComplex.asp page, the following statement calls the DisplayURLAction procedure with the member's UniqueName:

Call DisplayURLAction(cst.Axes(1).Positions(j).Members(h).UniqueName)

If DisplayURLAction finds actions, the procedure adds their image to the HTML stream and sends the stream to the client. The resulting page shows the results of an MDX query that uses the Course- Num member with the actions activated for course 2072, as Figure 5 shows. Note that the icon directly to the right of each course number is the visual cue that URL actions exist for this member.

A New Dimension

By adding support for drillthrough and actions to OLAP client applications, you can provide the user with a whole new dimension of information. Analysis Services reduces by an order of magnitude the amount of developer effort that was previously required to provide these options with OLAP Services. Take a look at how you can integrate drillthrough and actions into your Web applications. If you're one of the many developers who requested that Microsoft add these abilities, I won't have to make that suggestion twice.