Differences between CANVAS_SUBMISSION_DIM and CANVAS_SUBMISSION_FACT
We ran into some weird data while parsing the canvas tables which we were hoping you can explain à
Issue - Student has an assignment record in CANVAS_SUMISSION_FACT but does not have a record in CANVAS_SUBMISSION_DIM
CanvasUserid - '242384501320919508' Course - PSY2012.791 Term – Spring 19 Assignmentid – 130000006309566 (Concept Check Quiz – 1.1)
Queries used for your reference
select * from [dbo].[CANVAS_SUBMISSION_DIM] where ASSIGNMENT_ID = '130000006309566' ---returns no rows for userid '242384501320919508'
select * from [dbo].[CANVAS_SUBMISSION_FACT] where ASSIGNMENT_ID = '130000006309566' --returns one row for the userid '242384501320919508'
Can you help us understand why this is happening? Most importantly why is there is no referential integrity between the dim and the fact tables? The DIM table is important as it had the submission date and time which is an important reporting metric. Please let me know if I can share more details of the issue.