March 29, 2019 at 10:39 am
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.
March 29, 2019 at 10:56 am
March 29, 2019 at 11:05 am
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 ??
March 29, 2019 at 12:11 pm
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.
April 1, 2019 at 1:00 pm
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.
April 1, 2019 at 2:17 pm
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.
April 1, 2019 at 10:53 pm
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
April 3, 2019 at 10:10 am
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
April 3, 2019 at 12:23 pm
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,)
April 3, 2019 at 12:23 pm
test
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply