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.
