Automate build and deployment of SSAS Cubes using MSBUILD
The first thing to understand is that an Analysis Services project doesn’t follow the MSBUILD standard and, therefore, can not be built directly from MSBUILD. Instead, in the MSBUILD script, you call the Visual Studio command (devenv.exe) to build the Cube.
Building a Cube project produces the .asdatabase file (and other files) in the output folder (the bin folder in the SSAS project is the default). The output of the build is the input to the SSAS deployment wizard. The deployment wizard can also be executed from the command through Microsoft.AnalysisServices.Deployment.exe.
We use MSBUILD to automate the build and deployment of the Cube. We use MSBUILD because we automatically build an entire BI solution (including the Cube project, SSIS project, and SSRS etc.) all at the same time.
You need to read this post to understand how to configure the SSAS project and run the MSBUILD script, but you can download the MSBUILD script in its entirety.
Setting up your SSAS Project
The key to making this work successfully is to create different project configurations for each target environment, (e.g. DEV, TEST, PROD) in the SSAS project. For each environment you can define values for the target Analysis Services server, database, build path and Data Sources connection strings for each of your environments.
(Click to Enlarge)
The image above shows a typical development project configuration.
- In the project configuration.
- Set the ‘Output Path’ to the directory where you want your build output to go for the selected environment. Generally I specify different build folders for each environment, to avoid confusion and mistakes. In the example above, for the DEV configuration, representing the development environment, build output goes to C:\DW Build\DEV\SSAS.
- Set the Deployment Edition and Version to correspond to your target server. Note, for each environment you can build for different version of SQL Server 9.0 = 2005, 10.0 = 2008, 11.0 = 2012.
- Under the Deployment configuration set the target Server name and SSAS Database Name for the environment.
- To change the Cube Data Source database for an environment simply switch to the project configuration for the environment and edit the Data Source. Your changes for that project configuration are stored by the project.
Automating the Build
The first thing to do in the MSBUILD script is to define some properties. These properties can be passed on the command line when calling the MSBUILD script.
I’ve defined the following properties:
- env. Corresponds to the project configuration name for the environment I want to build.
- solutionPath. The path to the BI solution (the folder containing the sub folders for Dimodelo, Cube and Custom projects).
- ssasVersion. The version (2008, 2012, 2014) of the SSAS Cube being built. This property affects which version of Visual Studio is used to build the Cube.
Next you need to define a Target to build the cube. A Target is like a small procedure that can be called from the command line, or other Targets. The image below show a Target named BuildCube.
The ItemGroup elements retrieve a list of Cube project files within any sub-folder of the solutionPath. Usually only a single project. Note, this build script only works for a single project.
The devEnvTool property contains the path to the devenv.exe file. The value is conditionally set based on the ssasVersion property passed in the call to MSBUILD.
Finally the Exec target runs a command line command. The command uses the devenv.exe version specified in thr devEnvTool property, and builds the Cube project, specified by the CubeProjectFiles, for the solution and project configuration specified in the env property.
To execute the script I use a command line like this:
MSBuild.exe “Build.proj” /target:BuildCube /property:solutionPath=”C:Working Copy” /property:env=”DEV”
Note: MSBUILD will need to be in your PATH environment variable on your PC. In the example above, the MSBUILD script is saved in a file called Build.proj. MSBUILD is installed with .NET 4.0 or Visual Studio 2012 and above etc. You can also download it here.
Automating Cube Deployment
The automated cube deployment utilizes the SSAS deployment wizard executed from the command line through Microsoft.AnalysisServices.Deployment.exe.
Again, it is important to correctly configure the deployment options in the SSAS project prior to deployment. These settings are incorporated into the files produced by the build (.asdatabase etc), and make it possible to deploy the cube to a server in silent mode.
- Processing Option. Set this to ‘Do Not Process’. Usually you don’t want the cube to be process after deployment. This doesn’t affect the ‘Process’ option in Visual Studio.
- Server Mode. Use ‘Deploy All’. I find this safest. I’ve noticed that changes to data source connection strings are not deployed, if you use ‘Deploy Changes Only’ option.
- Server. The target server for the environment.
- Database. The name of the target Cube database on the server.
For deployment I’ve defined an additional buildPath property required in the MSBUILD script. The buildPath specifies the folder where the build output is written. The .asdatabase file in the buildPath is used for deployment. In this example, the buildPath will be passed (via the command line) as C:DW Build. This correlates with the ‘Output Path’ build property in the SSAS project configuration.
The MSBUILD target code to deploy a cube is shown below:
The SSASDeployTool property contains the path to the Microsoft.AnalysisServices.Deployment.exe file. The value is conditionally set based on the ssasVersion property passed in the call to MSBUILD.
The ItemGroup elements retrieve the .asdatabase file within any sub-folder of the buildPath environment folder. Remember I generally specify different build folders for each environment, to avoid confusion and mistakes.
Finally the Exec target runs a command line command. The command uses the Microsoft.AnalysisServices.Deployment.exe version specified in the SSASDeployTool property in silent mode (/s). The command uses the information in the .asdatabase and associated files to deploy the Cube to the target server.
To execute the script I use a command line like this:
MSBuild.exe “Build.proj” /target:DeployCube /property:buildPath=”C:DW Build” /property:env=”DEV”
A note about security and passwords
In theory it should be possible to define a user name and password in the data source for your Cube, for each environment (project configuration). Unfortunately in practice I have not been able to get this to work. If you want to automate Cube deployment, I suggest you use windows authentication, with service accounts on servers. This is probably more secure anyway.
If you do want to use a user Id and password, then configure the Cube project not to remove passwords. The passwords will (or are supposed to) get encrypted and placed into the cube.assecurityinformation file in the output directory. A password needs to be set before cube.assecurityinformation will be generated. The deployment wizard is supposed to use this encrypted file to set the password on deployment. I have never been able to get it to work. There are some additional tips this post. Good luck !
Partition Deployment Gotcha
See Chris Webb’s article on issues with partition deployment using the wizard:
References:
Thanks for the script!
The build (deployCube) target still seems to succeed even when the cube can’t be deployed. Is there any way to ensure that msbuild returns and exit status >0 ?
Unfortunately this is a known issue with Microsoft.AnalysisServices.Deployment.exe. See – https://connect.microsoft.com/SQLServer/feedback/details/700066/the-deployment-utility-microsoft-analysisservices-deployment-exe-should-return-a-non-zero-exit-code-on-error.
There is a workaround there but its convoluted.
Ok. Thanks for your reply.
I’ve now resorted to checking the stdout and stderr for error messages.
Regarding the issue with data sources: it seems that the connection string does not get correctly formed in the “*.configsettings” file that get created by the build phase. If I manually set these after the build then I can automatically deploy and process the cube.