THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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>HP7\DENALI</Name>

          <ID>HP7\DENALI</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=HP7\\denali");

    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 localhost\denali string with the name of your SSAS instance):

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

$server = new-object Microsoft.AnalysisServices.Server
$server.Connect("DataSource=localhost\denali")
write-host $server.ServerMode

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

Published Monday, July 18, 2011 11:15 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Thanks Marco. I have verified that SQL Browser is running, but I get this error message when trying to .Connect using PowerShell:

http://yfrog.com/kev5hp

Any ideas?

Thanks,

Aaron

July 18, 2011 11:20 AM
 

Marco Russo said:

I tried my script by also using the machine name instead of localhost (like in your example) and it still works.

Are you sure that SSMS is using the same name? Just to check - is SQL Browser service active?

Marco

July 18, 2011 11:34 AM
 

AaronBertrand said:

Yes, SQL browser is running. I can connect to default instances of SSAS no problem, but named instances do not work. Several people have filed before me (all closed as no repro) but I filed a new issue for Denali anyway:

http://connect.microsoft.com/SQL/feedback/details/679792/sql-browser-cannot-connect-to-named-instances-of-analysis-services

Are you using a named instance or a default instance? If both, which one did you install first?

July 18, 2011 12:55 PM
 

Marco Russo said:

I have only named instance on this PC. No default instance. I installed SQL 2008 first, then 2008 R2 and then Denali. I don't know if it could be related.

Marco

July 18, 2011 1:05 PM
 

AaronBertrand said:

No matter what I did with SQL Browser, it would not let me connect via the named instance. I had to go track down msdmredir.ini, find out what port number the named instance was listening on, and hard-code that in my PowerShell and SSMS connections. The instance name is never mentioned, just computer name:port number, e.g.:

$server.Connect("VM-DENALI:50560")

This works flawlessly but if you're connecting to a lot of machines then you have lots of machine/instance:port number associations to memorize. :-(

July 18, 2011 2:53 PM
 

Marco Russo said:

It seems that it's really the SQL Browser not answering for some reason.

Did you check firewall? I would try writing in the SSAS forum in order to do some escalation.

July 18, 2011 6:29 PM
 

AaronBertrand said:

Instance name works just fine from all clients/applications if I start sqlbrowser.exe manually with the -c switch.

July 18, 2011 6:35 PM
 

Chu Xu said:

Seems like this solution only works for SQL Server 2012. How to check it for SQL Server 2008 R2 in PowerShell script?

January 10, 2014 8:16 PM
 

Marco Russo (SQLBI) said:

Chu,

Considering you only have PowerPivot for SQL 2008 R2 and that its instance name is Always "POWERPIVOT", you might check the Name or Id property matching the "\POWERPIVOT" suffix.

Marco

January 11, 2014 1:10 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement