Running the UW Frozen Snapshot of Employee Data View in EPM
OverviewThis view is designed to facilitate trend analyses. It gives a fixed (frozen) snapshot of a time period (October or March) used frequently as a date of record for analyses. This data view is designed to have the same individuals as those included on the InfoAccess October and March Payroll analytical views, but also includes individuals not paid in October (or March) who have active jobs, such as $0 appointments or individuals on leave.
- Data is added to the view each year. The field UW_REPORTING_YEAR is used to identify the year associated with the data.
- Because it is fixed, it is not normally updated after it is created each year. For example, a query of October 2012 data run in Dec 2012 or in May 2014 will return identical results. In contrast, using ALLJOB or other views that are refreshed regularly will incorporate any retroactive changes and may return different results if the query is run on different dates.
- Each unique combination of EMPLID, EMPL_RCD and UW_JOB_START_DATE is considered a job and will generate a separate record. In addition, if a faculty member has more than one tenure home or faculty relation department, a separate record is generated for each combination of tenure home (EG_OTH_DEPT) and faculty relation department (UW_FACREL_DEPTID).
- PS_UW_HR_OCTJOB_VW (October Frozen Job Data View) contains a snapshot of job, person and tenure data related to people with active jobs during October. A corresponding data view, UW_HR_MARJOB_VW, contains a snapshot of all active jobs and employees during March. The views include both paid and unpaid jobs. An individual with more than one job during the month will have a record for each job held.
- The October and March Payroll views are sourced from the budget system and are still maintained in InfoAccess, not EPM. See InfoAccess OCTOBER_PAYROLL and MARCH_PAYROLL data views.
For years prior to 2011, annual October frozen snapshots of employee data are available from the legacy system in InfoAccess. See OCTOBER_FROZEN_APPT for 1997 through 2010.
An individual with more than one job during the month will have a record for each job held. This includes jobs held simultaneously and jobs held sequentially. If a person transfers to a new job during the month and the new job has a different UW_JOB_START_DATE, both the job transferred from and the job transferred to will be included. Any job that is active for at least one day in the month is included. Canceled jobs that start and end on the same date are excluded from the view.
If rate, FTE, title, pay basis, or other attributes of a particular job are changed during the month, the attributes with the latest effective date during the month are reported.
Example: Records from ALLJOB showing a promotion with jobcode change and rate change during March
Records for same individual contained in MARJOB:
Sample analyses addressed with this view:
- Annual Accountability Report to UW Board of Regents: counts of employees by employee type, gender, race/ethnicity, international status
- Annual Data Digest: counts of the number of employees by employee type, job category, gender and race/ethnicity -- ten year time trend
- IPEDS HR reporting of employees by job category, tenure status, gender, race/ethnicity, international status, salary, and salary basis. Mandatory report to federal government each year based on October data.
- Faculty salary scatterplots of salaries by department by faculty rank and gender, prepared annually. Used for setting salaries, pay equity considerations.
- Department Planning Profiles. Report run each semester with data on job security and tenure status by academic department. Useful for program review and budget planning.
- Job Security report. Annual report to Academic Staff on the number of academic staff by division, type of job security and gender.
- Academic Analytics. Annual list of faculty and Academic Staff principal investigators by faculty tenure home, primary department, and faculty relation departments.
- US News and World Report and other national and international surveys and rankings requesting counts of faculty by gender, race, citizenship.
Example Types of Data Included:
- Identifiers: Emplid, Empl_Rcd, Name, Position Number
- Sensitive Demographic data: birthdate, gender, ethnic/race heritage, international visa status
- Employment data: job code, employee class, pay basis, annual salary, FTE, FLSA status, working title, job continuity, job security and guaranteed length, salary grade, UW Job Start Date, salary effective date, job title effective date, bargaining unit, salary minimum and maximum, etc.
- Faculty data: tenure home, tenure FTE, faculty relationship, faculty relation department, divisional affiliation, sabbatical percent, sabbatical start and end dates, mandatory tenure review date, etc.
- UW Madison Data Digest: https://apir.wisc.edu/data-digest/
- Accountability Report Dashboard: https://www.wisconsin.edu/accountability/
- EPM access
- EPM Data Dictionary: https://uwservice.wisc.edu/administration/data-warehouse.php#dd
- InfoAccess OCTOBER FROZEN APPT view and OCTOBER PAYROLL and MARCH PAYROLL views: https://data.wisc.edu/infoaccess/available-data-views/budget/