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