Assessment Data Export Samples
dw_course_hierarchy.csv
institution_id,institution_code,institution_name,institution_description,college_id,college_code,college_name,college_description,college_administrative,department_id,department_code,department_name,department_administrative,course_id,course_subject_code,course_course_number,course_code,course_name,course_description,course_version_uuid,course_lms_id,course_section_id,course_section_subject_code,course_section_course_number,course_section_code,course_section_name,course_section_section,course_section_lms_id
"1","aunew","AEFIS University","AEFIS University opened its doors in 2012 and is one of the country's top higher education institution. ","2","SoE","School of Education","School of Education","f","2","DTU","Department of Teacher Education","f","2","BUS","101","BUS 101","Fundamentals of Business","Fundamentals of Business","2121306D-F670-454B-BA14B46637A53B89",,"29","BUS","101","BUS 101 1","Fundamentals of Business","1","BUS.101.1.20182"
"1","aunew","AEFIS University","AEFIS University opened its doors in 2012 and is one of the country's top higher education institution. ","2","SoE","School of Education","School of Education","f","2","DTU","Department of Teacher Education","f","2","BUS","101","BUS 101","Fundamentals of Business","Fundamentals of Business","2121306D-F670-454B-BA14B46637A53B89",,"180","BUS","101","BUS 101 1","Fundamentals of Business","1","BUS.101.1.20171"
"1","aunew","AEFIS University","AEFIS University opened its doors in 2012 and is one of the country's top higher education institution. ","2","SoE","School of Education","School of Education","f","2","DTU","Department of Teacher Education","f","2","BUS","101","BUS 101","Fundamentals of Business","Fundamentals of Business","2121306D-F670-454B-BA14B46637A53B89",,"181","BUS","101","BUS 101 1","Fundamentals of Business","1","BUS.101.1.20172"
dw_fact_assessment_response.csv
assessment_detail_response_id,assessment_mapping_response_id,assessment_syllabus_id,course_section_id,program_map_id,term_id,user_id,program_id,program_version_uuid,program_term_id,response,expected_percentage,expected_level,performance_indicator_rubric_id,performance_indicator_rubric_name,performance_indicator_rubric_description,program_rubric_id,rubric_level,success
"2","10","6","69","2","4","17954","2","FA993982-6F04-49CC-BE3099E70BB15355","18","1","80","2","7","Exceeds Expectations","","12","3","t"
"3","11","6","69","3","4","17954","2","FA993982-6F04-49CC-BE3099E70BB15355","18","1","80","2","10","Exceeds Expectations","","12","3","t"
"4","12","6","69","4","4","17954","2","FA993982-6F04-49CC-BE3099E70BB15355","18","1","80","2","10","Exceeds Expectations","","12","3","t"
Assessment Data Export Table Details
-
dw_mapping_hierarchy: The dw_mapping_hierarchy model is a comprehensive tool that maps out the relationships between various educational elements such as courses, learning objectives, performance indicators, and accreditors. It provides a clear picture of how these elements interact and influence each other, which can be used to improve educational programs and strategies. The model also includes features that allow for the assessment of students and the tracking of their progress. It can reveal patterns such as common learning objectives across different courses or the impact of certain teaching methods on student outcomes.
Contains information about outcome mappings. There are four types of mappings;
-
course <-> outcome: Course to outcome mapping
-
course <-> PI: course to performance indicator mapping
-
CLO <-> outcome: course learning objective to outcome mapping
-
CLO <-> PI: course learning objective to performance indicator mapping
-
Depending on the value of the mapping_type column, related columns will contain values accordingly and non-related columns will be null.
-
dw_program_rubric_hierarchy: The dw_program_rubric_hierarchy model is a comprehensive tool that provides a detailed overview of educational programs. It includes information about courses, learning objectives, performance indicators, and assessment methods. This model can be used to track and analyze the effectiveness of different teaching methods, identify patterns in student outcomes, and make data-driven decisions to improve educational programs. It also includes features that allow for the mapping of course objectives to performance indicators, which can help in curriculum planning and development.
-
dw_assignment_hierarchy: The dw_assignment_hierarchy model is a tool that organizes and presents data about educational assignments. It includes details like assignment ID, name, description, maximum grade, status, grading type, and submission type. It also indicates whether the assignment is a group task, a quiz, or part of a Learning Management System (LMS). The model also links assignments to specific course sections, assignment templates, and learning objectives.
-
dw_assignment_rubric_hierarchy: The dw_assignment_rubric_hierarchy model is a tool that organizes information about assignments in an educational setting. It includes details about the assignment itself, the course it's part of, the grading rubric used, and whether it's a group or quiz assignment. This model can help educators and administrators track and analyze assignment data, such as grading trends and assignment types, to improve course design and student learning outcomes.
-
dw_deg_assessment_assignment: The dw_deg_assessment_assignment model is a tool that links assignments to specific courses and programs, and provides details like the calculation percent, success score, and rubric thresholds. This model can be used to track student progress, identify areas of improvement, and make data-driven decisions in curriculum planning.
-
dw_fact_assessment_response: Contains fact data about assessment results for courses linked to a program. This table is ONLY used for assessment data when courses are part of a program thus relates to curriculum mapping information.
-
dw_fact_assignment_rubric: The dw_fact_assignment_rubric model is a comprehensive tool that tracks and analyzes student assignment data. It combines information about assignments, their submissions, grades, and associated rubrics. It also distinguishes between regular assignments and quizzes. This model can be used to monitor student performance, identify grading trends, and evaluate the effectiveness of different assignments or rubrics. It can also help in identifying patterns like the correlation between submission dates and grades, or the impact of different rubric criteria on grades.
-
dw_fact_assignment_submission: The dw_fact_assignment_submission model is a tool that provides a comprehensive view of student assignment submissions across different educational institutions. It collects data such as assignment details, grades, submission status, and associated course and department information. This model can be used to track student performance, identify patterns in grades across different courses or departments, and monitor submission rates. It can also help in understanding the effectiveness of different assignments or courses, and in making data-driven decisions in curriculum planning and student support.
-
dw_assignment_rubric_outcome_hierarchy: The dw_assignment_rubric_outcome_hierarchy model is a comprehensive tool that organizes and presents data related to assignments in an educational setting. It includes details about assignments, such as their ID, name, description, maximum grade, status, and type. It also provides information about the course section, assignment template, rubric criteria, and quiz questions if applicable. This model can be used to track and analyze assignment, identify patterns in grading, and understand the relationship between different assignment elements. For example, it can help identify if certain types of assignments or grading rubrics are more effective in certain courses or terms.
-
dw_course_section_assessment_hierarchy: The dw_course_section_assessment_hierarchy model is a tool that organizes information about assessments in a course. It includes details like the assessment's name, description, start and end dates, and status. It also provides information about the course section and the last update of the assessment data. This model can be used to track the progress of assessments, identify active syllabuses, and monitor updates. It can be particularly useful for educational institutions to manage their course sections and assessments effectively.
-
dw_clo_rubric_hierarchy: The dw_clo_rubric_hierarchy model is a comprehensive tool that provides detailed information about various courses, their sections, and learning objectives. It includes data on course descriptions, rubrics, expected performance levels, and enrollment counts. This model can be used to analyze course, identify patterns in student enrollment, and set expectations for learning outcomes. It can also help in understanding the correlation between different courses and their learning objectives. This model is particularly useful for educational institutions for planning and improving their curriculum.
-
dw_clo_hierarchy: The dw_clo_hierarchy model is a comprehensive tool that provides detailed information about various courses, their sections, and learning objectives. It includes data such as course names, descriptions, term details, and enrollment counts. The model also provides insights into the expected performance levels and percentages for each course. It identifies whether a course's learning objectives are linked, inherited, or main inherited. This model can be used to analyze and improve course structures, learning objectives, and student performance expectations.
-
dw_fact_course_assessment: The dw_fact_course_assessment model is a tool that helps to analyze student performance in various courses. It collects data on course assessments, including the expected and actual performance levels. This model can be used to identify patterns in student success, such as which courses have the highest success rates or where students tend to struggle. It can also help to identify if there are any correlations between different factors, such as the course, department, or institution, and student success. This information can be used to improve course design and teaching strategies.
Sample Assessment Result Query
with fact as (
select
asub.student_user_id, asub.assignment_id, aa.program_map_id, ar.rubric_id submission_rubric_id, ar.value submission_rubric_value,
asub.note as submission_note, asub.is_submitted submission_is_submitted, asub.grade submission_grade, asub.assignment_submission_id,
asub.submitted_date, asub.grade_note submission_grade_note,
dwar.expected_percentage performance_goal_percentage, dwar.expected_level performance_goal_rubric_level,
dwar.assessment_detail_response_id,
dwar.program_rubric_id assessment_results_course_learning_objective_id, dwar.rubric_level assessment_results_user_rubric, dwar.success assessment_results_user_success, dwar.response assessment_results_user_response,
pr.color program_rubric_color, pr.rubric_level program_rubric_level, pr.name program_rubric_name, pr.program_rubric_id
from dw_fact_assignment_submission aSub
inner join dw_deg_assessment_assignment aa on aa.assignment_id = aSub.assignment_id
inner join assessment_evidence ae on ae.assignment_submission_id = aSub.assignment_submission_id
inner join dw_fact_assessment_response dwar on dwar.assessment_detail_response_id = ae.assessment_detail_response_id and aa.program_map_id = dwar.program_map_id
inner join program_rubric pr on pr.program_rubric_id = dwar.program_rubric_id
left join dw_fact_assignment_rubric ar on aSub.assignment_submission_id = ar.assignment_submission_id
)
select
dwcsh.course_code, dwcsh.course_id, dwcsh.course_name, dwcsh.course_description, dwcsh.course_version_uuid,
dwcsh.course_section_code, dwcsh.course_section_syllabus_form_id, dwcsh.course_section_id,
dwcsh.course_section_term term_code, dwcsh.course_section_term_description term_name, dwcsh.course_section_term_id term_id, dwcsh.course_section_term_term_type_name term_type_name, dwcsh.course_section_term_status term_status,
dwrh.course_learning_objective_id clo_id, dwrh.course_learning_objective_code clo_code, dwrh.course_learning_objective_description clo_description,
dwrh.student_outcome_id, dwrh.student_outcome_description, dwrh.student_outcome_name, dwrh.student_outcome_code,
iif(dwrh.source = 'p', dwrh.program_id, dwrh.accreditor_id) as accreditor_id,
iif(dwrh.source = 'p', dwrh.program_code, dwrh.accreditor_code) as accreditor_code,
iif(dwrh.source = 'p', dwrh.program_name, dwrh.accreditor_name) as accreditor_name,
dwrh.accreditor_description, dwrh.source as accreditor_source,
arh.assignment_id, arh.name as assignment_name, arh.description as assignment_description,
arh.maximum_grade as assignment_maximum_grade, arh.grading_type as assignment_grading_type, arh.program_map_id as assignment_program_map_id,
arh.rubric_criteria_id, arh.rubric_criteria_code, arh.rubric_criteria_name, arh.rubric_criteria_description, arh.submission_type assignment_submission_type,
arh.rubric_id, arh.rubric_code, arh.rubric_name, arh.rubric_description, arh.rubric_value, arh.rubric_color, arh.rubric_points,
ddaa.assignment_link_id assignment_link_id, ddaa.calculation_percent assignment_link_calculation_percent, ddaa.assignment_template_id assignment_link_assignment_template_id,
ddaa.success_score assignment_link_success_score, arh.program_map_id assignment_link_outcome_course_map_id, ddaa.criteria_link assignment_link_criteria_link,
f.submission_note, f.submission_is_submitted, f.submission_grade, f.assignment_submission_id, f.submitted_date, f.submission_grade_note,
f.performance_goal_percentage, f.performance_goal_rubric_level, dwrh.program_outcome_map_id, dwrh.student_outcome_id program_outcome_map_outcome_id,
dwrh.is_placeholder performance_indicator_is_place_holder, dwrh.program_description,
dwrh.performance_indicator_code, dwrh.performance_indicator_description,dwrh.performance_indicator_id, dwrh.performance_indicator_name,
dwrh.program_map_id, dwrh.program_id, dwrh.developmental_level_id, dwrh.developmental_level_level, dwrh.developmental_level_color, dwrh.developmental_level_name,
dwrh.program_name, dwrh.program_code, f.assessment_detail_response_id,
dwrh.program_rubric_id program_map_rubric_id, dwrh.program_rubric_color program_map_rubric_color, dwrh.program_rubric_level program_map_rubric_level, dwrh.program_rubric_name program_map_rubric_name,
f.program_rubric_id, f.program_rubric_level, f.program_rubric_name, f.program_rubric_color, f.submission_rubric_id, f.assessment_results_user_success, f.assessment_results_user_response, f.submission_rubric_value
from dw_assignment_rubric_outcome_hierarchy arh
inner join dw_program_rubric_hierarchy dwrh on dwrh.program_map_id = arh.program_map_id
inner join dw_course_section_assessment_hierarchy dcsah on dcsah.course_section_id = arh.course_section_id
inner join dw_deg_assessment_assignment ddaa on ddaa.assignment_id = arh.assignment_id
and dcsah.program_id = ddaa.program_id
and arh.program_map_id = ddaa.program_map_id
inner join dw_user_enrollment_hierarchy dwueh on arh.course_section_id = dwueh.course_section_id
inner join dw_course_section_hierarchy dwcsh on dwcsh.course_section_id = arh.course_section_id
left join fact f on f.assignment_id = ddaa.assignment_id
and f.program_map_id = ddaa.program_map_id
and COALESCE(f.submission_rubric_id,0) = COALESCE(arh.rubric_id,0)
and dwueh.user_id = f.student_user_id
where
1 = 1
and f.student_user_id = 123456