Upgrade Tabular Model project to SQL 2012 SP1

SQL Server 2012 SP1 introduces new features for Analysis Services running in Tabular mode, including optimized storage for measures and KPIs, extended data categorizations, extended characters, hierarchy annotation, and improved support when importing from Data Market data feeds. In some cases, Tabular model projects being deployed may not be compatible with an Analysis Services deployment server instance. With SP1 applied, you can specify Compatibility Level when creating new Tabular model projects, when upgrading existing Tabular model projects, when upgrading existing deployed Tabular model databases, or when importing PowerPivot workbooks.

So in order to be completely running the latest SQL 2012 Service Pack 1 in your environment, you need to upgrade SQL Server Analysis Services Server (SSAS) Tabular mode, Tabular Model projects and Tabular Model databases separately.

In our environment, the SSAS Tabular deployment server and workspace server are separate, we have SSAS production and test where all Tabular Model databases have been deployed and a dedicated workspace SSAS server so developer can use it for Tabular Model workspace database. After we installed SP1 on SSAS Prod/Test and Workspace Server, I ran into an issue where I couldn’t open and load the Model.bim file inside the Tabular Model Project and generated an error:

Error: (STRING REVIEW) Please open BIM file in Tabular Designer in order to view or change compatibility level (See screenshot below with error message in Compatibility Level)

1_complevelerror

When I tried to Open Model.bim file again, I got a different error message Error:

2_openBIMfileError

============================

Error Message:

============================

Tabular databases do not support Compatibility Level downgrade.

In order to resolve this issue, I found a workaround where I was able to upgrade my Tabular Model project to SP1 and deployed it to SSAS Tabular mode server.

So, back to the Model.bim properties page, change the Workspace server to another SSAS Tabular SP1 server

After entering a different Workspace server name, hit Enter, you will be prompted with the following window, click Apply Change to connect to the new SSAS Tabular SP1 server

4_ChangeWorkspaceServerWarning

Now you can open the Model.bim file. To upgrade the Model.bim to SP1, right click on Model.bim, select properties and change the Compatibility Level to SQL Server SP1

5_ChangeCompLevel

Once, you changed the Compatibility Level to SP1, another windows will prompt you with the following message, click Yes

6_ChangeCompLevelWarning

After click yes, the Model.bim file will close and re-open again with new compatibility level

7_Final

Now, your BISM project is upgraded successfully to SP1, you can change the workspace server back to the original one.

6 thoughts on “Upgrade Tabular Model project to SQL 2012 SP1

  1. An easier option is to open the Project in SSDT, right-click Model.bim, select View Code, scroll to the bottom and change the compatibility level value manually to 1103

  2. Pingback: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model

    • There is no SP2 compatibility level, It was just SQL Server 2012 Service Pack 1 (SP1) which introduced new features for Analysis Services running in Tabular mode. You can say it is SP1 and later compatibility level.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s