Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

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

share|improve this answer
    
Thanks for your feedback and your experience sharing! We have more or less the same manual "way to do" now (except the PS scripts). –  el_grom Jan 23 '13 at 13:27
1  
Well, then the solution to your problem is to use some scripting language to do your task. Powershell is not easy to learn, because it's ugly as hell, but it's well integrated with SQL, .NET.. and it's easy to use after you have a set of scripts that already work. –  Marian Jan 23 '13 at 13:38
    
Yes, I already started this solution. However, I'm very surprised to see that there is no "built-in" tool in SQL Server to do that. –  el_grom Jan 23 '13 at 13:47
    
Well, the deployment work is not really a part of the SQL Server itself. There may be tools that offer continuous integration in an SQL Server environment, but I'm not very familiar with them. –  Marian Jan 23 '13 at 14:14
    
Anyone have experience using TFS with analysis services? –  sam yi Feb 18 '13 at 16:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.