It is common to want a fixed key for a member, so report selections are preserved when the caption is updated in the source data system.
To do this, you will define a Key column and the Caption column it maps to. The key column will map to the base Caption column and apply any locale translations.
To do this, more columns need to be renamed so the user sees useful names when building a report: ProductCategoryKey > Product Category, ProductSubcategoryKey > Product Subcategory, and ProductKey > Product.
Next, you need to tell Anaplan XL how to map the key to the caption when running a query. This is done by adding an extra table to the data model, which you should hide. The table must be called XL3ColumnTranslations and have columns TableName, KeyColumnName, and CaptionColumnName.
To reduce report administration and maintenance, you add a calculated table to the model:
Add a Hierarchy using the key columns:
When you create a grid based on the keys, the captions are retrieved by Anaplan XL and translated into your preferred language:
Because drills are linked to the member keys, the report won't change if the product captions are updated in the source system. The same would apply if you had placed the selections in the Headers area of the grid.
If I had used the attribute hierarchy for any key columns, for example, Product Category, the members would also have been translated from key to caption. You don't need to create a user hierarchy unless you need it in your model.
As the hierarchy is created using keys, the default is to sort the members by those keys. If you wish to sort the hierarchy members by the caption or a custom sort column, you should set Sort By Column in your model.
