Create an Analysis Services Project Based on an Existing Analysis Services Database

In most the MSBI projects, if the volume of data is huge then generally we create a SSIS package for creating partitions automatically under the corresponding measure group on yearly basis, quarterly basis (or monthly basis...depends on the volume of data and the requirement). For achieving this, generally we create a package with a "Script task" and write AMO (Analysis Management Objects) code for creating partitions. We then schedule that package using "SQL Server Agent" in such a way that it should create a partition before the new year,quarter or month begins (I have explained the steps in one of my blog to create the package in order to automate the creation of partitions Automate creation of cube partitions). So if you execute the package then it will create a partition in the cube but that partition does not exist in your original SSAS database project  file which you have created while designing cube (Original project file).So basically the original SSAS project file and the another one which exist on the SSAS Server (On-Line mode copy) does not match. Now just consider, server copy of your SSAS database contains some partitions which are not present in the original project copy and then client comes with the requirement in which they want to add some more dimensions and measures to the existing cube and that will create a question in front of you, either to do the changes in the "On-Line mode" or to create those extra partitions manually in the original solution file in order to get both the versions in sync. But basically most the people are not aware that there is one option in BIDS i.e. "Import Analysis Services Database" and by using this option, you can import a project file (i.e. create a BIDS project file) from the existing SSAS database which is present on SSAS Server so I thought lets share this with this post. You can follow following steps for importing analysis services database from the existing one which is present on SSAS server.

1. Open BIDS and press "Ctrl + Shift + N", so this will open a "New Project" wizard.Under "Project Types", select "Business Intelligence Project" and then select "Import Analysis  Services Database" option. Mention the proper "Name", "Location" in the Wizard and click on OK  button. Refer following screen shot for the same.

2. When you click on OK button of "New Project" wizard, it will open "Import Analysis Services Database Wizard" window, click on "Next" button. Under "Source database" wizard, give the analysis server name under "Server" option and select analysis services database name from the "Database" drop-down. (As I am demonstrating using "Adventure Works DW 2008R2", I have selected the same which you can refer in following snapshot).

3. After doing appropriate selection in the prior step, click on "Next" button. As soon as you click on Next button, wizard start creating the copy of "Adventure Works DW 2008R2" SSAS database project file. After completion, click on "Finish" button and you will find the SSAS project file named "AdventureWorks" (as we have given the name as "AdventureWorks" in step 1, so this wizard creates the project file with whatever name you provide). If you compare the partitions under project file then you will find all the new partitions there as this is the same copy which is present on server. Now you have a project file similar to server copy and you can made changes on it.