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

Comments on this post

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

Requesting Gravatar...
Thanks Darren for the introduction to the XMLA. I am trying to have a nice grid or list of cubes in my database, if the cube is processed or not and the size. In fact, i am trying to have the same "meta data" windows available in SQL Server 2000 Analysis Manager.

I found an interesting blog about this : http://www.ssas-info.com/VidasMatelisBlog/34_wsh-scripting-for-analysis-services-2005#comment-697

Is there a way to have a better output with XMLA then the XML code currently showing?

Simon Deslauriers
Left by Simon Deslauriers on May 02, 2008 4:28 AM

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

Requesting Gravatar...
To get the same cube metadata as displayed in SQL Management Studio, you can use the DISCOVER_XML_METADATA request in an XMLA query like so:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions >
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>SSAS - Premium and Claims V2</DatabaseID>
<CubeID>PDW04 1</CubeID>
<ObjectExpansion>ReferenceOnly</ObjectExpansion>
</RestrictionList>
</Restrictions>
<Properties />
</Discover>

Note you need to specify the cube ID (in this case "PDW04 1") instead of the cube name ().
Left by Nathan Griffiths on Jan 27, 2009 11:58 AM

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

Requesting Gravatar...
Yes, DISCOVER_XML_METADATA will let you see all nearly all of the metadata related to a server, database or any of the objects inside the database. The only problem is that it can return a lot of data.

I have a function in the ASSP project www.codeplex.com/asstoredprocedures which will extract a table of information from the DISCOVER_XML_METADATA call:

CALL ASSP.DiscoverXmlMetadata("\Database\Cubes\Cube")
Left by Darren Gosbell on Jan 27, 2009 1:51 PM

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

Requesting Gravatar...
Hello how can I use this query :

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_MEASURES</RequestType>
<Restrictions />
<Properties>
<PropertyList>
<Catalog>PortalEmpleo</Catalog>
</PropertyList>
</Properties>
</Discover>

in SSIS project.

I want to insert its result into a table in the database

thank you
Left by pabsaa on Apr 15, 2009 1:34 AM

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

Requesting Gravatar...
@pabsaa

I am not aware of anyway to run XMLA Discover queries directly from SSIS, however there are a couple of other possible approaches. If you are using SSAS 2008 I would use an OLEDB source and run a DMV query. eg

SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES

If you are using SSAS 2005 you can use the DMV function in assembly from http://asstoredprocedures.codeplex.com to do a similar thing. eg.

CALL ASSP.DMV("SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES")

If you are using SSAS 2005 and for some reason are not able to use the .Net assembly approach then your last resort would be to use a script source and write some .Net code to either run the discover query or to list the measures using AMO.
Left by Darren Gosbell on Apr 15, 2009 7:21 AM

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

Requesting Gravatar...
Thank you Darren.

Yes i'm using SSAS 2005, and please
do you have somewhere example to run a discover query using AMO
Left by pabsaa on Apr 15, 2009 11:26 PM

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

Requesting Gravatar...
Hello,

Nice post and very exact start "There are not a lot of example XMLA queries available"... :) Very true!

I am trying to achieve a very unusual thing - I suppose. We are using SQL Server 2005.
We have implemented an SQL Server Agent job which starts cybe synchronization using the standard XMLA command Synchronize. The cubes we use are being created and processed on a remote server and the cubes are later synchronized with a local server. A lot of times the synchronization starts before processing of the cubes on the remote server is complete - and results in errors. I would like to add a step to the job we use which ocurrs before synchronization starts to check whether the state of the cube is Processed. And if it is, continue with sync; otherwise - retry in 15 minutes.

It would be great if you could tell me your opinion regarding this idea. I am not sure whether I can perform this kind of check for the state of the cube?

Thanks a lot!
Left by Yordanka Dragieva on Jul 27, 2009 5:19 PM

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

Requesting Gravatar...
Hi Yordanka,

It depends on how you do your processing. If the processing is all done in a single transaction your remote cube will always report that it is in a "processed" state. You could either look at initiating both the processing and the synchonization from the one process or you could try the technique from this post http://geekswithblogs.net/darrengosbell/archive/2008/12/02/ssas-are-my-aggregations-processed.aspx to see if the processing operation is still active.
Left by Darren Gosbell on Jul 27, 2009 8:39 PM

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

Requesting Gravatar...
Thanks, Darren!
Yes, basically there are 2 approaches - one is to use the 'cube processing' process to start the synchronization (work is done on source server); the other is to add something as a check before the synchronization is done (work is done on destination server).
I agree implementing the first approach is easier but there is (in our case) a long delay - because this server is property/responsibility of a different organization - so it would take time to "make" them do what is needed... :)

Thanks a lot for your feedback!
Left by Yordanka Dragieva on Jul 28, 2009 9:25 PM

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

Requesting Gravatar...
Yordanka,

In that case I would look into checking the locks on the processing server as I am guessing that this maybe what is causing you issues while attempting to synchronize.
Left by Darren Gosbell on Jul 28, 2009 10:55 PM

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

Requesting Gravatar...
Hi,you are right there is very less information on discover method in BOL. I wanted to use it to get information like sessions on SSAS, what query they are doing, performance etc...
Please can you help with some examples.
Left by Anoop Agarwal on Sep 24, 2009 4:16 PM

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

Requesting Gravatar...
In SSAS 2008 you can use the new DMV functionality to get this sort of information.

SELECT * FROM $System.DISCOVER_SESSIONS

Or if you are using SSAS 2005 you can use the DMV function from http://asstoredprocedures.codeplex.com

CALL ASSP.DMV("SELECT * FROM $System.DISCOVER_SESSIONS")

Then there is a DISCOVER_SCHEMA_ROWSETS rowset that will tell you all of the other rowsets that are available.
Left by Darren Gosbell on Sep 25, 2009 7:19 AM

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

Requesting Gravatar...

Alternative method here:


http://biblog.pl/jak-pobrac-liste-wszystkich-baz-danych-na-serwerze-analysis-services/?lang=en
Left by Matju on Oct 03, 2010 8:59 PM

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

Requesting Gravatar...
Really Nice posts.
Left by Madhu on Dec 23, 2010 12:50 AM

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

Requesting Gravatar...
Darren,

I really have a simple, straight forward question, but it likely doesn't have the same simple, straight forward answer. At least none of the things I've found or tried have worked.

My question is, is there a way to retrieve the list of SSAS databases and insert that into a SQL Server table for automating and maintaining a current list of available SSAS databases? A caveat being that I want to be able to run it from a SQL Job or SSIS package (and without CLR).

Thanks,

-Patrick
Left by Patrick Purviance on May 18, 2011 7:09 AM

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

Requesting Gravatar...
@Patric, One way to do this would be to setup a linked server to SSAS. Then you could run a query like the following from the SQL side (I have created a linked server called LINKED_OLAP on my SQL Server).

select Catalog_Name
from openquery(LINKED_OLAP, 'select [CATALOG_NAME]
from $system.dbschema_catalogs')

You could either use this as a source in SSIS or you could do an INSERT INTO in T-SQL or even just put this in a view and run it live each time. The only thing you may need to watch is that I believe the list will only show databases for which the user running the query has permissions. So if you populate this as part of a scheduled job you will need to make sure that the user that executes the command has the appropriate permissions in SSAS.

-Darren
Left by Darren Gosbell on May 18, 2011 9:23 AM

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

Requesting Gravatar...
Hi Darren,

I am getting below output from the above queries.Is there any way to fetch only cube name(Catelog Name)? Please let me know.

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="uuid">
<xsd:restriction base="xsd:string">
<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="xmlDocument">
<xsd:sequence>
<xsd:any />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="DESCRIPTION" name="DESCRIPTION" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="ROLES" name="ROLES" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="DATE_MODIFIED" name="DATE_MODIFIED" type="xsd:dateTime" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<row>
<CATALOG_NAME>ProdDB_F119_Daily_Gap</CATALOG_NAME>
<DESCRIPTION />
<DATE_MODIFIED>2011-05-03T14:28:57</DATE_MODIFIED>
</row>
<row>
<CATALOG_NAME>ProdDB_F120_Daily_Gap</CATALOG_NAME>
<DESCRIPTION />
<DATE_MODIFIED>2011-05-03T14:31:24</DATE_MODIFIED>
</row>
</root>
</return>

Thanks for your nice blog post.

Regards,
Vaibhav
Left by Vaibhav Biradar on Jun 27, 2011 4:25 PM

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

Requesting Gravatar...
@Vaibhav - When you are using XMLA you cannot alter the schema of the resultset. If you are using SSAS 2008 or later you could use a DMV query instead of XML.

eg. select [CATALOG_NAME] from $system.dbschema_catalogs

If you are using SSAS 2005 you either just have to ignore the extra columns or you could use the DMV function from http://asstoredprocedures.codeplex.com

Left by Darren Gosbell on Jun 27, 2011 4:54 PM

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

Requesting Gravatar...
Thanks Darren it helped me a lot as I am using the SSAS/MDX 2008.
One more doubt I need to ask, if there are serveral cube database but cube name is same say Portfolio.how can we ensure that MDX query is getting executed on Cube database A out of available databases A,B,C,D,E etc.

Please suggest.

Thanks once again!

Vaibhav
Left by Vaibhav Biradar on Jun 27, 2011 7:14 PM

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

Requesting Gravatar...
@Vaibhav - you specify the database to use in the connection string. So the cube will always be the one in the database specified in the connection string.
Left by Darren Gosbell on Jun 27, 2011 10:26 PM

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

Requesting Gravatar...
very nice tutorial.
i want to get all the measures in a cube.
for ssas 2008 i have used DMV and its working fine.
but its not working on ssas 2005 is there any way to get measures in ssas 2005 /
Left by Awais on Sep 24, 2011 4:11 PM

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

Requesting Gravatar...
@Awais - the DMV feature was only added in the 2008 version. Prior to that you either had to use the XMLA approach or you could use the DMV function in http://asstoredprocedures.codeplex.com if you don't mind installing a .net assembly.
Left by Darren Gosbell on Sep 26, 2011 7:52 AM

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

Requesting Gravatar...
How can I retreive the ID of a cube given its name . Or is there a DMV or any query that can give me the list of Database ID's
Left by Dora on Feb 03, 2012 3:32 AM

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

Requesting Gravatar...
Hi Dora, Unfortunately there is no DMV that lists the IDs. You can get them with a DISCOVER_XML_METADATA call, but that returns a lot of data.

If you are using AMO the Databases collection has FindByName and GetByName methods. Or you could write a script with something like Powershell to list all of the IDs.

One other shortcut is to right-click on the database and choose "Script Database As - Delete To" - that will give you a short XMLA script with the database ID in it.
Left by Darren Gosbell on Feb 03, 2012 7:19 AM

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

Requesting Gravatar...
Darren,

When using the DISCOVER_XML_METADATA is there a way to limit the results to only display the following properties?

<Database>
<Name></Name>
<ID></ID>
</Database>

I am finding on a few instances that the Name differs from the ID. I tried to use $system.dbschema_catalogs but that only displays the value of the Name which you see in SSMS. Which is the same when using:

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

This is the last step to fully automate the backup process of any and all SSAS databases. I am hoping to accomplish this with XMLA and TSQL. Push comes to shove I'll see if I can export the results from the DISCOVER_XML_METADATA query and then enumerate and parse the required fields.
Left by Samson J. Loo on Feb 09, 2012 3:38 AM

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

Requesting Gravatar...
Hi Samson,

It would be really nice to be able to do this. You can get kind of close by doing a discover request with the following restriction.

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

<RequestType>DISCOVER_XML_METADATA</RequestType>

<Restrictions>
<RestrictionList>
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>


This will return all server properties, but it will also return a <Databases> element with children that look like the following:

<Database>
<Name>PDW</Name>
<ID>PDW</ID>
<CreatedTimestamp>2007-02-07T19:57:37</CreatedTimestamp>
<LastSchemaUpdate>2007-02-07T21:45:06</LastSchemaUpdate>
<LastProcessed>1699-12-31T00:00:00</LastProcessed>
<State>Processed</State>
<LastUpdate>2010-06-28T06:27:34.803333</LastUpdate>
</Database>

Another thing that might be handy is the DiscoverXmlMetadataFull() function from the http://asstoredprocedures.codeplex.com project. It returns more data than you need, but it does flatten it into a rowset which may be easier to work with if you are able to use a .Net assembly on your server.

call assp.DiscoverXmlMetadataFull("Database")
Left by Darren Gosbell on Feb 09, 2012 6:52 AM

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

Requesting Gravatar...
Darren,

That is a significant amount of savings from a previous 129980 line to a mere 992. Definitely better! I might have to give the DiscoverXmlMetadataFull() a try. It might be a better option. I appreciate you taking the time to respond.

Thanks for the help!!!
Left by Samson J. Loo on Feb 09, 2012 10:53 AM

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

Requesting Gravatar...
The query is not returning the unprocessed cubes. Is there a way to retrive the unprocessed cubes as well?
Left by Nagi on May 21, 2012 6:05 AM

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

Requesting Gravatar...
@Nagi - these DMVs are designed to only return cubes which can be queried. The only way to see unprocessed cubes is to use an admin call like DISCOVER_XML_METADATA, but that returns quite a lot of data. It's probably easier to use AMO from .Net to get information on unprocessed cubes.
Left by Darren Gosbell on May 21, 2012 6:30 AM

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

Requesting Gravatar...
Darren, thanks for the clarification.
Left by Nagi on May 21, 2012 7:22 AM

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

Requesting Gravatar...
Can you please help me with a SQL XMLA or MDX or DMX query which give information about OLAP cubes name, OLAP cubes size and OLAP dubes last modfied date.

Regards
Ritesh
Left by Ritesh Kumar Panigrahi on Mar 27, 2014 9:17 PM

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

Requesting Gravatar...
@Ritesh - To get the cube names and last schema update you can do the following

SELECT CUBE_NAME, LAST_SCHEMA_UPDATE
FROM $SYSTEM.MDSCHEMA_CUBES

there is no simply way to get the cube size in a query. Using AMO via c# or powershell you can get the estimated cube size, but that is not always accurate. The most accurate way to get the size is to check the size of the folders in the data folder on the server.
Left by Darren Gosbell on Mar 28, 2014 6:31 AM

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

Requesting Gravatar...
Darren,

When using the DISCOVER_XML_METADATA is there a way to limit the results to only display the following properties?

<Database>
<Name></Name>
<LastUpdate></LastUpdate>
<DatabaseSize></DatabaseSize>
</Database>

Left by Sunny on May 28, 2014 5:39 PM

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

Requesting Gravatar...
@Sunny - no there is no way to select a subset of properties from the DISCOVER_XML_METADATA command. If you look at the ObjectExpansion restriction it supports a couple of different subsets of data, but the problem with your requirement is that none of them returns the size, but then you can get the other properties much easier from the DBSCHEMA_CATALOGS dmv.

If you don't mind using a .Net stored procedure you could use the DiscoverXmlMetadata command from htt[://asstoredprocedures.codeplex.com. The following call will give you a few extra columns, but it will return everything in a nice tabular rowset that you can call from a report.

call assp.DiscoverXmlMetadata("Database")

Note that this only returns the EstimatedSize, there is no esay way to get a single accurate "Size" figure from the DMVs.
Left by Darren Gosbell on May 29, 2014 7:10 AM

Your comment:

 (will show your gravatar)