How to simplify SSAS deployment from DEV to PRO environment

Question

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!

Answer 1

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.

Answer 2

Its been a couple of years, but I believe this is what we had working in my old position.

Starting with an AS database with roles, but no role members, generate the scripts to add the members and save them off as a separate XML file. Also, if there are any members that need to be removed, script that as a separate file as well.

When migrating the AS database to a new instance, these permissions scripts are executed after the main database is completed. You can execute them with powershell or SSIS to move toward automating deployments.

To further automate this, you can script each individual permission and make a couple SQL database tables that contain the relationship between AS databases and permissions and script locations. Then use powershell or SSIS to programmatically apply the permissions to the AS database as part of the deployment process.

In summary, the roles that you create can persist in all of your environments (and TFS). It is just the members of the roles that need to be changed between environments, which can be handled with scripts. The initial setup requires some investment, but once complete it works fairly smooth.