Mastering OLAP: Extending MDX

This month, we look at how you can extend the Multidimensional Expression (MDX) language to more tightly integrate it with your OLAP applications. MDX is a very capable language, but in some situations, you might want to add functions so that you can use non-OLAP information or functions in your MDX queries. For example, you might want to write an MDX query that returns year-to-date sales for all sales representatives who have 10 or more years of experience. If the years-of-experience parameter doesn't exist in your OLAP cube, you can write a user-defined function (UDF) that draws this information into your OLAP cube from another data source. As another example, you can use your own forecasting algorithm to plot a forecast curve. WebSQL subscribers can download the MDX sample program referred to in this article using the "Download the Code" link at the top of the page.

User-Defined Functions

The MDX puzzle we presented last month (for the answer, see "September MDX Puzzle Solution Revealed") lays the groundwork for the functionality we want to introduce this month. In Listing A in "September MDX Puzzle Solution Revealed," the CStr function demonstrates a powerful MDX feature: the ability to add third-party functions or your own custom functions by creating ActiveX DLLs, OLE custom controls (OCXs), and executables (EXEs), and registering them with OLAP Services. These UDFs are a way to solve problems such as those in the following scenario.

Frequently, when dimensions become large, you need to look for particular dimension members. You might have tens of thousands of customers and want to use part of a name to find someone. No built-in Sounds Like or Contains MDX function is available, so you need to write a UDF that checks whether one string contains another. Let's use such a function to search a dimension for member names. This function is similar to the CONTAINS operator in Transact SQL (T-SQL).

One easy way to write a UDF is to make an ActiveX DLL in Visual Basic (VB). UDFs must run in-process with OLAP Services, so they have to be in a DLL or an OCX, not an EXE. You can choose any language that can create ActiveX components. We chose VB for this example. The rule for creating these functions is simple: The parameters and return types must be numeric or string.

Listing 1 shows a simple UDF. The function IncludesStr checks whether a string contains another string. If so, the function returns the string True; otherwise, the function returns False. To create an ActiveX DLL, start VB 6.0 and select the ActiveX DLL project type from the initial screen. Enter the code from Listing 1 into the VB editor, and save the project as UDFSample.vbp. We named the class module UserFunc.cls. Then, to create the ActiveX DLL, pull down the file menu and select Make DLL.

The next step in using a DLL that contains one or more UDFs is to register the DLL with OLAP Services. Remember that you need to register the UDF DLL on the PC on which OLAP Services executes the MDX that references the UDF. Usually this PC is the client workstation, but if you want to use the UDF with server-defined calculated members, register the UDF on the server. Otherwise, register the UDF on the client because OLAP Services executes all other MDX statements on the client. You can configure OLAP Services to execute MDX on the server; however, this practice is usually not a good idea because OLAP Services doesn't scale well to many clients if you force an MDX query to execute on the server. OLAP Services is optimized to maximize scalability when it distributes the execution load across server and client. If you want experiment with server-side execution, see the white paper "Working with Large Levels in SQL Server 7.0 OLAP Services." The paper describes how to maximize scalability within very large dimension levels. You can distribute UDFs as part of the front-end application by adding the UDF DLL to your installation process and by using one of the following techniques to register the DLL with OLAP Services. Similarly, if you are implementing a three-tiered Web solution with OLAP client tools that run on the Web server, deploy the UDF on the Web server. To deploy a UDF on a Web server, add the UDF DLL to your installation process and use one of the described techniques to register the DLL with OLAP Services.

You can choose from a couple of ways to register an ActiveX component for use with OLAP Services. You can use the OLAP Manager on the machine that you want to register the function on, or you can use the MDX command USE LIBRARY in your OLAP client software. Using the OLAP Manager is a manual technique, but it's handy if you don't need to automate the process for distribution to a large number of machines. You can find this feature in the OLAP Manager by right-clicking on a cube and selecting Edit Cube. Although you register the UDF in the Edit Cube portion of OLAP Manager, the UDF is available with all cubes on the PC, not just the one you are editing. Then click the toolbar button that looks like a calculator; this button takes you to the Calculated Member Builder. Here, use the Register Function button and browse to the location of your DLL or OCX, as Screen 1 shows.

The easiest way to register UDFs for use by server-defined calculated members (members that you define in the Calculated Member Builder) is to use the OLAP Manager Calculated Member Builder. However, if you plan to use the UDF in a front-end application, as in our example, we'll show you how in the MDX sample program.

The alternative to using the OLAP Manager is to use the USE LIBRARY command in your OLAP client software to register the function library. This command registers your UDF library with the OLAP client software for only the duration of the session, unless your client software remembers the library and reregisters it for you in each session. The command is

   USE LIBRARY "c:functionsUDFSample.dll"

Try this command in the MDX sample program, but make sure you change the path to the location of the UDFSample.dll that you just created so that the command will find this .dll file. After you execute UDFSample.dll, you won't notice any difference in the MDX Sample program, but executing it lets you run the query in Listing 2, which uses a UDF we created to solve the problem and search for members.

This MDX query returns Unit Sales for the quarters in 1997 for all product brand names that include "oo," as Screen 2 shows. The query uses the Filter function to return this data. The Filter function accepts a set of members (all brand names in the product dimension) as input and accepts a filter criterion to check against each member in the set. The output of the filter is only members that meet the filter criterion (where the IncludesStr function returns True). Try to run the query in Listing 1, but be sure to run the USE LIBRARY statement first; otherwise, OLAP Services won't recognize the UDF. Screen 2 shows the result.

We took the long way to reach the solution. We said earlier that VBScript functions are available from MDX. The InStr function in our UDF is a VBScript function, so we didn't need to write a UDF in VB to search for members; Listing 3 returns the same result as Listing 2.

In Listing 3, the VBScript InStr function searches for product brand names that include "oo." The InStr function returns the position of the first occurrence of one string within another. The first parameter determines the character position to start the search at. The string to search is in the second parameter, and the string to search for is the third parameter. If the query doesn't find an occurrence of the "oo" string, the InStr function returns 0. Therefore, any non-zero result means the query found the string.

In Mastering OLAP, "Indispensable Calculated Members" (June), we explained that CurrentMember is a placeholder that is filled for each member of a set. The Filter function iterates through all the dimension members in the Brand Name level of the product dimension. The InStr function executes for each dimension member in which the CurrentMember keyword is replaced by the dimension member name. Using the InStr function directly from MDX is more efficient than using the UDF in this example because there is one less layer of code.

Extending MDX with UDFs is a powerful way to augment the basic capabilities of MDX for a specific OLAP application. It lets you, for example, use non-OLAP information to query an OLAP cube. You can add string manipulation, search, number translations, or database lookups in the middle of an MDX statement. These UDFs can be an efficient way to query OLAP data.