Human Resources Data Store — HRDS

The Human Resources Data Store (HRDS) contains data from the People @ Columbia System (PAC). The People @ Columbia System (PAC) is used by the Human Resources Department to maintain the University's personnel records. Through PAC, Human Resources keeps information on regular employees and retirees accurate and up-to-date by registering personnel changes as they occur. The Data Warehouse contains employment status information for individual employees and retirees. Data is updated daily and is available from 1995 to present; prior years’ data is archived and available on request.

The following information about the HRDS is available:

• HRDS - Human Resources Entity Relationship Diagram
• Human Resources Data Refresh Schedule
• Human Resources Report Inventory
• Tables
• HRDS Job Aids and Tools

HRDS - Human Resources Entity Relationship Diagram

Human Resources Data Refresh Schedule

EMPLOYEE:

An individual with whom the University maintains a regular employment relationship, i.e., one who on a regular basis provides services of benefit to the University. The EMPLOYEE table contains static demographic information, unrelated to the individual's position.

Primary Key:

PS_Employee_Id

Data Warehouse Table Joins:

EMPLOYEE_LEAVE
EMPLOYEE_POSITION
EMPLOYEE_DISTRIBUTED_PAY
RESEARCH_STAFF_MEMBER

Extract Logic:

Data in the EMPLOYEE table is static; that is: (a) changes normally are not made during an individual's employment at the University (e.g., gender, date of birth) or (b) when changes are made, only the revised data need be stored for the Data Warehouse.

There is one, and only one, row on the EMPLOYEE table for each individual who currently holds, or who held an active position or was on leave of absence from a position, any time during the period September, 1990 to the present. If a value for a particular employee changed in PAC during that period, the EMPLOYEE table reflects the last value in PAC for the period. Previous values are not stored in Data Warehouse on the EMPLOYEE table..

Frequency:

The EMPLOYEE table is updated as part of the PAC extract procedure which occurs daily.  HR data prior to the implementation of PAC (Fall 2004) is archived and available upon request.

EMPLOYEE_POSITION:

The record, if any, of leaves of absence by a particular employee from a specific position within the University. The EMPLOYEE LEAVE table documents leave information for a particular point in time. (Source: People @ Columbia System)

Primary Key:

Department_Number
PS_Employee_Id
Employee_Record_Number
Term_Calendar_Year
Term_Code
Leave_Of_Absence_Type_Code
Leave_Of_Absence_Begin_Date

Data Warehouse Table Joins:

EMPLOYEE
EMPLOYEE_DISTRIBUTED_PAY
EMPLOYEE_POSITION
RESEARCH_STAFF_MEMBER

Extract Logic:

On the EMPLOYEE_POSITION table in Data Warehouse, there are multiple rows for each employee: at least one row for each employee active or on leave during each academic term from the autumn term, 2004, through the most recently concluded term.

There would be several rows, therefore, for someone who was employed during the entire period. If neither the employee's position nor data about the position (e.g., salary) changed during the period, the rows would be identical (except for the term identifier. For an employee who concurrently held multiple positions, there would be two or three rows for each specific period of time, resulting in a greater number of rows for that individual. The data has been arranged so that if an employee has more than one active or on-leave position in a given period of time, the 'primary' position will always have a value of '1' in the Position_Number column. A join from the EMPLOYEE_POSITION table based on PS_Employee_Id, may produce double or triple counts in the results unless the selection is limited.

Frequency:

The EMPLOYEE_POSITION table is updated as part of the PAC extract procedure which occurs daily. HR data prior to the implementation of PAC (Fall 2004) is archived and available upon request.

EMPLOYEE_LEAVE:

The fulfillment by an employee of a specific position within the University. The EMPLOYEE_POSITION table contains information at a specific point in time, which is identified on each row, specific to the individual, and specific to the position which he or she held at that time.

Primary Key:

Department_Number
PS_Employee_Id
Term_Calendar_Year
Term_Calendar_Code
Employee_Position_Number

Data Warehouse Table Joins:

EMPLOYEE
EMPLOYEE_DISTRIBUTED_PAY
EMPLOYEE_LEAVE
RESEARCH_STAFF_MEMBER

Extract Logic:

For each row on the EMPLOYEE_POSITION table where the employee has taken an authorized leave from that position during the specified academic term, there is a corresponding row on the EMPLOYEE_LEAVE table with information about the leave. It is possible for an employee to have more than one leave during a particular academic term; in that situation each of the leaves will be represented. For an employee who concurrently held multiple positions and was on leave in each of those positions, there would be two or three rows for each academic term the employee was on leave.

Frequency:

The EMPLOYEE_LEAVE table is updated as part of the PAC extract procedure which occurs daily. HR data prior to the implementation of PAC (Fall 2004) is archived and available upon request.

Human Resources Report Inventory

Source Application: Data Warehouse

HRDS Job Aids and Tools

• HR Data Reports Job Aid - provides guidance for accessing the 'HR Data Reports' from the HR Manager Resources tab in myColumbia.
• HR Data Reports Training Guide - provides guidance for accessing and generating HR Data Reports.