---
title: Tables
slug: tables
docTags: 
createdAt: 2026-04-07T14:53:48.071Z
---

# 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.

![](https://api.archbee.com/api/optimize/dAaYdF15xm67t_NLKoQQv/zpkU6x0BtTnIyWMRFJYao_chatgpt-image-jun-25-2026-10-10-11-am.png)

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 

### dw\_student\_custom\_fields

**Description:** Institution-defined custom student fields with column names reflecting each institution's configured field labels.

**When to use:** Use to query and analyze institution-specific student data alongside standard Data Warehouse tables.

**Key fields**

- **student\_key:** Identifier 
- **Institution-defined custom field columns:** Column names and data types vary by institution based on configured custom field definitions.

## 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)****