X-XMLA: v. Creating XMLA Script from Visual Studio and Deploy the Database

This will be the easiest entry in this series.  This is a reblog of an article I wrote last year.

Simple batch script to generate XMLA and deploy SSAS DB

As I was working through some issues with my cube design, I realized that I wanted to deploy my SSAS databases using XMLA in SSMS. This can be easily scripted from SSMS, but I had not deployed the database yet. As a result, I created a batch file to build the XMLA used to deploy a database. This script can be used for automated builds or even automated deployments if that fits into your model. I don’t write a lot of batch files so you may want to update the syntax as needed.  Read more…

X on XMLA: iii. Basic DDL Functions in XMLA (Create, Alter, Delete)

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).

Create

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.

Alter

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.

Delete

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.

Results

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.

X on XMLA: ii. Basic Structure of XMLA

The second post in the XMLA series focuses on the basic structure of XMLA.  At its heart, XMLA is XML.  As a result it is usually not a comfortable work environment for most database developers or administrators.  It is no different for Analysis Services developers or administrators.  While a discussion on XML formatting is out of scope for this conversation, I will break down the XML structures used in XMLA so you can see how the basic construction of the language works.

XML Namespace

In XML, a namespace is required to help the tools understand how to interpret the content.  The namespace used is urn:schemas-microsoft-com:xml-analysis. Usage of the namespace is handled at the method level.

XMLA Methods

There are two methods supported in the XMLA standard – Discover and Execute.

Discover

The Discover method will return XML-formatted results.  The Discover statement is similar in function to a SELECT statement in SQL.  Discover can be used to query the data within the multidimensional database as well as server information.

Within the Discover method, the following properties are supported which are implemented as child elements:  RequestType, Properties, and Restrictions.  The RequestType property determines the schema rowset to be returned.  The Properties property is a collection of options that can be implemented which are determined by the RequestType.  Finally, the Restrictions property can be used to restrict the results returned by the Discover Statement.

Here is an example of getting the list of dimensions from the Adventure Works sample cube (available on CodePlex.com).

Execute

The Execute method executes statements against the multidimensional database.  The Execute statement can be used to process, deploy and execute MDX statements.  It too returns its results in an XML format, including error messages.

The Execute method supports two properties which are implemented as child elements: Command and Properties.  The Command property contains the actual command that is being implemented.  Properties, like for Discover, enables Command specific properties which can be used to direct the command.

This method is implemented two ways in Management Studio.  In the first example, the syntax is explicitly called.

In this example, you can see that Management Studio will wrap execute statements with the Execute and Command elements making it simpler for you to work with Execute methods.

SOAP

What I do not discuss in this blog is the SOAP implementation.  As most of us use Management Studio or similar tools which don’t look at the SOAP envelope, I did not cover it.  However, if you need more details on SOAP or on the XMLA standards refer to http://xmla.org for more details.

Microsoft Data Appliances Help Simplify MSBI Projects

As some of you know, I am really excited about the data appliances Microsoft and HP have released this year.  I really believe that they make it even easier to get MSBI projects up and running while minimizing the complexity of building out servers. Read more about my thoughts on this in an article I wrote for Magenic:  Microsoft Data Appliances Lower the Entry Bar for MSBI Adoption.