Marco RussoDISCLAIMER: Microsoft might not support the technique described in this article. Use it at your own risk!
When you define a data model in Power Pivot or Analysis Services Tabular, you expose all the physical names also as user interface elements. For this reason, a best practice is importing data from using views that already do the necessary renaming of all the table and columns so that the user interface present good, meaningful and complete names to the user. However, even the best-planned project might encounter the need of renaming one or more objects because of user requirements.
Sometime, this is the result of the addition of a new table or measure, which makes the name of an existing one the possible source of confusion. For example, consider a single Date table, used to identify the order date. In a following revision, you add a Ship Date table, and now it would be better to rename the existing “Date” name to “Order Date”. Nevertheless, you should consider the side effects of such a renaming:
- You should also rename all the references to the renamed object in all existing DAX formulas for calculated columns and measures. There is no refactoring option available in Power Pivot or Tabular.
- All external references to renamed objects will be no longer valid. For example, an existing Excel pivot table will remove measures and columns that was renamed since last refresh.
The ideal solution would be a decoupling layer between the physical structure of the data model used in DAX, and the names shown to the user, so that renaming a measure or a column would not require any change to existing DAX expressions and queries. In reality, we might use the “hidden” translation feature in Tabular as a way to obtain such a decoupling layer.
Translations in Analysis Services Tabular
The feature presented in this article is not available for Power Pivot. For this reason, you can use the following technique only in an Analysis Services Tabular model by now. However, consider that you can import a Power Pivot model in Tabular, and then expose the resulting renamed Tabular model to users, without having to touch any existing measure in the data model.
Analysis Service offers the ability to translate objects of a data model. This feature is designed for Multidimensional models, where you can rename also the content of an attribute, and not only metadata names (dimension, attribute, columns, hierarchies, and measures in a multidimensional model). This service is available also for Analysis Services Tabular, even if Visual Studio does not expose any user interface to handle it. Moreover, in Tabular, you can only rename the model’s metadata, not the content of columns. In other words, you can rename the table Products and the column Product Name, but you cannot rename each individual product name inside the table.
In order to access the translation feature in Tabular you need BIDS Helper. After installing it, you can create one translation for each language. If the client connecting to the model has a language available in the translations, it will see the translated names. If the language is not present in the translations available in the data model, the client will see the original names of the data model.
The “original” language is the language used to create the data model. This information is visible in the Language property of the data model.
You cannot modify this property in Visual Studio, but you can edit the Model.bim using the View Code feature.
The View Code feature displays a text window containing the native XML format of the data model. As you can see, the Language tag for English (United States) corresponds to the number 1033, and you can change it to any supported language.
You can create translations using BIDS Helper (see instructions here). For example, we renamed the DimCustomer table to Customer using the English (United Kingdom) language using the dialog box provided by BIDS Helper.
The following is the content of the Model.bim file in XML.
In order to avoid losing translation when Visual Studio regenerates part of the file because of changes in data model, BIDS Helper also create a copy of translations in an Annotations node.
As you see, a first approach to the renaming problem is to create a translation and use it as a way to connect all the users. You might create the model in one language that is not used by your users, and then create translations for the language code correspondent to the one of your users.
However, even if the user interface of BIDS Helper does not allow you to do that directly, you can create a translation that has the same locale settings of the original language. In order to do that, follow these steps:
- Create a translation with BIDS Helper and change the translation of at least one object (otherwise BIDS Helper will not create the new translation nodes in the XML file).
- Open the Model.bim file with View Code, and search in the XML file the tag correspondent to the new language you defined. For example, if you created English (United Kingdom), you have to look for the value 2057.
- Rename all the instances of the translated language to the “original” language, which is 1033 for English (United States):
2057
to
1033 - Close the XML file and reopen the model with the Visual Studio Editor. Now you will use the Tabular Translation Editor provided by BIDS Helper to rename table, column and measure names in your model.
This is an example of the user interface that you will see in the Tabular Translation Editor after making the changes above to the Model.bim file.
Renamed names will be used only in the user interface of Excel and Power View, and all the DAX and MDX queries will be created using the “original” names.
Please, consider the following limitations of the solution described:
- First and most important: I do not think that Microsoft may support this technique. Test and use it at your own risk!
- Power View does not automatically rename column names imported in a report, but the query produce the correct result, using the same underlying physical name.