Freigeben über

What is new for the Tabular model in SQL Server 2012 SP1 and compatibility level

  • Artikel

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.

Comments

  • Anonymous
    January 14, 2013
    Hi Kasper,hoping to see you at SQLPASSBA in Chicago. I am concerned that after several fresh installs of SQL Server and SSDT, on laptops (Win7,8) and servers (2008, 2012) in on the OS, and in Hypervisors and Azure, I am not seeing what you are seeing. Specifically:SSDT Dec 2012: fresh install without VS2012 - there are no Business Intelligence templatesSQL Server / SSMS / SSDT 2010: the changes you indicate above w.r.t. compatibility level are not visible the new way, even after installing the various versions (there seem to be a few of them) SSAS: Excel Power View cannot query a Tabular or Multidimensional cube (and only allows WinAuth) Help! Am I doing something wrong? Is there a 'hidden' update to download somewhere (like there was for DataMining)Cheers,Daragh

  • Anonymous
    January 15, 2013
    Hi Daragh,Please use the SQL Server 2012 SP1 install media to install SSDT for BI, the engine and SSMS.Thanks,Kasper

  • Anonymous
    January 15, 2013
    Hi Kasper,Thanks for the response.By "install media" I presume you mean the ISOs available from:www.microsoft.com/.../details.aspx(I installed from SQLServer2012SP1-FullSlipstream-ENU-x64.iso)Was your posting dependent on any items from the feature pack (from 2013-Jan-09) too?Cheers,Daragh

  • Anonymous
    January 15, 2013
    Me again. Is this what I need? Thanks in advance.www.microsoft.com/.../details.aspxMicrosoft SQL Server 2012 With Power View For Multidimensional Models CTP

  • Anonymous
    January 16, 2013
    Hi Daragh,You need to install www.microsoft.com/.../details.aspx Microsoft® SQL Server® 2012 Service Pack 1 (SP1).Kasper

  • Anonymous
    March 18, 2013
    Hello,I was able to upgrade successfully, but am having issues with calculated measures and columns breaking due to "incompatible types". Is this a common issue and is there an easy workaround?

  • Anonymous
    October 06, 2013
    Hi Team,I am facing an issue. My problem is :I import an existing tabular model by using "Import From Server(tabular)" option. It open that project into SSDT.Now I again deploy it from SSDT.It deployed successfully. And in SSAS it create a new analysis database.But when I check inside this new analysis database, I am not able to see any data present. Even when I try to create SSRS report on the same. Report also return balnk columns only.I really appreciate if someone help me on that.

  • Anonymous
    May 27, 2014
    I have posted one question on SSAS regarding RefreshPlz refer following MSDN linksocial.msdn.microsoft.com/.../an-error-occurred-during-an-attempt-to-establish-a-connection-to-the-external-data-source

  • Anonymous
    November 04, 2014
    Will there be a SP2 compatibility level added? We just installed SP2, but there isn't an available SP2 value.

  • Anonymous
    November 04, 2014
    No new compatibility level was introduced with sp2. Sp1 works flawlessly with sp2 and even SQL 2014