September 1999

Manipulate and Query OLAP Data Using ADOMD and MDX, Part II: Writing the App

Carl Nolan

Multidi­mensional Expressions and ActiveX Data Objects Multidimensional can be used to construct Web apps for querying OLAP data. My sample app offers two means of querying the OLAP data, either by selecting dimensions for each axis or by selecting from a set of predefined queries.

This article assumes you're familiar with MDX, ASP, ADO, and DHTML

Code for this article: OLAP ASP Code.exe (10KB) Carl Nolan works for Microsoft Consulting Services (http://www.microsoft.com/norcal). He is an MCSD and MCP + Site Builder who specializes in SQL Server. He can be reached at carlnol@microsoft.com.

In my last article, I introduced you to Multidimensional Expressions (MDX) and ActiveX® Data Objects Multidimensional (ADOMD) in order to construct a Web-based application for querying OLAP data. The application presented here will offer two means of querying the OLAP data, either by selecting dimensions for each axis or by selecting from a set of predefined queries, which the user would otherwise not be able to construct. In either case, you'll be able to drill down into a particular member or data cell.
      As in the August 1999 article, "Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions," I will use the sales cube in the sample FoodMart database to assemble and test the application against. This information is installed as a sample database with Microsoft OLAP Services.

Figure 1 Retrieving Data in the Browser

Figure 1 shows how the application will look once data has been retrieved. Having drilled down into the USA customers, the view shows the customer dimension on the rows and measures on the columns. Although the final MDX statement is shown, the user only has to make a few mouse clicks to get at this display. Application Architecture       The application consists of two sections contained within a frameset. The first section is where you can either select the axes or choose from the list of predefined MDX queries. The second section presents the data and allows the user to click on any member or cell to drilldown into that specific data. The same presentation page and frame is used when making an initial data selection or drilling down into the cube.       To support this, it was necessary to break down the MDX statements into four sections: calculated member, column definition, row definition, and data measure. With these values, the required MDX statement is easily constructed. More importantly, as you will see, it lets either axis to be redefined to allow drilldown, while easily maintaining the other components of the MDX statement.       When giving the user a predefined query, the required four values can be easily defined. For the selection of dimensions and measures, you first need to construct dropdown lists for all possible options. Figure 2 shows what the required values for the dropdown list should be. All that remains is to build OPTION lists, one for the dimensions and another for the measures. Figure 3 outlines the ASP code for returning such an option list.       Once you have a list of the dimensions and measures, an HTML table to allow axis selection can be constructed. All that remains for the input section of the OLAP application is setting the four MDX sections and passing this information to the results section. This can be achieved in many ways, but for this application I chose to use a manual form with four hidden text fields to hold the MDX section values.        Figure 4 shows the BODY section of the completed page. There is a single form with four hidden text fields, which hold the sections of the MDX statement. The OPTION list of dimensions and measures is created in HTML by the ASP code that writes out the values calculated by the code in Figure 3. The remaining section is the script components for processing the user's information requests.       The script components are shown in Figure 5. The main purpose of this script is to set the values of the form's variables before submitting the form. The main component of the script is the function formInputSubmit, which validates the user request and sets the values of the required form fields. Form submission for the selection of axes is handled by the function viewData, and by the function queryChange for the predefined queries.       For the predefined queries, the field values to construct the MDX statement are derived based on the index of the dropdown list item selected. When you're selecting axes and measures, the actual value of the dropdown list item is used for the appropriate field values. As you can see in the code in Figure 5, the only selection required is that of a column dimension.       I have purposely glossed over the details here because this is very similar to standard ASP database programming. For the data presentation and drilldown capabilities, you need a more detailed understanding of MDX statement construction. These drilldown capabilities are the power of OLAP applications. Drilldown       There are two considerations in presenting OLAP cube data: the actual presentation of the data and the drilldown functionality. To support this drilldown the MDX statements are separated into the four components mentioned earlier. Let's first concentrate on the data presentation.       Since the data presentation page does not receive an MDX statement, the first step will be to construct the statement. This will be derived from the fields submitted to the page by the form submission. Once you have these fields, the MDX statement can easily be constructed. Note that the only required value is the definition of the columns axis (see Figure 6).       Once you have an MDX statement, an ADOMD Cellset object can be constructed and iterated through to format the HTML display. The Cellset object can be constructed directly from a connection and an MDX statement:

Set cellFoodMart = Server.CreateObject("ADOMD.Cellset") cellFoodMart.Open strMDX, conFoodMart

The obvious means of data display is an HTML table. Figure 7 outlines this process for an MDX statement that has both rows and columns. The table is constructed within a local string variable, which can then be output using the ASP Response.Write function. Although this method requires an understanding of HTML tags, it does allow the code to be easily ported into a compiled object, such as a Visual Basic® ActiveX DLL or ATL COM DLL.       The HTML table is constructed row-by-row, the first row being the column headings. You get these by taking the caption of the first member of the column axis positions. Remember, the first column heading needs to be blank.       Access to the actual data derived from the MDX expression is through the Cellset object. You can access a Cell object within the Cellset using standard array notation. In the case of a two-axis query, it will be a two-dimensional array. The key here is to iterate through the positions of the axes. Each position has a property called Ordinal that represents its location along the axis. The Ordinal property is used in extracting the Cell from the Cellset:

Set colFoodMart = cellFoodMart.Axes(0) Set rowFoodMart = cellFoodMart.Axes(1) For Each posFMRow In rowFoodMart.Positions ' Access the row axis names strRowCaption = posFMRow.Members(0).Caption For Each posFMCol In colFoodMart.Positions ' Access the data cell value strDataCell = cellFoodMart(posFMCol.Ordinal, posFMRow.Ordinal).FormattedValue Next Next

Now, let's incorporate the drilldown functionality. The key is stamping each cell within the table with two properties, the row and column axis definitions. This will enable the construction of a new MDX statement with the appropriate hierarchy drilldown.       There are many ways to perform this task, such as making each table detail an anchor, and then using a query string to pass the new MDX components to the processing page (in this case, the same page). I chose to use DHTML because it has the ability to extend a tag with custom attributes. Each cell will have one attribute that represents the new axis definitions and another to indicate whether the cell supports drilldown capabilities. When a user clicks on a cell that supports drilldown, the custom attribute's values are retrieved and saved into the form variables. Afterward, a manual form submission is executed.       But how do you calculate these custom attributes? When you're constructing the column axis table heading, you iterate through the positions of the appropriate axis. The default member has two important properties: UniqueName and ChildCount. The UniqueName property is the fully qualified name of the current member. To perform a drilldown into this level, the MDX statement would need to take the children of this unique name. To determine whether or not a particular level has children, the ChildCount property needs to be validated. If a ChildCount property returns zero, then the application should not support drilldown into this member. As a result, a customer attribute called nodrill is defined as such:

Set colFoodMart = cellFoodMart.Axes(0) For Each posFMCol In colFoodMart.Positions strDrillCol = posFMCol.Members(0).UniqueName If posFMCol.Members(0).ChildCount = 0 Then strDrill = "nodrill" Else strDrill = "" End If strValue = posFMCol.Members(0).Caption strCell = "<TD drillrow='' drillcol='" & strDrillCol & "'" & strDrill & ">" & strValue & "</TD>" Next

The situation is very similar for row axis table headings, but gets a little more complicated for the data cells. A data cell should only support drilldown if both the row and column members do. New definitions will be required for both the row and column axes to support the construction of a new MDX statement. To determine if drilldown is supported, you only have to validate the ChildCount property of the position's member on both axes:

If posFMCol.Members(0).ChildCount = 0 Or posFMRow.Members(0).ChildCount = 0 Then strDrill = "nodrill" Else strDrill = "" End If

Similarly, to calculate the value of the property to support member drilldown, the UniqueName of the position's member on both axes is derived:

strDrillRow = posFMRow.Members(0).UniqueName strDrillCol = posFMCol.Members(0).UniqueName

That takes care of the case where both row and column axis definitions are given. When only a column is specified the situation is slightly different, but the same rules for drilldown apply. Figure 8 contains the code for creating an HTML table given the appropriate MDX statement components. This example uses class definitions to separate functionality and style—a must if you're going to encapsulate the code within compiled objects. A separate stylesheet defines the formatting for each of the classes. For completeness, the stylesheet definition used to achieve the output from Figure 1 is listed in Figure 9. The classes not only help define the style, but also indicate if the HTML tag contains information pertaining to a dimension of measure value.       In this code, a function called ReturnConnection returns an ADO Connection object to the required OLAP database. If the application is being created for a few users, it would be beneficial to save the connection as a Session variable, created in the global.asa file. This would reduce the overhead of connecting to the OLAP database for each data query. On the other hand, this technique would be inappropriate for a moderate to large user base. Submitting the Data       Once the data has been displayed to the user with the appropriate custom attributes, all that remains is capturing the user's interaction with the data. Using DHTML the simplest way to do this is to capture the click event for the table or its container. Because of event bubbling, you can then capture the click on the table detail tag and cancel the event bubbling. Once a click event is captured, all you have to do is read the custom attributes, define the new values of the MDX components, and force a manual submission of the form on the page. The form submission is the process by which the new MDX component values are fed into the page for redisplaying the requested data.        Figure 10 contains all the code for this process. The viewData function reads the customer attributes and submits the form. The include file contains the function that will return the HTML table definition as a string variable, as shown in Figure 8. As I mentioned earlier, the functionality contained within this file could be encapsulated with a compiled object (possibly under MTS control), giving the benefit of executing compiled rather than interpreted code.       So how do you derive the new MDX component values? The new column definition for the MDX statement is derived from a property called drillcol, which equates to the unique name of the cell member. To drilldown into this member, all you have to do is derive an MDX statement that returns the children of this member:

var item = event.srcElement; if (item.getAttribute("nodrill")== null) { var strDrillCol = item.drillcol; if (strDrillCol!="") document.all.txtCol.value = strDrillCol + ".CHILDREN"; }

Before the user request is processed, you must determine if the clicked member actually has children, as indicated by the absence of nodrill, a custom property. Whether or not this property is present depends on whether the getAttribute function evaluates to null. Prior to this, the class of the item is also validated to ensure it contains either dimension or measure information.       Finally, cells that sport drilldown should indicate this to the users. Once again, this is where the item's class and custom nodrill attribute come into play. When a user moves the mouse over a cell that supports drilldown, the style of the cell is modified. The cell data is underlined and formatted with a red font and, more importantly, the cursor is changed to a hand. Obviously, the default values are returned once the user moves the mouse from over the cell. This action mimics the behavior of an anchor href tag, but you could just as easily define custom behaviors. Office 2000 Web Components       The advent of Office 2000 means you have one more way to present OLAP data to clients in an intranet. Two new Web components in Office 2000 are of interest to OLAP applications: a pivot table control and a graph control that can be bound directly to the data within the PivotTable® control. Using these ActiveX controls with very little coding, you can construct an application within minutes. A few additional lines of code will allow for custom views of the cubed data to be defined for the user.       Let's quickly go over the PivotTable control. Figure 11 shows the control when set up to view unit sales for stores by time in the FoodMart database. In this case, the first level of both store and time has been expanded.

Figure 11 Hosting the PivotTable Control

The control has a few interesting features. The obvious one is the PivotTable Field List window. You can select dimensions and measures using a simple drag and drop operation. You can also select a filter field, which lets you drag and drop to perform slicing on a cube. The PivotTable control has a simple toolbar that includes buttons for displaying the field list window, exporting the selected data into a Microsoft Excel PivotTable, and filtering and sorting. The plus signs make it easy for the user to see where they can drill into a dimension.       Since this is an ActiveX control, the first thing you must do is place an <OBJECT> tag on the Web page. The easiest way to do this is by using FrontPage® 2000, as the Office 2000 components are contained within the toolbar. The complete definition of the <OBJECT> tag is as follows.

<object classid="clsid:0002E520-0000-0000-C000-000000000046" id="FoodMartCube" width="518" height=""384"> <param name="XMLData" value="&lt; xml xmlns:x=&quot;urn:schemas-microsoft- com:office:excel&quot;&gt; &lt;x:PivotTable&gt; &lt;x:OWCVersion&gt;9.0.0.2710&lt;/x:OWCVersion&gt; &lt;x:CacheDetails/&gt; &lt;/x:PivotTable&gt; &lt;/xml&gt;"> </object>

The ID assigned to the object in this case is FoodMartCube. The only omission from this definition is the <CodeBase> tag. This will need to be included, pointing to your local installation file.       The rest of the work for controlling the cube data is done in code, albeit very little. The only operation that needs to be performed is defining the database connection information, much in the same way as you do for ADOMD:

// Define the required references to the pivot table var objPivot = document.all.FoodMartCube; // connect to the sales cube objPivot.ConnectionString = "Provider=msolap;Data Source=myserver;Initial Catalog=FoodMart"; objPivot.DataMember = "Sales";

At this point, the control is connected to the sales cube. To make things simpler for the user, it may be helpful to define a set of default axes and enable useful properties.       When working with the PivotTable control, two objects are important: the ActiveView and the FieldSets associated with the view. These are easily derived from the object reference:

// define a reference to the control's view and fields var objView = objPivot.ActiveView; var objFlds = objPivot.ActiveView.FieldSets;

The object view has three important properties relating to data selection: RowAxis, ColumnAxis, and DataAxis. When defining a predefined view of a cube, methods on these properties will need to be executed. For RowAxis and ColumnAxis, a method named InsertFieldSet is executed, which takes a field definition as its parameter. This adds the specified field into the appropriate axis. The field definition is derived from the previously defined FieldSets collection.

objView.RowAxis.InsertFieldSet(objFlds("[Time]")); objView.ColumnAxis.InsertFieldSet(objFlds("[Store]"));

A similar process occurs for defining the cube slice on the required measure. Here the function to call is InsertTotal, which takes as its parameter a special field called a total, derived from the Totals collection. The Totals collection represents the collection of measures:

objView.DataAxis.InsertTotal(objView.Totals("Unit Sales"));

Although in this example both axes and the measure are predefined, this does not restrict a user from selecting other dimensions and measures. In some situations, after a connection is made to the cube, it may be beneficial to present the user with a clean slate. The user is then free to select the data he or she wants to view.       At this point, the PivotTable is fully connected to the cube and has a set of defined axes. Certain properties can be set to make the user experience easier. One thing to note about displaying cube measures is that the values do not have a consistent display format. This can be remedied by defining a format for the required totals using a property called NumberFormat. The NumberFormat property takes a value similar to the Visual Basic Format function, as the following example shows:

objView.Totals("Unit Sales").NumberFormat = "#,#.00"

One other useful property of the control's view is the TitleBar. This property allows you to assign a caption to the title bar:

objView.TitleBar.Caption = "FoodMart Sales Database";

To end this brief discussion on the PivotTable object, it is worth mentioning some properties of the control itself. A property called DisplayFieldList controls whether the field list window is displayed. In addition, to ensure that the expansion indicator and toolbar are displayed, the appropriate properties can also be set:

objPivot.DisplayFieldList = 1; objPivot.DisplayExpandIndicator = 1; objPivot.DisplayToolbar = 1;

As mentioned earlier, the PivotTable control allows a drag and drop selection of a dimension for defining a slice on a cube. To enable this functionality, all you have to do is define a property called AllowFiltering:

objPivot.AllowFiltering = 1;

As you can see from this simple example, the PivotTable control very quickly allows a Web browser to be used as a client for OLAP analysis. There are many other features to this control, but the ones I mentioned should be sufficient as a starting point. Conclusion       Although the outlined application is simple, it provides a great amount of functionality. Before MDX and ADOMD, such functionality would not be possible with such a small amount of development time.       The ASP application presented can be extended in a variety of ways. Currently there is no support for tuples, which are multiple dimensions on a single axis. This can easily be incorporated if the need arises. Another idea is to allow the input of free-form MDX. Although not very useful for business applications, it can be valuable for developers. The possibilities are endless.       Although I've concentrated on ASP, for some intranet environments the Office 2000 Web components are very attractive. With very little effort, access to multidimensional data can be achieved on a Web client. The advantage or using ASP is the unlimited browser audience. In addition, the ASP solution allows for a greater degree of control in data presentation, especially with the types of MDX statements that can be utilized. The choice is up to you!

In "Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions" (MSJ, August 1999), the final code snippet in the Advanced MDX Statements section contained a filter statement which ultimately compared numeric values, when it should have compared members. The correct example should use UniqueName. SELECT [Store Type].MEMBERS ON COLUMNS, FILTER([Store City].MEMBERS AS StoreCities, StoreCities.CURRENT.PARENT.PARENT.UniqueName = "[Store].[All Stores].[USA]") ON ROWS FROM Sales WHERE (MEASURES. Sales Average], [Time].[1997])

For related information see: Microsoft ADO MD Programmer's Reference at http://msdn.microsoft.com/library/psdk/dasdk/adom1647.htm. Also check http://msdn.microsoft.com for daily updates on developer programs, resources and events.

From the September 1999 issue of Microsoft Systems Journal.