Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2012 Analysis Services XMLA


By:   |   Read Comments (5)   |   Related Tips: > Analysis Services Administration


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

Cube developer often flippantly use the term XMLA in there discussion of maintaining, scripting, backing up, and restoring cubes and other SSAS objects; what exactly is XMLA and how can it be used?  Check out this tip to learn more.

Solution

XMLA is an XML based, exclusive protocol used to handle communication between clients and a SSAS database. It is SOAP based and is designed to be a standard access point for any multidimensional source. XMLA is the driving scripting language behind many of the tasks within SSAS. Some of the scripting tasks that can accomplished by XMLA include:

  • Creating and changing objects
  • Processing objects
  • Handling connections
  • Backup and restores
  • Designing aggregations
  • Merging partitions 

XMLA contains 2 basic methods, Discover and Execute. The Discover method retrieves lists of information in XML format while the Execute method sends commands to the SSAS instances for execution by the instance. We will discuss several of the execute tasks and methods in the next several sections of this tip.

Creating and Altering Objects with SSAS XMLA

Using the Create or Alter XMLA commands, new SSAS objects can be created or existing objects changed based on the issued command. Included in the list of potential objects that can be changed by Create or Alter are:

  • Databases
  • Dimensions
  • Cubes
  • Roles
  • Datasources
  • Partitions
  • Measure groups

One question that often surfaces in initial discussions on XMLA is where do we execute these scripts. Simply open SQL Server Management Studio (SSMS) and connect to your Analysis Services database. Generally, your default query type will be MDX, so to execute an XMLA script, select File from the Ribbon Menu, then New, then Analysis Services XMLA Query.


New MDX Query

At this point we are ready to create and run some XMLA scripts. All the examples for this tip will come from the AdventureWorks 2012 DW SSAS database, which is freely available on CodePlex. Of course, the difficult part is know what fields are needed in your script. A good way to see what is needed in a particular script is to allow Management Studio to generate the scripts for you.  Following the below example to create a new cube based on an existing cube, first right click on the selected cube.  Then, click Script Cube as; next click Create To, and then last select New Query Editor Window.

Script New Cube

SSMS produces the create cube XMLA script which will generate an exact replica of the selected cube, Adventure Works in our example. As the script is many lines long only a small portion of the script is included below; however I highly recommend you try this process on your internal test system and review the entire script. Furthermore, if you attempt to execute this exact script on the same SSAS database, an error would result, because a cube by the same name already exists. 

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>
</ParentObject>
<ObjectDefinition>
<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<ID>Adventure Works</ID>
<Name>Adventure Works</Name>
<Annotations>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:UseDiagramDefaultLayout</Name>
<Value>true</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowRelationshipNames</Name>
<Value>false</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramBoundingTop</Name>
<Value>-31250</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowFriendlyNames</Name>
<Value>true</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramBoundingLeft</Name>
<Value>-25400</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramViewPortLeft</Name>
<Value>-7669</Value>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>
<Value>
<dds xmlns="">
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="MSDDS.Rectilinear" defaultlineroute="MSDDS.Rectilinear" version="7" nextobject="144" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="-7669" scrolltop="-14113" gridx="150" gridy="150" marginx="5000" marginy="5000" zoom="30" x="8361" y="12197" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="0" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="1" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />

Most objects can be scripted in a similar way; however much care should be taken when executing the scripts. Any changes made to the individual values are applied and overwritten to the object when the script is executed, and if a particular property is omitted, then the property value is not set at all!

The create element, which is part of the execute method, allows the designer to code the entire creation of new SSAS objects. The create element requires 1) a ParentOject element which defines the object's parents elements based where the object resides in the hierarchy of objects (i.e. Database > Cube > Measure Group > Partition), and 2) certain ObjectDefinitions which are synonymous with the object's properties. The ObjectDefinitions needed depend on the object being created. Although Books Online (BOL) is somewhat sparse on what items are required, a good site to review is the Analysis Services Scripting Language (ASSL) XML Reference.  Using the script below which creates a new partition called Finance_2 as an example, the ParentObjects of that partition includes:

  1. Database - AdventureWorksDW2012Multidimensional-EE
  2. Cube - Adventure Works
  3. Measure Group - Fact Finance

Again, the difficult part is knowing what fields are needed in the ObjectDefinitions section. For the Partition element used in the below example, the ObjectDefinitions for the partition element can be found at: http://msdn.microsoft.com/en-us/library/ms126977.aspx.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Finance</MeasureGroupID>
</ParentObject>
<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<ID>Finance_2</ID>
<Name>Finance_2</Name>
<Source xsi:type="DsvTableBinding">
<DataSourceViewID>Adventure Works DW</DataSourceViewID>
<TableID>dbo_FactFinance</TableID>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<AggregationStorage>MolapOnly</AggregationStorage>
<Source xsi:type="ProactiveCachingInheritedBinding">
<NotificationTechnique>Server</NotificationTechnique>
</Source>
</ProactiveCaching>
<EstimatedRows>39409</EstimatedRows>
</Partition>
</ObjectDefinition>
</Create>

Looking at the above scripts, you will notice each of the distinct parts of the XMLA:

  • First, the Create Element is used
  • Next, the Parent Object which defines the objects owner is included
  • Last, the object's definition elements are defined and include the type of object to be created, the object's name and id, and several additional properties pertaining to this new partition.

Running this script produces the results shown in the below screen print; these results are certainly not descriptive of a successful execution. However, we can see in our partition list, after a refresh, that we now have a second partition called Finance_2.

New Partition Results

If we run the XMLA script again though, we immediately see an error message that states that the partition already exists!

New Partition Results Error

The basic structure of the alter element, displayed below, is similar to the Create script except the header row is now Alter. Additionally, no ParentObject is used, and note that all the ObjectDefinitions (aka properties) for the object are specified, so care must be taken when changing and running the script to be sure all required elements are included as the entire object is overwritten when the script is executed!

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>
<DataSourceID>Adventure Works DW</DataSourceID>
</Object>
<ObjectDefinition>
<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xsi:type="RelationalDataSource">
<ID>Adventure Works DW</ID>
<Name>AdventureWorksDW2012</Name>
<ConnectionString>Provider=SQLNCLI11.1;Data Source=MyNewServer\SQL2012;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2012</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
<DataSourcePermissions>
<DataSourcePermission>
<ID>DataSourcePermission</ID>
<Name>DataSourcePermission</Name>
<RoleID>Role</RoleID>
<Read>Allowed</Read>
</DataSourcePermission>
</DataSourcePermissions>
</DataSource>
</ObjectDefinition>
</Alter>

Of course, in addition to creating and altering objects, the delete element command is also available; please be careful to not confuse the Delete element with the Drop element which removes attributes from a dimensions. An example of the delete command is listed below; the entire hierarchy path must be specified; this syntax is similar to the how the ParentObject was expressed in the create command.

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Finance</MeasureGroupID>
<PartitionID>Finance_2</PartitionID>
</Object>
</Delete>

Backup and Restore with SSAS XMLA

Probably the most widely expressed reason for using a XMLA script is to backup and restore a SSAS database. As displayed below, this script is potentially one of the simpler in scope. First the backup method is expressed. Next the database ID is specified, and then last the backup options are written. In the below script, the file name, the allow overwrite, and the apply compression options are included. One other common option is adding a password to the backup.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>
</Object>
<File>AdventureWorksDW2012Multidimensional-EE.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>

This entire process could be automated using a SQL Agent job or even via PowerShell as described by Daniel Calbimonte in Automate SQL Server Analysis Services Tasks with PowerShell - Part 2. Of course, the restore command is similar to the backup command, so we will not display it here. However, a great MSSQLTip by Ashish Kumar Mehta describes the restore process in detail. A complete list of XMLA command elements can be found at: http://msdn.microsoft.com/en-us/library/ms187159.aspx, and be sure to review the Next Steps section for other handy MSSQLTips using XMLA. Finally, SSAS also offers many DMV's which address several of the query needs that could be achieved using the Discover element; I am hoping to do a tip on these DMV's very soon.

Conclusion

XMLA is the XML based protocol used to communicate with an Analysis Server. It can be used to script discover and execute methods including Create and Alter, Backup and Restore, and Processing cube objects. These scripts are run in Management Studio and can be automated via PowerShell or the SQL Agent.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 25, 2015 - 12:44:45 PM - Morris Mntoninzi Back To Top

My problem is still on  Processing the Cube of the Project. Can you use database you created or the database must first be in data warehouse? Why I say that I created a SSAS project on BIDS using database I created, but when I try to deploy and process, processing failed and I could not Browse the Cube but you do process the AdventureWorks in your examples. Again I do not get that part of the screen that have Default and Change settings in my system when I execute Process. I have been running the Trial of SQL Server Enterprise. How do you build a warehouse and how it differs fro separate databases? What do I do incorrectly?

 

I may have missed something as I am teaching myself SQL Server.


Friday, February 14, 2014 - 10:28:22 AM - scott murray Back To Top

Very cool.. good old Powershell, shoud have known.


Friday, February 14, 2014 - 8:31:54 AM - BD Back To Top

Actually it seems there is a way: http://www.databasejournal.com/features/mssql/article.php/3818381/Script-a-cube-using-Windows-PowerShell-and-AMO.htm


Friday, February 14, 2014 - 7:39:17 AM - Scott Murray Back To Top

I am not aware of a way to complete that task programmatically; I am sure it is doable, but could be quite extensive.  Maybe turn on the profiler and see what it is doing behind the scenes.


Thursday, February 13, 2014 - 6:35:53 PM - BD Back To Top

Hi, is there any way to programmatically do the "script cube as create" action and save the script to a file? As in if I wanted to automate the cube XMLA script creation and run it periodically? Thanks!


Learn more about SQL Server tools