Outcomes Survey Star Schema

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

A diagram that shows the relationships between tables

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-answer_dim.

Table 85. 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.

Table 86. 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.

Table 87. 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_DIM 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.

Table 88. 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.

Table 89. 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.

Table 90. 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.

Table 91. 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