Prevent wiping out of SSAS partitions

There have been a few times when I have created SSAS partitions outside of BIDS using script code in an SSiS package, then later made a change in BIDS and deployed the cube, wiping out those partitions (since they did not exist in the BIDS project).

To prevent partitions getting wiped out if you do a deploy from BIDS and have created partitions outside of your project, you should use the deployment wizard: Click Start, point to All Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Deployment Wizard.

If you wanted to update your project so that it reflects those partitions created outside of the project, you can “reverse engineer” a SSAS database and build the Visual Studio analysis services project from a deployed SSAS database.  Just go into Visual Studio, and from the menu choose File -> New -> Project… -> “Import Analysis Services 2008 Database” (in 2012 it is “Import from Server (Multidimensional and Data Mining)”).  Then enter the name and location to save the new project, and that is all that is too it.

Note that if you have an sql server agent job that runs xmla script to process the cube, and this job is run after the deployment is complete in BIDS, then you don’t want the cube to automatically process when you do a deployment (which is the default).  To fix that: right-click on the project node in BIDS and click ‘Properties’.  Then under ‘Configuration Properties’ click ‘Deployment’ and set the ‘Processing Option’ to ‘Do Not Process’.

More info:

Using the SQL Server Analysis Services (SSAS) Deployment Wizard

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

SSAS: BIDS Deployment vs. Deployment Wizard

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, SSAS. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>