Detect Tabular or Multidimensional Version in #ssas #denali #ctp3

If you want to detect if a particular instance of Analysis Services “Denali” is running in Tabular or Multidimensional mode, you have several choices.

In the Books On Line you can find a few instructions to detect the version by looking at the icon used by SQL Server Management Studio when you connect to a SSAS instance, and if you have access to the file system of the server, you might open the MSMDSRV.INI file and look for the DeploymentMode property (0=Multidimensional, 1=SharePoint, 2=Tabular).

However, if you want to do that in a programmatic way, you should use a XMLA query. If you send this query to your server:

<Discover xmlns=“urn:schemas-microsoft-com:xml-analysis“>

       <RequestType>DISCOVER_XML_METADATA</RequestType>

       <Restrictions>

              <RestrictionList>

                     <ObjectExpansion>ReferenceOnly</ObjectExpansion>

              </RestrictionList>

       </Restrictions>

       <Properties>

              <PropertyList>

              </PropertyList>

       </Properties>

</Discover>

You will obtain this result (boring parts omitted with …), which contains a ddl300:ServerMode property that can be Multidimensional, SharePoint or Tabular, according to the server mode you installed. The following example is the result returned on my Denali instance I’m using:

<return xmlns=“urn:schemas-microsoft-com:xml-analysis“>

  <root xmlns=“…“>

    <xsd:schema targetNamespace=“…” elementFormDefault=“qualified“>

      …

    </xsd:schema>

    <row>

      <xars:METADATA xmlns=“…”>

        <Server>

          <Name>HP7DENALI</Name>

          <ID>HP7DENALI</ID>

          <CreatedTimestamp>2011-07-14T01:20:08.486667</CreatedTimestamp>

          <LastSchemaUpdate>2011-07-14T01:20:08.49</LastSchemaUpdate>

          <Version>11.0.1440.19</Version>

          <Edition>Evaluation64</Edition>

          <EditionID>610778273</EditionID>

          <ddl300:ServerMode>Tabular</ddl300:ServerMode>

        </Server>

      </xars:METADATA>

    </row>

  </root>

</return>

 

If you are lazy with XML and you like C#, you really need just three lines of code (remember to reference the Denali version of the Analysis Management Objects assembly, which is named Microsoft.AnalysisServices.dll):

    Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();

    server.Connect(“Data Source=HP7denali”);

    Console.WriteLine(server.ServerMode);

The ServerMode property returns an enum containing the Multidimensional, SharePoint and Tabular values.

And if you want to use PowerShell? No problem, this is a ready to use script (replace the localhostdenali string with the name of your SSAS instance):

[System.Reflection.Assembly]::LoadWithPartialName( “Microsoft.AnalysisServices”)

$server = new-object Microsoft.AnalysisServices.Server
$server.Connect(“DataSource=localhostdenali”)
write-host $server.ServerMode

Thanks to Jeffrey Wang, Darren Gosbell, Julie Strauss, Andrea Uggetti, Ashvini Sharma and Greg Galloway for providing me useful info.