Tool Search: Data Extract Utilities
This guide outlines how to use the Data Extract Utilities tool to create a data extract for third-party systems that require student demographic and contact data.
Create the Third-Party Extract
- Select the New icon.
- Select the Extract option and click Submit. A series of editors will appear.
- Enter Admin/Vendor Provided Data. The credentials for this process must be obtained from your third-party system. These credentials usually include username, password, server (IP Address or server name), port number, and sometimes the path directory.
- Select an Extract Method of SQL
- In the SQL/Prism field, enter EXEC get_supersetV1
- This stored procedure accepts two parameters:
Parameter Type Default Description @date VARCHAR(10) Today's date Determines which homeroom section to resolve for the student on that specific calendar day @includeSSN BIT 1 When 0, both the student and guardian SSN fields return NULL - Give it an Extract Instance Name (i.e., Third-Party Extract).
- Determine the Output File Name.
- Fill out the rest of the File Information fields (as applicable).
- Determine the File Extract Format. Field definitions can be found on the Data Extract Utilities article.
- Set the Data Extract Schedule.
- Click the Save icon when finished.
At this time, a user can select the appropriate buttons to test the extract, test the database connection, or run the extract. Extracts will display in a new window in the selected format.
- The Test Extract button generates the requested information using SQL or Ad hoc. Data is not written to the database or stored anywhere.
- The Test Connection button checks the connection to the entered server by attempting to write a 1-byte file to the configured location. If it is successful, a success ribbon is displayed; if it is unsuccessful, a pop-up with the reason the connection failed is displayed.
- The Run Extract button runs the extract, sends and stores the file where needed (in case of an issue with an auto-run extract).
Example executions
| Example | Description |
EXEC get_supersetV1 | Get today's snapshot with SSNs (default behavior) |
EXEC get_supersetV1 @includeSSN = 0 | Get today's snapshot without SSNs |
EXEC get_supersetV1 @date = '09/05/2025', @includeSSN = 1 | Get a snapshot for a specific date with SSNs included |
| EXEC get_supersetV1 @date = '09/05/2025', @includeSSN = 0 | Get snapshot for a specific date without SSNs (safe for sharing with a third-party vendor) |
EXEC get_supersetV1 @date = '08/19/2025' EXEC get_supersetV1 @date = '01/06/2026' | Run for multiple dates to compare homeroom changes across the year |
| EXEC get_supersetV1 @date = '11/12/2025', @includeSSN = 0 | Verify what a scheduled export would have produced on a specific past date |
How it functions
- Finds each student's "best" enrollment for the active school year: prioritizes Primary service type (P) over Secondary, then the most recent start date. Excludes summer school calendars and no-show enrollments.
- Resolves the student's primary household by selecting the household where the student is an active member, and, if available, the non-secondary household with a guardian relationship.
- Resolves the primary contact/guardian from the chosen household, picks the first guardian in the relatedPair table who is also a current household member.
- Resolves the mailing address: from the student's active household locations, prefers the primary household's mailing address, then falls back on the secondary flag.
- Resolves the homeroom section for @date: walks the schedule structure (roster > section > placement > period > day) to find the section the student is rostered into on the given date, prioritizing courses flagged as homeroom or homeroom sections, then by period sequence.
Data fields
The data extracted will include the following fields in this order:
| Column | Source | Meaning |
|---|---|---|
| mydate | @date parameter | Echo of the date parameter used for the extract. Useful for the consuming system to know which date snapshot it received. |
| StudentDistrictID | person.studentnumber | District-assigned student number |
| StudentSSN | identity.ssn | Student's Social Security Number Returns NULL when @includeSSN = 0 |
| StudentStateID | person.stateID | State-assigned student identifier |
| StudentEnrollmentStatus | Computed from enrollment.enddate | 1 = currently enrolled (end date is today or later), 0 = enrollment has ended |
| StudentSiteCode | school.number | School's numeric code |
| StudentDistNum | district.number | District's numeric code |
| StudentHomeroom | room.name (from resolved section) | Name of the homeroom; defaults to 'NHR' if no homeroom is resolved |
| StudentHomeRoomTeacherLastname | identity.lastname (teacher) | Homeroom teacher's last name |
| StudentHomeRoomTeacherFirstname | identity.firstname (teacher) | Homeroom teacher's first name |
| StudentLastName | identity.lastname | Student's last name |
| StudentFirstName | identity.firstname | Student's first name |
| StudentMiddleName | identity.middlename | Student's middle name |
| StudentBirthdate | identity.birthdate | Formatted MM/DD/YYYY, trimmed |
| StudentGender | identity.gender | Gender code (typically M/F) |
| StudentGradeLevel | enrollment.grade | Grade level from the enrollment record |
| StudentRace | identity.raceEthnicity | Race/ethnicity code |
| StudentEmail | v_Contact.email | Student's email from the contact view |
| StudentPrimaryContactLastName | identity.lastname (guardian) | Guardian's last name |
| StudentPrimaryContactFirstName | identity.firstname (guardian) | Guardian's first name |
| StudentPrimaryContactRelationship | relatedPair.name | Relationship label (e.g., "Mother", "Father") |
| StudentPrimaryContactSSN | identity.ssn (guardian) | Guardian's SSN Returns NULL when @includeSSN = 0 |
| StudentPrimaryContactEmail | v_Contact.email (guardian) | Guardian's email |
| StudentPrimaryWorkPhone | v_Contact.workphone (guardian) | Guardian's work phone |
| StudentPrimaryContactCounty | address.county | County of the mailing address |
| StudentPrimaryContactStreetAddress | Computed from address fields | Full street address; handles PO Box vs. standard format (number + prefix + street + tag + dir + apt) |
| StudentPrimaryContactCity | address.city | City |
| StudentPrimaryContactState | address.state | State code |
| StudentPrimaryContactZip | address.zip | ZIP code |
| StudentPrimaryContactPhoneNumber | household.phone | Household phone number (empty string if null) |
