on 05-03-2018 5:41 PM
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:
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.
What would be the best practice to create Webi documents so that analysis and reporting can be done more efficiently?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.