Developing Files – The Basics

Transcript

Welcome. In today’s tutorial we will cover some basic rules on how to develop your files.

“Postsecondary Data Partnership (PDP) is a grant-funded initiative to improve student outcome transparency. Participating institutions will receive access to Key Performance Indicators (KPIs) and a KPI Executive Summary twice per year, along with access to a secure and private Tableau business intelligence tool year-round. The goal of the KPIs is to help create the necessary transparency infrastructure to help close attainment gaps and increase the number of students earning credentials across institutions and systems of institutions.”

“Upon service activation, we recommend institutions send files to the Clearinghouse twice a year: fall and spring. Please note, the submission opening dates and effective dates vary each submission cycle. The effective date is based on when we match your institution’s certified, or completed, PDP data set with the Enrollment and Degree data submitted up to that point by your institution. We highly recommend all institutions to complete their PDP data set by the effective date in order to ensure timely processing of your reports.”

“To receive your Analysis Ready File and Dashboards, you will need to submit two new data files up to twice a year that include cohort and course data. Please note, the financial aid file is an optional file. Listed on the screen is a high level of what’s in the cohort and course file.”

Lets discuss the student populations in each file.

For the Cohort File, you want to include all students that enrolled at your institution for the first time in a reporting term(s). Please provide only those students that are considered first time and transfer in at your institution. This file contains only one row of data per student and static data at the time of enrollment. For the Course File, you want to include all students from previous and current cohorts that are currently enrolled at your institution in a reporting term(s). This file contains dynamic data for each term the student attended at your institution. For the Financial Aid File, please follow the same logic as the Course File.

Upon joining the Postsecondary Data Partnership, we recommend institutions submit historical data within the last 3-5 years. Your reports will provide better information with at least five years of historical data, but reports can be produced with just 3 years of historical data, plus the latest available term course and cohort files.
For each cohort year submitted, please include every student that enrolled at your institution as a first time or transfer student in that particular year. Then submit the supporting course data for each of those years. The course files are cumulative, and you should expect the records in each file to grow in volume as you progress through the years.
Please note, there are no system limitations for the size of data files. For example, you may submit all of your cohort data in one file or submit by academic year or term.

The PDP application only supports file formats in comma delimited format or .txt extension. If you submit an unsupported file type such as an excel file, your file will not upload in the PDP application.

To begin creating your files, please open your file in excel or the application you are using to build your files. If you are using excel, please make sure to have a working file saved to make any edits before you save and submit the file in csv format.

The file naming convention must include your PDP-specific FTP username as a prerequisite to the file name of your choice. This will ensure that your file will be picked up by the Clearinghouse. If you do not receive an automated email after submitting a file, please check that you provided your FTP username.

THE GLOSSARY FOR FILE STRUCTURE
“Now that we have named the file, we will now decode the data definition table in the data submission guide to create your files. “ The Sequence # field is optional, which means that the order in which the data fields and column names appear in your file do not need to follow a specific order. However, the record type values (such as DCE01, DCE02, DCE03, CH1, D1 and T1) are special – they need to be the first field in the row, otherwise you will receive an “Invalid Record Type” error.

The Variable name is ONLY found in the Header and Trailer sections. It is NOT the Column Header Row.

The NSC Format indicates what type of characters are allowed for each data element. String means any character goes. Enumerated is a constrained field that only accepts certain values.

The NSC Length column indicates the maximum amount of characters allowed in a given field.

The Definition is a written description of the field. Some are very brief, others such as the Cohort field are very descriptive, so please read these carefully.

The Required field identifies which fields are required. Y is required, N is Not required or optional, and CY is conditionally required.

The Allowed Values column specifically applies to Enumerated fields, and in some cases in other fields. This will tell you exactly what values you are allowed to enter in a set field. For example, for an Alpha Only formatted field, it may indicate some special characters or spaces are allowed, so please pay attention to this field. In some of the required fields, such as “Course CIP” we allow the value of “missing” if the data is not available.

The Validation/Business Rule defines further constraints around the field.

Each file contains the following sections:
The Header Record identifies the sending organization and the file type (animations of cohort, course, financial aid files)
The Column Header Record, which is case sensitive, must be entered exactly as shown in the submission guide. Please make sure you have the correct spelling.
The detail record, or body, is where all student records are stored.
And finally the Trailer Record, which identifies where the data ends in your file. Please note, all fields need to be under 255 characters. Not following this criteria will result in a database error, which means you will not receive a notification of the status of your files.

Here are samples of the Cohort and Course File Header Records.
These are the Record Type Values. They must always be the first field in the Record.
The Service Account Number was provided to you during your onboarding. If the Service Account Number is not provided, your file will not upload in the PDP application.
The Organization ID is your 6-digit OPEID for institutions. For Systems, it is your Org ID
You must enter 2 zeroes in the Branch Code field
Next are the filler fields. Please leave these fields blank.
The File Certified Date is the date in which the file is submitted to the Clearinghouse
The filler fields
The Client File ID, which can be any unique internal identifier
Followed by additional filler fields

The next row of data is the Column Header Record. A sample of the Column Header Record is provided.

Here is an introduction on how to create the different sections in both the Cohort and Course files in .txt. If you are using excel, the columns denotes a comma.

Here is a sample of an entire cohort file. Each Detail Record must begin with D1, as shown. The Trailer Record must always begin with T1 and the Total Record Count field should be the sum of all rows of data, including the Trailer Record. Please make sure you are referring to the latest Submission Guide as the data fields may change.

Here is a sample of an entire course file. The Record Type values remain the same as the Cohort file, except for the value in the Header Record, which begins with DCE02.

Please note if a field contains a comma, wrap the contents of the data element with double quotation marks. For example, the first name John, Jr should be entered as open quotation mark “John, Jr” closed quotation mark. This rule applies to all data fields.

Do not leave a required field blank or enter a NULL value. Doing so will cause your file to fail. Please check if there are values listed in the Definitions and the Allowed Values columns. Some required fields allow you to populate the field with the word “missing” or “UK” (without quotations) which indicates Unknown, such as the Gateway Status fields in the Cohort file and the Course CIP field in the Course file.

Now it is time to submit your files. Please go to the link on the screen and login with your PDP specific, FTP account.

If your file was successfully submitted, you will receive an automated receipt email indicating that your file was received by the Clearinghouse.

if you submitted a file and received a receipt confirmation email that the file was successfully uploaded but did not receive the automated email on the status of your file meaning pass or fail, a database error may have occurred. This error is different from the structural and field level validation errors. Please check all fields to ensure they do not exceed the maximum character limit of 255 characters. If the issue occurs with your course file, please pay special attention to the course description field. To resolve this error, please shorten the field in question and resubmit to the Clearinghouse.

Next, you will receive another automated email on the status of your files. Here is a sample of a structural error email. Structural errors are reported one at a time, which means you will need to correct and resubmit the error in order for the file to continue processing.

If your file has no structural errors, the system will then check for any field-level errors. Here is a sample of an automated Field-Level Error Email.

This is a sample of an automated PASSED email.

For additional tutorials on how to check the status of your files, Tableau access and more, please go to:
https://www.studentclearinghouse.org/academy2/courses/pdp/

If you have any questions or need assistance, please reach out to the PDP team at pdpservice@studentclearinghouse.org