X on XMLA: i. Using XMLA


In my previous blog, I introduced the “X on XMLA” series.  In this blog, I will do more of an introduction to XMLA by discussing how I have used XMLA in my experience with SSAS.

What is XMLA? XML for Analysis or XMLA is the API used to interact with Analysis Services and other similar multidimensional database servers.  The goal was to build out a standard API to meet this goal and it has been widely accepted by the leading OLAP vendors.  You can find out more about the standard at XMLAforAnalysis.  This is a good resource for generic XMLA topics including the current specifications document and a newsfeed.

A key point about XMLA is that it is, as its name states, XML.  The API uses SOAP protocols to communicate with the servers.  (Refer to the specification above if you would like more details.)  I like this because XML is easy to read.  While it can be annoyingly verbose at times, you are able to easily identify what you are doing which reduces the learning curve.

Uses of XMLA.  Now that you have the cursory introduction to XMLA, what is it commonly used for?  I have primarily used XMLA in the areas of loading data and deploying objects.  I use SQL Server Management Studio (SSMS) extensively to interact with my SSAS databases so I do not use the interrogation or query functions within XMLA that often.  However, you should be familiar with all of these uses.

Deploy.  I use XMLA to deploy new objects and alter existing objects on a regular basis.  In particular, this is a standard way to test deployment and to script deployment operations for production releases.  XMLA supports Create, Alter and Delete commands for every object in SSAS.  In SSMS you can generate any of these scripts using the short cut menus as noted in the image below.  You should become familiar with using XMLA as a deployment method as it is an simple way to promote changes between environments.

XMLA Create Script Menu

Load.  The Process commands are used to load data in a variety of methods including full replace or reload, incremental load, and updates.  XMLA also supports commands which will drop and recreate indexes and aggregations if that is required.  SSAS exposes additional processing options through XMLA that are not available in SSMS or Business Intelligence Development Studio (BIDS).  The script below is a basic example of processing a dimension using XMLA.  (Note:  ->> designates a line break.)

<Process xmlns=http://schemas.microsoft.com/  ->&gt;
        <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>
        <DimensionID>Dim Time</DimensionID>

Interrogate.  While I have not used XMLA for this activity as much as the two activities noted above, I will be discussing this in detail in one of the later blogs.  The Discover method allows you to retrieve information about the SSAS instance and related database objects.  For instance, you can use XMLA to return a list of available databases on the target server.

Query. XMLA can also retrieve data from the cube.  It is able to accomplish this by executing MDX against the target.  I do not usually have a need to query from XMLA, but I will discuss this usage in this series as well.

Hopefully this gives you an idea of how powerful XMLA is and why it is a necessary tool for any BI developer working with multidimensional database servers.