Design Fact Table for survey

  • Lidou123

    Hall of Fame

    Points: 3046

    Hi All
    I'm trying to design a model in PowerBi.
    I've developped a model with a fact table like this.

    1- SurveyFact as

    Respondant| Date     | Question                                | IdResponse  | Count 
    Frank          | 201801 | Where do you live  ?             |Germany   | 1
    Stephane    | 201801 | Where do you live  ?             |France   | 1
    Frank          | 201801 | What Brand is the best ?       |Coca    | 1
    Stephane   | 201801 | What Brand is the best ?        |Pepsi    | 1
    Frank         | 201801 |Does this Brand inspire quality to you ? Coca | Yes    | 1
    Frank         | 201801 |Does this Brand inspire quality to you ? Pepsi| No    | 1
    Stephane   | 201801 |Does this Brand inspire quality to you ? Coca | No    | 1
    Frank         | 201801 |Does this Brand inspire quality to you ? Pepsi| No    | 1
    Stephane   | 201801 |Does this Brand inspire quality to you ? Coca | No    | 1
    Frank         | 201801 |Does this Brand inspire quality to you ? Pepsi| No    | 1

    The difficulty I encounter is that I have to develop a report that is filtered based on the brand.

     Could I create a dimension Brand in this model ??

     

     Thank you for your help.

  • pietlinden

    SSC Guru

    Points: 62365

    https://www.daxpatterns.com/survey/

  • Lidou123

    Hall of Fame

    Points: 3046

    pietlinden - Friday, March 29, 2019 10:56 AM

    Thank you.
    I saw this pattern.
    But it doesn't solve my problem.
    Is it possible to create a Brand dimension ??

  • Chris Harshman

    SSC-Forever

    Points: 41661

    What dimensions to you already have?  If you are trying to model these tables for a generic survey with a variable number of questions it may be difficult to get the information back out of this table unless you define something that ties the answers together, unless you are only allowing a respondent to complete one survey per date period?  I'd think you'd want the following:
    Date Dimension
    Survey Dimension
    Respondent Dimension
    Question/Answer Dimension (I think these would be together since each question may only allow its own set of answers, but these could be 2 separate dimensions depending on the data and how it's used)
    Fact Table

    Otherwise this "Fact" table as you have it designed will be very wide with a lot of repeated text values.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Lidou123

    Hall of Fame

    Points: 3046

    Hello

    Thank you for your answer.

    Yes there is only a respondent to complete one survey per date period.

    I think this design is the best that I could have.

    But is there a way to add a brand dimension?

    Most questions are questions about the brand.

    And users have reports sliced by brand.

    Any idea ? A fact table like this:

    Date Dimension

    Survey Dimension

    Brand Dimension

    Respondent Dimension

    Question/Answer Dimension

  • Lidou123

    Hall of Fame

    Points: 3046

    Hello !!

    Any idea ??

    Is there a way to merge 3 fact table in one ?

    In my case I have these 3 Fact Table

    1- SurveyFact as

    IdDate | IdBrand | IdResponse/Question | Count

    201801 | Coca |Best | 1

    201801 | Cola |Worst | 1

    2- BrandFact as

    IdDate | IdBrand | Count

    201801 |Coca/MyBestDrink |1

    3- Response Fact as

    IdDate | IdResponse/Question | Count

    201801 |Paris/Town where I live | 1

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

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

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