Skip to main content
pdf?stylesheet=default
Blackboard Help

Outcomes Submissions Star Schema

The Submissions star schema provides submission level information about Outcomes instrument submissions. Instrument types include Portfolios, Artifacts, Course Evaluations, Surveys and Offline Instruments. "Submission level" means that this star schema only contains data about the person who was supposed to submit the instrument and whether they actually submitted it. The contents of the actual submission are not provided by this star schema.

Diagram

Image illustrating associated text

View

ODS_SUBMISSIONS_FACT_VW contains one record for each potential submission for any deployment. The word potential is used to indicate that there will be a submission record for each entity (Survey, Portfolio, Artifact, and so on) that might come back as a result of a deployment. Some submission records will indicate that the submission has been received (will have a non-null received_date) and some records will indicate that the submission has not yet been received.

Submissions Facts
Column Description Used to filter ODS_SURVEY_ANSWER_FACT_VW records by
pk1 Unique Identifier.  
person_pk1 Identifies the ods_person_dim_vw record for the person that sent this submission. Personal attributes such as gender or zip code.
deployment_pk1 Identifies the ods_deployment_dim_vw record for the deployment that 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.
received_date The date this answer was submitted. Null indicates that this submission was never actually received. The system deployed an instrument but the receiver never submitted a response.  
instrument_pk1 Identifies the ods_instrument_dim_vw record of the instrument that this submission is for. Instrument attributes.
affiliation_pk1 Identifies the ods_affiliation_dim_vw record for the entity that the submitter was affiliated with that caused the Evaluation Instrument to be deployed 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 Evaluation Instrument deployed to them. Distribution List.
program_pk1 If the Course Evaluation or Survey was deployed to this submitter because of a Program Affiliation, this column identifies the ods_program_vw record for that 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 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 Section 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 Section. Educational Experience Section User Defined Attributes.

Sample Query

This query shows the total number of responses received and not received for a deployment. The counts are sorted by the Units, Courses, and Programs that the response was expected for.

SELECT sd.course_ee_title COURSE_TITLE,

               ( SELECT count(*)

                              FROM ods_submission_fact_vw isf

                                             INNER JOIN ods_section_dim_vw isd ON isd.pk1 =

isf.affiliation_pk1

                              WHERE isf.received_date is not null AND

                                             isf.deployment_pk1 = dd.pk1 AND

                                             isd.course_ee_pk1 = sd.course_ee_pk1

               ) RESPONSES_RECEIVED,

               ( SELECT count(*)

                              FROM ods_submission_fact_vw isf

                                             INNER JOIN ods_section_dim_vw isd ON isd.pk1 =

isf.affiliation_pk1

               WHERE isf.received_date is null AND

                              isf.deployment_pk1 = dd.pk1 AND

                              isd.course_ee_pk1 = sd.course_ee_pk1

               ) RESPONSES_NOT_RECEIVED

FROM ods_deployment_dim_vw dd

               INNER JOIN ods_submission_fact_vw sf ON dd.pk1 = sf.deployment_pk1

               INNER JOIN ods_section_dim_vw sd ON sf.affiliation_pk1 = sd.pk1

WHERE dd.pk1 = 2

ORDER BY sd.course_ee_title