When deploying or processing a cube or dimension you may encounter an error similar to this:

Errors in the metadata manager. The dimension with ID of ‘Student Financial Aid ~MC-Student AK’, Name of ‘Student Financial Aid ~MC-Student AK’ referenced by the ‘Student Financial Aid ~MC’ cube, does not exist.

Errors in the metadata manager. An error occurred when loading the Student Financial Aid ~MC cube, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\SSAS Labs.0.db\Student Financial Aid ~MC.1.cub.xml’.

(Microsoft.AnalysisServices)

If you try to process, deploy or delete this cube database you will get the same error over and over. You may even get this error when trying to deploy or process a totally separate cube database or dimension in another project or solution.

Basically, there has a been a corruption of the XML file that holds your cube definition possibly because of conflicting dimension definition between your cube structure inside your project and the deployed cube database or changes in the underlying source schema. The exact reason why this happens is a little unclear as I have not been able to replicate the circumstances why this happens. I have come across this issue only twice. The purpose of this post is to show you how to get rid of the cube database, allowing you to re-deploy it from your BIDS project.

First, stop the SQL Server Analysis Services service from the Windows Services console,  from SQL server Configuration Manager or from the command prompt using net stop <service> (or even from PowerShell) . Once the service  has stopped rename or delete the folder where the cub.xml file resides as specified on the error window.

In my case, I need to rename or delete the folder “SSAS Labs.0.db” as specified on the third sentence: “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\SSAS Labs.0.db\”. You can rename this folder whatever you want, such as “XXX SSAS Labs.0.db” or simply delete it.

Once you rename or delete the folder, restart the SQL Server Analysis Services service. Connect to your SQL Server Analysis Services instance via SQL Server Management Studio (SSMS) and delete the cube database. Now you should be able to re-deploy your cube database from your BIDS project.

Once I replicate the circumstances that cause this issue, I will do a follow-up post. Make sure to check back or subscribe to my blog.

*** Update 5/10/2011: Apparently this is a SQL Server 2008 R2 issue when re-deploying several times in a short period of time. I was advised this issue should be corrected in Service Pack 1. If you are having this issue in SQL Server 2008 R1 or still experiencing it after installing SQL Server 2008 R2 Service Pack 1 please comment.