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.
Filed under: Business Intelligence, Errors, SQL Server, SQLServerPedia Syndication, SSAS | Tagged: #sqlserver, 2005, 2008, analysis services, business intelligence, cube, dimension, error, errors, metadata, processing, Server, SQL, ssas |
Thank you Joe. You are the man. That is exactly the error I got today and your instructions were spot on. When I intially saw the error I thought I was doomed. Thanks again.
Great! I’m glad my post was helpful.
Thank you, this worked to solve this problem!
thanks I got same problem !!!!! It is working now
I am currently having this exact problem but am unable to resolve this issue. We have SQL Server 2005 SP4. I used the same procedures but the problem persisted. Then I discovered it wasn’t just the one database, it was all the databases on our Analysis Services server. Odd thing is, we can run MDX queries but we can’t run anything else. We did recently move the server onto a virtual machine so I wonder if that’s what’s causing the issue.
I would make sure both servers are at the same service pack and cumulative update level. Did you do a backup/restore or did you deployed th database from BIDS. I would recommend renaming or deleitng the OLAP databases on the new virtualized server and re-deploying them from BIDS by changing the target server under the project properties. If the problem persists, let me know, it will be a interesting case to crack.
A different team handled the move to the virtual server so I don’t know how they did it but I assume they used a backup and restore. Even if we delete the databases, we are unable to redeploy.
I did find out that a patch was applied that affected the XML reader on the day the errors started occurring. We are talking with Microsoft right now about our next move.
We’re using SQLServer 2008 R2 and have applied Service Pack 1 but are seeing this issue 2 or 3 times a week. Anyone with any advice – would be much appreciated.
Lorraine
Hey thanks man.. This was the error that I’m struggling with.
Now it is working fine.
I’m glad this post helped you solve your issue.
I experienced this error today in sp1. The instructions here fixed the problem.
I was getting similar errors in SQL2008R2 RTM. Installing SP1 did not help. I’m following the MCTS 70-448 self paced training kit book, my troubles began in the data mining chapter exercises. I’ve had to flush the database files several times.
From one Joe to another, glad that the decaffeinated have something to contribute. Thanks for the detailed post.
Awesome!
You’re the man. Good stuff, saved me so much time trying to figure what what the issue is..
cheers
I’m glad this blog post helped you.
Thank you. Perfect solution. I got this error with SqlServer2008R2. I assume this happened after overwriting the underlying database (respectively backup and restore?).
Glad this solution helped. I need to research more on root cause.
Spot on. BIDS 2008 and SQL Server 2008 R2.
Similar lead-up (handful of deploy attempts) led to a similar error.
In my case, I actually reverted back to a known-good set of BIDS files using my version control, and when I attempted to deploy I got this error. In the meantime, i had renamed things, and added/deleted things, and basically “mucked around”).
Fixed pronto. Thanks.
Excellent. Glad this helped.
I got same error, resolved my issues with in 10 min with the help of ur helful post. thank you very much , you saved my time.
Excellent. I’m glad this post helped.
Thanks, this helped me…i was getting really frustrated.
Great post, SQL Joe. I was stuck with the same problem referencing a completely different project. The steps provided walked me through the resolution. Much appreciated, man.
Thanks SQL Joe. it solved my problem.
Keep up the good work !
Thanks for your feedback.
Thanks mate, had the same error on SQL Server 2005 SP4 was able to sorted out thanks to your article.
Cheers
Awesome. Cheers!
Hi there, I am experiencing this same issue, but in SQL 2012 with SQL Server Data Tools (we are currently on RC0) : your proposed solution works like a charme, only problem is that this is getting now rather annoying: we are in development and, of course, we have to redeploy at least once a day. This has already caused a fair amount of rework…any idea what the cause could be and how can it be avoided altogether??
Thanks for your help
Try changing the Deployment Mode under the project properties from Deploy Changes Only to Deploy All. I haven’t had this issue come up again after changing this option in BIDS for SQL Server 2008R2. Let me know if this works in 2012 as well in order to propose it as a solution.
Thanks for the quick response: we are actually not using a BIDS solution file but are making changes directly on the server version of the cube by opening the cube directly in BIDS, and I can’t seem to find a way of changing the server mode here. I’m not sure what the best practice would be, maybe we should change our approach and make and deploy changes via an SSAS project file instead?
No luck with changing the deployment method… we are still experiencing the same issue and I really can’t understand why this is happening. We were on RC0 and hoped that the upgrade to RTM would have helped, but it wasn’t the case. I will contact Microsoft and see if they can be of any help, but if you have any other ideas, please let me know
That’s quite useful, thanks for the information.
I have come across with a scenario where I don’t see my Cube DB at all and I was in an impression that it might have been dropped by someone but I also learnt that, “A database can be dropped by SSAS at start up in case of cube corruption”. I am curious to understand is there anyway I can track or check in Logs that Cube got dropped by SSAS.
Please Help !!!
I got this same error today. I’m not sure why this happend. I’m novice in this field. Anyway, your explanation solved the problem. I greatly appreciate your taking the time to share youre experience with us all.
Thank you!
many thanks!
First of all, Thank you very much. 🙂
Could you compare the xml files of the same cube with its renamed copy? Did you find anything? FYI, I use 2008 R2 Developer version.
That would be a great way to identify and understand the root cause.
Experienced this error today: no need to stop AS and delete the file for me, simply retrieved the last changed .dim for a backup in my project, and can deploy again
Thanks Joe, very useful to me. BTW – I am trying to get some idea about Tabular Model, have you written any blog on SSAS Tabular Model? I tried with no luck…
I will be doing a couple of posts soon. Subscribe to my blog feed to receive blog post updates.
A great help – Thanks
Thanks for the post. It helped 100%! Cheers!
Excelente punto, pude resolver siguiento tal cual los pasos indicados, muy buena la solución, sencilla y directa al grano
Me agrada que te haya servido mi post.
thank you. @ Jose Chinchilla aka SQLJoe
Thanks Jose. You are the man!
Many thanks, very grateful for being pointed in the right direction.
Thanks Joe – seems my Windows Server 2008 / SQL Server 2008 R2 setup is quirky — haven’t had these gremlins with my WS 2003 / SQL Server 2008 setup. Great to know folks like you have taken the time to post the fixes!
[…] https://sqljoe.wordpress.com/2011/03/22/ssas-errors-in-the-metadata-manager-the-dimension-with-id-of-… […]
Found my self in trouble with the same issue.. After reading your article I simply followed the steps & got immediate solution. Thanks a million… Just 1 more doubt, Y this happen???
Regards,
Hiren Parikh.
Thanks! It solved the problem.
Best regards
Filipe Gaspar
Thanks Joe,
Your suggestion works, I have faced this issue in SQL Server 2008 R2. But we Need to process the cube in Full Processing Mode from BIDS to resolve the issue.
Regards,
Ashish
tks, its working
Awesome help… thanks I was tangled since 2 days….
Yes, we have 2008R2 SP 1 and I’m going through a tutorial so I’m re-deploying frequently and running into this problem all the time. I can’t get through the tutorial. Thanks so much for your instructions. I’m able to deploy again!
Hi,
Been having the problem with SQL Server 2008 SP1, 64bit, not the 2008 R2 version. Going to see if the SP2 cures it and try the same process as above
Thanks a lot!! This article saved my life!
Greetings from México
Jose Chinchilla aka SQLJoe .. you Rock man! .. it worked .. my entire team was thinking how to do it and you gave the answer
Thank you very much! It works 2008 R2 version
Thank You!!!
Brilliant, thank you very much ! Works on 2012 too = )
Really helpful, thank you very much!.
Thank you very much! Your post make my day !
Thank you very much!
Excellent solution proposed. Thanks it worked at first go itself
Thank you very much, in my case the SSAS database was also deleted(dont know why its deleted from my local database). I think this happen just after my windows 8 update on my SSAS 2012.
I have the version Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
and this problem is permanent.
I’ll do this procedure that you mentioned
We received the same error and I think I may know a cause to it. Our SSAS database uses drives on a SAN, so on the SSAS box, the “E:” drive it was using was disconnected abruptly due to the SAN installing windows updates and restarting…corrupting the DB.
this post is old but was helpfull for me today thanks
Thanks a ton for posting this! I encountered on SQL Server 2012… You solution worked like a charm! Cheers!!
Perfect! I didn’t realize that my error message referred to a Cube that was not being processed. I assumed it was a permissions error and spent days modifying the file and directory permissions. When I deleted and then restored the offending database, Analysis Services was happy again… and so am I! Thank you so much!
i just facing same problem just now. try restarting server did not give solution but when we try to restart SQ: service, error was solve. So not so sure what is wrong.