Andrew Drinkwater
Most of you have heard me say that you should use databases to store your enrolment forecasting data. Well, that’s not always the case…
Databases are great at storing the inputs and outputs of your enrolment forecast. Depending on your configuration, horsepower, and forecast design however, they may be very subpar for the steps that happen between input and output.
Here’s an example.
Many institutions have developers with a deep expertise in creating stored procedures in the database. I was one of these, once upon a time, but I’d never place myself at the level of expertise seen in some of my colleagues.
A stored procedure is often a series of SQL (structured query language) statements that are stored in the database so that they can be used repeatedly. In some database systems, you can group various stored procedures together, which is usually called a package.
These packages can be run manually, but they can also be called by triggers.
Taken together, packages, stored procedures, and triggers can be a good way to automate elements of your enrolment forecasting process: preparing base data to feed into your model, inputs into various steps of the process, outputs from other steps, and so on. You’ll likely need to bundle the database steps with a separate enrolment model made in Python, R, or similar as most databases don’t support this type of modelling nor are well suited to it.
The challenge often comes in the often sequential processes that make up an enrolment forecast: start with base data, pull in intakes, run a projection model, and determine how those outputs trickle through to headcounts, FTEs, and potentially individual courses. Each of these steps can involve reading from and writing back to a database. These steps take time, making it harder to stay in the flow of analysis. They’re especially slow with big inserts of data, such as inserting all the courses you expect to offer in 2025.
If you’ve been following my series on enrolment forecasting, you’ll know I’m a big believer in working collaboratively with other users (see Learning Together: Leveraging Paired Analysis to Make Your Enrolment Forecast Successful and Having Multiple Professionals Build Your Enrolment Forecast). An additional challenge relates to collaboration. Is your database and model set up to handle both the Faculty of Arts and the Faculty of Science submitting a scenario at the same time? If not, you’ll need to build out a multi-threading capacity or queuing system.
In either case, if you’re not super careful with your code, using triggers and procedures to auto-populate different steps can sometimes result in a table lock. This happens when a table is still being written to or deleted from prior to the commit being made to the database.
So, while the database can be an enticing tool to help with your forecasting, you should make sure the performance meets your requirements. You may be better served to have more of your process embedded in models built outside of the database and use the database as the landing place for the final outputs.