Just a quick note to share one point re deploying changes in SSAS cubes in a “scheduled” way. I mean as opposed to “deploy from BIDS” way. So we want to deploy some cube changes but we can’t do that online from BIDS during the day because (for example) it takes a long time to fully process the cube and the users can’t tolerate the cube to be unavailable that long (say 2 hours).
Say your cube* is processed “full” daily at night, after the warehouse is loaded. And you want to deploy the cube changes tonight, just before that nightly “cube refresh” *.
*when I say “cube” here I mean “SSAS database”. And when I say “refresh” here I mean “process”.
To do this, we can use SSAS Deployment Wizard. It’s not in BIDS. It’s in SQL Server branch in the Start menu, Program Files. So click Start, Program Files, SQL Server 2008, Analysis Services. There you will find it (the wizard).
In this wizard, first you need to select the database files (the extension is .asdatabase). This is your local copy of the solution files, so for your “sales” cube go to (for example) c:\data\SSAS projects\Sales\Sales\bin\ folder and select Sales.asdatabase.
After selecting the database file, navigating through this wizard, you’ll be offered
a) change the data source connection (from Dev SQL Server to Prod for example)
b) whether you want to deploy the roles and members or not, and
c) whether you want to deploy the partitions or not.
Then after that it asks if you want to script it or not. Choose yes and you will get an XMLA file containing the cube definition. The default location of this file is the same folder as the .asdatabase file, and you can change it to any folder you want. Ah, 1 more thing, the wizard also asks how do you want to process the cube (i.e. full, default, or not processed).
Copy and paste this XMLA into a step in SQL Agent job and you can schedule it to run at certain time tonight or after midnight (it’s called morning if it’s after midnight right 🙂 ). Or you can modify an existing job that process the cube, inserting one new step containing this XMLA. In this case in the wizard you should choose “don’t process the cube”.
If your existing “cube refresh” job does “process default” on all the dims, then does process full on certain partitions*, you probably want to disable those “refresh cube” steps and specify “process full” on the Deployment Wizard.
*the ETL stores information about which partitions changed / need to be processed
That’s it, that’s how we can do “scheduled deployment” for a SSAS cube, i.e. put the XMLA output into a SQL Agent job.