Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to change compatibility level of a SSAS Tabular Database using SSDT


By:   |   Updated: 2016-03-16   |   Comments (1)   |   Related: > Analysis Services Administration

Problem

Upgrading SQL Server Analysis Services (SSAS) to higher versions by applying service packs or installing a totally new version is a routine process in production environments. Each new version generally adds a series of feature enhancements to the product. To exploit the benefit of these features, upgrading SSAS databases to the higher compatibility levels is a standard exercise. In this tip, we would look at how to change the SSAS compatibility level.

Solution

The SQL Server Data Tools (SSDT) provides an option to change the compatibility level of SSAS Tabular Model while creating a new model as well as for existing models.

Selecting the compatibility level while creating a new SSAS Tabular database model

For this tip, we assume that SSAS 2012 or a higher version is already installed on the machine where this exercise is being performed.

Create a new SSAS Tabular Project using SSDT from the File -> New -> Project -> Analysis Services Tabular Project menu option. Once a name is provided for the project and the OK button is pressed, a new dialog opens up as shown below. The number of compatibility levels available depends on the version of SSAS installed. If you have SQL Server 2012 SP1 or higher installed, you should be able to find the options displayed in the below screenshot.

Tabular Model Designer

Changing the compatibility level of an existing SSAS Tabular database model

For the purpose of discussing this use case, I will be using Adventureworks Tabular Model - SQL Server 2012 version. You can download and install the same on your development system from here.

If you open the SSAS Adventureworks 2012 database in SSMS and view the database properties, you should be able to find the compatibility level of the database as shown in the below screenshot.

SQL 2012 Compatibility Level

In order to update the database model, open the Adventureworks Tabular model in SSDT. Select the properties of the model, and you should be able to find the Compatibility Level property as shown in the below screenshot.

Compatibility Level

Change the compatibility level from 1100 to 1103 i.e. SQL Server 2014 / SQL Server 2012 SP1 and save. Generally the project is read-only, so all the files of the project may be read-only. So when you make any changes to the project, you may get a prompt that the file is read-only. Once you make the file writeable, you should be easily able to save the changes. If you try to deploy a project with a lower compatibility level on a SSAS server that supports higher compatibility level, you may get the prompt as shown in the below screenshot which notifies the user regarding the availability of a higher compatibility available on the server.

Compatibility Notification

Assuming that the compatibility level has been changed, and the project is being deployed, you may get a notification to close and reopen the project, as shown in the below screenshot.

Update Alert

Once you do the same, it should allow you to deploy the project easily. Keep in mind that once the compatibility level is updated, it cannot be downgraded back to lower levels. After the project has been successfully deployed, you can check the compatibility level property of the newly deployed database from SSMS and you should be able to see the higher compatibility level.

SQL 2014 Compatiblity

Changing the compatibility level from 1100 to 1103 would indicate that the newly deployed database is compliant with the higher version. This would mean that the new features introduced with SQL Server 2012 SP1 would be now available to the newly deployed DB. Some of such features are as mentioned below:

  • Optimize storage for measures and KPIs
  • Extended data categorizations and characters
  • Hierarchy annotation
  • Improved support for data feeds
Next Steps
  • Try to install the Adventureworks Tabular SQL 2012 sample on SSAS 2014 or SSAS 2016 CTP.
  • Change compatibility levels and test the features that were not available with the lower compatibility levels to check whether they become available after upgrade.


Last Updated: 2016-03-16


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 22, 2019 - 3:23:02 AM - Kumar Back To Top

Hi Siddharth, I don't have much knowledge about SSAS and not entirely sure about "Changing the compatibility level of an existing SSAS Tabular database model".

You mentioned "In order to update the database model, open the Adventureworks Tabular model in SSDT. Select the properties of the model, and you should be able to find the Compatibility Level property as shown in the below screenshot. Are you able to give step-by-step instructions on how to open the existing tabular model from SSDTS to get to its properties.

Or, we need to Create New Project and select "Import from Server (Tabular)" ?


Learn more about SQL Server tools