Data Selection

The AMC Human Resources (AMC-HR) data set is originated from two database sources, namely, PeopleSoft and Oracle. The two sources do not overlap to each other and both cover a different time period, that is, the PeopleSoft database contains records from 1996 to 2008 and the Oracle database contains records from 2008 to 2013. However, after the system migration, not all variables from PeopleSoft were migrated to Oracle using the same names or used at all causing some data are misaligned.

Our data transformation pipeline involved a data preparation step to fix the misalignment issue, which is called the data selection step. The goal is to have a consistent data schema across the two sources before deciding on the data mappings. We identified 29 key variables for the new data schema and for each key variable we looked for a matching column in the PeopleSoft and Oracle data schemas. The table below shows the list of the key variables along with the matching columns found in PeopleSoft and Oracle.

Description	Key Variable	PeopleSoft	Oracle
Primary employee identifier	eessno	eessno	eessno
Employee’s ethnicity	ethnicde	ethnicde	ethnicde
Employee’s sex	sex	sex	sex
Date of birth	dobdt	dobdt	dobdt
Date of job hiring	hiredt	hiredt	hiredt
Date of job termination	termdt	termdt	termdt
Date of death	deathdt	date_of_death	date_of_death
Date of HR event	effdtdt	effdtdt	effdtdt
HR action code	actioncd	actioncd	--
HR action name	action	actionde	action
HR action description	actionde	actionde	action
HR action reason code	reasoncd	reasoncd	--
Job location code	locatcd	locatcd	locatcd
Job location name	locatdes	locatdes	locatdes
Employment status	empstats	empstats	--
Employment type	emptype	emptype	compfreq
Job code	jobcode	jobcode	hourly_pv_jobcode
Job category	jobfamily	--	oracle_jobfamily
Job title	jobtitle	jobtitle	hourly_pv_jobtitle
Department code	deptcode	deptabr	deptcode_cd
Department name	deptname	deptname	deptname
Business unit code	bucode	busunit	--
Business unit name	buname	bundesc	bundesc
Pay group	paygroup	paygroup	paygrades
Compensation rate	comprate	comprate	comprate
Annual salary	annual	annual	annual
First risk score	first_risk_score	first_risk_score	frist_risk_score
Risk score by year	risk_score_year	risk_score_year	risk_score_year
Dataset source name	source	“PV”	“OR”

We created a script to extract values from those columns in their corresponding data source, transform them using the key variables and load them into a new table that we called the "combined" data set. In step 2, we will use this combined data set to produce the main data mappings that will transform its structure to the HR-CDM data schema.

Proceed to Step 2: Data Mapping