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.


Feedback

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

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 5/2/2008 4:28 AM | Simon Deslauriers

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

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 (). 1/27/2009 11:58 AM | Nathan Griffiths

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

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") 1/27/2009 1:51 PM | Darren Gosbell

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

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 4/15/2009 1:34 AM | pabsaa

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

@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. 4/15/2009 7:21 AM | Darren Gosbell

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

Thank you Darren.

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

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

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! 7/27/2009 5:19 PM | Yordanka Dragieva

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

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.
7/27/2009 8:39 PM | Darren Gosbell

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

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! 7/28/2009 9:25 PM | Yordanka Dragieva

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

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. 7/28/2009 10:55 PM | Darren Gosbell

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

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. 9/24/2009 4:16 PM | Anoop Agarwal

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

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. 9/25/2009 7:19 AM | Darren Gosbell

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


Alternative method here:


http://biblog.pl/jak-pobrac-liste-wszystkich-baz-danych-na-serwerze-analysis-services/?lang=en 10/3/2010 8:59 PM | Matju

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

Really Nice posts. 12/23/2010 12:50 AM | Madhu

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

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
5/18/2011 7:09 AM | Patrick Purviance

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

@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 5/18/2011 9:23 AM | Darren Gosbell

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

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 6/27/2011 4:25 PM | Vaibhav Biradar

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

@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

6/27/2011 4:54 PM | Darren Gosbell

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

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 6/27/2011 7:14 PM | Vaibhav Biradar

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

@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. 6/27/2011 10:26 PM | Darren Gosbell

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

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 / 9/24/2011 4:11 PM | Awais

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

@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. 9/26/2011 7:52 AM | Darren Gosbell

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

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 2/3/2012 3:32 AM | Dora

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

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. 2/3/2012 7:19 AM | Darren Gosbell

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

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. 2/9/2012 3:38 AM | Samson J. Loo

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

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") 2/9/2012 6:52 AM | Darren Gosbell

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

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!!! 2/9/2012 10:53 AM | Samson J. Loo

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

The query is not returning the unprocessed cubes. Is there a way to retrive the unprocessed cubes as well? 5/21/2012 6:05 AM | Nagi

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

@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. 5/21/2012 6:30 AM | Darren Gosbell

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

Darren, thanks for the clarification. 5/21/2012 7:22 AM | Nagi

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

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 3/27/2014 9:17 PM | Ritesh Kumar Panigrahi

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

@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. 3/28/2014 6:31 AM | Darren Gosbell

Post a comment





 

 

News

About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me

MVP


Legal
Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page

Twitter












Tag Cloud


Article Categories

Archives

Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs

Syndication: