Fact table for comparing survey responses (with relative weight)

View previous topic View next topic Go down

Fact table for comparing survey responses (with relative weight)

Post  equipoBI on Mon Mar 08, 2010 5:50 am

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.

equipoBI

Posts : 1
Join date : 2010-03-08

View user profile

Back to top Go down

Re: Fact table for comparing survey responses (with relative weight)

Post  nash on Fri Mar 12, 2010 6:02 pm

equipoBI wrote:
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.
Based on the info provided, it would appear you need following
1. a Person dimension (or an appropriate name for those surveyed)
1. a Question dimension (all question attributes, if question weight is functionally dependent on this, then it will be here too)
2. a Response dimension (could be a flag in the fact table if values are only yes/no)


equipoBI wrote: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.

Now the fact table will contain the 'Weight' measure as the fact. Weight being relative is unclear to me, I assume there must be a rule to derive relativity that is calculated in the ETL process. Weight will be asserted at the concatenation of Person, Question and Response dimensions.
Now a BI tool can do all the smarts with this data that you described as requirements. Let me know if there is more to it.

nash

Posts : 18
Join date : 2010-03-12

View user profile

Back to top Go down

Re: Fact table for comparing survey responses (with relative weight)

Post  Mj1978 on Wed Mar 17, 2010 11:58 am

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
avatar
Mj1978

Posts : 8
Join date : 2010-03-10

View user profile

Back to top Go down

Re: Fact table for comparing survey responses (with relative weight)

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