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

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

    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.

  • RonKyle

    SSC-Dedicated

    Points: 31462

    I'm not positive, but a Brand dimension doesn't really work here. I've done survey data in a DW before. I had an answer dimension. Depending on how many questions and answers that are possible, you may have to have a separate answer dimension for each question or you may be able to combine the cartesian product of the answers into a single junk dimensions. As I've had no more than four questions, each with 10 possible answers, I've used the junk dimension approach so that all the answers are contained in a single dimension.

  • RonKyle

    SSC-Dedicated

    Points: 31462

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

    Agreed.

    Regarding the brand dimension, what you likely really need is an answer dimension, as indicated above. If the questions have a lot of possible answers, or if the cube is dedicated to surveys, you could have a separate dimension per answer. If there are a fixed number of questions and answers and surveys are just a part of the cube, you may want to use a "junk dimension." This junk dimension would either be the cartesian product of all the answers or have a mechanism to add a new combination of answers. Our surveys have four questions, each with 10 possible answers and the surveys are part of a larger cube. So I use a junk dimension so that all the answers are in one dimension.

  • 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

  • RonKyle

    SSC-Dedicated

    Points: 31462

    I'm not sure my posts are getting through. I can't see them. But on one resubmission, I received a warning saying I'd already posted one with the same text. I also can't find an email so that I could let something know something is wrong.

    As for the question about merging the fact tables, I have already said that if the number of questions and answer range isn't too large, then create a dimension that is the cartesian product of all the possible answers. That way a single dimension has all the results.

    Fact tables must reflect levels of granularity (an inviolable design rule for OLAP databases) and can't simply be merged.

    (I'd be grateful for some confirmation that this has been posted--Thanks,)

  • RonKyle

    SSC-Dedicated

    Points: 31462

    test

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

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