engage_AssignmentSubmissionFact View

Purpose

Describes a student's Submission for an Assignment created in a  Learning Management System (LMS)

SQL Object Name

analytics.engage_AssignmentSubmissionFact

Usage Notes

None

Data Definition and Sources

Depends on the lmsx.Assignment and lmsx.AssignmentSubmission tables that are introduced by the LMSX extension from the LMS Toolkit. In theory this table can be populated directly through API calls, if the LMSX extension is called. However, in most cases the data will be loaded using the custom components in the LMS Toolkit. When using the Toolkit, recall the workflow:

1. Extractors pull data from a source system into CSV files.
2. The Data Store Loader uploads those CSV files into tables in the lms schema
3. The Harmonizer synchronizes those records with Sections and Students defined in the core Ed-Fi tables, resulting in records loaded into lmsx.Assignment  and lmsx.AssignmentSubmission.

In the table below, the immediate source is given, along with the source from the LMS Toolkit table / CSV files. For more information on the upstream sources from the LMS, please see the mapping notes documentation: Schoology Mapping Notes, Google Classroom Mapping notes, and Canvas Mapping Notes.

Column	Data Type	Source	Description
AssignmentSubmissionKey	varchar	lmx.AssignmentSubmission.AssignmentSubmissionIdentifier → When using the LMS Toolkit, this value comes from SourceSystemIdentifier in lms.AssignmentSubmission.	This is the natural key from the source system
AssignmentKey	varchar	lmsx.Assignment.AssignmentIdentifier → This is the Assignment's natural key from the source system. When using the LMS Toolkit, this value comes from SourceSystemIdentifier in lms.Assignment.	Unique key for the related assignment (see engage_AssignmentDim View)
SchoolKey	int	lmsx.Assignment.SchoolId → When using the LMS Toolkit, this value is inferred from the relationship of the SisSectionIdentifier in the lms.LMSSection  table to SectionIdentifier  in the edfi.Section table.	Unique key for the related school
StudentSchoolKey	varchar	Concatenation of edfi.Student.UniqueId and SchoolId from lmsx.Assignment. * See notes above and below for LMS Toolkit sources	Unique key for a student-in-a-school
StudentKey	varchar	edfi.Student.UniqueId * When using the LMS Toolkit, this value originally comes from the SISUserIdentifier  value in lms.AssingnmentSubmission.	Unique key for a student
SubmissionDateKey	varchar	lmsx.AssignmentSubmission.SubmissionDateTime formatted as YYYYMMDD When using the LMS Toolkit, this value comes from SubmissionDateTime  in lms.AssignmentSubmission. If the assignment is Past Due, then this value will be null.	Date key for joining to the DateDim View.
EarnedPoints	int	lmsx.AssignmentSubmission.EarnedPoints When using the LMS Toolkit, this value comes from EarnedPOints  in lms.AssignmentSubmission. If the assignment is Past Due, then this value will be null.	Number of points earned by the student on the submission
NumericGrade	int	lmsx.AssignmentSubmission.EarnedPoints / lmsx.Assignment.MaxPoints When using the LMS Toolkit, see description above for EarnedPoints. The MaxPoints value comes from lms.Assignment. If the assignment is Past Due, then this value will be null.	The numeric percentage grade
LetterGrade	varchar	lmsx.AssignmentSubmission.LetterGrade When using the LMS Toolkit, this value comes from LetterGrade  in lms.AssignmentSubmission. If the assignment is Past Due, then this value will be null.	The letter grade given on the submission
IsPastDue	bool	1 if the lmsx.SubmissionStatusDescriptor maps to descriptor constant "SubmissionStatus.IsPastDue", else 0. * When using the LMS Toolkit, this value is inferred from the SubmissionStatus  in lms.AssignmentSubmission.	Boolean indicating if the submission is past due (missing)
SubmittedLate	bool	1 if the lmsx.SubmissionStatusDescriptor maps to descriptor constant "SubmissionStatus.SubmittedLate", else 0. * When using the LMS Toolkit, this value is inferred from the SubmissionStatus  in lms.AssignmentSubmission.	Boolean indicating if the submission was turned in after the due date
SubmittedOnTime	bool	1 if the lmsx.SubmissionStatusDescriptor maps to descriptor constant "SubmissionStatus.SubmittedOnTime", else 0. * When using the LMS Toolkit, this value is inferred from the SubmissionStatus  in lms.AssignmentSubmission.	Boolean indicating if the submission was turned in before the due date
LastModifiedDate	datetime	lmsx.AssignmentSubmission.LastModifiedDate * When using the LMS Toolkit, this value comes from LastModifiedDate  in lms.AssignmentSubmission.	The last modified date for the submission