Saturday, September 08, 2012

SSAS : Save hours by automating the cube deployments!

Every now and then it is quite an overhead in large development environments to deploy the SSAS cubes at a large scale. Automating this process particularly helps when doing lots of changes in the SSDT (SQL Server data tools) in the development environment and when finished the changes then deploying the cubes in the UAT environment. Then after completion of next round of issues the cubes have to be deployed on the production server. With dozens of cubes, with each of it requiring deployment to two additional environments it is a considerable overhead to do it manually.

I have recently automated the deployment of a few dozen of cubes. There are several ways to do it, the below one is among the simpler ones. Basically it is about creating deployment profiles in SSDT and then invoking the SSDT via command line to build the cubes and use a SSAS utility (Microsoft.AnalysisServices.Deployment.exe )  to deploy the cubes.

The Microsoft.AnalysisServices.Deployment.exe utility can be found in this path:

Drive:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio

For creating the deployment profiles in SSDT, right click on the top line in the solution explorer, i.e. the solution and then Properties. Then click the “configuration manager” and then under ‘Active Solution configuration’ in the drop down, click “New” and give the configuration profile a name, e.g. SSASProject_Production. Now for the newly created profile, edit the deployment location in the properties page. It will change the deployment address only for that particular profile.

The data source connection properties have to be edited in the SSDT project file (found with the extension .dwproj , can be opened in notepad), it can be found inside the SSDT project folder. This file has a format like this and the location of the profile with data source connection can be found near to these kind tags around end of the file.

  <Name>SSAS Prod</Name>

All steps can be combined in a batch file.
----------------Batch file start ------------------------------------------------
ECHO Build started . . .

REM  here is the location of your visual studio/SSDT exe, in this case its C, the project solution file location, the deployment profile name “SSAS Prod” here and the location of the build log file, it can be anywhere.

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe" "C:\***\****\***\***\******\SSAS_Project.sln" /build "SSAS Prod" /out "C:\ ***\****\***\***\******\\ssasbuild.log"

 ECHO Build completed . . .

 ECHO XMLA Script generation started 

REM now invoking the deployment utility. The first argument is location of the SSAS database file, can be fund inside bin folder of SSDT project with extension .asdat and the location of .xmla file which is to be generated here for deployment.

Microsoft.AnalysisServices.Deployment.exe "C:\ **\****\***\***\******\SSAS_Project.asdatabase" /d /o:"C:\ ***\****\***\***\******\SSAS_Deployment.xmla"

ECHO XMLA Script generation complete . . .

ECHO Deployment is starting now.......

REM Finally deploy the solution.

Microsoft.AnalysisServices.Deployment.exe "C:\ ***\****\***\***\******\SSAS_Project.asdatabase" /s:"C:\ ***\****\***\***\******\SSAS_Proejct\deployment.log"


PAUSE Completed

----------------Batch file End ------------------------------------------------


1)      Create configuration profiles for each deployment environments

2)      Update the batch file with locations of each project or make one file for all of the projects/cube.

3)      See log for any deployment issues!

4)      process your cubes via SSIS or another method!

You are done!

No comments: