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