One of the problems I faced recently was trying to write a click-through deployment for a web application which not only has a SQL backend, it also needs to deploy & build an Analysis Services project.
The issue is that when you create a web setup project, there's no intuitive interface to allow you to do such a deployment as part of your installation.
Solution? Deploy the analysis services project dynamically.
The first thing you need to do, if you haven't done it already, is to create a new class library to handle all these sorts of custom tasks during your installation.
- Create new class library
- Create a new folder within it called Olap
- In the Build Events section of this project, add the following Pre-build event command line:
- copy /Y $(SolutionDir)\Olap\* $(ProjectDir)\Olap
- where $(SolutionDir)\Olap\ is the project directory of your analysis services project
- This will copy over all the project files for your analysis project to your installer class before each build
- Build the project to copy over all the files from your analysis services project
- Within your class library, add the new files to your project, and change their build action to Embedded Resource
- In the root of your class library, add a new Installer Class called Actions
What we have now are all the resources needed in order to deploy our cube on install. I recommend that you have a look inside your class library to see where your olap files are stored. Many people have a problem with not being able to find their embedded resources within a given assembly due to leaving out namespaces or what have you. A great tool to peek inside your .net assemblies is Lutz Roeder's Reflector for .NET http://www.aisto.com/roeder/dotnet/ or you can always use the [assembly].GetManifestResourceNames() if you feel so inclined.
The other thing you need to do is to ensure that your class library gets called when your MSI is installing. Add the primary output from your class library to the setup project, and then in Custom Actions, add 'Primary output from <your class library>' in the install directory.
Jump into your class library's installer class (Actions.cs) and override the Install method. The general process is that you read through each of the analysis services project files in order of dependency, building an XMLA script. XMLA (XML for Analysis) is essentially a macro language that instructs analysis services to perform a whole heap of actions. We can leverage this to build & then process our olap project.
First off, we need to put all of our operations in a batch, as we want to execute the XMLA at the end as a single script. Create a new XmlDocument that'll hold your XMLA script. It should have a root node of:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
</Batch>
Next you need to add to this an Xml Element to create a new analysis services database on the server:
private XmlDocument xmlaInstall;
private string databaseId = "MyXMLADatabase";
private Assembly setupAssembly;
private void OlapCreateDatabase()
{
XmlElement create = OlapAddCreate(false);
XmlElement definition = create.FirstChild as XmlElement;
XmlElement database = xmlaInstall.CreateElement("Database");
XmlElement name = xmlaInstall.CreateElement("Name");
XmlElement description = xmlaInstall.CreateElement("Description");
name.InnerText = databaseId;
description.InnerText = "A database made through XMLA";
database.AppendChild(name);
database.AppendChild(description);
definition.AppendChild(database);
}
private XmlElement OlapAddCreate(bool hasParent)
{
XmlElement create = xmlaInstall.CreateElement("Create");
if (hasParent)
{
XmlElement parentObject = xmlaInstall.CreateElement("ParentObject");
XmlElement databaseID = xmlaInstall.CreateElement("DatabaseID");
databaseID.InnerText = databaseId;
parentObject.AppendChild(databaseID);
create.AppendChild(parentObject);
}
XmlElement objectDefinition = xmlaInstall.CreateElement("ObjectDefinition");
create.AppendChild(objectDefinition);
batchNode.AppendChild(create);
return create;
}
Which should produce something like:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create>
<ObjectDefinition>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>MyXmlaDatabase</Name>
<Description>A database made through XMLA</Description>
</Database>
</ObjectDefinition>
</Create>
</Batch>
What you need to do next is simply read through your embedded resources to add the rest of the <Create> elements for your project. You'll need to create them in the following order:
string[] extensions = new string[] { "ds", "dsv", "dim", "role", "cube", "partitions" };
setupAssembly = Assembly.GetAssembly(this.GetType());
string[] resourceNames = setupAssembly.GetManifestResourceNames();
foreach (string extension in extensions)
foreach (string resourceName in resourceNames)
if (Path.GetExtension(resourceName).Equals("." + extension, StringComparison.OrdinalIgnoreCase))
OlapCreateResource(resourceName);
private XmlElement OlapCreateResource(string resourcename)
{
XmlElement create = OlapAddCreate(true);
XmlElement parent = create.FirstChild as XmlElement;
XmlElement definition = create.LastChild as XmlElement;
XmlDocument nodeInfo = new XmlDocument();
using (StreamReader reader = new StreamReader(setupAssembly.GetManifestResourceStream(resourcename)))
{
nodeInfo.LoadXml(reader.ReadToEnd());
reader.Close();
}
XmlNode olapObj = definition.AppendChild(xmlaInstall.ImportNode(nodeInfo.FirstChild, true));
return definition;
}
You need to remember that you'll probably have a whole heap of connection strings that you'll need to set if it's a data source, so remember to edit the XML as needed. Once this is done, you need to add a process command to build the cube for the first time.
private XmlElement OlapProcess()
{
XmlElement process = xmlaInstall.CreateElement("Process");
XmlElement type = xmlaInstall.CreateElement("Type");
XmlElement writeBackTableCreation = xmlaInstall.CreateElement("WriteBackTableCreation");
XmlElement obj = xmlaInstall.CreateElement("Object");
XmlElement databaseID = xmlaInstall.CreateElement("DatabaseID");
obj.AppendChild(databaseID);
databaseID.InnerText = databaseId;
type.InnerText = "ProcessFull";
writeBackTableCreation.InnerText = "CreateAlways";
process.AppendChild(obj);
process.AppendChild(type);
process.AppendChild(writeBackTableCreation);
batchNode.AppendChild(process);
return process;
}
And then you're done. All you need to do now is create a connection to the analysis services server and execute the generated XMLA as a AdomdbCommand as such:
using (AdomdConnection conn = new AdomdConnection(ssasConnectionString))
{
conn.Open();
AdomdCommand com = new AdomdCommand(xmlaInstall.InnerXml, conn);
com.ExecuteNonQuery();
conn.Close();
}
Remember, you can also save the XMLA and run it directly through Sql's management studio to see what bugs you're turning up.
Hopefully this'll give you a good introduction into XMLA and how you can use it not only for deployment, but also to administer and manage your analysis services projects from your applications.