A SQL database snapshot of all the records submitted in each confirmed SDR is created by ebs. Snapshots provide a method of backing up previously confirmed SDR submissions, so that users can identify changes in data when the SDR is resubmitted. A database management tool (for example: SQL Tools, SQL Server Management Studio and so on) can be used to report on snapshots, and delete historical snapshots.
The following snapshots are created:
- SDR_SNAPSHOT
- SDR_COURSE_COMPLETION_SNAPSHOT
- SDR_COURSE_ENROLMENTS_SNAPSHOT
- SDR_COURSE_REGISTER_SNAPSHOT
- SDR_QUAL_COMPLETION_SNAPSHOT
- SDR_STUDENTS_SNAPSHOT
The following information is stored by ebs against a snapshot:
- The return type (return_id)
- The date and time it was created (created_date)
- The username of the person who submitted the SDR files (created_by)
-
The status of the return (sdr_status) - can be E (extracted), or S (submitted)
The return status remains at E until submitted, and is then overwritten with a status of S.
The ID of the return can be used to search for specific snapshot file content. For example:
- select * from sdr_course_completion_snapshot where sdr_snapshot_id = ‘141’;
When a SDR is run and the snapshots created, the PARENT_ID field (shown in the above graphic) captures the unique identifier of an enrolment row as selected by the relevant SDR view.
The PARENT_ID field stores the following data in these snapshots:
- SDR_COURSE_COMPLETION_SNAPSHOT - the PEOPLE_UNITS.ID
- SDR_COURSE_ENROLMENTS_SNAPSHOT - the PEOPLE_UNITS.ID
- SDR_QUAL_COMPLETION_SNAPSHOT - the ATTAINMENTS.ATTAINMENT_CODE
The creation of SDR snapshots allows you to:
-
Update one or more of the records used to create the last confirmed set of SDR files - the contents of the snapshot do not change if the relevant submission has been confirmed
Snapshots are overwritten if a previously run submission for the same return type has not been confirmed or submitted.
- Generate another SDR for a return type that has already been confirmed - the system retains the old snapshot and generates a second one for this new run
- Confirm the second SDR - the system preserves the corresponding snapshot
Snapshots are retained indefinitely unless deleted manually using SQL commands.