Tabular databases do not support CompatibilityLevel downgrade

_{Thursday, May 4, 2017 6:59 AM}

Hi,

Situation :

• I've 2014 tabulars in my VS project.
• The SSAS instance is 2016. The reason for this difference is that Excel and Tabular 2016 doesn't support Quick explore on dimensions in Excel anymore(!?) and customer really needs that for drilling.
• We are working with multiple people in the project.
• We are now working via an RDS server and not on the same machine.

When I open the tabulars in VS, first I recieve an error that localhost\instance is not found. Change local host to the right servername. But then an error occurss:

Tabular databases do not support CompatibilityLevel downgrade

When I look in the SSAS instance I see that the compatibility of the workspace database is on 2016 and I don't want that!. I want it on 2014. 

Can someone give me a clue how to solve this?

Hennie

_{Thursday, May 4, 2017 7:39 AM}

Hi Hennie,

Thanks for your question.

After upgrading an existing tabular model project to 1200 compatibility level, you cannot downgrade to an earlier version.

If you have a backup abf file with compatibility level 1103, you can restore it to your SSAS 2016 Tabular instance.

When you upgrade a model to Compatibility Level 1200, you will then be prompted for the upgrade. Make sure you have a backup copy of your Visual Studio project files before hitting “Yes”, otherwise you will not be able to downgrade to an earlier version.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Thursday, May 4, 2017 12:50 PM}

That probably means that someone at some point has upgraded the database (maybe played with upgrading the database, but then rolled back the change in their project). Unfortunately this will leave the project in an earlier version than the workspace database.

The only way I know of to "fix" this is to:

1. open the project so that it triggers this error
2. connect to the workspace server with SSMS and delete the workspace database
3. close visual studio
4. re-open visual studio and open the project

now that the workspace has been deleted SSAS will not be able to re-attach it so it will re-create a blank workspace database using the compatibility setting from the project.  

http://darren.gosbell.com - please mark correct answers

_{Friday, May 5, 2017 5:55 AM}

HI Darren and Wilson,

Thank you for your replies.

As far as I know the other team members didn't play with the compatibility level. They know that this will cause problems (At least I hope). Somehow it seems that changing the localhost\instance will trigger that the workspace DB is in 2016 somehow. Also localhost is something they didn't change (I assume). 

@Darren\. I executed your suggestions and that seems to be working. Although I'm not sure that solve the whole problem. May be It has something to do with the Tools->options etc settings. But that is an user level setting.

I'll monitor it in the coming days/weeks and let you know my findings...

Hennie

_{Monday, May 8, 2017 3:06 AM}

If you have not been into the Tools > Options settings for the SSAS Tabular designers and changed the default workspace server Visual Studio will attempt to use localhost. By default when you change the workspace server Visual Studio will ask the server if it can attach an existing copy of the workspace database. If a detached workspace database exists it will be loaded, if that database is a later compatibility version than the current project you will get that error about not being able to downgrade.

Even if you rollback the project file after doing the upgrade, the copy of the workspace database has already been upgraded and it's not stored in the project folder so it stays at the higher compatibility setting. There are no auto-upgrade mechanisms on the server, the only way to change the compatibility setting is by doing it explicitly in the project (or by running an explicit alter script)

http://darren.gosbell.com - please mark correct answers