This is a blog post in an upcoming series of articles about the new Compatibility Level 1200 of SSAS Tabular Models in SQL Server 2016. In this series, we will share our experiences and recommendations from working with Compatibility Level 1200 in real-life scenarios.
With the release of SQL Server 2016, Microsoft has introduced the new Compatibility Level 1200 for Analysis Services Tabular Models. SQL Server 2016 is backwards compatible with level 1100 (SQL Server 2012) and level 1103 (SQL Server 2012 SP1 and SQL Server 2014), meaning you can easily deploy your existing tabular model to SQL Server 2016, either through backup and restore, or through Visual Studio. Once deployed, tabular models in SQL Server 2016 should generally have much better performance, both when processing and querying data. However, it is still recommended to thoroughly test your model, before upgrading your production servers to 2016.
In order to benefit from many of the new features of Analysis Services in SQL Server 2016, you will need to upgrade your model to Compatibility Level 1200. These features include bi-directional filtering, calculated tables, native translations and display folders, better and less glitchy IDE, the new TMSL json-based scripting language, the TOM (Tabular Object Model) and more. Note however, that you need Visual Studio 2015 for working with models at this level.
Migrating to Compatibility Level 1200
To migrate a model to Compatibility Level 1200, simply open the model in Visual Studio 2015, and change the Compatibility Level in the properties pane. You will then be prompted for the upgrade. Make sure you have a backup copy of your Visual Studio project files before hitting “Yes”.
After the project has been reopened, the model should be at Compatibility Level 1200, meaning that you now have access to the new features mentioned above.
Warning: If you have used BIDSHelper to add translations and/or display folders to your SQL Server 2012 or 2014 Tabular Model, these will NOT have been migrated. In other words, all translations and display folders are lost when upgrading to Compatibility Level 1200. The same goes for other BIDSHelper-enabled features such as Actions and HideMemberIf.
Since translations and display folders are now natively supported in SQL Server 2016, we need a way to migrate these from our SQL Server 2012 and 2014 models. That is indeed the scope of this article. As of this writing, BIDSHelper does not support SQL Server 2016. This also means that Actions and HideMemberIf cannot be used with Compatibility Level 1200. While the HideMemberIf-functionality can be replicated using DAX as described here, no such workaround exists for Actions.
Why was my Translations and Display Folders lost?
As mentioned above, BIDSHelper does not support SQL Server 2016. In SQL Server 2012 and 2014, BIDSHelper uses annotations to store the metadata for translations and display folders in the Model.bim file. When deploying, BIDSHelper generates and adds XMLA code to the various dimensions, attributes and measures, for the translations and display folders to show up. This works, because in SQL Server 2012 and 2014, the XMLA structure of tabular models is identical to that of multidimensional models, so adding an XMLA tag to a measure-node, will work just as it does in multidimensional, and most MDX and DAX clients will support it.
However, with Compatibility Level 1200, the tabular model is no longer scripted as XMLA. Instead, models are stored using the JSON-based TMSL language. You can see this by viewing the Model.bim file as code, after upgrading to level 1200.
During the upgrade to level 1200, Visual Studio does not pay any special attention to the XMLA annotations created by BIDSHelper, which is why the translations and display folders are nowhere to be found after upgrading. Surely, they can be manually added to the model after upgrading, but for any mid- to large-sized tabular models, you could have 100s or even 1000s of objects to go through. For Display Folders, the UI is not bad. Simply click the object (column, hierarchy or measure) that you want to add a Display Folder to, and enter the folder name in the properties pane (use backslashes to create subfolders as usual):
Unfortunately, Microsoft did not provide a UI for editing translations. Instead, you use the “Translations” > “Manage Translations” option under the “Model” menu, to export a JSON-file, which can then be edited in any text editor, to add translated names for objects, their descriptions and their display folders. When done, you import the JSON file through the “Translations” > “Import Translations” menu option.
Kasper de Jonge has created a very helpful application that lets you edit the translation JSON-files through an UI similar to what we previously had in BIDSHelper. But the workflow is still a matter of going through objects one-by-one and applying the translations manually.
So is there an easier way to set the Display Folders and Translations of many objects at once, instead of manually working through tables, columns, hierarchies and measures one-by-one? Turns out the answer is yes.
Working with the Tabular Object Model (TOM)
Thanks to the new Tabular Object Model, Microsoft has made it very easy to script changes directly to a deployed model. This can be done through TMSL (which is executed in an XMLA query window in SQL Server Management Studio) or directly through PowerShell, C#.NET or VB.NET. The Tabular Object Model makes it easy to access objects and their properties. Contrary to the old XMLA based metadata, the TOM is structured in a way that makes sense for Tabular models. Anyone working with Tabular models should find the object model hierarchy straightforward to work with. All objects in the hierarchy has accessible properties (“Name”, “DisplayFolder”, “Description”, etc.) similar to the ones available in the Properties Pane in Visual Studio.
More details on the Tabular Object Model hierarchy can be found here.
Setting Display Folders using PowerShell
Using PowerShell, for example, we can set the Display Folder of a measure and an attribute using the below code:
The DisplayFolder property is available on the following objects within the model hierarchy:
In addition to the DisplayFolder, these objects support a range of other properties. Basic documentation of these objects and their properties can be found here.
Note, that in order for this code to work in PowerShell, you must have a local instance of Analysis Services installed, in which case you can execute the code from within SQLToolsPS.exe located here:
C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn
Alternatively, if you do not wish to execute the code from a machine running Analysis Services, you must install the necessary PowerShell components according to this article.
Setting Translations using PowerShell
For translations, it gets a little more complicated, since a translation is not simply a property available on an object. Instead, at the root level of the Model hierarchy, there is a collection of Cultures. Each Culture object represents a translation of the Tabular Model into one specific locale. Within the culture, you specify the individual ObjectTranslations, one for each object (table, column, measure) and property (caption, description, display folder) that you wish to translate.
To add ObjectTranslations to a culture, use the following PowerShell code:
The example above assumes that your model already contains a Danish culture (da-DK). Cultures are added through the “Model” > “Translations” > “Manage translations” UI in Visual Studio, as shown in Figure 3. Alternatively, you can use the following code to create the Culture directly from PowerShell, if it does not already exist in the model:
In the example above, we create a translation for the caption and the display folder of the “Postal Code” column in the “Customers” table. The translated caption will show up instead of the name in client tools, whenever possible. However, MDX and DAX queries will still use the original name, just like in Analysis Services multidimensional. These are the properties that can be translated:
- Caption (Property = 1)
- Description (Property = 2)
- Display Folder (Property = 3)
It is worth noting that if you do not provide a translated value for the Display Folder, the object will show up without a folder. In other words, the translation will not reuse the original Display Folder property that was set on the object itself.
You can apply ObjectTranslations to the following objects:
- Hierarchy Levels
Getting the changes into Visual Studio
Using the examples above, you can now write a complete PowerShell script to set up all Display Folders and Translations in your model. Once you understand how the code works, this approach will be easier and less time-consuming than going through the UI to update Display Folders one-by-one, or editing the JSON-files generated by the “Manage Translations” window.
One downside to this approach, however, is that you can only alter the Tabular Object Model of a database that is already deployed. Admittedly, I have not tried connecting to the TOM of a workspace database, but my guess is that it will not work, since Visual Studio will re-apply the (non-existing) display folders or translations to the workspace database, when you close and re-open the Model.bim file.
Instead, once you have applied all your display folders and translations in a deployed database, you can use the “Import from Server (Tabular)” option to create a new project in Visual Studio, which will include all display folders and translations that were set up directly in the TOM.
If you are only interested in applying the translations to an existing project, another option is to script out the TMSL of the translations after applying the changes in the TOM, save the script as a .json file and then import it into the model via the Visual Studio UI. This is a topic for a future blog post.
Generating the PowerShell script from BIDSHelper annotations
Instead of hand-writing the entire PowerShell script necessary to migrate all display folders and translations from your SQL Server 2012 or 2014 model, it would be far better if there was some way to automatically extract the metadata from the old model, using it to generate the PowerShell script for us.
Last year, I blogged about extracting metadata from tabular models using PowerQuery for documentation purposes. In this post, I showed how we can use PowerQuery (aka. “Get & Transform” in Excel 2016) to access relevant XMLA nodes inside the Model.bim file, to produce lists of calculated columns, measures and their DAX expressions. The exact same technique can be used to access the BIDSHelper annotations in order to obtain lists of Display Folders and Translations. These lists can then be used to generate the PowerShell script for updating the SQL Server 2016 model as showed above.
In the next blog post of this series, we will discuss this technique in detail. For now, feel free to download the Excel-sheet below, which contains a set of PowerQuery queries that will generate the PowerShell scripts for you.
Automated migration process
The process of migrating Display Folders and Translations from a SQL Server 2012 or 2014 model, to SQL Server 2016 Compatibility Level 1200 would then be the following:
- Take a backup copy of your original SQL Server 2012 or 2014 Tabular project files.
- Open your SSAS project in Visual Studio 2015.
- Set the compatibility level to 1200 to upgrade the model.
- Deploy the model – note that all translations and display folders are gone, as discussed above.
- Open the “BIDSHelper Migration.xlsx” Excel-sheet, fill out the information and follow the instructions in the sheet to generate a PowerShell script. Requires Excel 2013 or 2016.
- After executing the generated PowerShell script against your deployed model, all Display Folders and Translations should now be in place.
- Create a new project in Visual Studio 2015 – use the ”Import from Server (Tabular)” Analysis Services project type, and point it to import from the deployed model.
- Voila! You now have a SSAS 2016 tabular project with compatibility level 1200, containing all your translations and display folders.
We have seen how we can use PowerShell together with the Tabular Object Model, to apply properties and translations to a Tabular Model with Compatibility Level 1200. This technique can be used to recreate lost Display Folders and Translations which were available thanks to BIDSHelper in SQL Server 2012 or 2014, but which are not automatically migrated when upgrading a model to Compatibility Level 1200.
An Excel sheet that uses PowerQuery to generate the necessary PowerShell scripts has been provided. An upcoming blog post will describe the inner workings of this sheet.
If you have any questions or comments, feel free to discuss below.