Tables

data warehouse tables the ampeducator data warehouse organizes your institution's data into a curated set of reporting tables designed specifically for analytics and business intelligence rather than exposing the hundreds of tables that power the day to day operation of ampeducator, the data warehouse presents the information most commonly used for reporting in a simplified structure this makes it significantly easier to build sql queries, dashboards, and reports without needing an in depth understanding of the application's internal database how the data warehouse is organized the data warehouse currently includes 17 reporting tables , covering the major functional areas of ampeducator, including students prospects programs courses enrolments attendance grades financials instructors student activity each table has been designed to represent a specific area of your institution while maintaining relationships with the other reporting tables how the tables relate most reports begin with a student and expand outward by joining related information from other tables the diagram below provides a high level overview of how the data warehouse tables relate to one another most reports begin with the dw students table and join additional tables such as enrolments, attendance, grades, financial summaries, and courses depending on the information being analyzed for example, you might begin with a list of students, then join enrolments to determine which programs they're in attendance summaries to monitor participation grade summaries to review academic performance financial summaries to identify outstanding balances this structure makes it easy to answer questions such as which students are currently at risk? which programs have the highest completion rates? which students have poor attendance and outstanding balances? what are the average grades by instructor or course? nightly data refresh the data warehouse is refreshed automatically each night changes made throughout the day—including new students, enrolments, attendance, grades, financial transactions, and other activity—are processed overnight and made available the following day for reporting this allows the reporting database to remain optimized for analytics without impacting the performance of the live ampeducator system curated for reporting the data warehouse is intentionally designed as a reporting database rather than a direct copy of the live ampeducator database only the data most commonly required for reporting and business intelligence has been included this keeps the tables easier to understand, improves query performance, and reduces unnecessary complexity if there is additional reporting data that would benefit your institution, please contact ampeducator support we continually review requests for additional reporting fields and tables available tables the following sections describe each data warehouse table, its purpose, and the fields it contains dw students description master list of students and prospects, including core personal details, contact information, and current program when to use use this table for general student information and as a starting point for most reports key fields student key unique identifier used to join across tables student type prospect or enrolled student name, email, phone contact details location name assigned location current program name current program current program status program status dw prospects description admissions data for prospects, including lead stages, sources, and assigned advisors when to use use for admissions tracking, lead analysis, and conversion reporting key fields student key prospect identifier lead stage current stage source lead source admission advisor assigned advisor expected program name intended program is converted to student conversion indicator dw prospect stage history description timeline of prospect stage changes throughout the admissions funnel when to use use for funnel analysis, conversion rates, and time in stage reporting key fields student key prospect identifier stage funnel stage entered at, exited at stage timing time in stage days duration in stage dw courses description course level data including schedule, enrollment, and performance metrics when to use use for course reporting and performance analysis key fields course key course identifier course name course name course start, course end timeline num enrolled, num completed enrollment metrics course percentage average, course gpa average performance dw course instructors description mapping of instructors assigned to courses when to use use to analyze instructor workload and course assignments key fields course key course identifier instructor name instructor name is primary primary instructor flag dw instructors description instructor information including contact details and status when to use use for instructor reporting and staffing analysis key fields instructor key unique identifier full name, email contact details location name assigned location status active or inactive dw sessions description academic sessions or terms with associated dates when to use use to group or filter data by term key fields session key unique identifier session name term name session start, session end timeline dw student programs description student progress and performance within each program when to use use for program level reporting and student outcomes key fields student key, program name identifiers program start date, program expected end date timeline grade point average, percentage grade average performance total hours completed, total hours missed attendance dw student sessions description student performance summarized at the session (term) level when to use use for term based academic reporting key fields student key, session key identifiers credits attempted, credits earned progress completion rate completion metric dw student enrollments description student enrollments in courses, including status, grades, and attendance when to use use for course level student analysis key fields student key, course key identifiers start date, end date timeline is active, is completed, is withdrawn status grade point average performance dw student grades description detailed grade and assessment data when to use use for grading analysis and assessment level reporting key fields student key, course key identifiers grade name assessment percentage, final weighted grade scores is passed pass/fail indicator dw student attendance description detailed attendance records per student when to use use for granular attendance tracking key fields student key, course key identifiers attendance date date attendance status present, absent, or late attendance hours hours attended dw student attendance summary description aggregated attendance metrics per student when to use use for high level attendance reporting key fields student key identifier total attended, total absences counts total hours attended, total hours missed hours dw student account activity description transaction level financial activity including charges and payments when to use use for detailed financial analysis key fields student key identifier activity date transaction date activity type type of activity amount transaction amount dw student financial summary description summary of student financial status including balances and aging when to use use for financial reporting and account health key fields student key identifier balance, balance due financial totals balance due 30 days, balance due 60 days aging dw student academic summary description high level academic performance per student when to use use for quick academic summaries key fields student key identifier grade point average gpa percentage grade average average grade academic status standing dw student overview description combined view of student data across academic, financial, attendance, and program areas when to use use for dashboards and reporting without needing joins key fields student key identifier student status status current program name program balance, balance due financial attendance status attendance grade point average academic common joins student key links student related tables course key links to course data session key links to academic sessions next steps learn how to use these datasets with sample queries example queries docid 2hwk9jckeqa3xs21to1ru you can also review overview docid\ ptudjpwbbjsb18lwlhzo3 connecting to bi tools docid\ j0tpcbbrvjek6oeh6p4en