Using ADO MD with .NET

Now that Visual Studio .NET and the Microsoft .NET languages are commercially available, many developers are going beyond experimentation and using Visual Studio .NET to develop production applications. If you're like me and you use SQL Server 2000 Analysis Services to develop applications, you probably hit a snag when you tried to combine it with Visual Studio .NET. The .NET Framework doesn't natively support data access for multidimensional (i.e., OLAP) data sources. So, OLAP developers have to use existing COM-based ADO MD for application development. At first glance, this problem doesn't seem important because .NET languages interoperate with COM. But have you tried making .NET and ADO MD work together? I have—and I found that it's not straightforward. Here are some of the problems I ran into and some ways you can avoid similar complications when you try to integrate .NET with your existing COM applications.

Programming with ADO MD
Programming with ADO MD is much like programming with ADO. Like ADO, ADO MD has two primary ways of retrieving information: You can retrieve metadata from the schema rowsets, or you can execute queries. However, ADO MD applications differ from ADO applications in that ADO MD applications typically rely more heavily on schema rowsets than ADO applications do. Schema rowsets in ADO MD contain dimensions, levels, and members that not only describe the structure of the data but are also a large part of the data. You can get answers to some user queries by using ADO MD's OpenSchema method to retrieve a schema rowset instead of using an MDX query. For example, in the FoodMart 2000 Sales cube, you can use an OpenSchema call to answer the question "What are all the product departments within the Drink product family?" OpenSchema is the method call that retrieves schema rowsets. With ADO—and relational databases in general—the structure of the tables typically doesn't change with the contents of the tables. With ADO, questions such as this one always require an SQL query.

To start programming with ADO MD from Visual Studio .NET, you need to import the ADO MD type library into your .NET project. You can do this the same way you add a reference in Visual Basic (VB) 6.0. In Visual Studio .NET, open the Project menu and select Add Reference. In the resulting dialog box, click the COM tab and scroll down to select Microsoft ActiveX Data Objects (Multi-dimensional) 2.x Library. You also need to select a non-multidimensional version of ADO named Microsoft ActiveX Data Objects 2.x Library. After you've selected both of these type libraries, click Select, then click OK. You've added the type definitions from these libraries to your project. Finally, if you're using C#, you can include the ADO MD type library so that you don't have to prefix all ADO MD type references in your source code with the name of the ADO MD type library. You can include the type library in each C# source file by adding the appropriate USING clause to your source code (e.g., USING ADOMD).

Problems with C#
Because ADO MD relies heavily on schema rowsets, you need to be able to fully utilize the OpenSchema method. Other than MDX, OpenSchema is the only mechanism that ADO MD provides to retrieve the contents of the schema rowset tables. Unfortunately, the OpenSchema method is difficult to call from a .NET language such as C# because of the complexity of the OpenSchema parameters. Two factors create this complexity: The parameters are optional, and the criteria parameter is an array. Each of these factors creates a problem when you try to integrate .NET and COM.

OpenSchema is designed so that you call it with one, two, or three parameters, as the following example calls show:

OpenSchema QueryType
OpenSchema QueryType, Criteria
OpenSchema QueryType, Criteria, SchemaID

However, in C# you can't issue the first two of these calls because you can't omit parameters—even when they're declared optional in the COM type library. This restriction is part of C# syntax. In other functions that take optional parameters, this type of restriction isn't a problem because you can issue the call with all parameters, passing default values for any parameters you would have omitted if the language had supported optional parameters (like VB does). But with OpenSchema, passing default parameters is difficult and risky because the values for the SchemaID parameter aren't available in the ADO MD type library. You'd have to determine what the default value for SchemaID is and declare the equivalent value in C#. Using SchemaID's default value is risky because a future version of ADO MD could change the values of these constants, and your code would no longer work because its declaration would contain an old value. The methods I used to solve these problems don't include redeclaring any ADO MD SchemaID constants in C# that could possibly change in the future. Let's look at each of the two problems you face with OpenSchema.