ADOMD: GetSchemaDataSet musing and aggravation

I made a rediscovery today in ADOMD.NET: GetSchemaDataSet. This is a function that is callable from the AdomdConnection object and returns a DataSet, and a very useful function it is.

First off, what does it look like? Go here. If function complexity were judged by number of overloadable options, this guy should be awarded a medal at minimum.

You want a list of Catalogs/Databases on the server? Call GetSchemaDataSet. Like this:

AdomdConnection oConn = new AdomdConnection("Provider=MSOLAP;data source=local;"); DataSet ds = oConn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, new object[0]);
OK, so where does that get us? What GetSchemaDataSet returns is a standard DataSet with one Table. The columns in the table are as follows: CATALOG_NAME, DESCRIPTION, ROLES, and DATE_MODIFIED. The values we are most interest in are in the first column: CATALOG_NAME.

Now, retrieving the list of Catalogs is just one trick that can be performed by this function, you can get a taste of that by looking at the static AdomdSchemaGuid class. Browse to this page to see all of the possibilities. Using GetSchemaDataSet with the AdomdSchemaGuid you can get all sorts of data out of your cubes that is not available anywhere else in the API.

unfortunately, this isn't all of the story. Retrieving the list of catalogs is an easy task, same with retrieving a list of cubes, dimensions, kpis, measures, and members. But what if you only want a select group of members? Well, time to introduce yourself to the second parameter: restrictions. A entirely different beast there, and there is a reason it takes an array of objects.

The nice thing for the API designer about an array of objects is that it can take anything. The bad thing about an array of objects for the developer using the API is that it can take anything. And without documentation as to what can go there...oh yes, that is right were we are. Plus, there are some interesting compromises that were made as part of this.

When calling GetSchemaDataSet you can pass in a string and a AdomdRestrictionsCollection OR a GUID and a object array (there are some other options, but this is the basics of the dilemma). Also, the AdomdSchemaGuid thing I was talking about earlier, it is a static class with a bunch of properties that all return Guids. It is not, I repeat, NOT an enumerated type. Why is it not an enumerated type when you clearly treat it like an enumerated type? I have no idea.

Now the other part that sucks. If you want to use the AdomdRestrictionsCollection you have to pass in a string based schema -- but for the life of me I cannot figure out what format of string the thing wants! So as of right now I can't use the AdomdRestrictsionsCollection. Oh well I guess.

Furthermore, setting up the object array is not exactly trivial either. OK, technically creating an object array is easy. But getting it to return the data you want, not as easy. Here are the basics: for every value you want to pass in as a restriction you create two spots in the array. The first value is the column name, the second value is the value to filter by.
So technically, if you want to filter Dimensions by CATALOG_NAME, it should look something like this:

object[] oValues = new object[2]; oValues[0] = "CATALOG_NAME"; oValues[1] = "Adventure Works"; DataSet ds = oConn.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, oValues);
At least that is what it would look like if it worked. But it looks like there is actually a bug report out on that feature (see here) and a fix, but not until SP1.