XMLA Documentation - SQL Server Analysis Services

XML for Analysis (XMLA)

XML for Analysis (XMLA) is an open, XML-based standard that provides a common interface for client applications to communicate with OLAP (Online Analytical Processing) and data mining technologies. It is a key component for interacting with SQL Server Analysis Services (SSAS) programmatically.

Overview

XMLA defines a set of SOAP-based XML messages that can be used to perform various operations on SSAS, including:

Executing MDX (Multidimensional Expressions) queries
Executing DAX (Data Analysis Expressions) queries
Executing DMX (Data Mining Expressions) queries
Executing XMLA commands for database management (creating, altering, dropping objects)
Discovering schema information

Key Concepts

Commands: Represent actions to be performed on SSAS, such as executing a query or processing a cube.
Requests: Messages sent from a client application to SSAS.
Responses: Messages sent from SSAS back to the client application, containing results or status information.
Data: The structure and content of data returned by SSAS, typically in XML format.

XMLA Operations

The primary operations supported by XMLA include:

Execute: Used to send a command to SSAS for execution. This is the most common operation.
Discover: Used to retrieve metadata and schema information from SSAS.

Example: Executing an MDX Query

XMLA Request (Execute)

<?xml version="1.0" encoding="utf-8"?>
<Command>
  <Statement>SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
    {[Date].[Calendar Year].MEMBERS} ON ROWS
  FROM [Adventure Works DW2019]</Statement>
</Command>

XMLA Request (Execute with SOAP Envelope)

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS,
          {[Date].[Calendar Year].MEMBERS} ON ROWS
        FROM [Adventure Works DW2019]</Statement>
      </Command>
      <Properties>
        <PropertyList>
          <DataSourceInfo>Provider=MSOLAP;Data Source=localhost;</DataSourceInfo>
          <Catalog>AdventureWorksDW2019</Catalog>
        </PropertyList>
      </Properties>
    </Execute>
  </soap:Body>
</soap:Envelope>

Example: Discovering Data Sources

XMLA Request (Discover)

<?xml version="1.0" encoding="utf-8"?>
<Discover>
  <RequestType>DISCOVER_DATASOURCES</RequestType>
  <Properties>
    <PropertyList>
      <DataSourceInfo>Provider=MSOLAP;</DataSourceInfo>
    </PropertyList>
  </Properties>
</Discover>

Microsoft Learn