Data Mapping - AmericanManufacturingCohort/HR-CDM-Data-Transformation GitHub Wiki

PERSON
EMPLOYEE
HR_EVENT
EMPLOYMENT_RECORD
WORK_SITE
LOCATION
SITE_EVENT
SITE_HISTORY

A number of considerations were made when deciding the data mappings:

• The unique employee number (eessno) is not used to connect the data between the HR-CDM tables, but instead the newly generated employee_id or person_id is used as the primary key to make the interconnection.
• The variables person_id and employee_id are in parallel, meaning, both are sharing the same unique identifier that can refer to the same subject human (either as a person or an employee).
• Each row in the EMPLOYMENT_RECORD table has one corresponding event data in the HR_EVENT table.
• Each row in the EMPLOYMENT_RECORD table depicts a snapshot of one's employment history (e.g., job title, work location, amount salary, etc.) in a given time. The HR event that causes the record to be produced is documented through a reference to the HR_EVENT table.
• Mappings for the variable work_state in the EMPLOYMENT_RECORD table take into account multiple variables in the source data set, feed them into our pre-defined decision rules and obtain the output.
• Records in the LOCATION table define the physical location or address information of work sites.
• Records in the WORK_SITE table map a physical location to a name that corresponds to a certain function (e.g., factory, warehouse, headquarter).
• Each row in the SITE_HISTORY table depicts a snapshot of a work site (e.g., name, ownership, function, etc.) in a given time. The work site event that causes the update in the site history is documented through a reference to the SITE_EVENT table.
• Currently, there are no designated vocabularies, domains or classes that can represent an HR event, employment status, and employment type.

We used these data mappings above to create data transformation scripts written in the R language and produce a collection of CSV files as the output.

Proceed to Step 3: Data Loading