Data Model For Survey Data

[#1]

Hi Guys

I have to prepare a data model for survey data in which we have a excel file which contains all responses from user, question details and possible response values.

There are single response and multi response questions in survey

Data :

user_id	que_id	subque_id	response
u1	q1		1
u1	q2	1	0
u1	q2	2	1
u2	q1		2
u2	q2	1	0
u2	q2	2	1

Var_Info :

q_id	subque_id	desc	subdesc
q1		What is your Gender
q2	1	What is the age of children	2 years old or younger
q2	2	What is the age of children	Between 3 and 5 years old

Var_Values :

q_id	subque_id	var_value	value_detail
q1		1	Male
q1		2	Female
q2	1	0	Unchecked
q2	1	1	Checked
q2	2	0	Unchecked
q2	2	1	Checked

Now I want to show some statistics in terms of bar graph.

Requirement1: For q1 I want to create a bar graph showing count of users based on gender (gender on x axis and count on y axis).

Requirement2: For q2 I want to create second graph showing count based on subdesc (subdesc on x axis and count of users having response                          value 1 for every subdesc).

What could be best approach to design this in QV. This is only sample data for two questions.

It would be very helpful to get expert suggestions on this scenario.

//Tribhuwan

[narender123]

see the attachment.

Thanks.

Narender

[#3]

Thanks for your response.

I want to make generic as I have list of questions and some questions have more than 10 sub questions.

Its not only Gender that I could write in load script.

//Tribhuwan

[#4]

You should start thinking about your datamodel. Based on the info you give I'd suggest you create a fact table for all the surveys and users IDs (I'd recommend also storing dates).

You can then create dimension tables for the users and the surveys. Each question will have it's row with the value filled in.