SQL Server Analysis Services

XMLA Syntax Reference

Introduction to XMLA Syntax

XML for Analysis (XMLA) is a standard XML-based protocol for accessing and manipulating data stored in OLAP (Online Analytical Processing) and data mining systems. It provides a consistent way to interact with Analysis Services databases, allowing clients to send commands and receive results in a structured format.

Core Concepts

  • Commands: Actions to be performed (e.g., executing a query, discovering metadata).
  • Objects: Entities within Analysis Services (e.g., databases, cubes, dimensions).
  • Properties: Attributes of commands and objects.
  • Data Types: Standard XML Schema data types used for values.

XMLA messages are typically sent over HTTP or SOAP. The structure of an XMLA message involves a root element, usually Envelope, containing a Body, which in turn holds the specific XMLA command or response.

Key Syntax Elements

XMLA syntax is based on XML. The following are common elements and attributes you'll encounter:

  • Command: Contains a specific XMLA command.
  • Discover: Used to retrieve metadata or data.
  • Execute: Used to execute commands, often MDX or DMX queries.
  • root: Specifies the root object for a Discover request.
  • Attributes: Contains a list of attributes for a Discover request.
  • Properties: Specifies properties of the request or response.
  • Name: The name of an object or property.
  • Value: The value of a property or attribute.

XMLA Data Types

XMLA leverages standard XML Schema data types. Common types include:

  • xsd:string
  • xsd:integer
  • xsd:boolean
  • xsd:dateTime
  • xsd:decimal

Common XMLA Commands

EXECUTE Command

The EXECUTE command is used to send an MDX (Multidimensional Expressions) or DMX (Data Mining Expressions) query to Analysis Services. 

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope
  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Statement>
          SELECT {[Measures].[Sales Amount]} ON COLUMNS,
                 {[DimProduct].[Category].[Category].MEMBERS} ON ROWS
          FROM [AdventureWorksDW]
        </Statement>
      </Command>
      <Properties>
        <PropertyList>
          <DataSourceInfo>Provider=MSOLAP;Data Source=YourServerName;</DataSourceInfo>
          <Catalog>AdventureWorksDW</Catalog>
          <Format>Tabular</Format>
        </PropertyList>
      </Properties>
    </Execute>
  </soap:Body>
</soap:Envelope>

DISCOVER Command

The DISCOVER command is used to retrieve metadata about the Analysis Services instance or specific objects. 

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope
  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body>
    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
      <RequestType>MDSCHEMA_DIMENSIONS</RequestType>
      <Properties>
        <PropertyList>
          <DataSourceInfo>Provider=MSOLAP;Data Source=YourServerName;</DataSourceInfo>
          <Catalog>AdventureWorksDW</Catalog>
        </PropertyList>
      </Properties>
    </Discover>
  </soap:Body>
</soap:Envelope>

BATCH Command

The BATCH command groups multiple commands together to be executed as a single unit. 

<Batch xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Xact>
    <Commands>
      <Command>
        <InsertObject>
          <DatabaseID>MyDatabase</DatabaseID>
          <ObjectDefinition>
            <Cube xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">...</Cube>
          </ObjectDefinition>
        </InsertObject>
      </Command>
      <Command>
        <Execute>
          <Command>
            <Statement>CALL UpdateAddinCube()</Statement>
          </Command>
        </Execute>
      </Command>
    </Commands>
  </Xact>
</Batch>

BEGIN TRANSACTION Command

Starts a transaction. Commands executed after this will be part of the transaction.

COMMIT TRANSACTION Command

Commits all changes made within the current transaction.

ROLLBACK TRANSACTION Command

Rolls back all changes made within the current transaction.

Working with Analysis Services Objects

XMLA allows you to interact with various Analysis Services objects.

Databases

You can discover, create, drop, and process databases using XMLA. DISCOVER_DATABASES is a common request type.

Cubes

Cubes are the core multidimensional structures. You can discover cube schemas, create/alter cubes, and execute MDX queries against them.

Dimensions

Dimensions provide the context for measures. You can discover dimension attributes, and manage dimension data.

Measures

Measures represent the quantitative data in your cubes.

Members

Members are the individual instances within dimension hierarchies.

XMLA Properties

Various properties can be set to control the behavior and output of XMLA requests. Some common properties include:

  • DataSourceInfo: Connection string for the Analysis Services instance.
  • Catalog: The name of the database to connect to.
  • Format: Specifies the output format (e.g., Tabular, Xml).
  • AxisFormat: Controls the formatting of axes in query results.
  • ShowHiddenObjects: Whether to include hidden objects in discovery results.

Understanding these elements and their syntax is crucial for effectively interacting with SQL Server Analysis Services programmatically.