Detect Tabular or Multidimensional mode with AdomdClient

In this excellent blog post, Marco Russo explains how to programmatically detect if an instance of SSAS is in Tabular or Multidimensional mode.

This post is a great resource but didn’t fully resolve my own issue. The project I’m working on already had a reference to Microsoft.AnalysisServices.AdomdClient.dll and if I could avoid a second dll of this kind it would be a nice achievement. Seriously, if you can avoid to package and deploy dlls related to SQL Server … do it!

The second enlisted option in Marco’s post is based on Analysis Management Objects and means that you need to reference Microsoft.AnalysisServices.dll. As previously explained: no way!

So the only remaining option viable for my case is to use the XMLA approach (with the Microsoft.AnalysisServices.AdomdClient.dll). The AdomdCommand class has a nice method ExecuteXmlReader. This method returns an XML reader, it sounded like a nice point to start. But it’s definitely not a valid option, the AdomdCommand doesn’t support discovery request. If you try to provide the xml discovery request in the property CommandText then, at the execution, you’ll receive this kind of message.

Executing the query ...

The Discover element at line 7, column 72 (namespace urn:schemas-microsoft-com:xml-analysis) cannot appear under Envelope/Body/Execute/Command.

Execution complete

How to perform a discovery request on a cube with just the AdomdClient library? Simply by using a nice method of the AdmodConnection (Yes connection, not command) class named GetSchemaDataSet. This method expects the name of the AdomdSchema that you want to query: In our case DISCOVER_XML_METADATA. You also need to provide some restrictions to avoid a gigantic response from SSAS. In this case the restriction is “ObjectExpansion” with value “ReferenceOnly”.

using (var conn = new AdomdConnection(connectionString))
{
   conn.Open();
   var restrictions = new AdomdRestrictionCollection();
   restrictions.Add(new AdomdRestriction("ObjectExpansion", "ReferenceOnly"));
   var ds = conn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
}

Next task is to decrypt the response sent by SSAS. This response is stored in a dataset with one table and one row. The unique field contains the whole xml string. The best option is to load this string into an XmlDocument before parsing it.

var xml = ds.Tables[0].Rows[0].ItemArray[0].ToString();
var doc = new XmlDocument();
doc.LoadXml(xml);

You need to query this xml document to extract the ServerMode node’s text. Don’t forget to correctly configure the namespaces before querying the document!

protected string ParseXmlaResponse(XmlDocument doc)
{
   var root = doc.DocumentElement;
   var nm = new XmlNamespaceManager(doc.NameTable);
   nm.AddNamespace("ddl300", "http://schemas.microsoft.com/analysisservices/2011/engine/300");
   var node = root.SelectSingleNode("//ddl300:ServerMode", nm);
   if (node == null)
   throw new ArgumentException("Unable to locate the node for ServerMode.");
   return node.InnerText;
}

Note that you’ll need, at least, the version 11.0 of the AdomdClient library, previous versions will not return the node ServerMode.

Side note: This discovery request also returns the version of the SSAS instance. Even with the AdomdClient library 10.0 (or previous), you can parse this field “version” and if its content is less than 11.0, you can take the conclusion than this is surely a multidimensional instance. 😉

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s