Separate business names from technical names using SSAS and Power BI

I have heard this question come up now and then where someone wants to separate the names and columns seen by users from the names and users used in SSAS and in the reports. The main benefit this has is that name changes will not result in broken reports.

Turns out there is a neat trick that will allow you to do this. The trick is for you to add a translation to the model in the same language as the culture of the model is set to. As you know at the time of writing Power BI doesn’t support translations (it is planned though: https://docs.microsoft.com/en-us/business-applications-release-notes/october18/intelligence-platform/power-bi-service/dataset-metadata-translations) but when you add a translation for the default language (again the same as the model) Power BI will pick it up automatically Smile.

Lets take a look. First you need SQL Server Tabular 2016 and up or Azure Analysis Services to support translations. Next I create a new model in SSDT and to make sure I check the culture by looking at the BIM file (using code view):

image

Now we need to add a translation for the same language. Unfortunately the tool doesn’t allow you to add the same language as the model (oops I was the PM who added this feature..) but it is easy to change later.  Close the bim file and double click the model again to open it in the visual editor and select manage translations under Model in the top toolbar. Now add any language, select the language and press export.

image

Now open the JSON file with your favorite editor:

image

And change the name to the same string as the culture of your model and add the translations.

image

Save the file and load it back in, go to Model, Translations, Load translation and select the JSON file again.

Now you see the translations is added:

image

and now when I connect to that model using Power BI I see the translated names:

image

If I now use them in a visual it shows the translated name:

image

If I now rename the field using translations again to something else the visual still works and now has the same name (after a refresh):

image

Nifty trick Smile

4 Replies to “Separate business names from technical names using SSAS and Power BI

  1. This is such an amazing feature as it will give so much flexibility. Too bad I didn’t see this article earlier. However, I have a question. The exported file need to be part of the same solution and needs to be included when promoting to other environments ? I guess I am looking for advice on best practices on where to store this translation json file.

    Best,
    SN

    1. You don’t have to store this translation file, the translations themselves will become part of the model after you commit them (you can see them in the bim file). The json file is just a convenience feature to make editing easier.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.