This article concerns daily demographic snapshots. For Annual Report data, click here: https://ciresconnect.helpjuice.com/pmt-annual-report-demographics

Set Up and Code

The demographic data are stored in a Type 2 Slow-Changing Dimension (SCD) format. The demographic.PersonHistory table holds rows for each employee (and some affiliates) along with ValidFrom and ValidTo dates. This saves space while still allowed information to be stored for each person for each day. The SCD data requires some special handling when calculating averages overtime, since ValidFrom and ValidTo dates must be taken into account.

In addition to the PersonHistory table, a PersonHistoryFriendly view exists that allows the user to view data in a human-friendly format.

The PersonHistory data is updated via an InsideCIRES cron at /root/cron/populatePmt/demographicSnapshot. This script runs daily. Changes are logged in human-readable format and displayed at https://insidecires.colorado.edu/pmt/reports/demographicLog.php. 

Data Tables 

Demographic data are stored in the “demographic” database. More information about these tables can be found at https://insidecires.colorado.edu/pmt/help/pmtTableHelp.php. Search on “demographic.” 

Discrepancies

With Annual Report Data

The Annual Report is not concerned with CIRES Fellows and only has a “Faculty” category. All Faculty who are Fellows (which is everyone who has a faculty appointment) is listed in this category. The annual report typically lists non-faculty Fellows as “Research Scientists.” In contrast, the demographic snapshots categorizes all Fellows as either “Faculty Fellows” or “Non-faculty Fellows.”

The annual report counts the number of unique people who worked during a fiscal year. For example, say Alice worked the first part of the FY, and then she left and was immediately replaced by Ben. The annual report would count both Alice and Ben as two people. In contrast, if a user requested the number of people that worked during the FY from the demographic data, the demographic page would average the daily snapshots. In this case, Alice and Ben would be counted as one person, since there was one FTE in the job.  

General Data Discrepancies

CIW may only report terminated appointments two weeks after the termination date. This is usually the case for student hourly positions. In these cases, the daily script counts people as active during this window. It does not attempt to retroactively correct the data to reflect the actual end date, so the averages may be very slightly inflated.