Compatibility Level – Tabular Model

Before we talk about Compatibility Level on Tabular model project, it is important to explain what is Compatibility Level. Compatibility Level  is the version of SQL Server with which a Database or Tabular model is compatible. Follow the compatibility level for databases:

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008 and SQL Server 2008 R2
110 = SQL Server 2012

Tabular model is a new feature of SQL Server 2012 and on a Tabular model project there are two Compatibility Level, as shows below:

1100 = SQL Server 2012
1103 = SQL Server 2012 SP1

Note: Compatibility level 1103 is available after install SQL Server 2012 SP1.

Differences between Compatibility Level 1100 and 1103 on Tabular Model

The compatibility level 1103 (SQL Server 2012 SP1) has added new features to tabular model as listed below:

• Optimize storage for measures and KPIs.
• Extend data categorizations.
• Extend characters.
• Hierarchy annotation
• Improved support for Data Market data feeds.

Setting Compatibility Level on a new Tabular Model project

Follow the steps to create a new Tabular Model project.

• Open Visual Studio Data Tools.
• Click New Project.
• Select Business Intelligence -> Analysis Services and then select Analysis Services Tabular Project.
• Enter the project name and then click OK.
• Enter the Tabular Model Instance and also choose the Compatibility Level and then click OK. The latest compatibility level will be selected by default.

Upgrade Tabular model project from Compatibility Level 1100 to 1103

For this topic I will use the Tabular Model project “AW Internet Sales Tabular Model” available on CodePlex web site.

• Open AW Internet Sales Tabular Model.sln solution.
• Enter the server name for the Tabular Instance and Click OK.
• Right Click on Model.bim and then click Properties.
• Compatibility Level will be disabled to change and property value will show: “(STRING REVIEW) Please open BIM file in Tabular Designer in order to view or change compatibility level”.
• Double click on Model.bim to open the Tabular Model.
• Right click on Model.bim and then click Properties.
• In Compatibility Level change it from SQL Server 2012 RTM (1100) to SQL Server 2012 SP1 (1103).
• Click Yes in the warning message. Remember that after change the Compatibility Level to 1103 you will not be able to change back to 1100.