Outcomes Survey Star Schema
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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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
