ampEducator Data
Data Warehouse
Tables
the data warehouse is organized into a set of structured tables, each designed for a specific reporting purpose these tables are prefixed with dw and are built to simplify reporting by reducing the need for complex joins and calculations each table focuses on a specific area of the system, such as students, admissions, courses, financial activity, or academic performance together, they provide a complete and consistent view of your data in a format that is optimized for analysis table structure all tables follow a consistent structure to make them easier to use across reports and tools most tables include shared keys such as student key, course key, or session key these keys allow you to connect data across different tables when needed, while still keeping each dataset simple and focused in many cases, summary tables are also available these tables combine data from multiple sources into a single view, allowing you to work with pre calculated values instead of building everything from scratch types of tables the data warehouse includes several types of tables, each serving a different purpose core tables provide foundational information such as students, prospects, courses, and instructors, and are often used as a starting point for reporting activity and transactional tables capture detailed records such as attendance, grades, enrollments, and financial activity these are useful when more granular analysis is required summary tables bring together data from multiple areas into a single dataset these are ideal for dashboards and high level reporting where simplicity and performance are important choosing the right table when building reports, it’s best to start with the table that most closely matches your goal for example, if you are looking for a high level view of student performance, a summary table may already contain everything you need if you need more detailed insights, such as individual attendance records or grade components, you can use the corresponding detailed tables over time, most reporting can be done using a small number of commonly used tables, especially those designed specifically for reporting table reference a complete list of available tables, along with descriptions and key fields, is provided below use this reference to understand what each table contains and when to use it available tables 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 docid 2hwk9jckeqa3xs21to1ru you can also review docid\ ptudjpwbbjsb18lwlhzo3 docid\ j0tpcbbrvjek6oeh6p4en