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.
