Survey-Answer fact table design

View previous topic View next topic Go down

Survey-Answer fact table design

Post  rens on Mon Jun 20, 2011 2:51 am

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.


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

View user profile

Back to top Go down

Re: Survey-Answer fact table design

Post  John Simon on Mon Jun 20, 2011 2:53 am

Why would you have Answer as the measure? The measure would be count, and Answer a dimension.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Survey-Answer fact table design

Post  rens on Mon Jun 20, 2011 2:58 am

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?


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

View user profile

Back to top Go down

Re: Survey-Answer fact table design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum