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.

Error while Synchronizing SSAS database

While doing Google, I found that most of the professionals are facing following error message when they are trying to Synchronize the cube between two servers.

Database synchronization started.

The physical TCP/IP connection failed: An existing connection was forcibly closed by the remote host.  

The peer prematurely closed the connection. 

Backup and restore errors: An error occurred while synchronizing the 'AdventureWorks' database. 

An error was encountered in the transport layer. 

Database synchronization failed.

Following is the screen shot for the same.

If you want to synchronize the SSAS database from one server to another then your active directory user should have administrative permissions on the destination SSAS services. I have already mentioned the steps in one of mine blog for the same.
http://aniruddhathengadi.blogspot.com/2011/02/how-to-give-administrative-rights-on.html

For resolving above mentioned issue, run analysis services with domain user who has admin rights instead of local system account.
Follow following steps for the same.

• Logon to SSAS "Destination server" (i.e. the destination server on which you want to Synchronize SSAS database from source server).
• Open "Computer Management" (you can open "Computer Management" simply by typing shortcut "compmgmt.msc" in the "Run" window).
• Under "Computer Management", expand "Services and Applications" node and then select "Services" node. 
• Under Services panel, right click on "SQL Server Analysis Services(MSSQLSERVER)" and select Properties.
• Under Properties window, click on "Log On" tab and select option "This account" instead of "Local System account". Mention the credentials of the active directory user who has admin rights (i.e username and password) and restart the services.