SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...