Skip to main content
pdf?stylesheet=default
Blackboard Help

Outcomes Survey Star Schema

The Survey star schema provides information about Outcomes Surveys and Course Evaluations and their submissions.

Diagram

Image illustrating associated text

View

ODS_SURVEY_ANSWER_FACT_VW contains a row for each answer (response) to any question contained in an Outcomes Survey or Course evaluation. This is a fact view without an actual fact except in the case where the question is open-ended. In that case, this record will contain 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_vw.

Answer Facts
Column Description Used to filter ODS_SURVEY_ANSWER_FACT_VW 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_vw 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.  
affiliation_pk1 Identifies the ods_affiliation_dim_vw record for the entity that the submitter was affiliated with that caused them to have the Survey or Course Evaluation deployed to them in the first place. Affiliation attributes. If the entity that the person is affiliated with is a Section or Educational Experience Section, it is possible to filter on attributes of the Course or Educational Experience that owns the Section and up to 3 levels of attributes of the owning Place (Unit).
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.  
program_pk1 If the Course Evaluation or Survey was deployed to this submitter because of a Program Affiliation this column identifies the program. Program attributes or the attributes of the Unit that owns the program.
ou_pk1 If the Course Evaluation or Survey was deployed to this submitter because of a Unit affiliation, this column identifies the ods_ou_vw record for that Unit. Unit attributes.
course_section_pk1 If the Course Evaluation or Survey was deployed to this submitter because of a Course Section affiliation, this column identifies that Course Section. Course User Defined Attributes.
ee_section_pk1 If the Course Evaluation or Survey was deployed to this submitter because of an Educational Experience Section affiliation, this column identifies that Educational Experience Section. Educational Experience User Defined Attributes.

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,

(select title from ods_ou_vw ou where ou.pk1=sd.level_3_ou_pk1) level_3_ou,

(select title from ods_ou_vw ou where ou.pk1= sd.level_2_ou_pk1) level_2_ou,

               sd.level_1_ou_title level_1_ou,

               sd.course_ee_title, sd.section_title, sd.instructor_name,  

               ad.name answer, ad.points,

               per.gender;

from ods_survey_answer_fact_vw saf;

               INNER JOIN ods_section_dim_vw sd ON saf.affiliation_pk1 = sd.pk1;

               INNER JOIN ods_question_dim_vw qd ON saf.question_pk1 = qd.pk1;

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

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

where saf.deployment_pk1 = 3 and;

               qd.question_type <> 'O';

               order by qd.question_display_order,

               sd.level_3_ou_pk1,

               sd.level_2_ou_pk1,

               sd.level_1_ou_title,

               sd.course_ee_title,

               sd.section_title,

               ad.display_order