PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

A few more about translation of cube dimensions

Translations are a very usefull feature in multidimensional cubes. We can translate not only the cube structure, but also the data. Of course we need to have the data already translated in our table fields. AdventureWorksDW has a dimension table named dimProducts that has EnglishProductName, SpanishProductName and FrenchProductName, so it’s easy to configure translation, so each user will see the product names in their own language.

In the Dimension Editor, we add a new translation language and configure caption for the attributes that we would like to translate, as in the image bellow.

NewLanguage.png

DataTranslation.png

However, some product names aren’t translated and for some products SpanishProductName is null. In these cases the best would be to show the EnglishProductName to the user, but the tranlation feature hasn’t this option. The user will see ‘null’ instead the product name, as you can see bellow.

NullProducts.png

There is an easy solution for this: We can create a calculated member in data source view to check if SpanishProductName is ‘null’ and, if so, use the EnglishProductName. Let’s call this new member as ‘SpanishTranslatedProductName’. The formula will be this:

Isnull(SpanishProductName,EnglishProductName)

CalculatedMember.png

Now we just need to use SpanishTranslatedProductName , now if the product doesn’t have a translation the user will see the EnglishProductName. This is very good to allow the translation team to work unhurried while the users can see their work in progress.

SpanishTranslatedProductName.png

TranslatedProductName.png

However, for AdventureworksDW sample database the formula needs to be different. This happens because SpanishProductName field doesn’t allow null values. The empty values that we see are in fact empty strings, so the formula will be the following:

Case SpanishProductName

When ” then EnglishProductName

Else SpanishProductName

End