As part of the continuing series on Building dynamic OLAP data marts on-the-fly, this blog entry will focus on how to automate the deployment of OLAP cubes using SQL Server Integration Services (SSIS) and Analysis Services Management Objects (AMO).

OLAP cube deployment is usually done using the Analysis Services Deployment Wizard. However, this option was dismissed for a variety of reasons. Firstly, invoking external processes from SSIS is fraught with problems as (a) it is not always possible to ensure SSIS waits for the external program to terminate; (b) we cannot log the outcome properly and (c) it is not always possible to control the server's configuration to ensure the executable works correctly. Another reason for rejecting the Deployment Wizard is that it requires the 'answers' to be written into four XML files. These XML files record the three things we need to change: the name of the server, the name of the OLAP database and the connection string to the data mart. Although it would be reasonably straight forward to change the content of the XML files programmatically, this adds another set of complication and level of obscurity to the overall process.

When I first investigated the possibility of using C# to deploy a cube, I was surprised to find that there are no other blog entries about the topic. I can only assume everyone else is happy with the Deployment Wizard!

SSIS "forgets" assembly references

If you build your script task from scratch, you will have to remember how to overcome one of the major annoyances of working with SSIS script tasks: the forgetful nature of SSIS when it comes to assembly references. Basically, you can go through the process of adding an assembly reference using the Add Reference dialog, but when you close the script window, SSIS "forgets" the assembly reference so the script will not compile. After repeating the operation several times, you will find that SSIS only remembers the assembly reference when you specifically press the Save All icon in the script window. This problem is not unique to the AMO assembly and has certainly been a "feature" since SQL Server 2005, so I am not amazed it is still present in SQL Server 2008 R2!

Sample Package

So let's take a look at the sample SSIS package I have provided which can be downloaded from here: DeployOlapCubeExample.zip  Below is a screenshot after a successful run.

Connection Managers

The package has three connection managers:

• AsDatabaseDefinitionFile is a file connection manager pointing to the .asdatabase file you wish to deploy. Note that this can be found in the bin directory of you OLAP database project once you have clicked the "Build" button in Visual Studio
• TargetOlapServerCS is an Analysis Services connection manager which identifies both the deployment server and the target database name.
• SourceDataMart is an OLEDB connection manager pointing to the data mart which is to act as the source of data for your cube. This will be used to replace the connection string found in your .asdatabase file

Once you have configured the connection managers, the sample should run and deploy your OLAP database in a few seconds. Of course, in a production environment, these connection managers would be associated with package configurations or set at runtime.

When you run the sample, you should see that the script logs its activity to the output screen (see screenshot above). If you configure logging for the package, then these messages will also appear in your SSIS logging.

Sample Code Walkthrough

Next let's walk through the code. The first step is to parse the connection string provided by the TargetOlapServerCS connection manager and obtain the name of both the target OLAP server and also the name of the OLAP database. Note that the target database does not have to exist to be referenced in an AS connection manager, so I am using this as a convenient way to define both properties.

We now connect to the server and check for the existence of the OLAP database. If it exists, we drop the database so we can re-deploy.

svr.Connect(olapServerName);

if (svr.Connected)

{

// Drop the OLAP database if it already exists

Database db = svr.Databases.FindByName(olapDatabaseName);

if (db != null)

{

db.Drop();

}

// rest of script

}

Next we start building the XMLA command that will actually perform the deployment. Basically this is a small chuck of XML which we need to wrap around the large .asdatabase file generated by the Visual Studio build process.

// Start generating the main part of the XMLA command

XmlDocument xmlaCommand = new XmlDocument();

xmlaCommand.LoadXml(string.Format("<Batch Transaction='false' xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'><Alter AllowCreate='true' ObjectExpansion='ExpandFull'><Object><DatabaseID>{0}</DatabaseID></Object><ObjectDefinition/></Alter></Batch>", olapDatabaseName));

Next we need to merge two XML files which we can do by simply using setting the InnerXml property of the ObjectDefinition node as follows:

// load OLAP Database definition from .asdatabase file identified by connection manager

XmlDocument olapCubeDef = new XmlDocument();

olapCubeDef.Load(Dts.Connections["AsDatabaseDefinitionFile"].ConnectionString);

// merge the two XML files by obtain a reference to the ObjectDefinition node

oaRootNode.InnerXml = olapCubeDef.InnerXml;

One hurdle I had to overcome was removing detritus from the .asdabase file left by the Visual Studio build. Through an iterative process, I found I needed to remove several nodes as they caused the deployment to fail. The XMLA error message read "Cannot set read-only node: CreatedTimestamp" or similar. In comparing the XMLA generated with by the Deployment Wizard with that generated by my code, these read-only nodes were missing, so clearly I just needed to strip them out. This was easily achieved using XPath to find the relevant XML nodes, of which I show one example below:

foreach (XmlNode node in rootNode.SelectNodes("//ns1:CreatedTimestamp", nsManager))

{

node.ParentNode.RemoveChild(node);

}

Now we need to change the database name in both the ID and Name nodes using code such as:

XmlNode databaseID = xmlaCommand.SelectSingleNode("//ns1:Database/ns1:ID", nsManager);

if (databaseID != null)

databaseID.InnerText = olapDatabaseName;

Finally we need to change the connection string to point at the relevant data mart. Again this is easily achieved using XPath to search for the relevant nodes and then replace the content of the node with the new name or connection string.

XmlNode connectionStringNode = xmlaCommand.SelectSingleNode("//ns1:DataSources/ns1:DataSource/ns1:ConnectionString", nsManager);

if (connectionStringNode != null)

{

connectionStringNode.InnerText = Dts.Connections["SourceDataMart"].ConnectionString;

}

Finally we need to perform the deployment using the Execute XMLA command and check the returned XmlaResultCollection for errors before setting the Dts.TaskResult.

XmlaResultCollection oResults = svr.Execute(xmlaCommand.InnerXml);

// check for errors during deployment

foreach (Microsoft.AnalysisServices.XmlaResult oResult in oResults)

{

foreach (Microsoft.AnalysisServices.XmlaMessage oMessage in oResult.Messages)

{

if ((oMessage.GetType().Name == "XmlaError"))

{

FireError(oMessage.Description);

HadError = true;

}

}

}

If you are not familiar with XML programming, all this may all seem a bit daunting, but perceiver as the sample code is pretty short.

If you would like the script to process the OLAP database, simply uncomment the lines in the vicinity of Process method. Of course, you can extend the script to perform your own custom processing and to even synchronize the database to a front-end server. Personally, I like to keep the deployment and processing separate as the code can become overly complex for support staff.

If you want to know more, come see my session at the forthcoming SQLBits conference.