Connecting Data with Insight
Automation - Course Enrollment, Centralized Mode
Data Challenge – The project had to generate a course enrollments report showing registrations over time with color-coding based on course percent full.
Requirements:
- Automate data extraction and transformations
- Report distribution designed for multiple colleges
- Present information graphically in a PDF, color-coded in Excel (pseudo dashboard), and in PowerBI with drill-through functionality
Each step is focused on automation. For example, course section data is scheduled for extraction at 6:00 AM every day. At 6:15 AM SAS reads the csv file and appends the information to a master file containing a history of enrollment. The challenge comes in when we need to run it for multiple colleges with various registration periods. A SAS macro do loop with SYMPUTX is utilized to run through the SAS program for every college in the control file. This process was presented at the 2017FA Community College Planning and Research Organization conference, presentation, and SAS Program.
With this report, leaders can quickly and efficiently review enrolment patterns and make appropriate adjustments to maximize resources. This process ran for more than 23 colleges at once with one desktop computer and SAS – all automated. In total, 35 of 58 (60%) of colleges utilized this report.
Fayetteville Technical Community College, AB Tech Community College, James Sprunt Community College, and Martin Community College participated in the project. The process worked for larger and smaller community colleges.
Automation - Curriculum Program Review, Within College Model
Data Challenge – Accreditors require each curriculum program be reviewed on an annual basis for enrollment, course success, matriculation, and overall success. Also, they require the information in a PDF. The information must be easy to read, interpret, and distributed. The process must be dynamic enough to automatically generate each report while giving the end-user the ability to edit the list of academic programs. The process must also be dynamic enough to run at any community college in North Carolina.
- Build a reporting process driven by a control file
- Provide enrollment, applications, course success, FT vs. PT enrollment, and demographics as represented in graphs – bar charts and line graphics
- Present the program outcomes next to the college level reports to give program chairs the ability to compare and contrast program and college patterns
Give clear directions to different colleges on the extraction, transformation, and reporting solutions. Identify data locations and valid methods of cleaning and storing the information. Present at various conferences and workshops throughout North Carolina, presentation, directions, report example.
Dynamic - Certificate Finder, Within College Model
Data Challenge – Often, students do not realize they have taken the correct sequence of courses to be awarded a credential. The data challenge is to identify students who:
- Completed the correct sequence of courses to be awarded a credential
- Have zero restriction on their academic record
- Current GPA above 2.0
- Have not graduated with a similar or higher degree
- Utilize local course requirements are used in the review
- Make the process transferable to all 58 North Carolina Community Colleges
Process Solution
Identified all data points holding the required information and reviewed each piece to ensure validity. Create a report demonstrating validity to WCC’s Registrar. This report was featured in WRAL’s Tech Wire article of the week: WCC students get credit they didn’t realize they’d earned. Thirty percent of all credentials awarded by the College between 2016 and 2020 materialized as a result of this initiative. The process was presented at the 2017 Spring CCPRO Conference, directions, presentation, SAS code as txt.
Automation - Daily FTE and Headcount Reporting
Data Challenge – Automate the extraction, transformation, and warehousing of data for distribution as MS Excel reports, PDFs, and a PowerBI dashboard daily displaying full-time enrolment and student headcount. The following steps are completed to accomplish the task:
- Identify and extract raw row-level data from the College’s transactional database
- Filter and calculate daily FTE (((contact hours*weeks)number of students)/512)
- Filter and count all eligible students by student type
- Append daily data to a master file
- Distribute and generate data structures suitable for various reporting needs (Excel, PDF, and PowerBI)
- Create a pseudo Dashboard with SAS for Excel users
- Generate PDF with SAS for all tables with clean graphs
- Extract and generate CSV files for PowerBI that reduces the amount of I/O by PowerBI (makes the dashboard faster)
Process Solution
The biggest challenge with this report is providing context to answer the question: Is the College up or down compared to the last three years by relative registration day? For example, are we up as compared to the third day of registration? A crosswalk between all three terms aligning registration days must be created. After that, it is a matter of automating the extract, transform, and loading of the daily FTE and headcount table – simple. This report is under the intellectual property rights of the College. Therefore, specifics about actual code and process flows are prohibited from public consumption. However, the general process flow can be discussed in a public space. Contact me directly if you need additional information.
Collaboration - Applied to Transcribed, Across College Model
Data Challenge – The College lacked a benchmark for the students who made it through the College admission process and into the classroom. Yes, the College could compare past academic terms to gain a sense of success, but the ability to compare to other colleges was nonexistent. The challenge was to
- identify low I/O research methods to report the percent of students who applied and enrolled within the same term,
- develop research methods usable by all 58 community colleges, and
- develop reports for all participants, so they to could develop a benchmark.
As a result, leadership determined an admissions benchmark, designed action plans to increase matriculation rates, and were confident in the College’s admissions process.