Subscribe to Blog feed
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)
Cheers!
Boyan Penev said
We hit a related issue today.
If you have SSDT without SP1 and you try to create a new Tabular solution using a workspace instance upgraded to SP1 you get an error message stating that the workspace db is not compatible. So, what happens is this: SSDT (RTM) issues a create statement with no Compatibility Level in it. This results in a db with the default (latest) Compatibility Level for the instance (e.g. SP1). Then, SSDT tries to open it and fails. In this case we’ll have to create a RTM Compatible (1100) db using a SSDT SP1 and then use that in SSDT RTM. I hope thy fix all this in SP1 RTM.
Dave said
Dan,
Thanks for the post. I have been banging my head trying to figure out why hierarchies and KPIs were not showing up in the Excel 2013 PowerView.
However, I am still having a few issues…
After installing SQL2012 SP1, my database shows SQL Server 11.0.3000 but a right-click on the database then properties and then Options shows Compatibility Level: SQL Server 2012 (110) not 1100 or 1103. The drop down list also only shows compatibility 90, 100 and 110.
On my SSAS instance I do see Supported Compatibility Level 1103 but the actual Database in SSAS is set to 1100 with no drop down list.
Could you shed some light?
Thanks!
denglishbi said
The compatibility level that you are interested and that I posted about is for Tabular SSAS. This is a new setting. You can update yours in the model properties in SSDT and redeploy. That is what I have posted here. Another option is to simply script your SSAS database in SSMS and then update the compatibility level value and then run the alter script to change your value. Unfortunately you are not able to simply modify this in the property window where you are seeing the value and like I posted in my blog with the screenshots. HTH, good luck.
Dave said
Scripting did the trick! Thanks a ton!
Dave
Dave said
Dan,
Here is an interesting finding (maybe). I could not update compatibility mode remotely using SSDT because the database that I was connecting to was located on another machine running SQL 2012 SP1 (11.0.3000) and my local machine was running SQL 2012 (11.0.2100.6), When I looked at the .bim file properties the Compatibility Level was grayed out. Once I updated the local machine to SQL 2012 SP1 I was able to update using SSDT. The Compatibility Level drop down list was enabled in the .bim file properties window.
Hope that helps someone else…
Dave
denglishbi said
Yes, you need to also have SP1 installed on the remote machines as well.
Cignum said
Hi Dan,
thanks so much for your article, it really saved my …
Though I had to change the Compatibility Mode in my SQlServer Data Tools project and re-deploy: I could not change it in SSMS (as from your screenshots) because it appeared disabled.
denglishbi said
Good to hear. If you want to do it in SSMS you can script the database and with the alter statement that is generated you will be able to modify the compatibility value, but you would need to know what a valid setting is of course.