Building a language specific dwh

Hi, I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.

Productnr Productdesc Language
1            product       EN
1            produkt       DE

One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but then you have to always select a language in a filter (I think)..So that does not seems a elegant solution either.

I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).

Anyone better ideas?

Greetz,

Hennie

From a data storage point of view, the simplest thing to do is have an outrigger dimension table with a compound primary key consisting of the main dimension key and the language code. This outrigger would only contain text columns that can be in different languages.

The main and text tables would be joined, filtered on language code.

You could consider storing english text in the main table and other language data in the outrigger.

As far as user access goes, it is usually handled with views, either defined in a BI tool's metadata or as actual database views. You could establish different default databases for users to place them in a database with views that present the dimensions in their preferred language.

By having the language specific text in an outrigger it becomes easy enough to present multiple languages if desired.

Thank you for your information.

I've created for every language a specific language calendar dimension table .

In the view and with aid of some metadata, I combine the username, language information and the right calendar dimension table.

And this is works elegantly. Now when a german logs in,he sees the german words and when a English user logs in, he sees in same columns english words.