Data Warehousing and Business Intelligence

16 June 2010

Scheduled Deploy in SSAS

Filed under: Analysis Services — Vincent Rainardi @ 7:13 am
Tags:

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.

Advertisements

1 Comment »

  1. Here are this and some other articles on SSAS Deployment:

    http://ssas-wiki.com/w/Articles#Deployment

    Comment by Sam Kane — 24 February 2011 @ 4:19 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: