Outcomes Rubric Star Schema

This schema provides results of instrument submission evaluations completed using rubrics.

A diagram showing the relationship between tables

ODS_RUBRIC_EVAL_CELL_FACT

ODS_RUBRIC_EVAL_CELL_FACT has one row in this view for each row selected in each rubric evaluation. Only rows belonging to evaluations of the most recent submissions are included (for a user-deployment combination). Rows belonging to self evaluations are filtered out.

Table 93. Fact table

Column	Description	Used to filter `ods_rubric_eval_cell_fact` records by:
`pk1`	Unique Identifier.
`person_pk1`	Identifies the person that submitted the instrument being evaluated.	Personal attributes such as gender or zip code.
`rubric_cell_pk1`	Identifies the `ods_rubric_cell_dim` record for the cell that was chosen as part of the published evaluation.	Rubric attributes.
`submission_pk1`	Identifies the `ods_submission_fact` record for the submission that the published Rubric is evaluating.	In cases where a single student responds twice to the same deployment and both are evaluated with the same Rubric, the two sets of `ods_rubric_eval_cell_fact` records can be grouped by their `submission_pk1` values.
`submission_recieved_date`	The date that the instrument being evaluated was submitted.
`deployment_pk1`	Identifies the deployment that caused the survey or course evaluation being answered to be sent to the submitter.	Deployment, or by the instrument being deployed. Only one instrument can be deployed at a time.
`rubric_eval_pk1`	Identifies the evaluation during which this cell was chosen.	Data about the evaluation such as when it was last saved.
`instrument_pk1`	Identifies the instrument that this submission is for.
`distr_list_pk1`	No longer used.
`points`	Max points defined in the rubric cell.
`submission_date`	The date this evaluation was submitted.

ODS_INSTRUMENT_RUBRIC

ODS_INSTRUMENT_RUBRIC contains a mapping of rubrics to the instruments whose responses the rubric can be used to evaluate.

Table 94. Fact table

Column	Description
`rubric_pk1`	Identifies the rubric that has an instrument mapped to it.
`instrument_pk1`	Identifies the instrument that is mapped to a rubric.
`rubric_title`	The title of the rubric that has an instrument mapped to it.
`rubric_points`	The maximum amount of points that could be awarded for this combination of rubric and instrument.
`curr_instr_version_pk1`	Identifies the current (i.e. "live") version of the instrument referred to by instrument_pk1.

ODS_RUBRIC_CELL_DIM

ODS_RUBRIC_CELL_DIM contains one record for each cell defined for any rubric. Also contains information about the row, column and rubric that this cell belongs to.

Table 95. Fact table

Column	Description
`pk1`	Unique Identifier.
`rubric_pk1`	Identifies the rubric that this cell belongs to.
`rubric_row_pk1`	Identifies the rubric row that this cell belongs to.
`rubric_column_pk1`	Identifies the rubric column that this cell belongs to.
`rubric_title`	The title of the rubric that this cell belongs to.
`rubric_desc`	The description of the rubric that this cell belongs to.
`rubric_row_header`	The displayed header of the rubric row that this cell belongs to.
`rubric_row_order`	The zero-based display order of the row that this cell belongs to.
`rubric_column_header`	The displayed header of the rubric column that this cell belongs to.
`rubric_column_order`	The zero-based display order of the column that this cell belongs to.
`points`	The points assigned to this cell.

ODS_RUBRIC_EVAL_DIM

ODS_RUBRIC_EVAL_DIM contains one row for each time any rubric is used to evaluate any instrument.

Table 96. Fact table

Column	Description
`pk1`	Unique Identifier. In the case of a collected rubric evaluation also need the instrument_pk1 to uniquely identify an evaluation since the same evaluation (pk1) can apply to multiple evidence sets (the instrument_pk1) if a single rubric evaluation was collected in multiple evidence sets.
`rubric_pk1`	Identifies the rubric used to do this evaluation.
`submission_date`	When this evaluation was last saved.
`deployment_pk1`	Identifies the deployment (if any) for which the submission being evaluated was received.
`total_value`	Total points awarded in this evaluation. This value is scaled to the instrument and reflects any overrides entered by the evaluator.
`instrument_pk1`	Identifies the instrument that the submission being evaluated corresponds to.
`item_received_date`	When the submission being evaluated was submitted.
`distr_list_pk1`	Identifies the distribution list (if any) that caused the instrument to be sent to the person whose submission is being evaluated.
`person_pk1`	Identifies the person that submitted the submission being evaluated. This value is null for non-outcomes instruments.

ODS_RUBRIC

ODS_RUBRIC contains one row for each rubric.

Table 97. Fact table

Column	Description
`pk1`	Identifies a rubric.
`rubric_type`	N=numeric, P=percentage, T=no points, R=point range. Set to N (numeric) for outcomes rubrics (where course_pk1 is null).

ODS_RUBRIC_ROW

ODS_RUBRIC_ROW contains one row for each rubric row.

Table 98. Fact table

Column	Description
`pk1`	Identifies a rubric row.
`header`	The header text for the rubric row.
`row_order`	The order of the rubric row.
`rubric_pk1`	The parent rubric that the rubric row is defined within.

ODS_RUBRIC_COLUMN

ODS_RUBRIC_COLUMN contains one row for each rubric column.

Table 99. Fact table

Column	Description
`pk1`	Identifies a rubric column.
`header`	The header text for the rubric column.
`column_order`	The order of the rubric column.
`rubric_pk1`	The parent rubric that the rubric column is defined within.

ODS_RUBRIC_ROW_MAX_POINTS

ODS_RUBRIC_ROW_MAX_POINTS contains one row for each rubric cell in a rubric that is associated with an instrument.

Table 100. Fact table

Column	Description
`rubric_row_pk1`	Identifies a rubric row.
`curr_instr_version_pk1`	The live version of the instrument that is associated with this rubric.
`row_max_points`	This is the maximum points defined for the rubric row when used for the instrument identified by curr_instrument_version_pk1.

ODS_RUBRIC_CELL_POINTS

ODS_RUBRIC_CELL_POINTS contains one row for each rubric cell in a rubric that is associated with an instrument.

Table 101. Fact table

Column	Description
`rubric_cell_pk1`	Identifies a rubric cell.
`curr_instr_version_pk1`	The live version of the instrument that is associated with this rubric.
`points`	This is the points defined for the rubric cell for percent and numeric rubrics, null for other rubric types. This value is scaled to the instrument.
`max_points`	This is the maximum points defined for the rubric cell for range rubrics, null for other rubric types. This value is scaled to the instrument.
`min_points`	This is the minimum points defined for the rubric cell for range rubrics, null for other rubric types. This value is scaled to the instrument.

Sample query

This query lists the results of a rubric that was used to evaluate the submissions for a single deployment of an instrument. Counts of how many evaluations chose each rubric cell are grouped by row and column and sorted by the gender of the submitter.

SELECT cell.rubric_row_header, cell.rubric_column_header, count(*)

  FROM ods_rubric_eval_cell_fact rf

       INNER JOIN ods_rubric_cell_dim cell ON cell.pk1 = rf.rubric_cell_pk1

       INNER JOIN ods_person_dim per ON per.pk1 = rf.person_pk1

 WHERE rf.deployment_pk1 = 1 AND

       cell.rubric_pk1 = 1

GROUP BY cell.rubric_row_header,

         cell.rubric_column_header,

         per.gender

ORDER BY cell.rubric_row_header,

         cell.rubric_column_header,

         per.gender