Writing XMLA Query to retrieve or manipulate data from Microsoft SQL Server Analysis Services

Microsoft SQL Server Analysis Services uses the XML for Analysis (XMLA) protocol to handle all communication between client applications and an Analysis Services instance. At their most basic level, other client libraries such as ADOMD.NET and AMO construct requests and decode responses in XMLA, serving as an intermediary to an Analysis Services instance, which uses XMLA exclusively.

To support the discovery and manipulation of data in both multidimensional and tabular formats, the XMLA specification defines two generally accessible methods, Discover and Execute, and a collection of XML elements and data types.

Because XML allows for a loosely coupled client and server architecture, both methods handle incoming and outgoing information in XML format. Analysis Services is compliant with the XMLA 1.1. specification, but also extends it to include data definition and manipulation capability, implemented as annotations on the Discover and Execute methods.

As a programmer, you can use XMLA as a programmatic interface if solution requirements specify standard protocols, such as XML, SOAP, and HTTP. Programmers and administrators can also use XMLA on an ad hoc basis to retrieve information from the server or run commands.

Discover Method (XMLA)

Retrieves information, such as the list of available databases or details about a specific object, from an instance of Microsoft SQL Server Analysis Services. The data retrieved with the Discover method depends on the values of the parameters passed to it.Namespace   urn:schemas-microsoft-com:xml-analysis

SOAP Action   "urn:schemas-microsoft-com:xml-analysis:Discover"

Now let see how to get 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>

Once you run, you will get the output and save it as XML file, you will see the output will be look like below.

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>

 In these query no any conditions apply under RESTRICTION, it retrieves the entire cube under all the databases.

Let see how to specify the particular database and retrieve only the cubes under the database.

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

                <RequestType>MDSCHEMA_CUBES</RequestType>

                <Restrictions />

                <Properties>

                                <PropertyList>

                                                <Catalog>UmaTEST</Catalog>

                                </PropertyList>

                </Properties>

</Discover>

Execute Method (XMLA)

Sends XML for Analysis (XMLA) commands to an instance of Microsoft SQL Server Analysis Services. This includes requests involving data transfer, such as retrieving or updating data on the server.

Namespace   urn:schemas-microsoft-com:xml-analysis

SOAP Action   "urn:schemas-microsoft-com:xml-analysis:Execute"

The following code sample is an example of Execute method call that contains a Multidimensional Expression (MDX) SELECT statement.

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

                <Command>

                                <Statement>

                                                SELECT [Measures].MEMBERS ON COLUMNS FROM [Adventure Works DW2012]

                                </Statement>

                </Command>

                <Properties>

                                <PropertyList>

                                                <Catalog>UmaTEST</Catalog>

                                                <Format>Multidimensional</Format>

                                                <AxisFormat>ClusterFormat</AxisFormat>

                                </PropertyList>

                </Properties>

</Execute>

Cheers!