Sql-server – How to simplify SSAS deployment from DEV to PRO environment

deploymentsql serverssas

Here is my issue: In my company, there is a team of developper who creates cubes, dimensions(…) in SSAS in a DEV environment (let's call it SSASDEV). This environment is bounded to a SQL Server database in DEV (let's call it SQDEV).
My job is to deploy their work from DEV environment to PRO environment. This PRO environment (SSASPRO) is based on another SQL Server database (SQPRO).

For now, the developper team script the SSAS, send me the XMLA script, I need to change every security rule defined and the connection string specified in this XMLA (security rules because it's based on different Roles depending on the environment and the connection string because of the SQL Server database).
It's a heavy work to do for each deployment so I want to automatize this.

The only ways I found is

(1) – to script the DEV cube and apply the XMLA on PRO (what is done now).

(2) – to synchronize cubes (which means, unprocess, re-apply security rules and modify connectionstring).

I can not imagine I'm the only one in this situation in the entire world!
Does anyone has a tip or a hint for me? Does another simpliest way exists and I missed it?
Is my intern organization (differents databases for each environment) not logical for SSAS?

I work with SSAS 2008R2 and 2012

Thanks a lot for your responses!

Best Answer

I will tell you the way we deploy in my place SSAS projects between different environments. We use a set of Powershell scripts that:

Part 1:

  • get the last version from the build server and deploy the file to generate the last working version of XMLA;
  • take the generated XMLA from the build server and copy it to the QA server;

Part 2:

  • Now, on the QA server, we change manually the server name, the database name (set as parameters to the PS script), we verify that all required scripts are there;
  • one PS script runs the resulted XMLA on the current QA server, so the SSAS database is created (not processed though);
  • a XMLA script that commands the full process of the cube and its dimensions;

All of these steps are done as automatically as possible, with as little intervention from the developer/QA guy as possible. We do, though, insert some eyeballing in between, just to keep our minds safe. I won't say that it's the best, but it works and gets the job done. Before the last step I'm sure you can insert any XMLA script to remove existing roles and create new ones. And to change a PS script to execute some new SQL or XMLA script is not hard at all. I suppose that the roles and permissions don't change that much, so they can be scripted and reused.

PS: this is an environment with domain users and fixed servers (so we can use shares, domain accounts and all that fluff). If you have another environment it might be a more difficult to get it done.

Related Question