Skip to Content
0

How to analyze survey data in Webi 4.2 with numerous records for each person?

May 03 at 04:41 PM

90

avatar image
K P

I need to analyze and report survey data. Each respondent provides responses to questions, such as, name, age, gender, birth date, state, etc. Collected data is available in three dimensions in Webi 4.2: Respondent (ID), Question, and Answer as shown in the screenshot below:

Survey Data Analysis Sample

As you would notice in the screenshot above, there are many rows for each respondent. It makes it difficult to provide simple analysis, such as average age of respondents or respondent gender distribution or ranking respondents by state etc. In reality, there are 100+ questions and thousands of respondents and years' worth of data to analyze and Webi queries max out the record limit and fetching only partial results.

I believe one row should represent one record and each column should represent one attribute of the data. I would like to have each question transposed to as a Dimension, as shown below, so that it would be easy to analyze the data.

Ideal Survey Data Structure

What would be the best practice to create Webi documents so that analysis and reporting can be done more efficiently?


10 |10000 characters needed characters left characters exceeded

Is my question too challenging for the experts? I am eagerly looking forward to a permanent solution so that I can do my development more efficiently. Please take a moment and advise your best suggestion whether at database level or at universe level or at Webi level.

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jyothirmayee A May 03 at 05:16 PM
0

Hi,

What is your data source for WebI?. How do you see the data results when run on the backend?.

can you post that resultset?.

Thanks,

Jothi

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Jothi,

Thanks for your reply. Data source is Vertica > Universe > Webi.

Here is the SQL:

SELECT
  wt_fact_survey_respondents.survey_respondent,
  com_survey_mapping.question_label,
  fact_survey_results.field_value
FROM
  wt_fact_survey_respondents INNER JOIN fact_survey_results ON (wt_fact_survey_respondents.survey_respondent=fact_survey_results.survey_respondent)
   INNER JOIN com_survey_mapping ON (fact_survey_results.field_name=com_survey_mapping.question_code)


0

Above is the output from the query.

surveydata.png (21.1 kB)
0

Jothi,

Do you have any comment? Let me know what you think per my responses to your questions.

Thanks,

KP

0
Ayman Salem May 03 at 07:27 PM
0

Hi,

You can do that with "cross table".

First, define a new variable (AnswerK) from the dimension "Answer" as a measure

Then insert Cross Table

....

I hope this gives you an idea of how you can do more

Ayman


Show 8 Share
10 |10000 characters needed characters left characters exceeded

Ayman,

Thanks for taking time in doing the exercise and replying with the helpful screenshots. Could you please tell me why we should use the variable as Measure? There are string values in the Answer, such as Name, State, Gender, DOB etc.

Thanks,

KP

0

In "simple" crosstabs, it does not matter what content is measured.

However, if you want to add a mathematical calculation, eg. Example: "average age", you must create a new variable "AvgAge" and convert "AnswerK" to the number in the formula ex: Average (ToNumber ([AnswerK])) and so on.

1

Thanks, Ayman. I have tried cross-tab but I ran into some limitations because of cross-tab structure. I don't think it's sustainable approach, but I agree that it is one of the approaches. Looking for more robust and full-proof solution.

0

Hi KP,

traditional approach would be creating detail variables for your Answer object.

Gender=[Answer] Where ([Question]="Gender")

=NoFilter([Answer] Where ([Question]="name"))

=Sum(ToNumber(NoFilter([Answer] Where ([Question]="sal")))) and so on..

Regards

Sateesh

output.png (37.3 kB)
1
K P
Sateesh Kumar Bukkisham

Sateesh,

Thanks for your reply. Yes, creating multiple detail variables for each question is an option but when there are 100+ questions, it becomes tedious process. Any other alternative possible?

0

KP,

I don't see any options apart from above , your requirement has to be deal with modeling on back end.

Thank you

Sateesh

0
K P
Sateesh Kumar Bukkisham

Sateesh,

Yes, that's exactly I was thinking. But where in backend? At universe level or at database level? May be at universe level this could be transposed as expected? I have provided SQL script and output above in response to Jothi, if you could take a look and advise accordingly.

Thank you,

KP

0

One of the problems I ran into is "partial results" fetched for query. The limit is 500,000 and will not be changed per BO Admins.

I just don't see any option that's full-proof :(

0