Survey-Answer fact table design
2 posters
Page 1 of 1
Survey-Answer fact table design
I am in the middle of warehousing a survery-question module where anwer is going to be the only measure (at this moment, which I can figure out).
The Dimensions are, SentDate, ReceiveDate (Both Date dimension) , SurveryInitiatedUser, SurveryVerifiedUser (Both Employee dimension), Customer and Questions . Question dimension is a broad dimension which is denormalised among SurveyType,QuestionSection (Demographics, Official, habitual, health etc..) , QuestionType (single value, multi select etc), AnswerType (Freetext, DropDown etc)
The fact is SurveryAnswer with Answer as the measure. Here the problem is some questions can have more than one answer, like 'Hobbies' from a checkbox or multi select dropdown.
Since I would like to design Answer as the measure , I feel like to avoid a 'Answer Group Key' bridge. And this bridge will make the fact with no 'measure'. Again, if such a bridge all Freetext answers will be added to the bridge which makes the AnswerBridge dim is as big as the Fact.
Can anybody suggest/discuss a better option
I have a degenerate dimension (DD) SurveryID which will group the records for each Survey.
EDIT: In OLTP these multiple answers will be different records, the table has the primary key QuestionAnswerID.
See post #3 in this thread for an update.
The Dimensions are, SentDate, ReceiveDate (Both Date dimension) , SurveryInitiatedUser, SurveryVerifiedUser (Both Employee dimension), Customer and Questions . Question dimension is a broad dimension which is denormalised among SurveyType,QuestionSection (Demographics, Official, habitual, health etc..) , QuestionType (single value, multi select etc), AnswerType (Freetext, DropDown etc)
The fact is SurveryAnswer with Answer as the measure. Here the problem is some questions can have more than one answer, like 'Hobbies' from a checkbox or multi select dropdown.
Since I would like to design Answer as the measure , I feel like to avoid a 'Answer Group Key' bridge. And this bridge will make the fact with no 'measure'. Again, if such a bridge all Freetext answers will be added to the bridge which makes the AnswerBridge dim is as big as the Fact.
Can anybody suggest/discuss a better option
I have a degenerate dimension (DD) SurveryID which will group the records for each Survey.
EDIT: In OLTP these multiple answers will be different records, the table has the primary key QuestionAnswerID.
See post #3 in this thread for an update.
Last edited by rens on Mon Jun 20, 2011 6:35 am; edited 2 times in total (Reason for editing : Edited to add more insight to the problem)
rens- Posts : 3
Join date : 2011-06-20
Re: Survey-Answer fact table design
Why would you have Answer as the measure? The measure would be count, and Answer a dimension.
Re: Survey-Answer fact table design
There are plenty of questions with Freetext answers like 'The last date you admitted in the hospital?' or 'Where did you meet your partner first time? Enen though these answers cannot help any decision making I need record all these, right?
Various other answers like 'Level of satisfaction in a scale of 1-7' etc can be a modelled as a QuestionAnswer dim (with reference to Question, a snowflake)
EDIT: On a second thought, I realized that Answer must be dimension for better drill-down and reporting. So an Answer dimension will be made. It is related to the DimQuestion by a foreignkey (QuestionKey) (Am I right?) . Yet again, with this model, how do I handle freetext answers?
Various other answers like 'Level of satisfaction in a scale of 1-7' etc can be a modelled as a QuestionAnswer dim (with reference to Question, a snowflake)
EDIT: On a second thought, I realized that Answer must be dimension for better drill-down and reporting. So an Answer dimension will be made. It is related to the DimQuestion by a foreignkey (QuestionKey) (Am I right?) . Yet again, with this model, how do I handle freetext answers?
Last edited by rens on Mon Jun 20, 2011 6:35 am; edited 1 time in total (Reason for editing : Adding more insights)
rens- Posts : 3
Join date : 2011-06-20
Similar topics
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Wide and large Dimension or Survey Factless Fact Table
» Fact table for comparing survey responses (with relative weight)
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact table design
» Wide and large Dimension or Survey Factless Fact Table
» Fact table for comparing survey responses (with relative weight)
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Fact table design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum