SSAS: Using XMLA to get a list of Databases and Cubes.

There are not a lot of example XMLA queries available, so I thought I might work through some examples on my blog. This first example was prompted be a question in the Olap newsgroup and demonstrates how to get a list of databases and cubes.

Getting a list of the databases from Analysis Services is fairly easy with a simple XMLA query like the following:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <
RequestType>DBSCHEMA_CATALOGS</RequestType
>
   <
Restrictions
/>
   <
Properties
/>
</
Discover>

You would think that getting a list of cubes would involve another fairly simple XMLA query like the following:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <
RequestType>MDSCHEMA_CUBES</RequestType
>
  <
Restrictions
/>
  <
Properties
/>
</Discover>

If you run this code from SQL Server Management Stuido (SSMS) you might notice an interesting thing, which is the reason for this post. And that reason is that you only get a list of the cubes for a single database despite not having applied any restrictions to the query. So what is going on here? Well the answer is that the query for the list of cubes is executed in the context of a  connection and that connection is either to a specific database or if one has not been specified the first database on the server is used. So you can either specify a particular database when you connect to SSAS or you can use a query like the following one.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <
RequestType>MDSCHEMA_CUBES</RequestType
>
  <
Restrictions
/>
  <
Properties
>
    <
PropertyList
>
      <
Catalog>Adventure Works DW</Catalog
>
    </
PropertyList
>
  </
Properties
>
</
Discover>

It's not immediately obvious what properties in the properties section of the XMLA query are actually used for. The follow is an excerpt from Books Online:

Each property allows the user to control some aspect of the Discover or Execute method, such as defining the information required to connect to the data source, specifying the return format of the result set, or specifying the locale in which the data should be formatted

In the XMLA example above the Catalog element sets the database property of the connection which executes the MDSCHEMA_CUBES query.

Print | posted on Sunday, August 6, 2006 10:16 PM