Tabular Models, Compatibility Level, and Power View
Posted by denglishbi on October 4, 2012
A couple of weeks ago I did a review of the CTP4 of SP1 that came out for SQL Server 2012 – Installing SQL Server 2012 SP1 CT4 Experience. Part of my test involved upgrading an Excel 2013 PowerPivot file to a Tabular Analysis Service project. After I did that I setup a BI Semantic Model connection file in SharePoint and explored the Power View designer updates. It was business as usual and I was seeing all of the new features and functionality that I had previously mention in my posts on Excel 2013 and Power View – Power View meet Microsoft Excel 2013 and Power View meet Microsoft Excel 2013 Part 2.
Well this past week I was prepping for my pre-con for the SQLSaturday #149 event and I noticed something odd. When I was connecting to my tabular models with Power View I was not seeing my hierarchies or key performance indicators (KPIs). I was a bit confused. I upgraded my environment to SP1, so what was going on. I thought the upgrade provided support for these model features now, right? I know I had seen them already, maybe I was just imagining things though. When I was doing my tabular project deployments I was also seeing a new dialog box that I was just clicking ‘Yes’ on without really understanding what it was talking about…
I completed out the pre-con still confused and then last weekend I did a bit more digging into this issue. I went back to my Excel 2013 SQLSaturday model that I upgraded and I reviewed the property settings on the database.
The item that I noticed that seemed like it might be important was the Compatibility Level setting. This database was showing SQL Server 2012 SP1 (1103). I then took a look at the database I was using for some of my demos.
Ahhh, now the light bulb was starting to get bright:) The database that I was deploying was still in the SQL Server 2012 (1100) setting even though I had upgraded my environment to SP1. Interesting. Apparently you have the ability to control this setting now if you want to run in different levels or do some testing in the same environment before upgrading all of your databases.
I didn’t realize that I now had to make a change on my model property settings before I deployed my database.
Once this setting is changed you will be prompted with the following:
Once again I click ‘Yes’ and redeploy to upgrade the database on the server. Now when I connect to my model with Power View I see my hierarchies and KPIs, perfect!
So just something to be aware of now when working with tabular model projects now. There is a property setting that we have to take a look at, Compatibility Level, and it is a pretty important one for the users of our data models:)
Here are a couple of additional reference links to take a look at (some of this documentation and instructions might differ from what you see with CTP4 of SP1, but they should be available in the RTM of SP1):
- Compatibility Level (SSAS Tabular SP1)
- Configure Default Data Modeling and Deployment Properties (SSAS Tabular)