This data collection is no longer being refreshed. As-of the summer of 2022, the system-of-record for student data at Penn is Pennant, and the new data collection in the warehouse is Pennant Student Records.
The legacy Student Records Data Collection, which was sourced from SRS, will remain as a static collection in the warehouse for historical reporting. People who are granted access to Pennant Student Records will also have the ability to query the legacy data collection.
ATTENTION: All users of Student Data, whether they write their own queries, or use data or reports extracted by someone else, agree to abide by University policy regarding confidentiality of student records, and by the Family Educational and Right to Privacy Act. Failure to abide by the policies and laws governing student data can result in disciplinary action, including termination.
Tables are listed in alphabetical order. Select Table to obtain the explantion, common uses, primary keys, indexed data elements, and related tables for a table. Select Elements to obtain the definition, list of values, formats, and not null values for each data element in the table. A description of table and data element terminology is in the document What the Warehouse Contains.
Looking for a specific element or caution? Try the searching the collection documentation for particular keywords (search page will open in a new tab).
| Address Table Elements |
Degree Term All V Table Elements |
| Advisor Term Table Elements |
Enrollment All V Table Elements |
| Assignment Table Elements |
Enrollment Section Type Table Elements |
| Athletic Table Elements |
Hiatus Table Elements |
| Census Term Table Elements |
Internal Course Equiv Table Elements |
| Course Inventory Table Elements |
Leave Table Elements |
| Course Xlist Inventory Table Elements |
Major Minor Term Table Elements |
| Course Inventory Desc Table Elements |
Masters Thesis Table Elements |
| Course Inventory Desc Text Table Elements |
Person All V Table Elements |
| Course Section Mtg Table Elements |
PhD Committee Table Elements |
| Course Section Table Elements |
PhD Dissertation Table Elements |
| Course Section Instructor Table Elements |
Room Profile Table Elements |
| Course Section Reg Control Table Elements |
SRS Admissions Data Table Elements |
| Course Section Type Term Table Elements |
Student Holds Table Elements |
| Degree Pursual All V Table Elements |
Student Term Table Element |
| Degree Received Table Elements |
Transcript Honors Table Elements |
| Transcript Text Table Elements |
|
|
|
Student APS Table Elements |
| LOOKUP TABLES (use only to decode values in other tables): | |
| Approved Academic Programs Table Elements |
Major Table Elements |
| Building Table Elements |
Present Period Table Elements |
| College House Table Elements |
School Table Elements |
| Country Table Elements |
SRS States Table Elements |
| Degree Table Elements |
SRS Term Calendar Table Elements |
| Dept Table Elements |
Subject Area Table Elements |
| Division Table Elements |
Student APS Table Elements |
| Institution Table Elements |
|
The Student Records System Data Collection is refreshed nightly, Monday through Saturday, beginning around 10:00 PM. The job runs at 5:00 PM on Sunday nights. Updates are generally completed by the start of business the following morning.
To see whether a particular data collection has completed its refresh, see the Load Status page. Authorized users may also query to LOAD_STAT table in the warehouse. Any changes to the regularly scheduled load of a data collection are posted as notices to the listserv for that data collection.
Source
Almost all of the Student data in the Data Warehouse that is accessible via the BusinessObjects STDTCANQ and STDTTERM universes comes from the Student Records System (SRS). Exceptions: PennID, which comes from the PennCard system, and Employee General information on instructors, which comes from Human Resources.
One of the most frequently used tables in the Student Data Collection is described in the STDTCANQ diagram. The diagram illustrates the architiecture of a specific BusinessObjects universe and identifies the join criteria between tables.
STDTCANQ universe
describes the Business Objects classes and objects, and how they are joined, in the data collection that is refreshed nightly from SRS. The universe can be viewed as a printable image (print in landscape orientation) or as an online diagram linked to the table definitions.
More information about the data model for Student Data is described in the online course Data Warehouse: Student Data Training. See the "Data Training" link for instructions on how to sign up for the online course.
The Business Objects Corporate Documents area provides access to pre-defined, prompt-driven reports that you can run to retrieve a standardized perspective on your data.
ACAD - matriculation, retention, TTD, graduation and attrition for a Grad group
For the specified Graduate Group, for students entering in the specified years, this report shows average Years in degree pursual, compares the Grad Group to the Cluster (clusters are groupings of similar academic areas), and shows a breakdown of students in the specified cohorts by ethnicity/citizenship, sex and age. Report prompts are for a range of entry years, degree, and graduate group. There are other reports in this same folder that retrieve similar data for a specified school, and also university-wide. Uses STDTCANQ universe. Requires “Provost Office Academic Reports” access request e-form.
Count Students as of Today
The report uses the warehouse data that is refreshed nightly from SRS. It contains detail and summary report tabs on students who are active as of the term specified when the report is executed. "Active" is defined as not exited as of the term specified, and not on leave of absence. The prompts are for a term and a divison. More than one division may be selected from the list of values, and it is an optional prompt: if no division is selected, the report will return data for all divisions for the selected term. The version number in the report title refers to the document revision number. Uses STDTCANQ universe.
Count Students with Tuition Billed
To run this report you must have access to Student and BRS data. The report contains billing detail and summary report tabs on students who are active as of the term specified when the report is executed, who have been billed for tuition for the term. "Active" is defined as not exited as of the term specified, and not on leave of absence. Upon refresh, indicate the "BRS Detail" context, then answer the prompts for term and division or divisions. The version number in the report title refers to the document revision number. Uses BRS_Combined universe.
Count PhD Students with Tuition Funded
To run this report you must have access to Student and BRS data. The report contains billing detail and summary report tabs on students who are active as of the term specified when the report is executed. "Active" is defined as not exited as of the term specified, and not on leave of absence. Upon refresh, indicate the "BRS Detail" context, then answer the prompts for term and division or divisions. Uses BRS_Combined universe.
Count Students with no Tuition Bill
To run this report you must have access to Student and BRS data. The report contains billing detail and summary report tabs on students who are active as of the term specified when the report is executed, and have not been billed for tuition for the term. "Active" is defined as not exited as of the term specified, and not on leave of absence. Upon refresh, answer the prompts for term and division or divisions. The version number in the report title refers to the document revision number. Uses custom SQL rather than a specific universe.
Count Students as of Fall 2011 census snapshot
This report should NOT be refreshed! This is a snapshot of the Count Students as of Today report, taken on October 22, 2011, following the creation of the Fall 2011 Student Term Census extract. This report contains data elements not included in the SDDTTERM universe or the Public report “Count Students as of Census“. Uses STDTCANQ universe.
CIT_code report to schools
Courses InTouch (CIT) information about instructors and their CIT codes, as assigned in SRS. Uses STDTCANQ universe.
Classlst
Basic list of attendees in a specified course section. Not to be used for cross listed courses. Uses STDTCANQ universe.
Course-CrseNum-Instructor
To list all courses within a range of course numbers for specific terms, with the instructor information. In addition, it provides basic enrollment information plus enrollment planning statistics. Users can also specify the TUITION_SCHOOL of interest, or use % as the wildcard to represent all non null TUITION_SCHOOLs at Penn. Uses STDTCANQ universe.
Course-CrseNum-Meeting
To list all courses within a range of course numbers for specific terms, with the building, room, days, and time. In addition, it shows enrollment. Users can also specify the TUITION_SCHOOL of interest, or use % as the wildcard to represent all non null TUITION_SCHOOLs at Penn. Uses STDTCANQ universe.
Course-Division-Instructor
To list all courses by division (where division is coded) for specific terms, with the instructor information. In addition, it provides basic enrollment data plus enrollment planning statistics. Users can also specify the SUBJECT_AREA of interest, or use % as the wildcard to represent all SUBJECT_AREAs within the division. Uses STDTCANQ universe.
Course-Division-Meeting
To list all courses by division (where division is coded) for specific terms, with the building, room, days, and time. In addition, it shows enrollment. Users can also specify the SUBJECT_AREA of interest, or use % as the wildcard to represent all SUBJECT_AREAs within the division. Uses STDTCANQ universe.
Course-Meeting
To list all courses for specific terms, with the building, room, days, and time. In addition, it shows enrollment. Users can specify the SUBJECT_AREA of interest, or use % as the wildcard to represent all SUBJECT_AREAs at Penn. Uses STDTCANQ universe.
Course-SectionNum-Instructor
To list all courses in a specified range of section numbers for specific terms, with the instructor information. In addition, it provides basic enrollment information plus enrollment planning statistics. Users can also specify the TUITION_SCHOOL of interest, or use % as the wildcard to represent all non null TUITION_SCHOOLs at Penn. Uses STDTCANQ universe.
Course-SectionNum-Meeting
To list all courses in a specified range of section numbers for specific terms, with the instructor information. In addition, it provides basic enrollment information plus enrollment planning statistics. Users can also specify the TUITION_SCHOOL of interest, or use % as the wildcard to represent all non null TUITION_SCHOOLs at Penn. Uses STDTCANQ universe.
Course-SubjectArea-Instructor
To list all courses for specific terms, with the instructor information. In addition, it provides basic enrollment data plus enrollment planning statistics. Users can specify the SUBJECT_AREA of interest, or use % as the wildcard to represent all SUBJECT_AREAs at Penn. Uses STDTCANQ universe.
Course-SubjectArea-Meeting
To list all courses for specific terms, with the building, room, days, and time. In addition, it shows enrollment. Users can specify the SUBJECT_AREA of interest, or use % as the wildcard to represent all SUBJECT_AREAs at Penn. Uses STDTCANQ universe.
Crosslist-multi
Lists enrollments in courses specified, including crosslisted sections. Can be used to report on enrollments for a specific course id or for all courses in a specific subject area. Uses STDTCANQ universe.
Crosslist-one
Lists students enrolled in a course, including all crosslisted enrollments, when the PRIMARY course section is specified. Uses STDTCANQ universe.
Currently on Leave
The prompt is for a Division. The query can be run for one or multiple Divisions.Student on leave are identified as anyone with an unresolved entry in the LEAVE table. This means anyone who has an entry in that table, where the Actual Return from Leave is either null, or is a term greater than the current term.The current term uses Current Academic Term from the PRESENT PERIOD table. Leave actions include the SRS actions LV, AL, FLThe major (or grad group) is from the Major Minor Term Last class, and looks at only the primary major. Uses STDTCANQ universe.
DECLMAJS
Prompts for major or minor , term, division, degree level. Returns a list of major(s) or minors in the selected areas with their name, email address, classification, and, if applicable, dual degree and joint degree codes. Uses STDTCANQ universe.
DEMG - Enrollment by term for a Grad Group
This report retrieves census enrollment counts of grad students in the specified Grad Group, sorted by demographic categories. Prompts are for an academic term and graduate group. There are other reports in this same folder that retrieve similar data for a specified school, and also university-wide. Uses STDTCANQ universe. Requires “Provost Office Academic Reports” access request e-form.
Diagnostics - course term problems
Finds potential problems with course term data. Uses STDTCANQ universe.
Diagnostics - dual and joint degree data problems
Finds people with a joint degree flag, but who do not have a dual program in the specified term. Uses STDTCANQ universe.
Diagnostics - problems with leave of absence exit term
Finds students whose leave information may have been entered incorrectly in SRS. Uses STDTCANQ universe.
Diagnostics - students who should have graduated
Finds students who were pursuing a degree, with an Expected Degree Term that is in the past but they never were graduated or exited from Penn. Uses STDTCANQ universe.
Dual degrees in a term
Uses SRS designations D (dual) and J (joint) to find students in multiple programs for a specified term. Uses STDTCANQ universe.
ENROLL by division
Lists all students for the specified division(s) in a specified term, who are enrolled. Enrolled is defined as total term registered credit > 0. Uses STDTCANQ universe.
EXAM - Benchmark exams for a Grad Group
Details on students taking qualifying exams, prelims and oral exams, for PhD students in a specified Grad Group. Prompts are for a range of admit years and a graduate group. The reports have multiple tabs showing Masters' final exam, Qualification and Prelim information and average time-to-exam, and Oral Defense dates. There is another report in this same folder that retrieves similar data university-wide. Uses STDTCANQ universe. Requires “Provost Office Academic Reports” access request e-form.
Faculty Load
After prompting the user for a SRS Term, this query will populate the Faculty Load Reports from the Student Data Warehouse. Uses “Faculty Load” universe.
Student Degree List
Lists students who are expecting to complete a degree in a user-specified division in a user-specified term. Uses STDTCANQ universe.
Students on Hold
Lists all students in a specified division or divisions in a specified term, who have any type of hold (registration or financial). The hold information is displayed on two reports: Active, and Cleared holds. On the Active hold tab, the effective term of the hold is displayed, and there is a flag indicating whether the student is on Leave for the selected term. Uses STDTCANQ universe.
In order to construct your own queries using the Student Data Collection in the Data Warehouse, you must complete Student Records System (SRS) training and warehouse student data training.
SRS Training is offered by the Office of the University Registrar. You will also need to obtain an SRS account.
FERPA and Information Security training are online tutorials in Workday Learning. FERPA training must be completed annually.
Warehouse Student data Training is also in Workday Learning. You can access the required Student data collection training. The course title is "Using the Student Data Collection in the Data Warehouse - ISC." This is a self-paced tutorial; you can exit and return as much as you wish. There are embedded quiz questions throughout that are there just for you to try. The final quiz at the end, however, is mandatory.
After completing this training, you should be able to construct most of the Student Data queries you need. Specifically, you should be able to:
- Select the correct data elements and impose the correct conditions so that you can retrieve data as efficiently as possible
- Know what resources are available when you need help in constructing a query
- Identify the security considerations involved when using Student data
- Identify what data is available, how it is organized, and how to access it as efficiently as possible
- Recall specific questions that should be considered when writing a Student Data query
Individuals who plan to use the Student Data collection must have Student Records System (SRS) access before they can query the data collection in the Data Warehouse. Users must complete a Student Data Access Request eForm and obtain supervisor and school access administrator (the same person who approves SRS accounts) approval. Users of student data must also take the online course.
For people who will be using BI Launch Pad only (refreshing the canned reports on the Business Objects repository), and not creating or editing their own queries, the above training is not mandatory though it is still recommended. In addition, the online course on Building Worksheets is recommended. See the Academic Planning Worksheet - training page for more information.
Regulations and Policies on Student Data
Federal regulations regarding access to student data are spelled out in the Family Educational Right to Privacy Act (Buckley Amendment), commonly known as FERPA. For more information, see the FERPA information on the Penn Privacy site.
The University's policy on the Confidentiality of Student Records is available at http://www.upenn.edu/privacy/policies_publications.htm.
Releasing Data Outside the University
The only two offices that may disclose Student Data outside the University are the Office of the University Registrar and Institutional Research. If you do not work for those offices and you receive a request for data to be sent outside the University, refer the requester to the Office of the University Registrar.
Releasing Data Within the University
Within the University, Student Data may be disclosed only if it is needed to do the business of the University, and only to those who need to know the information in order to do their jobs. If you are not sure whether to fulfill a request for Student Data, contact the Office of the University Registrar.
Query Results
If you save your query results in Excel, pdf, or any other local file type, you must see to it that any sensitive data stored on your peronal computer is safeguarded through physical security, access control software, or encryption. When a computer is left signed to an account, it is easy for someone to gain unauthorized access. Either sign off from your account before you leave your computer or restrict access by some other means (physical security or access control software).
For more information on security and privacy, contact the Office of Information Security.
Overview. Student data is complex, and prerequisite training in SRS and an online course is required. In addition, there is an established user community here at Penn that meets periodically, and a listserv is available to help you query Student data in the warehouse.
Training. SRS training is a hands-on class taught by an instructor in Student Registration and Financial Services. Contact the Registrar’s Office regist@pobox.upenn.edu for upcoming dates. The online course about student data is a self-paced tutorial, found on knowledgelink.upenn.edu. The tutorial explains the overall data model, walks through numerous examples, questions and answers, and describes the canned queries available for this data collection.
Listserv and user group. The users of the Student Data Collection in the Data Warehouse across the University have a wide range of expertise, and are a valuable resource when questions come up about student data: where it comes from and how it is used. When you are granted access to the data, you will automatically be given a subscription to the student-wh@lists.upenn.edu, the list devoted to users of the Student Data Collection, and you will be invited to meetings of the Student Data Collection user group. When you have questions or comments about the data, please send e-mail to the list or bring them up at a user group meeting. Announcements about dates for user group meetings will be sent to the list.
The list is also the place to send E-mail if you notice that some documentation needs updating. For documentation published on the Web, please specify the URL for the documentation that needs to be updated. For Business Objects documentation, please specify the Universe, Class, and Object.
Canned Queries. A good place to get started is to take a look at the existing BusinessObjects queries on the repository. You can review the Query Panel, and even look at the SQL the query generates. If you have suggestions for the existing queries or new queries to share, post your suggestions to the student-wh list, or bring your ideas to the next user group meeting.
Office Hours. The Data Administration student support team holds Office Hours approximately 5 times a year. Office Hours are hands-on, informal training sessions for people new to the student data collection. We walk through a common query together, and then have time for general questions and answers when you can present your particular query challenges and get assistance from a data analyst.