Modelling multi language datawarehouse

  • Lidou123

    Hall of Fame

    Points: 3046

    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 1st question:
    Would it make more sense to create only one PowerBI model for all countries or 50 PowerBI reports?

    -> My 2nd 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

    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 1st question:
    Would it make more sense to create only one PowerBI model for all countries or 50 PowerBI reports?

    -> My 2nd 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

    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

    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 1st question:
    Would it make more sense to create only one PowerBI model for all countries or 50 PowerBI reports?

    -> My 2nd 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.

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

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