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.

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.

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)

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.

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