The Submissions star schema provides submission level information about instrument submissions. 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 aren't provided by this star schema.

ODS_SUBMISSIONS_FACT

ODS_SUBMISSIONS_FACT 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) that might come back as a result of a deployment. Some submission records will indicate that the submission was received (will have a non-null received_date) and some records will indicate that the submission wasn't received.

Fact table
Column Description Used to filter ODS_SUBMISSION_FACT records by:
pk1 Unique Identifier.  
person_pk1 Identifies the ods_person_dim record for the person that sent this submission. Personal attributes such as gender or zip code.
deployment_pk1 Identifies the ods_deployment_dim 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 record of the instrument that this submission is for.  
distr_list_pk1 Identifies the distribution list that caused this submitter to have this Evaluation Instrument deployed to them. Distribution List.

crsmain_pk1

 Identifies the ods_ls_course record that the submitter was enrolled in. Only populated if this survey was sent to courses.  

crsmain_batch_uid

 Identifies the ods_ls_course record that the submitter was enrolled in. Only populated if this survey was sent to courses.  

Sample query

This query shows each standard associated with each course and the contents aligned with each standard.

SELECT lsc.course_name COURSE_TITLE,
               ( SELECT count(*)
                      FROM ods_submission_fact isf
                      WHERE isf.received_date is not null AND
                               isf.deployment_pk1 = dd.pk1 AND
                               isf.crsmain_pk1 = 1sc.pk1
               ) RESPONSES_RECEIVED,
               ( SELECT count(*)
                      FROM ods_submission_fact isf
                     WHERE isf.received_date is null AND
                              isf.deployment_pk1 = dd.pk1 AND
                              isd.crsmain_pk1 = lsc.pk1
               ) RESPONSES_NOT_RECEIVED
  FROM ods_deployment_dim dd
               INNER JOIN ods_submission_fact sf ON dd.pk1 = sf.deployment_pk1
               INNER JOIN ods_ls_course lsc ON lsc.pk1 = sf.crsmain_pk1
 WHERE dd.pk1 = 2
ORDER BY lsc.course_name