[Canvas Data] Historical Quiz Answer Data in Canvas Data 2

[DexterStrong]

Problem statement:

There are Answer ID's that exist in QUIZ_SUBMISSIONS:Submission Data that do not exist in QUIZ_QUESTIONS:Answers.

For example Question ID 1249, the following Answer ID's are missing from QUIZ_QUESTIONS:Answers 982 1320 1638 3590 7023 8927

I believe the root cause for this problem may be that the Question Type changed. Questions that were originally set up as multiple-choice/multiple answer were changed to multiple-choice/single answer and vice versa. When the Question Type changed, new Answer ID's were created, and it appears that only the current/latest Answer ID's exist in the Answers field in the QUIZ_QUESTION table (where the Answer Text is found). The problem is that we have historical transactions in the QUIZ_SUBMISSIONS table that use the older Answer ID's (from before the Question Type change) and we have no way of knowing what the Answer Text is for these older Answer ID's. When we join to the QUIZ_QUESTIONS table to get the Answer Text the join returns a null.

Proposed solution:

Store the Answer ID and Answer Text for all Answer ID's in QUIZ_SUBMISSIONS table in a dimension table

User role(s):

admin,instructor,observer