Migrating SSAS Tabular Models to SQL Server 2016: Translations and Display Folders

Dette er et blogindlæg i en serie af artikler om den nye Compatibility Level 1200 af SSAS Tabular Models i SQL Server 2016. I denne serie vil vi dele vores erfaringer og anbefalinger fra arbejdet med Compatibility Level 1200 i real-life scenarier.

Ønsker du at lære mere om Tabular Editor? Vi afholder kurser i Tabular Editor i København og Aarhus! The Master Wizard bag Tabular Editor, Daniel Otykier, vil undervise kurset. Læs mere om kurset og tilmeld dig her: https://www.kapacity.dk/kursus-boost-din-ssas-produktivitet-med-tabular-editor-del-l-basic/ 

Introduction

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”.

Figure 1. Migrating a SQL Server 2012 or 2014 model to SQL Server 2016 Compatibility Level 1200.

Figure 1. Migrating a SQL Server 2012 or 2014 model to SQL Server 2016 Compatibility Level 1200.

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):

Figure 2. Display Folders can now be edited directly in the Properties Pane.

Figure 2. Display Folders can now be edited directly in the Properties Pane.

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.

Figure 3. No UI for editing translations directly. Clicking “Export selected languages” generates a JSON-file that should be edited and then re-imported in the model to apply translations.

Figure 3. No UI for editing translations directly. Clicking “Export selected languages” generates a JSON-file that should be edited and then re-imported in the model to apply translations.

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.

Figure 4. The Tabular Object Model Hierarchy. In the PowerShell Code Example below, we are setting the DisplayFolder property of the “Sales Amount” measure and the “Postal Code” column within the “Sales” and “Customer” tables respectively.

Figure 4. The Tabular Object Model Hierarchy. In the PowerShell Code Example below, we are setting the DisplayFolder property of the “Sales Amount” measure and the “Postal Code” column within the “Sales” and “Customer” tables respectively.

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:

# Connect to model
$as = New-Object Microsoft.AnalysisServices.Server
$as.Connect(“localhost”)
$db = $as.Databases[“WorldWideImporters”]

# Set Display Folders
$db.Model.Tables[“Sales”].Measures[“Sales Amount”].DisplayFolder = ‘KPIs’
$db.Model.Tables[“Customers”].Columns[“Postal Code”].DisplayFolder = ‘Geography’

# Apply changes
$db.Model.SaveChanges()

The DisplayFolder property is available on the following objects within the model hierarchy:

Model.Tables[“<table name>”].Measures[“<measure name>”] Model.Tables[“<table name>”].Columns[“<column name>”] Model.Tables[“<table name>”].Hierarchies[“<hierarchy name>”]

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:

# Connect to model
$as = New-Object Microsoft.AnalysisServices.Server
$as.Connect(“localhost”)
$db = $as.Databases[“WorldWideImporters”]

# Get culture object
$c = $db.Model.Cultures[“da-DK”]

# Get object reference
$obj = $db.Model.Tables[“Customers”].Columns[“Postal Code”]

# Create ObjectTranslations for caption and display folder
$tcaption = New-Object Microsoft.AnalysisServices.Tabular.ObjectTranslation -Property { Object = $obj, Property = 1, Value = “Postnummer” }
$tfolder = New-Object Microsoft.AnalysisServices.Tabular.ObjectTranslation -Property { Object = $obj, Property = 3, Value = “Geografi” }

# Add ObjectTranslations to the culture
$c.ObjectTranslations.Add($tcaption)
$c.ObjectTranslations.Add($tfolder)

# Apply changes
$db.Model.SaveChanges()

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:

# Create translation culture if it does not exist:
$c = $db.Model.Cultures[“da-DK”] if (!$c) {
$c = New-Object Microsoft.AnalysisServices.Tabular.Culture -Property @{ Name = ‘da-DK’; }
$db.Model.Cultures.Add($c)
}

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:

  • Model
  • Tables
  • Columns
  • Measures
  • Hierachies
  • Hierarchy Levels
  • Perspectives
  • KPIs

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.

Figure 5. Visual Studio allows importing an existing Tabular Database as a new project.

Figure 5. Visual Studio allows importing an existing Tabular Database as a new project.

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:

  1. Take a backup copy of your original SQL Server 2012 or 2014 Tabular project files.
  2. Open your SSAS project in Visual Studio 2015.
  3. Set the compatibility level to 1200 to upgrade the model.
  4. Deploy the model – note that all translations and display folders are gone, as discussed above.
  5. 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.
  6. After executing the generated PowerShell script against your deployed model, all Display Folders and Translations should now be in place.
  7. 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.
  8. Voila! You now have a SSAS 2016 tabular project with compatibility level 1200, containing all your translations and display folders.

Summary

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.

Tilmeld dig Tabular Editor kursus

About the author

Daniel Otykier is a Senior Business Analytics Architect at Kapacity A/S. Daniel has 9 years of experience working with Microsoft SQL Server and the Microsoft BI technology stack. Before becoming a BI consultant, Daniel worked as a developer, using primarily C# and .NET.

do_blog

2018-09-24T15:20:26+00:0027. juli 2016|

5 Comments

  1. Freek 7. oktober 2016 af 14:52 - Svar

    Hi Daniel,

    I was very happy to see your solution to migrate the Display Folders from 2012/2014 to 2016 models.
    Unfortunately, I can’t get it to work. It complains about the PSCultures, Expression.Error: There weren’t enough elements in the enumeration to complete the operation.
    Details:
    Table

    Do you recognize this error? And I so, do you know a solution?

    I use Excel 2013

    • Daniel Otykier
      Daniel Otykier 10. oktober 2016 af 11:25 - Svar

      Hi Freek,
      This is because your model does not contain any BIDSHelper translations, which is unfortunately something the Excel worksheet assumes.

      Try to change the code in the “PSCodeFinal” query (use the Advanced Editor) to something like this, removing all items related to translations:

      let
          Source =
              "# Connect to model#(lf)#(lf)" &
              "$as = New-Object Microsoft.AnalysisServices.Server#(lf)" &
              "$as.connect('" & ConnectionInfo[ServerName]{0} & "')#(lf)" &
              "$db = $as.databases['" & ConnectionInfo[DatabaseName]{0} & "']" &
              "#(lf)#(lf)# Display folders below#(lf)#(lf)" &
              Text.Combine(PSCodeDisplayFolders[PS],"#(lf)") &
              "#(lf)#(lf)# Save changes#(lf)" &
              "$db.Model.SaveChanges()#(lf)"
      in
          Source

      This should let you generate PowerShell code for migrating just the Display Folders.

      Best regards,
      Daniel

  2. Freek 12. oktober 2016 af 13:14 - Svar

    Hi Daniel,

    This indeed did the job. Thank you for the nice article and fast reply.

    Best regards,

    Freek

  3. Rashid 11. december 2017 af 16:38 - Svar

    Hi Daniel

    Thanks for this (and other) articles.
    We are experiencing an issue with the Compatibility level 1200.
    After migrating our tabular cubes to this new Compatibility level 1200, then Excel pivots are not working as expected.
    The issue is as follows, in Excel pivots you can choose a table in the Show Fields, and then in the below part of the pivot it shows all the related tables, for instance you can choose to show the fct_sales, and then it only shows all the tables/dimensions that are related to the fct_sales. (it was at least the way it worked previously).
    After upgrading to Compatibility level 1200, then it only shows the fct_sales in the below list of related tables.
    I hope it made sense, and I am not sure if you have experienced this issue at some customer ??

    br
    Rashid

    • Daniel Otykier
      Daniel Otykier 13. december 2017 af 09:28 - Svar

      Hi Rashid,

      Yes, I see the same behaviour in Compatibility Level 1200. I tried upgrading a model to Compatibility Level 1400, and then the “Show related fields” functionality in Excel works as expected again, so this is definitely a bug in 1200 (SQL Server 2016). I suggest you file an MS Connect report on this.

      Best regards,
      Daniel

Læg en kommentar