|
|
| (9 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | =Overview=
| |
| − | The Analysis Services OLAP engine in Tabular mode has some missing features compared to the Multidimensional mode. The Tabular engine is used in Analysis Services on-premises, Analysis Services Azure and Power BI (desktop + cloud)
| |
| | | | |
| − | Among the features that Tabular mode does not replicate are
| |
| − | * Member translations
| |
| − | ** This allows multilingual display of a report based on the user.
| |
| − | ** e.g. An English speaker would see "Bikes" and a French speaker wouuld see "Vélo".
| |
| − | * Member Keys, separate from the member Caption
| |
| − | ** This allows reports to be built with member selections maintained when a Caption changes.
| |
| − | ** If you update a Caption in tabular mode and have a report filter containing that member, then the selection will no longer work.
| |
| − | *** In Power BI the old caption will be used, and the report will contain no data.
| |
| − | *** In an Excel PivotTable the selection will be lost when the data is refreshed, and the default (All) member will be displayed instead.
| |
| − |
| |
| − | The Tabular Translations feature in XLCubed aims to address both of these issues allowing you to build a single multiligual report that will work correctly when member captions are updated.
| |
| − |
| |
| − | =Translations=
| |
| − |
| |
| − | We will start with a simple data model conatining just the Product, Date and Sales tables from Adevnture works (I have combined the Product tables in a database view so we have a star schema)
| |
| − |
| |
| − | [[Image:InitialModel.png|350px|centre]]
| |
| − |
| |
| − | As you can see the dimension tables have the names in several languages. Currently each language is a separate hierarchy. Our aim is to present the user with a single hierarchy which will display the correct translation in XLCubed automatically.
| |
| − |
| |
| − | To do this XLCubed uses a naming convention to chose the best translation to show the user. For example we have the Product Category Name which should be translated in reports.
| |
| − |
| |
| − | We will rename {{code|EnglishProductCategoryName}} to {{code|Product Category Name}}, and this will be the default translation if no better one can be found.
| |
| − | Next we rename {{code|SpanishProductCategoryName}} -> {{code|Product Category Name_es}} and {{code|FrenchProductCategoryName}} -> {{code|Product Category Name_fr}}. The extra two letters are the language code, these are standardised by ISO so it is simple to find the correct code for any langauge you need. [https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes]
| |
| − |
| |
| − | We do the same for the {{code|Product Subcategory Name}} and {{code|Product Name}}.
| |
| − |
| |
| − | If you do not want the users to access these columns you can hide them in the model, translations will still work correctly.
| |
| − | Alternatively, you can add translations to the column names so that the users are presented with friendly captions when using these columns and attribute hierarchies. You do this using the standard metadata translation tools for Analysis Services.
| |
| − |
| |
| − | With this done our model now looks like this:
| |
| − |
| |
| − | [[Image:ColumnTranslatedModel.png|350px|centre]]
| |
| − |
| |
| − | Now if I turn on "Tabular translations" in the XLCubed -> Manage -> Manage Aliases screen then I will see the translations. Here is a before and after picture, both grids are using the same hierarchy:
| |
| − |
| |
| − | [[Image:ColumnTranslatedGrid.png|350px|centre]]
| |
| − |
| |
| − | =Member Key and Caption=
| |
| − | 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 we will define a Key column and the Caption column it maps to. We will map to the base Caption column defined above, and any locale translations will also be applied.
| |
| − |
| |
| − | To do this will do some more column renaming so the user sees useful names when building a report. {{code|ProductCategoryKey}} -> {{code|Product Category}}, {{code|ProductSubcategoryKey}} -> {{code|Product Subcategory}} and {{code|ProductKey}} -> {{code|Product}}.
| |
| − |
| |
| − | Next we need to tell XLCubed 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 {{code|XL3ColumnTranslations}} and have columns {{code|TableName}}, {{code|KeyColumnName}} and {{code|CaptionColumnName}}.
| |
| − |
| |
| − | you can create this however you like, but for ease I will add a calculated table to the model:
| |
| − |
| |
| − | [[Image:TranslationTableModel.png|350px|centre]]
| |
| − |
| |
| − | And finally add a Hierarchy using the key columns:
| |
| − |
| |
| − | [[Image:TranslationHierarchyTableModel.png|350px|centre]]
| |
| − |
| |
| − | Now when I create a grid based on the keys the captions are retrieved by XLCubed and translated into my preferred language:
| |
| − |
| |
| − | [[Image:HierarchyTranslatedGrid.png|350px|centre]]
| |
