How can I get a list of cubes in one Analysis Services 2005 database using XMLA?

Written by Vidas Matelis. Posted in Management FAQ

Q: How can I get a list of cubes in one Analysis Services database using XMLA?

A: You can use following XMLA discover command:

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

To test this start Microsoft SQL Server Management Sudio, connect to Analysis Services server and from menu choose File->New->Analysis Services XMLA query. Paste above query and execute.

Result from my PC:

<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" />
          <xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_TYPE" name="CUBE_TYPE" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_GUID" name="CUBE_GUID" type="uuid" minOccurs="0" />
          <xsd:element sql:field="CREATED_ON" name="CREATED_ON" type="xsd:dateTime" minOccurs="0" />
          <xsd:element sql:field="LAST_SCHEMA_UPDATE" name="LAST_SCHEMA_UPDATE" type="xsd:dateTime" minOccurs="0" />
          <xsd:element sql:field="SCHEMA_UPDATED_BY" name="SCHEMA_UPDATED_BY" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="LAST_DATA_UPDATE" name="LAST_DATA_UPDATE" type="xsd:dateTime" minOccurs="0" />
          <xsd:element sql:field="DATA_UPDATED_BY" name="DATA_UPDATED_BY" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="DESCRIPTION" name="DESCRIPTION" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="IS_DRILLTHROUGH_ENABLED" name="IS_DRILLTHROUGH_ENABLED" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="IS_LINKABLE" name="IS_LINKABLE" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="IS_WRITE_ENABLED" name="IS_WRITE_ENABLED" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="IS_SQL_ENABLED" name="IS_SQL_ENABLED" type="xsd:boolean" minOccurs="0" />
          <xsd:element sql:field="CUBE_CAPTION" name="CUBE_CAPTION" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="BASE_CUBE_NAME" name="BASE_CUBE_NAME" type="xsd:string" minOccurs="0" />
          <xsd:element sql:field="CUBE_SOURCE" name="CUBE_SOURCE" type="xsd:unsignedShort" minOccurs="0" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Adventure Works</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2007-04-30T01:28:28</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:41</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Adventure Works</CUBE_CAPTION>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Channel Sales</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2007-04-30T01:28:28</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:41</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Channel Sales</CUBE_CAPTION>
      <BASE_CUBE_NAME>Adventure Works</BASE_CUBE_NAME>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Direct Sales</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2007-04-30T01:28:28</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:41</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Direct Sales</CUBE_CAPTION>
      <BASE_CUBE_NAME>Adventure Works</BASE_CUBE_NAME>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Finance</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2007-04-30T01:28:28</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:41</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Finance</CUBE_CAPTION>
      <BASE_CUBE_NAME>Adventure Works</BASE_CUBE_NAME>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Mined Customers</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2006-01-02T20:06:43</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:43</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Mined Customers</CUBE_CAPTION>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Sales Summary</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2007-04-30T01:28:28</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:41</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Sales Summary</CUBE_CAPTION>
      <BASE_CUBE_NAME>Adventure Works</BASE_CUBE_NAME>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
    <row>
      <CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
      <CUBE_NAME>Sales Targets</CUBE_NAME>
      <CUBE_TYPE>CUBE</CUBE_TYPE>
      <LAST_SCHEMA_UPDATE>2007-04-30T01:28:28</LAST_SCHEMA_UPDATE>
      <LAST_DATA_UPDATE>2007-05-23T01:34:41</LAST_DATA_UPDATE>
      <DESCRIPTION />
      <IS_DRILLTHROUGH_ENABLED>true</IS_DRILLTHROUGH_ENABLED>
      <IS_LINKABLE>true</IS_LINKABLE>
      <IS_WRITE_ENABLED>false</IS_WRITE_ENABLED>
      <IS_SQL_ENABLED>true</IS_SQL_ENABLED>
      <CUBE_CAPTION>Sales Targets</CUBE_CAPTION>
      <BASE_CUBE_NAME>Adventure Works</BASE_CUBE_NAME>
      <CUBE_SOURCE>1</CUBE_SOURCE>
    </row>
</root>
</return>

This FAQ is based on Darren Gosbell blog entry.

Tags: faq, management