Outcomes Rubric Star Schema
This schema provides results of instrument submission evaluations completed using rubrics.
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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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.
| 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
