The Survey star schema provides information about Enterprise Surveys and their submissions.


ODS_SURVEY_ANSWER_FACT

ODS_SURVEY_ANSWER_FACT contains a row for each answer (response) to any question in an enterprise survey. This is a fact view without an actual fact except in the case where the question is open-ended. In that case, this record contains the actual answer in its answer_text column. Otherwise, the answer is indicated by answer_pk1 which is a foreign key to ods-anser_dim.

Fact table
Column Description Used to filter ODS_SURVEY_ANSWER_FACT records by
pk1 Unique Identifier.  
person_pk1 Identifies the person who submitted this answer. Personal attributes such as gender or zip code.
question_pk1 Identifies the question that this response is answering. The survey, course evaluation or question that this response is answering.
submission_pk1 Identifies the ods_submission_fact record for the submission that contained this answer. In cases where a single student responds twice to the same deployment, the two sets of answers can be grouped by their response_pk1 values.
answer_pk1 Identifies the answer that the submitter has chosen. This value will be null for answers to open ended questions. The answer that the submitter selected.
deployment_pk1 Identifies the deployment which caused the Survey or Course Evaluation being answered to be sent to the submitter. Deployment, or by the instrument being deployed. Only one instrument can be deployed at a time.
answer_text If this answer is responding to an open ended question, this is the text of the answer.  
distr_list_pk1 Identifies the distribution list that caused this submitter to have this Survey or Course Evaluation deployed to them. Distribution List.
received_date The date this answer was submitted.  
crsmain_pk1 Identifies the ods_ls_course record of the course that the submitter was enrolled in. Only populated if the survey was sent to courses.  
crsmain_batch_uid The batch_uid of the ods_ls_course record of the course that the submitter was enrolled in. Only populated if the survey was sent to courses.  
distr_list_pk1 No longer used.  

ODS_ANSWER_DIM

ODS_ANSWER_DIM contains a row for each possible answer to any survey question. For matrix questions this view will actually contain a row for each possible answer to any of the rows of the matrix.

Fact table
Column Description
pk1 Unique Identifier.
name The text of this answer.
display_order The zero-based index of the position in which this answer is displayed.
points The point value assigned to this answer.
question_pk1 Identifies the question that this answer applies to.
score_as_percent_of_range The score for this answer as a percentage of the range between the min and max values for this question.

ODS_QUESTION_TAG

ODS_QUESTION_TAG maps tags to ods_question_dim records.

Fact table
Column Description
tag_value The string displayed for the tag.
question_pk1 Identifies the question that has been tagged with tag_value.

ODS_QUESTION_DIM

ODS_QUESTION_TIM contains one record for each question in any survey or course evaluation. For matrix questions, this view contains one record for each row in the matrix and each of those view rows contains the matrix question's text in its super_question_desc column. The super_question_description column is only populated for view rows related to matrix questions.

Fact Table
Column Description
pk1 Unique Identifier.
question_desc The text that is displayed for this question. If this is a matrix question this is the test that is displayed to the left of a matrix row.
super_question_desc If this is a matrix question this is the text that is displayed for the matrix as a whole. If this is not a matrix question this column is null.
question_display_order The zero-based display order of this question.
question_type This question's type. M=Multiple Choice, L=Likert, X=Matrix, B=Boolean, O=Open Ended
question_type_label Can be used to retrieve the internationalized string for this question's type.
numbering_type Indicates how the answers for this question are numbered. N="1,2,3,4" L="a,b,c,d" A="A,B,C,D" I="I,II,III" R="i,ii,iii" X=none
survey_name The name of the survey or course evaluation that this question belongs to.
survey_desc The description of the survey or course evaluation that this question belongs to.
survey_type The type of the survey or course evaluation that this question belongs to. "S"=Survey,"C"=Course Evaluation.
super_question_pk1 Identifies the super question that this answer applies to.
survey_pk1 Identifies the survey or course evaluation that this question belongs to.

ODS_SUBMISSION_NODE

ODS_SUBMISSION_NODE contains a row for each node specified in the deployment that caused the survey to be sent to the person who submitted a particular survey response.

Fact table
Column Description
submission_pk1 Identifies a record in ods_submission_fact or ods_survey_answer_fact.
node_pk1 Identifies a record in ods_node.
node_batch_uid The batch_uid of the associated node.
deployment_response_pk1 No longer used.
deployment_pk1 Identifies a record in ods_deployment_dim.

ODS_SUBMISSION_ROLE

ODS_SUBMISSION_ROLE contains a row for each institutional role specified in the deployment that caused the survey to be sent to the person who submitted a particular survey response.

Fact table
Column Description
submission_pk1 Identifies a record in ods_submission_fact or ods_survey_answer_fact.
role_pk1 Identifies a record in ods_institution_role.
role_batch_uid The batch_uid of the associated role.
deployment_response_pk1 No longer used.
deployment_pk1 Identifies a record in ods_deployment_dim.

ODS_INSTITUTION_ROLE

ODS_INSTITUTION_ROLE contains a row for each institutional role in the system.

Fact Table
Column Description
pk1 Unique identifier.
role_name This role's name.

Sample query

This query selects all of the answers to non-open-ended questions for a specified Survey/ Course evaluation deployment. There can only be one Survey per deployment so it also covers only one Survey. The results are sorted by the Section that the submitter was affiliated with, the Section's owning Unit hierarchy, Course or Educational Experience, and finally the display order of the question.

select qd.question_desc,

       ad.name answer, ad.points

       per.gender

  from ods_survey_answer_fact saf

       INNER JOIN ods_question_dim qd ON saf.question_pk1 = qd.pk1

       INNER JOIN ods_answer_dim ad ON saf.answer_pk1 = ad.pk1

       LEFT OUTER JOIN ods_person_dim per ON saf.person_pk1 = per.pk1

 where saf.deployment_pk1 = 3 and

       qd.question_type <> 'O'

order by qd.question_display_order,

         ad.display_order