What is the definition of XML for Analysis or (XMLA), language abbreviated as Extensible Markup Language for Analysis. It’s a protocol built on top of XML language. It allows data access from standard multidimensional data sources. Client applications accesses Microsoft cubes and tabular models using the XMLA language and the HTTP protocol. First, what is XMLA or XML for Analysis? Microsoft Analysis Services OLAP cubes and Tabular models both uses XMLA. In other words, it’s the unique protocol that allows communication with end-user’s data analytics applications.
Table of Contents
1. What definition for XML for Analysis?
Secondly, how does Microsoft developers use XML for Analysis? Microsoft Business Intelligence developers use XMLA for client applications integration with Analysis Services. And without dependencies to the Microsoft .NET framework.
2. XMLA as a simple standard
In other words, to communicate from the client to the server, the only two necessary components are an HTTP connectivity and the XMLA or XML for Analysis language. Check this article also on Microsoft Business Intelligence, it’s about the MDX language, literally the SQL for OLAP cubes.
Bridging the Gap with XMLA
The primary advantage of XMLA is its ability to bridge different systems.
It does not matter whether the client is built on Microsoft technology or not; as long as it can send XMLA requests over HTTP, it can communicate with Microsoft BI services. This interoperability is a key factor in the widespread adoption of XMLA within the business intelligence community.
3. XMLA practical usage
Use XMLA for example to develop an SSAS Tabular Model programmatically.
Or to create deployment script of the connection, the tables – including the fact tables and the dimensions – the security Roles or the full tabular database. To generate these XMLA scripts, simply connect to an SSAS Tabular instance with SSMS, then right click and select Scripts the object. Three main options are available when generating XMLA scripts for a tabular database:
- CREATE
- CREATE OR REPLACE
- DELETE
Check the Wikipedia page on XML for Analysis standard. To go further and explain the benefits of XML instead of other proprietary standards for example: it’s open source, it’s easy to read for a human, it’s a simple file with tags. It means the structure is encapsulated in the file and any data structure can be stored inside an XML message.
3.1 XMLA script to deploy an SSAS In-Memory Tabular Model
To illustrate this XML for Analysis definition, let’s have a more practical approach of this standard. The XMLA script below uses the CREATE keyword to create an example Tabular Model reading data from a Sales table, called Tabular_Model_Sales.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create>
<ObjectDefinition>
<Database>
<ID>Tabular_Model_Sales</ID>
<Name>Tabular_Model_Sales</Name>
<Description>A simple SSAS Tabular Model Example</Description>
<CompatibilityLevel>1500</CompatibilityLevel>
<DataSources>
<DataSource xsi:type="RelationalDataSource">
<ID>DataSource</ID>
<Name>DataSource</Name>
<!-- Adjust connection string to your SQL database here -->
<ConnectionString>Data Source=localhost;Initial Catalog=YourDatabase;Integrated Security=SSPI;</ConnectionString>
</DataSource>
</DataSources>
<Model>
<Tables>
<Table>
<ID>SalesData</ID>
<Name>SalesData</Name>
<DataAccessOptions>Transactional</DataAccessOptions>
<Source xsi:type="QueryBinding">
<DataSourceID>DataSource</DataSourceID>
<Query>SELECT [Date], [Sales], [Quantity] FROM [Sales]</Query>
</Source>
<Columns>
<Column>
<ID>Date</ID>
<Name>Date</Name>
<DataType>DateTime</DataType>
<SourceColumn>Date</SourceColumn>
</Column>
<Column>
<ID>Sales</ID>
<Name>Sales</Name>
<DataType>Double</DataType>
<SourceColumn>Sales</SourceColumn>
</Column>
<Column>
<ID>Quantity</ID>
<Name>Quantity</Name>
<DataType>Int64</DataType>
<SourceColumn>Quantity</SourceColumn>
</Column>
</Columns>
</Table>
</Tables>
</Model>
</Database>
</ObjectDefinition>
</Create>
<!-- Select the Tabulat Model process here -->
<Process>
<Type>ProcessFull</Type>
<Object>
<DatabaseID>Tabular_Model_Sales</DatabaseID>
</Object>
</Process>
</Batch>
The main steps used in the tabular model definition, written in XML for Analysis, for the creation of the database are the following ones:
- Create the SSAS database: The XML for Analysis script starts with a Batch element, to indicate a collection of XMLA commands. The Create command is used to initiate the creation of the new database named Tabular_Model_Sales.
- Define Database: Within ObjectDefinition, the database details are specified, including its ID, Name, and CompatibilityLevel. This section ensure it operates smoothly within the Analysis Services environment, including the SQL Server version.
- Set Up Data Source: The DataSources section defines the data source for the database, specifying a DataSource element with a type attribute indicating it’s a relational database. The ConnectionString is provided to connect to the underlying data source where the actual data resides.
- Create Model: Within the Model section, the Tables are defined. A single table named SalesData is created with a QueryBinding source type, which includes a SQL query to select data from the source database.
- Define Columns: Inside the Table element, Columns are defined, specifying the ID, Name, DataType, and SourceColumn for each column in the table. This maps the columns from the source query to the columns in the tabular model.
- Process Database: Finally, a Process command is included at the end of the XML for Analysis script for the definition of the database process, here the option is Full. This step loads the data into the model using In-Memory technology, making the data available for analysis to the end users. Users can connect to the Tabular model using Excel Pivot tables, Power BI or Reporting Services.
3.2 XMLA Execute Command example
The Execute command in XMLA is used to perform operations such as running MDX or Data Mining Prediction (DMX) queries against an analytical data source. Here’s an example of an XMLA Execute command that runs an MDX query:
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>
SELECT [Measures].[Sales Amount] ON COLUMNS
FROM [Sales Cube]
</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>AdventureWorksDW</Catalog>
<Format>Multidimensional</Format>
</PropertyList>
</Properties>
</Execute>
3.3 The XMLA Discover Command
The Discover command is utilized to request metadata information about the data sources available on the server. This could include a list of databases, cubes, dimensions, measures, and so forth. Here is a basic example of a Discover command:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions/>
<Properties/>
</Discover>
4. XML for Analysis Power BI Endpoints
The Power BI connectivity with XMLA endpoints announced a couple of years ago allows to access Power BI datasets through the XMLA protocol. It makes any XMLA ready data sources accessible through a wide variety of tools, like for example:
- SQL Server Reporting Services (SSRS)
- Reports in Power BI Premium, Power BI Report Server
- Excel PivotTables
- Third party data-visualization tools like Tableau or QlikView
- SQL Server Management Studio (SSMS)
About the XML for Analysis protocol
In conclusion, the definition of XML for Analysis (XMLA) highlights that it is a crucial protocol that enables efficient communication between client applications and Microsoft Analysis Services, including OLAP cubes and Tabular models. Utilizing the Extensible Markup Language (XML), XMLA ensures standardized data access across multidimensional data sources.
This article not only explores XMLA’s fundamental concepts and practical uses but also illustrates its application through a script for deploying a tabular model. By facilitating seamless data interactions, XMLA plays a vital role in the data analytics and business intelligence landscape, demonstrating its importance in accessing and analysing complex data structures.
Be the first to comment