Modelling multi language datawarehouse

Question

Modelling multi language datawarehouse

Lidou123

Hall of Fame

Points: 3186
More actions
February 22, 2019 at 4:21 am

#379450

Hello
I need your help.

I work for a survey company and I am responsible for creating its architecture and modeling a data warehouse that analyzes the results of an international survey (50 countries)
For the architecture, we decided to create a tabular model in PowerBI to analyze our data and to create our reports.
Here below is the model as I thought:
However, I have a design problem.

Since the survey is international, the wording of my dimensions differs from country to country.

-> My 1^st question:
Would it make more sense to create only one PowerBI model for all countries or 50 PowerBI reports?

-> My 2^nd question:
My model must be multilingual
With my 50 countries, I have several languages (5 languages) and for the same language, I have several variants.
The British English labels differ from the US English labels.
For example, for the Response dimension for France the IdReponse = 1 has the wording 'Vrai' while for the USA the wording is 'True' and for the Britain is 'OK'

Do you know how to model multi language in a data warehouse?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lidou123 Hall of Fame Points: 3186 More actions · Answer 1

Lidou123 - Friday, February 22, 2019 4:21 AM
Hello
I need your help.

I work for a survey company and I am responsible for creating its architecture and modeling a data warehouse that analyzes the results of an international survey (50 countries)
For the architecture, we decided to create a tabular model in PowerBI to analyze our data and to create our reports.
Here below is the model as I thought:
However, I have a design problem.

Since the survey is international, the wording of my dimensions differs from country to country.

-> My 1^st question:
Would it make more sense to create only one PowerBI model for all countries or 50 PowerBI reports?

-> My 2^nd question:
My model must be multilingual
With my 50 countries, I have several languages (5 languages) and for the same language, I have several variants.
The British English labels differ from the US English labels.
For example, for the Response dimension for France the IdReponse = 1 has the wording 'Vrai' while for the USA the wording is 'True' and for the Britain is 'OK'

Do you know how to model multi language in a data warehouse?

Any Idea please ???

frederico_fonseca SSCoach Points: 16005 More actions · Answer 2

This is a lot more complex than what you think.
Look at one approach https://www.youtube.com/watch?v=JJryD2dXVVw- does have issues that I am not sure have been solved in the meantime.

Doing this just from an interface point of view is the harder part - on the above the publisher tried to address the issue with parameters - another reply to it mentioned using sliders - which I think may be what you need in order to allow it to work.
This is for PowerBI.
For other interfaces it would probably be a lot easier. - database side is the easy part as you basically have all "descriptions" on a table containing languageid, messageid, description text. All remaining tables containing description fields have nothing else than a FK to this table.

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 3

Lidou123 - Friday, February 22, 2019 4:21 AM
Hello
I need your help.

I work for a survey company and I am responsible for creating its architecture and modeling a data warehouse that analyzes the results of an international survey (50 countries)
For the architecture, we decided to create a tabular model in PowerBI to analyze our data and to create our reports.
Here below is the model as I thought:
However, I have a design problem.

Since the survey is international, the wording of my dimensions differs from country to country.

-> My 1^st question:
Would it make more sense to create only one PowerBI model for all countries or 50 PowerBI reports?

-> My 2^nd question:
My model must be multilingual
With my 50 countries, I have several languages (5 languages) and for the same language, I have several variants.
The British English labels differ from the US English labels.
For example, for the Response dimension for France the IdReponse = 1 has the wording 'Vrai' while for the USA the wording is 'True' and for the Britain is 'OK'

Do you know how to model multi language in a data warehouse?

This is doable, but the complexity of the solutions are directly related to the type and structure of the survey in question.
😎

If there is a direct correlation between each respond values, regardless of the language, then this can be done with a simple language dimension. If on the other hand, the wording of the answers implies a different meaning in different languages, you've got a problem.

My suggestion is to leave the analysis of the surveys separate and switch languages on the survey's UI and reporting.