XMLA can be used to manage the structure of your multidimensional databases. While many developers use Visual Studio (BIDS), to deploy changes, as systems move to production or need to be more clearly managed, XMLA comes into play.
Some of the most common DDL type uses for XMLA including partition management, deploying changes, and promoting between environments. In all of these cases, objects within Analysis Services need to be created, altered or deleted.
Before we dig into the details, I wanted to call out that the Execute method will be used and, keep in mind, that the full syntax is not required when using SQL Server Management Studio (SSMS). (See X on XMLA: ii. Basic Structure of XMLA for more details.) Furthermore, in SSMS, you can generate Create, Alter, and Delete XMLA by right clicking on the deployed object and choosing the Script To option. If you have questions about syntax, definitely use this function to discover more about the syntax and the object you are working with.
The following sections are using the Sales Channel dimension from the Adventure Works sample database to illustrate the command syntax. (This sample database is available on CodePlex).
The Create command is used to create new objects in the database. To child elements that are required are ParentObject and ObjectDefinition. The ParentObject specifies the list of objects that are the parent. In our illustration below, the parent object is the database. This is true because the dimensions in Analysis Services belong to the database. However, if we were creating a partition, the parent would be structured, database then cube then measure group. This is true because the partition belongs to a specific measure group. The order of the parent objects matter as they are read top to bottom in the XMLA.
The Alter command is used to modify existing objects in the database. The Alter command uses two child elements, Object and ObjectDefinition. The Object defines the object that is targeted. This, like the ParentObject, is structured from top to bottom with the last object in the list as the object being targeted for alteration.
The ObjectDefinition specifies the changes to make to the object. The changes to the structure MUST include all of the parts of the structure you want to keep the same. I cannot emphasize how important it is that you keep this in mind. You cannot send a simple change via XMLA. You must send the new version in its entirety. This is true at all levels including the database. Where this commonly creates some issues is in the user objects at the database level and the partitions in the measure groups. If you have specific users at the database level that are different between environment, which you should have, you need to update the alter for each environment. If you are modifying a measure group that you have added partitions to, you must make sure these new partitions are in your script as well.
The Delete command is used to remove existing objects in the database. It only has one child element – Object. This is the simplest of the commands here and only needs the proper object definition. Like the ParentObject and Object elements above, you need to have the proper order to delete the correct object. Use caution as this command will contain the database through the targeted object. If you execute the command at the database level you will delete your database.
The execution results are not always clear. When the query is successful you will see the following:
Not really conclusive. Error messages are also returned in an XML format and often contain some relevant information as to why the script failed. Be sure to read it closely as some times multiple errors are returned and the root cause may not be plainly evident.