What is new for the Tabular model in SQL Server 2012 SP1 and compatibility level
On the surface the SQL Server 2012 SP1 looks very much like SQL Server 2012 RTM but there are some hidden gems in there that will make you upgrade to SP1. This blog post will describe why you would want to upgrade your machines to SP1.
The first thing to know is that this SP1 is not a normal service pack, it is no coincidence that SP1 comes at the same time Excel 2013 RTM. As most of you probably know by now is that PowerPivot including the Analysis services engine ships inside Excel 2013. In order for us to be able to integrate the engine into Excel we had to change many internals of the engine, that caused it to be no longer backwards compatible with PowerPivot and our tabular engine as we knew it in RTM.
One of the features we support is that you are able to move a PowerPivot workbook to a tabular server through SSMS or SSDT, this scenario would have been broken because you cannot restore a model in a content that an SQL Server 2012 RTM server doesn’t understand. To be able to differentiate the models is why we introduced a compatibility level in the tabular model. SQL Server 2012 RTM models have compatibility level 1100 and a model created in SQL Server 2012 SP1 (Excel 2013) have compatibility level 1103. Important to understand is that a model set to 1103 can NOT be restored to server that only supports 1100 (RTM).
Ok what are the differences between SQL Server 2012 SP1 and SQL Server 2012 RTM:
- SQL Server 2012 SP1 servers support importing Excel 2013 workbooks through SSMS or SSDT
- 1103 models store measures in a different way (you won’t notice it)
- 1103 models store KPI’s in a different way (you won’t notice it)
- 1103 models have extended CSDL output that contains KPI’s and hierarchies. These new CSDL extensions are used by Power View for SharePoint SQL 2012 SP1 and Excel 2013 to read the model.
- 1103 models allow multiple data categories to be set where RTM only allowed Image Url. These data categories can be used for Power View maps for SP1
- 1103 models support OAuth connections to Azure marketplace. This means when your model is in compatibility mode 1103 you will get a different import method for Azure marketplace feeds.
- 1103 models support an extended character set for columns, tables and measures that are needed to support the extended language set supported by Office and Excel 2013.
In SQL Server 2012 SP1 we also made plenty bug fixes and did several performance enchantments, especially for the MDX queries that Excel sends against our Tabular models.
So how do I make sure my model is set to compatibility level 1103. You can upgrade the workbook by opening the workbook in Excel 2013 or upgrade the model in SSDT (more on this later). By default models that were already on the server before you upgraded the server will be kept 1100, you manually need to do the upgrade using SSDT, you cannot do it in SSMS.
When you install SSMS from the SQL Server 2012 SP1 media you can see what the compatibility level of the database is using the database properties:
You can also see which compatibility level is supported by the server. In the server properties we added a new Information tab that will give you information on your server instance:
In this case the server instance only support compatibility level 1100, not 1103.
When I connect to a SP1 instance I can see the compatibility level that is supported is 1103.
When you install SSDT that came with SP1 you get a choice of compatibility level on new project creation:
When you select an 1103 compatibility level please make sure your workspace server is also upgraded to SP1.
You can select “Do not show this message again” if you don’t want this to show each and every time. You can change these settings in the Options under tools, options in SSDT:
If you want to upgrade your existing model to Compatibility level 1103 you can go to the model properties and change Compatibility level from 1100 to 1103. This cannot be undone after upgrade, so be careful (we will warn you before upgrading).
After you upgraded there are only a few visual changes.
The new Azure marketplace import wizard:
When you upgrade your existing models and that contains an existing Azure marketplace you will be able to manage them using the “old” UI.
For each column you now have additional reporting properties in the form of a data category for each column:
Setting these properties on a column will give you additional features in Power View like maps:
and Hyperlink (make sure it’s a valid URL):
In the SQL Server 2012 RTM tools you had only one option “ImageUrl”, the model supported more but there was no use for it then. Now Power View supports more so we added a subset of elements to a 1100 model, you can only see the following categories:
These can be used against a SQL Server 2012 SP1 Power View model.
One more feature that we added to SP1,not related to any compatibility level, is search for metadata. If you click on the binoculars you will get a search metadata box.
This allows you to iterate through all the metadata in the model and find the object that you want with the name you searched for, It searches through tables, measures, columns, kpi's and all other objects.
As you can see many of the changes for SQL Server 2012 SP1 are under the covers and you don’t really see them but upgrading to SP1 will give you several benefits, better performance in Excel against the Tabular model and more features for Power View against the Tabular model.