# Fact table for comparing survey responses (with relative weight)

3 posters

Page

**1**of**1**## 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.

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

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

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)

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.

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

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

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

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

**Mj1978**- Posts : 8

Join date : 2010-03-10

Similar topics

» How to create fact table with measures derived from comparing two fact table rows

» Volume and Weight in Same Fact Table

» Survey-Answer fact table design

» Wide and large Dimension or Survey Factless Fact Table

» Relative Rank with Varying Dimensionality - Fact or MDX?

» Volume and Weight in Same Fact Table

» Survey-Answer fact table design

» Wide and large Dimension or Survey Factless Fact Table

» Relative Rank with Varying Dimensionality - Fact or MDX?

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum