Modelling multi language datawarehouse

• Lidou123

  Hall of Fame

  Points: 3046

  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?
  

• Lidou123

  Hall of Fame

  Points: 3046

  February 23, 2019 at 4:25 pm

  #2022747

  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

  SSChampion

  Points: 14053

  February 23, 2019 at 6:49 pm

  #2022750

  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: 182344

  February 24, 2019 at 7:27 am

  #2022761

  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.