Hilmar Buchta

SQL Server 2005 | SQL Server 2008

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.

 

image

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).

image

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):

image

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:

image

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.