Point-in-Time Data for IR Professionals

One of the most common requirements for IR offices is to report on point-in-time (PIT) data or frozen data. You might need to report census data exactly as it appeared on census. Maybe you want to freeze enrollment data so you report on numbers that won't change as corrections and backdating occur. There are lots of reasons why data changes and why pulling from your SIS one day will differ when you run the same report a week later.

One solution might be to create snapshots. The problem with creating snapshots of data is the amount of space this consumes. I have honestly seen colleges create entire snapshots of their student information system so IR offices can report on census dates. That's certainly one way to go, but there are easier ways.

A more ideal approach is to use a Type II history table that is integrated as part of your data warehouse or ODS' daily ETL. This method of storing snapshots is really quite nice. It creates a compact but complete history of data changes. Pulling data for any given day is done with a simple where clause like "where '2019-02-03' between StartDate and EndDate".

While querying a history table is easy, it's not easy to create and maintain these tables, and the work of integrating Type II into your ETL should be done by those with years of experience in this area. There are few database administrators that have this experience, and this is a particular skill we have here at institutionalresearchers.com.

Why should you go to the effort to integrate Type II into your data warehouse or ODS? Some things that come to mind are that most IR offices want to track data on key dates like census, but many of you may also want to track daily enrollment data and do year over year comparisons of same day frozen numbers. You might also want to track admissions and prospect data to identify how long it takes for applicants to complete various steps and to do a funnel analysis in order to identify bottlenecks and where you are losing prospects. Maybe you have a student information system that loses previous values when things like admission basis are updated.

Is implementing Type II realistic? Honestly, it requires experts to set up and maintain, but querying data from Type II history tables is very intuitive. You should at least have a conversation about how this could benefit your institution. We would love to have that conversation with you.