Fact table for comparing survey responses (with relative weight)

Hi everyone,

This is my first post on this forum, I'm glad I've found it and I hope someone could help me.

Our problem is that we dont know how to design a fact table to store responses from a survey in order to compare then later. For example, we need to know how many people answered "Yes" to Question q1 and q2, how many answered "Yes" to q1 and "No" to q2 and so on.

Each person answers to a survey wich has a relative weight, so its not just a simple count but a calculation of answer*relative weight for each response.

Any ideas about how to model this? We've looked at the example of The Datawarehouse Toolkit (page 197) but its not what we are looking for.

Thanks in advance for your help.

If the Relative Weight is assigned to every question and is fixed for every question then you can have that Weight Recorded in the fact table
as a degenerate Dimension at every intersection of the Survey(Question) and Person who takes the survey. But if the Realtive weight depends upon the response then keep in the Question Dimension and calculate the answer*relative weight and then load it in the fact table.

You can record answers Y and N as 1 and 0 which will help you to evaluate how many persons said Y to a question by just adding the response field for a question. This response field also can be a part of the fact table since It always get generated by an intesection of Person and Question dimension.

One possible solution might look as below.

DIM_SURVEY
----------------------------------------------
SURVEY_KEY SURVEY_QUESTION_TXT REL_WT
---------- ------------------- ------
1 aaaaaaaaaaaaaaaaaa 0.2
2 bbbbbbbbbbbbbbbbbb 0.3
3 cccccccccccccccccc 0.1
4 dddddddddddddddddd 0.2

DIM_PERSON
----------------------------------------------
PERSON_KEY OTHER ATTRIBUTES
---------- -----------------
100
200
300
400


FCT_RESPONSE
-------------------------------------------------------------------
FCT_KEY PERSON_KEY SURVEY_KEY ANSWER(Y=1,N=0) ANS_REL_WT
-------- ---------- ---------- --------------- ----------
1 100 1 1 0.2 (1 * 0.2)
2 100 2 0 0 (0 * 0.3)
3 200 3 1 0.1 (1 * 0.1)
4 300 3 1 0.1 (1 * 0.1)

how many people answered "Yes" to Question q1 and q2,

SELECT SUM(ANSWER) FROM FCT_RESPONSE
WHERE SURVEY_KEY IN (1,2);

how many answered "Yes" to q1 and "No" to q2 and so on.

SELECT SURVEY_KEY , SUM(ANSWER)
FROM FCT_RESPONSE
SURVEY_KEY IN (1,2)
GROUP BY SURVEY_KEY;

Hope it helps.

Thanks
Manik