January 29, 2009 at 6:49 am
I have a requirement to build a design to incorporate data from a Global census.
There are a series of questions to which responses are given by respondents in different countries.
The answers to these questions can be numeric (or monetory) as well as descriptive.
I have come up with a design with the following:
Dim Question (having a list of all questions)
Dim Respondent (the respondent list)
Dim Country (where that respondent belongs to)
Fact (Containing question Id, respondent id, country id and the answer itself)
The problem Iam facing is obvious.
I can only store rows in the fact relating to answers which are numeric (or monetory).
I cannot store descriptive answers as these cannot be aggregated.
Is there any way of solving this, or it is not feasible to build a star schema based on these requirements?
Thanks in advance.
Pramod
January 29, 2009 at 8:53 am
If you want to store the answer to the question, you are not building a datawarehouse. You are building a database that has question, respondent and answers. They are not dimension nor fact tables.
Think about what you want to build first!!!!
January 29, 2009 at 10:44 am
I have built this exact same schema for doing surveys. I would suggest denomalizing the survey question/answer and put these together. So you might have a question in the table more than once, but each time it will have the different answer for the question. That should solve your issue and provide you the ability to get at your results. You don't need to include the answer column in the fact table at all.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 30, 2009 at 3:10 am
With regards to the descriptive answers, I wonder if you could approach the problem slightly differently. Say for example a question (Q1) is "What constitutes a good response to a question posted on this forum?" The people analysing the answers to this are likely to be interested in how often certain themes are addressed. You could scan each answer for occurrences of "understanding", "thought", "intelligence", "post count" etc. Each of these quoted terms could be members in a DimResponseTerm, and each time an answer is found to use this term, a row is added to FactSurveyResponse.
Take this answer from respondent 1 (R1) : "I really like it when someone shows some understanding of my problem, puts some thought into it, and uses their intelligence to come up with a solution - rather than simply replying that I don't even know what I am trying to do and offering nothing, just to get their post count up." You could break that down into:
[font="Courier New"]
DimQuestion
id Code Question
1 Q1 What constitutes a good response to a question posted on this forum?
DimRespondent
id Code Respondent
1 R1 SomeGuy
DimResponseTerm
id Code Term
1 rt1 understanding
2 rt2 thought
3 rt3 intelligence
4 rt4 post count
FactSurveyResponse
QuestionId RespondentId ResponseTermId OccurrenceCount
1 1 1 1
1 1 2 1
1 1 3 1
1 1 4 1
[/font]
This could then go into a cube easy enough, and give some insight into emerging themes. A Drill Through report could quite easily be set up to show, say, all answers containing a particular Response Term.
A fair bit of work, yes, but nice to have something to keep yourself busy for a while.
Good luck.
January 30, 2009 at 3:42 am
Hi Kenno_Rules,
That was a brilliant suggestion.
Yes it does offer help to my cause, There are definitely answers to questions, which can be repetitive, like - good, excellent, very good etc.. Like in any other survey.
But there are still answers which are just text input, meaning the respondent can type in whatever he wants.
I'll look into this and get back....
Thanks Again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply