How to deploy an Analysis Services database using a script

Deploying an OLAP application using deployment scripts is a common scenario in larger development environments. Of course we can use XMLA scripts to do the deployment but running the script from SQL Server Management Studio might not be an option in a fully automated installation script. Fortunately enough Microsoft provides the command line utility ascmd.exe which is much like sqlcmd.exe for the relational SQL Server. Ascmd.exe gives a simple method to execute XMLA scripts against a SQL Server Analysis Services server (SSAS).

If you don’t have the ascmd.exe as a precompiled version yet you can easily build it from the source code provided in the server samples for the Analysis Services which can be downloaded from the SQL Server Samples page at Codeplex.com.

After installing the samples you should find the source code for ascmd.exe in the folder

C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd

There is also a readme provided on how to compile this application (you will need to create a keypair first).

 

For an example let’s create a deployment script for the Adventure Works BI Database. First, load the Adventure Works BI solution into you development environment and build the project. After doing so you should find the subdirectory "bin" in your Adventure Works project directory. In this directory you will find a larger xml file named "Adventure Works DW.asdatabase". This script starts with a <Database>-tag and contains all the cubes and dimensions for the OLAP project.

An easy way to convert this file to a deployment script is to use the Deployment Wizard (Start/SQL Server 2005/Analysis Services/Deployment Wizard). Here start with selecting the asdatabase-file created by the build above. You can also add other parameters like how to deal with existing partitions and roles.

Another way to create the script is to grab it from SSMS and paste the asdatabase-file in there manually. This processed is described here in the blog: Simply connect to you SSAS server and open the dialog to create a new database. The name of the database doesn’t matter but you should set the user account the the service account. Then click the "Script"-button and copy the script to the Clipboard.

 

Copy the script to a text editor. In the script you will note a region starting with <Database… and ending with </Database>. For our purpose we are not interested in that so we can simply delete it (including the database-tags).

 

After that we can paste the database script "Adventure Works DW.asdatabase" that we create in the first step, to the position where we’ve just removed the old database-tag. The file should look like this now (I removed most of the actual code for better readability):

 

 

This is the script we can now execute using the ascmd.exe utility.

C:\>ascmd -S localhost -i aw.xmla
Microsoft (R) Analysis Services 2005 Command Line Tool
Version 9.0.86.1 MSIL
Copyright (C) 2006 Microsoft Corporation.  All Rights Reserved.
<return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"></root></return>

 

After that, the database is fully created on the SSAS server but not yet processed. In order to process the database we also need a process xmla-script. Like above we simply create this script by grabbing it from SSMS. The script looks like this:

 

Let’s assume we saved the processing script as process.xmla, we can simply execute it using ascmd.exe like this:

 

C:\>ascmd -S localhost -i process.xmla
Microsoft (R) Analysis Services 2005 Command Line Tool
Version 9.0.86.1 MSIL
Copyright (C) 2006 Microsoft Corporation.  All Rights Reserved.
<return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas-microsoft-com:xml-analysis:empty"></root></return>

 

Any errors would be returned in the xmla response (check the return-tag). An empty message means success so our database is now fully processed and ready to be used.

Advertisements
This entry was posted in Allgemein. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s