Solving Integration Problems
First, let's look at how to deal with the problem of OpenSchema's optional parameters. As I just explained, C# doesn't support omitting parameters, and the values for the third parameter, SchemaID, aren't included in the ADO MD type library. Thus, you have to determine an innovative way to call OpenSchema. To omit a parameter, you have to take advantage of late binding. Usually, binding takes place during a program's compilation, but late binding means that a method call and its parameters aren't validated against its definition until runtime. Listing 1 shows an example of how to use late binding with only one parameter to call the Open method on the Connection object. The example works because the InvokeMember method in the .NET runtime library can determine how to issue a method call with some parameters omitted, which the C# compiler can't do. Calling the Open method with just a connection string is convenient because you rarely use other parameters (e.g., UserID, Password, Options) with Analysis Services. The UserID and Password parameters are typically unnecessary because Analysis Services uses Windows authentication to determine the identity of the client application.
The use of InvokeMember() in Listing 1 deserves an explanation. InvokeMember() is a flexible and powerful method that lets you perform late binding to access an object property (e.g., GET or SET) or lets you call a method in which the structure of the call is determined at runtime. Because ADO and ADO MD support retrieval of type information about the available methods, parameters, and properties at runtime, InvokeMember() can determine how to package the parameters you provide it so that it creates the correct call to the Open method on the Connection object.
The InvokeMember method is available on each Type object in .NET. Don't confuse a Type object with an object of a particular type. A Type object in .NET contains information about the type but isn't an instance of the type. (The fourth parameter of InvokeMember, Conn in Listing 1, is the instance of the type on which you issue the method call.)
The first parameter of InvokeMember, Open in Listing 1, is the name of the method or property to be accessed. The second parameter, BindingFlags.InvokeMethod, tells InvokeMember what type of operation you want performed. In the example that Listing 1 shows, you want InvokeMember to invoke a method. The third parameter is the binding object. If you pass NULL as the third parameter, as Listing 1 shows, you get the default binding object. The binding object controls the manner in which the .NET runtime library accesses the object (which is the fourth parameter, Conn). Finally, the fifth parameter, new Object\[], is the list of parameters that you pass to the method. I could have added multiple items to the array to pass more parameters to the method, but instead I passed an array containing just the connection string.
The second problem you encounter when you issue method calls to OpenSchema is figuring out how to declare and construct the Criteria parameter in C#. When I was trying to integrate my COM applications with .NET, I encountered this problem because I didn't have any examples of how to declare the elements of the criteria array in C#. Usually, you can use the Visual Studio .NET object browser to see a parameter's type. But in this case, the types can change with different calls to OpenSchema (depending on what restrictions you want to use) and the parameters are nested inside an array—the object browser doesn't show types inside an array parameter. C#'s data types aren't exactly the same as those in COM, so to match the parameters in a COM method call correctly, you need to know how the COM interoperability layer in .NET will translate the .NET data types into COM data types. Unfortunately, if you get the data types wrong, you get a generic error message that doesn't give you a clue about how to fix the problem. In COM, the Criteria parameter is a safe array of BSTR variants; you'd never need to know that information if you used ADO MD from VB 6.0. You can pass a regular VB array to OpenSchema, and it just works. Explaining a safe array of BSTR variants is beyond the scope of this article, but I'll demonstrate how to define the equivalent criteria array in .NET.
Listing 2 shows how to use a criteria array to call OpenSchema from C#. You declare a standard C# array of objects and fill it with either NULL or strings. The criteria are values for some of the columns in the schema rowset; criteria define which rows you want returned from the rowset. For example, if you want to return the dimension rowset rows in which the cube name is Sales, your criteria would specify a restriction on the Sales cube's name column.
Each schema rowset has its own possible criteria, which SQL Server Books Online (BOL) lists in the "Schema Rowsets, OLAP" topic. For each rowset, a section of BOL lists the columns in that rowset that you can use as criteria. The column order is important. For example, the dimension schema rowset has the criteria columns CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_NAME, and DIMENSION_UNIQUE_NAME. If you want to use criteria to retrieve specific information from any schema rowset, your array of criteria must be in the order that BOL specifies. In Listing 2's code, I restricted the dimension schema rowset by cube name, so I had to include space in the array for the preceding array locations CATALOG_NAME and SCHEMA_NAME. However, I didn't want to restrict the resulting rowset to those columns, so I placed a NULL in the first two array locations.
Let's look at another example of how to construct OpenSchema's criteria array. One of the most complicated schema rowsets is the members rowset. To filter the members returned in an OpenSchema call, you can use 11 restriction columns and a tree operator. You use the first 11 restriction columns the same way that Listing 2 shows, but the final members rowset restriction is different. The tree operator restriction lets you retrieve dimension members by placing a related member in the MEMBER_UNIQUE_NAME restriction. For example, as Listing 3 shows, if you wanted to retrieve all the dimension members that are the immediate children of the CA member, you could place CA's unique name—[Customers].[All Customers].[USA].[CA]—in the MEMBER_UNIQUE_NAME criterion, then place the MDTREEOP_CHILDREN constant in the tree operator criteria. The criteria array is flexible; each element of the criteria array has a meaning that depends on which schema rowset you're accessing. This particular example differs from those I described previously because the element in the criteria array is an operator rather than a rowset column value.
Notice in Listing 3 that I defined constants for the different tree operators because the operators aren't available in the ADO MD type library after you import it into Visual Studio .NET. I don't know why the constants don't appear, but redefining the constants helps you issue OpenSchema calls on the members rowset.
I recommend using the tree operator with the OpenSchema call instead of running MDX queries to find the children of a dimension member because OpenSchema is significantly faster. Regardless of the technique you use, always be aware that dimensions can contain large numbers of members, and retrieving more dimension members than you need can be costly. For example, avoid retrieving all of a member's descendants or even all the members of a dimension level. You could end up retrieving a million or more members if you're using your application against a large cube.
Now that you know how to issue OpenSchema calls to ADO MD from C#, you can use the code samples in this article as a guide for using Visual Studio .NET to develop your next analysis application. Be aware that making COM method calls from .NET does incur some overhead because marshaling (a data and code transition between process address spaces) is necessary. However, this overhead won't materially affect your application's performance unless you call ADO MD thousands of times in succession.