Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

The Deployment Wizard, and when “Retain Partitions” doesn’t retain partitions

with 5 comments

I learned something the hard way this week about how the Deployment Wizard actually works (thank goodness for daily backups) that I thought I’d share.

I’ve used the Deployment Wizard lots of times to deploy changes to a SSAS database in production. If you have a system where new partitions are generated dynamically, for example in an SSIS package that creates new partitions automatically when new data is loaded into the cube, then these partitions are not going to be present in your project in BIDS and you don’t want them to be overwritten when you deploy the new version of your database. The Deployment Wizard allows you to stop this happening, and also to not overwrite other setting that might be different in dev and prod, such as connection strings and security roles.

However, what I didn’t realise was that in order for the Deployment Wizard to work properly in all cases it needs to be able to see the server you want to deploy to, and in my current customer’s environment you can’t see prod from dev. Working on dev, what I did was to edit the .deploymenttargets input file manually to enter the name of the database in prod I wanted to deploy to, then ran the wizard with the /a argument (see here for more information on these settings) to set other options in the input file such as Retain Partitions, then ran the wizard again with the /o and /d argument to generate the xmla deployment script without needing to connect to the target server (which I couldn’t, of course, see). And then, when I ran the resulting script in prod, I saw all the partitions that weren’t in dev disappear even though I’d selected the Retain Partitions option.

What I had assumed was that by selecting the Retain Partitions option the wizard would somehow generate the XMLA Alter command it outputs so that it would just ignore the partitions in each measure group. However this is not correct: you can’t have an Alter command for a database that doesn’t list all the partitions in all the measure groups in the database. This is why the wizard needs to connect to the target server: it scripts out all the partitions that currently exist on the target and inserts them into the script it generates, instead of just the partitions that are in the .asdatabase file. If, though, you use the /d argument you’re telling the wizard to use only the information that’s in the input files and not to connect to the target server to see what’s there, so it can’t know what partitions are present on the target server and it has to use the partitions from the .asdatabase file instead. So, if you use the /d argument, even if you specify the Retain Partitions option you may see partitions deleted on the target when you run the wizard’s script.

Written by Chris Webb

July 23, 2010 at 5:07 pm

Posted in Analysis Services

5 Responses

Subscribe to comments with RSS.

  1. Excellent finding, Chris. I too recently found this issue the hard way while trying to "retain roles and members". I ended up "scripting out" the roles from the destination server – as an "alter" statement – and ran it after the cube was deployed using the script generated by the deployment utility.For partitions – I generate a delete/create script using SSIS – and apply it after the deployment.

    Harsh

    July 24, 2010 at 1:15 am

  2. Thanks for sharing the info… I noticed one more issue with deployment wizard. If you point to the target server , the roles and partitions are retained but the aggregations assignment is lost. I had to manually assign the aggregations to the partitions after the deployment. Did you notice this issue ?

    Nitin Gulati

    March 12, 2011 at 4:59 pm

    • No, I haven’t seen this one. I’m pretty sure aggregation assignments have worked for me…

      Chris Webb

      March 12, 2011 at 7:39 pm

  3. [...] The Deployment Wizard, and when “Retain Partitions” doesn’t retain partitions [...]

  4. […] Chris Webb’s article on issues with partition deployment using the […]


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

Follow

Get every new post delivered to your Inbox.

Join 3,144 other followers

%d bloggers like this: