• Post Categories

  • Browse Blogs

  • Blog Stats

    • 633,971 hits

  • Syndications

    SQLServerPedia Contributor

SSAS errors: Errors in the metadata manager. The dimension with ID of ‘xxx’ referenced by the ‘yyy’ cube, does not exist.

Posted on March 22, 2011 by Jose Chinchilla aka SQLJoe

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: , , , , , , , , , , , , , |

« »

72 Responses

  1. Eugene Jenkins, on May 19, 2011 at 8:16 pm said:

    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.

    Reply
  2. jf, on July 6, 2011 at 3:55 pm said:

    Thank you, this worked to solve this problem!

    Reply
  3. NB, on July 26, 2011 at 2:47 am said:

    thanks I got same problem !!!!! It is working now

    Reply
  4. AT, on August 1, 2011 at 5:51 pm said:

    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.

    Reply
    • Jose Chinchilla aka SQLJoe, on August 2, 2011 at 5:41 pm said:

      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.

      Reply
    • AT, on August 3, 2011 at 1:34 pm said:

      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.

      Reply
  5. Lorraine Dixon, on September 21, 2011 at 6:46 am said:

    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

    Reply
  6. RaJa SeKhar, on December 1, 2011 at 5:56 am said:

    Hey thanks man.. This was the error that I’m struggling with.
    Now it is working fine.

    Reply
  7. Cathan, on December 29, 2011 at 3:10 pm said:

    I experienced this error today in sp1. The instructions here fixed the problem.

    Reply
  8. Monte Erickson, on January 9, 2012 at 12:43 am said:

    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.

    Reply
  9. Joe M, on January 12, 2012 at 11:51 am said:

    From one Joe to another, glad that the decaffeinated have something to contribute. Thanks for the detailed post.

    Reply
  10. Sam, on January 16, 2012 at 5:42 pm said:

    You’re the man. Good stuff, saved me so much time trying to figure what what the issue is..
    cheers

    Reply
  11. TStein, on January 17, 2012 at 5:09 am said:

    Thank you. Perfect solution. I got this error with SqlServer2008R2. I assume this happened after overwriting the underlying database (respectively backup and restore?).

    Reply
  12. Crosby Grant, on January 19, 2012 at 2:35 pm said:

    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.

    Reply
  13. G N Rao, on January 24, 2012 at 7:33 am said:

    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.

    Reply
  14. TSouthAfrica, on February 22, 2012 at 11:59 am said:

    Thanks, this helped me…i was getting really frustrated.

    Reply
  15. clonyem, on February 28, 2012 at 2:29 pm said:

    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.

    Reply
  16. SenaM, on March 10, 2012 at 2:40 pm said:

    Thanks SQL Joe. it solved my problem.
    Keep up the good work !

    Reply
  17. Thilina, on March 25, 2012 at 9:09 pm said:

    Thanks mate, had the same error on SQL Server 2005 SP4 was able to sorted out thanks to your article.

    Cheers

    Reply
  18. Val, on April 19, 2012 at 1:23 pm said:

    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

    Reply
    • Jose Chinchilla aka SQLJoe, on April 19, 2012 at 3:06 pm said:

      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.

      Reply
      • Val, on April 20, 2012 at 4:27 am said:

        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?

      • Val, on April 26, 2012 at 10:03 am said:

        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

  19. Srinivas, on May 25, 2012 at 5:21 am said:

    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 !!!

    Reply
  20. amilkar0417, on June 7, 2012 at 1:00 pm said:

    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!

    Reply
  21. Javi, on June 21, 2012 at 10:19 am said:

    many thanks!

    Reply
  22. Mehrdad, on June 29, 2012 at 6:37 am said:

    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.

    Reply
  23. fchatel, on July 20, 2012 at 10:15 am said:

    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

    Reply
  24. Srinivas, on August 9, 2012 at 3:43 am said:

    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…

    Reply
  25. John C, on August 15, 2012 at 5:07 am said:

    A great help – Thanks

    Reply
  26. Kevin, on September 20, 2012 at 6:44 pm said:

    Thanks for the post. It helped 100%! Cheers!

    Reply
  27. Jean Carlo Di Maio, on November 2, 2012 at 12:36 pm said:

    Excelente punto, pude resolver siguiento tal cual los pasos indicados, muy buena la solución, sencilla y directa al grano

    Reply
  28. aseff, on November 15, 2012 at 12:24 am said:

    thank you. @ Jose Chinchilla aka SQLJoe

    Reply
  29. Shabbir Mala, on December 3, 2012 at 3:31 pm said:

    Thanks Jose. You are the man!

    Reply
  30. Adrian Waddy, on December 11, 2012 at 11:31 am said:

    Many thanks, very grateful for being pointed in the right direction.

    Reply
  31. John Turner, on December 28, 2012 at 3:22 pm said:

    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!

    Reply
  33. Hiren Parikh, on January 31, 2013 at 6:55 am said:

    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.

    Reply
  34. Filipe Gaspar, on June 18, 2013 at 8:40 am said:

    Thanks! It solved the problem.

    Best regards
    Filipe Gaspar

    Reply
  35. Mubin, on July 25, 2013 at 3:00 am said:

    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

    Reply
  36. uma, on October 23, 2013 at 7:04 am said:

    tks, its working

    Reply
  37. Pavan Kalyan, on November 25, 2013 at 7:34 am said:

    Awesome help… thanks I was tangled since 2 days….

    Reply
  38. Kim, on November 26, 2013 at 1:58 pm said:

    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!

    Reply
  39. Jon, on November 29, 2013 at 6:25 am said:

    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

    Reply
  40. Israel Gress, on December 6, 2013 at 11:24 am said:

    Thanks a lot!! This article saved my life!
    Greetings from México

    Reply
  41. shailesh, on December 30, 2013 at 3:58 pm said:

    Jose Chinchilla aka SQLJoe .. you Rock man! .. it worked .. my entire team was thinking how to do it and you gave the answer

    Reply
  42. Davide, on March 27, 2014 at 12:43 pm said:

    Thank you very much! It works 2008 R2 version

    Reply
  43. Andrea Menzel, on May 13, 2014 at 5:26 am said:

    Thank You!!!

    Reply
  44. dan, on August 22, 2014 at 10:17 am said:

    Brilliant, thank you very much ! Works on 2012 too = )

    Reply
  45. Carlos Horqque, on November 14, 2014 at 4:04 pm said:

    Really helpful, thank you very much!.

    Reply
  46. lixia Zhang, on November 17, 2014 at 8:25 am said:

    Thank you very much! Your post make my day !

    Reply
  47. Thanda, on January 16, 2015 at 5:20 am said:

    Thank you very much!

    Reply
  48. Arun, on February 20, 2015 at 5:45 am said:

    Excellent solution proposed. Thanks it worked at first go itself

    Reply
  49. vipul vala, on May 15, 2015 at 1:07 pm said:

    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.

    Reply
  50. Christian, on August 19, 2015 at 3:41 pm said:

    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

    Reply
  51. Alex, on October 22, 2015 at 7:36 pm said:

    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.

    Reply
  52. mmejid@yahoo.fr, on November 25, 2015 at 9:09 am said:

    this post is old but was helpfull for me today thanks

    Reply
  53. Kevin James, on January 6, 2016 at 7:19 pm said:

    Thanks a ton for posting this! I encountered on SQL Server 2012… You solution worked like a charm! Cheers!!

    Reply
  54. Glen, on March 23, 2016 at 8:31 am said:

    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!

    Reply
  55. Anna, on February 26, 2017 at 8:45 pm said:

    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.

    Reply

Leave a reply to TSouthAfrica Cancel reply