I am sure that this has happened to someone else before. You are making a change to your SSAS cube, within your SSAS cube you have created your initial partitions. But on your production server you have programmatically added additional partitions. Now by mistake or just not thinking you deploy your project, and when it prompts to overwrite your current database, you click YES.

Now your production SSAS cube has all the wrong partitions. SO then you have to go about creating them again and processing them again.

So below are the steps that I do, before I make changes to my SSAS project, so that if I happen to deploy it by mistake I will not have to recreate the partitions. You will still have to process them again, but it does save the hassle of having to re-create them all.

Example:

·         Our current Internet Sales Partition has the following partitions created on our Production Server

o   

·         We are going to manually create a new Partition called:

o    Internet_Sales_2009

·         Then we are going to go through the manual steps to get this partition information into our existing SSAS Project.

o    So what when we are finished we will see our Internet_Sales_2009 Partition within our SSAS Project.

o    Currently the Project looks like this:

o   

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

NOTE: We are using SQL Server 2014, and SSDT for Visual Studio 2013

Creating new Partition on Server

1.       What we did was to script out our current partition and then modify it to create one for the year 2009

2.       Below is a snippet of where we made the changes

a.       

3.       Once we ran this we then could see our Partition for the year 2009

a.       

Creating new SSAS Project and importing SSAS Database

In the steps below we are going to create a new SSAS Project and then import our SSAS database into our Project.

1.       Within SSDT we are going to create a new Project with the following:

a.       

2.       Give your project a name.

a.        As with our example we gave it the name of Adventure Works – Production Import

3.       This will then start the Import Analysis Services Database Wizard

a.        Click Next on the first screen

4.       On the Source Database screen put in the details to your Server and select your database as with our example shown below:

a.       

b.       Click Next

5.       This will then import everything from your server.

6.       And once complete it will look like the following below:

a.       

b.       Click Finish

7.       If you now go to our Adventure Works Cube, click on Partitions you should see the following under the Internet Sales Measure Group

a.       

8.       When it first loads it does not update the Adventure Works.partitions file

9.       You need to do the following to put the XML data into the Adventure Works.partitions file

a.        Click on Build and then Build Adventure Works – Production Import

b.       Once this is done you will then see that your Adventure Works.cube has an asterix and needs to be saved:

c.       

d.       Click Save.

10.    Now you can verify that your Adventure Works.partition file has the information within the file by its file size:

a.       

11.    Now you can close this project down.

What we are going to do below is to now take the information from our project we created above (Adventure Works – Production Import) and put swop out the partition file so that when we open up our current SSAS Project it will then reflect the additional partition, (Internet_Sales_2009)

1.       Go to the location where your current SSAS Project is.

2.       Then make sure you go into the details where you can actually see all your project files.

3.       IN our example it would be in the following location:

a.       C:\Users\DomainUser\My Documents\Projects\Adventure Works DW 2012\Adventure Works DW 2012

4.       And it will look like the following:

a.       

b.       NOTE: You will see above the partition information stored in the Adventure Works.partitions

c.        NOTE II: Every cube that you create will always have a .partitions file, even if you have not created any partitions

5.       Now rename your Adventure Works.partitions file to Adventure Works.partitions.Backup_20140723

a.        NOTE: This is so that we know when we made the change.

b.       It will now look like the following:

c.       

6.       Now go the location where you created your Import project (Adventure Works – Production Import)

7.       In our example it would be in the following location:

a.       C:\Users\DomainUser\My Documents\Projects Adventure Works – Production Import\Adventure Works – Production Import

b.       In this folder copy the Adventure Works.partitions file

c.        NOTE: You will see it should be larger than our screenshot in step 4 above:

d.      

8.       Now go back to your folder location of your current SSAS Project. (which we have in step 3 above)

a.        Then paste the Adventure Works.partition file into the folder.

b.       NOTE: You should be able to paste it without any issues due to renaming the current partition file in step 5

9.       Now open your current SSAS Project and see when you go into the Adventure Works.Cube and go to Partitions if you can see the new partition.

a.       

Now if by mistake you do deploy your project at least the cube information is up to date.