Add individual Score Metric types to Staging.AssessmentResult

[AEMJohnnyCunningham]

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Johnny Cunningham
Authoring Organization(s)
CEDS
Email Address
jcunningham@aemcorp.com
Use Case Title
Add Assessment Score Metric types to Staging.AssessmentResult

Use Case Overview
This is a complementary ticket to Ticket #43 as well as the changes to RDS.FactK12StudentAssessments in v11.

Pull Request Number(s) (If applicable)

Use Case Background
Adding the individual Score Metric types will simplify the loading of the Staging.AssessmentResult table by providing the individual Score Metric types as well as the Standard Error for each Assessment rather than having to load multiple rows per student per assessment.

Components Affected
Delete everything from the list below except the components affected:

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Add the individual Score Metric types and the standard error for each to Staging.AssessmentResult

• AssessmentResultScoreValueRawScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorRawScore (nvarchar(35), null)

• AssessmentResultScoreValueScaleScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorScaleScore (nvarchar(35), null)

• AssessmentResultScoreValuePercentile (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorPercentile (nvarchar(35), null)

• AssessmentResultScoreValueTScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorTScore (nvarchar(35), null)

• AssessmentResultScoreValueZScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorZScore (nvarchar(35), null)

• AssessmentResultScoreValueACTScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorACTScore (nvarchar(35), null)

• AssessmentResultScoreValueACTSuperScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorACTSuperScore (nvarchar(35), null)

• AssessmentResultScoreValueSATScore (nvarchar(35), null)

• AssessmentResultScoreValueStandardErrorSATScore (nvarchar(35), null)

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Update the Staging-to-RDS data migration scripts for Assessments to use the individual Score Metric values.