SQL Server 2012 Analysis Services XMLA

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.

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.

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 theAnalysis 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

gain, 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.

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

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.

Source Collected from MSSQLTIPS.COM