Mastering OLAP: Writing Applications with ADO MD

In the past few articles, we've focused on problems you can solve with Multidimensional Expressions (MDX). This month we take MDX a step further and introduce how you can use ActiveX Data Objects, Multidimensional (ADO MD) to write applications. Transact SQL (T-SQL) and ADO have the same relationship that MDX and ADO MD share. MDX is a query language and ADO MD is an API that accepts MDX queries. ADO MD is a set of multidimensional extensions to ADO that lets you write COM object code to access data in an OLE DB for OLAP data source.

If you've used ADO or other Microsoft COM APIs, you'll be comfortable with ADO MD. You'll want to learn ADO MD because it's the easiest way to develop custom OLAP applications.

Even if you've never used ADO, you'll be able to write the code because we'll show you everything you need in Visual Basic (VB) to run an MDX query and get results. First, make sure you have the OLAP Services client tools, which SQL Server 7.0 includes, installed on your workstation. You also need the OLAP Services server on your local PC or on another PC on your network. Remember, you can run the OLAP Services server only on a PC with Windows NT or Windows 2000. The OLAP Services server won't run on Windows 9x. Alternatively, if you have a local cube file (*.cub), you can use ADO MD without the server portion of OLAP Services by specifying the name of the cube file when you open a connection. You can use Microsoft Excel 2000 to create local cube files.

Figure 1 shows how the OLAP pieces fit together. OLAP Services consists of a client and a server portion. The OLAP server supports the Decision Support Objects (DSO) API. The client, called PivotTable Services, implements OLE DB for OLAP, a standard interface for returning OLAP data. ADO MD extends ADO in the same way that OLE DB for OLAP is an extension of the well-known OLE DB interface. OLE DB for OLAP is a high-performance COM interface that doesn't support OLE automation and that requires some lower-level buffer handling, which is best conducted with C++ programming. ADO MD is a layer on top of OLE DB for OLAP that supports OLE automation, and ADO MD is VB-friendly because it adheres to VB naming standards and data types.

To develop an ADO MD program, you first need to reference the correct libraries. Pull down the VB Project menu, and select References. Then select the two ADO libraries, as Screen 1 shows. After you select Microsoft ActiveX Data Objects (Multidimensional) and the Microsoft ActiveX Data Objects 2.1 library, you're ready to use features from the traditional ADO library and the extended features in the ADO MD library. You need both libraries because ADO MD leverages some of the basic objects, such as the Connection object, from the regular ADO library.

Then open a connection to OLAP Services and list the available databases and cubes. Listing 1 creates a Connection object and opens the connection with a connection string. This ADO syntax specifies that you want to initialize a session with the MSOLAP provider on the local workstation (LocalHost). If you connect to a remote PC running OLAP Services, you need to change the LocalHost to the remote machine name. The Provider parameter specifies which OLE DB provider to connect to, then specifies PivotTable Services (MSOLAP), Micro-soft's implementation of OLE DB for OLAP.

After you establish an ADO MD connection, you can find the databases and cubes that reside on this server and are available for display to the user. (In the world of ADO and OLE DB, databases are called catalogs.) OpenSchema, a method of the Connection object, lets you retrieve a recordset with the list of databases. You can use the OpenSchema method with a single parameter that specifies which schema table you want to retrieve. The schema types that ADO MD supports are adSchemaCubes, adSchemaDimensions, adSchemaHierarchies, adSchemaLevels, and ad-SchemaMeasures. ADO supports many other schema types, but you use the ones we listed when you're developing OLAP applications.

In Listing 1, adSchemaCubes gets the list of cubes. Note that ADO MD has the same concept of a default database as ADO in that you set a default database before accessing the database's contents. Verify that the default database is FoodMart before you get the list of cubes. The schema rowsets for catalogs and cubes have useful meta data such as the last time a cube was processed and by whom, and when it was created. In Listing 1, the MsgBox function displays the cube name field.

Listing 2 shows how to use the Open method of the ADO MD Cellset object to execute an MDX query. The ADO MD Cellset object is similar to a Recordset object in ADO, but it supports more than two dimensions. A Recordset has rows and columns; a Cellset can have many axes. The shorthand names for the first five axes are: rows, columns, pages, sections, and chapters. The ability to access more than two dimensions is important because the underlying data is stored in more than two dimensions (hence the name multidimensional database). Because MDX can access and return more than two dimensions, it can access almost any subset of the underlying database.

The query in Listing 2 displays the dimension members on the filter axis, so you can see the dimension members that are filtering the result. The filter axis corresponds to the WHERE clause of an MDX query. If you don't include a WHERE clause in the MDX query, OLAP Services assumes that you're selecting the default dimension member from each dimension that the query doesn't reference. The output in Figure 2 displays 12 filter members, which is a correct result because the only dimension listed on the non-filter axes is the Time dimension. All other dimensions have their default member selected on the filter axis.

After displaying the filter members, the code in Listing 2 displays the number of axes in the Cellset, as Figure 2 shows. Our MDX query specifies only the contents of the column's axis, so the number equals one. If you include members on the rows and columns, the number is two. The filter axis is always included in the Cellset; it isn't counted in this number. You need the number of axes to determine how to display the result. Most OLAP applications display only two-dimensional result sets as charts and grids, but you can display a third and fourth dimension as tab pages in Excel worksheets, for example.

Next, you iterate through each position (coordinate) of the axis to display the dimension member for each column position of the Cellset, as Figure 2 shows. The Cellset has a Members collection for each position of the axis. In our simple query, each position has only one dimension member, so you index the collection with zero. If more than one dimension is nested on a single axis, the Members collection will have a dimension member for each dimension on the axis. Understanding how you iterate through each position and index the collection is important if you want to develop an OLAP application that can display different query result sets.

Look at the query and result in Screen 2. This query nests the Measures dimension with the Customer dimension on the columns axis. The result of this query has four positions on the columns. As the result shows, the CrossJoin function joins each dimension member from the two sets to form every possible combination.

In Listing 2, the UniqueName property displays the name of the dimension member at each column position. The UniqueName property is the fully qualified member name, which is distinct from all other member names in the cube. This fully qualified member name is useful for creating MDX queries, but it isn't user-friendly because it includes brackets and periods. Also, a Caption property on the Member object gives the name you want to display. For example, for the member with a UniqueName of [Time].[1997].[Q1] the caption is Q1.

Displaying the Cell Values

The final block of code in Listing 2 displays the cell values for the MDX query. It's important to know that each cell in the result of an MDX query contains more than one piece of information, which is different from a traditional SQL result set. You can retrieve the value in the cell as a numeric value or a formatted string. When you use the OLAP Manager to build a cube, you can specify how to format numbers for display. You can assign to each measure a different formatting specification (e.g., currency or percent), which uses the same format masks that you use in Visual Basic for Applications (VBA). This formatting specification is a great feature because it displays numbers in a consistent format among multiple front ends. Note that not all OLAP client tools can interpret format information, which is passed back with a Cellset.

In addition to retrieving a value as a numeric or formatted string, you can retrieve other cell properties for each cell in the Cellset. The OLE DB for OLAP specification defines a variety of cell properties, including foreground and background colors and font attributes. Currently, the OLAP Manager doesn't let you enter this information.

In Listing 2, the FormattedValue property displays the resulting cell values. As you can see in Screen 2, the FormattedValue property formats the cell values as currency with commas and two decimal places. Using the FormattedValue property is beneficial because different OLAP applications that use the same cube will display the data in the same format.

Only the Beginning

These ADO MD examples provide only a snapshot of what you can do when you build an application. ADO MD lets you write COM object code that accesses data in an OLE DB for OLAP data source, which saves time when you develop custom client-server or Web-based OLAP applications.

In future columns, we'll discuss ADO MD in more detail. We hope we've piqued your interest enough that you'll start exploring ADO MD on your own. (See also, "Mastering OLAP: Using English Query with OLAP" and "Mastering OLAP: Local Cube Files").