RETURN TO FACT TABLE REPORTS LIST
Employee Position Star

SGHE Enterprise Data Warehouse
Fact Table Target: WFT_EMPLOYEE_POSITION

Description Employee Position Fact table provides measures that may be compared and displayed based on the dimensions in this star schema. This set of measures is stored per person per time frame per non-terminated employee per position.

Target Column Business Definition Database Data Type Source Name Source Column Local Source Local Target Star Name
ADMINISTRATION_KEY Key for the administration dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

ANNUAL_SALARY Annual salary for the position based on the calendar year. NUMBER

EMPLOYEE_POSITION

ANNUAL_SALARY

NO

NO

EMPLOYEE POSITION

DEMOGRAPHIC_KEY Key for the demographic dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

EMPLOYEE_DEDUCTION_AMOUNT Employee cost; percentage of gross pay NUMBER

MONTHLY_DEDUCTION

EMPLOYEE_CONTRIBUTION_AMOUNT

NO

NO

EMPLOYEE POSITION

EMPLOYEE_KEY Key for the employee dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

EMPLOYER_DEDUCTION_AMOUNT Employer cost; flat amount deduction per pay NUMBER

MONTHLY_DEDUCTION

EMPLOYER_CONTRIBUTION_AMOUNT

NO

NO

EMPLOYEE POSITION

ENCUMBRANCE_AMOUNT Encumbered amount for the employee in all active positions and jobs. NUMBER

EMPLOYEE_POSITION

ENCUMBRANCE_AMOUNT

NO

NO

EMPLOYEE POSITION

HOURLY_FTE FTE or portion of an FTE represented by the employee in all the active positions and jobs, if the job has an hourly salary (otherwise zero). NUMBER

EMPLOYEE_POSITION

ANNUALIZED_FTE

NO

NO

EMPLOYEE POSITION

JOB_KEY Key for the job dimension. This key permits all of the information below to be only for this position and job suffix and not all positions if more than one is held by the employee. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

MULTI_SOURCE_KEY Key for the multi source dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

OTHER_EARNINGS Total other earnings for this employee. This will be all the earnings for the employee on the MPT_EMPLOYEE_EARNING_CY that have an EARNINGS code of Other and all positions. This can be indicated by the EDW cleansing rules that combine groups of codes together for this purpose. NUMBER

EMPLOYEE_EARNING_CY

FUNCTION

NO

NO

EMPLOYEE POSITION

OVERTIME_EARNINGS Overtime earnings for this employee. This will be the earnings for the employee on the MPT_EMPLOYEE_EARNING_CY that have an EARNINGS code of Overtime. This can be indicated by the EDW cleansing rules that combine groups of codes together for this purpose. NUMBER

EMPLOYEE_EARNING_CY

FUNCTION

NO

NO

EMPLOYEE POSITION

PERSON_UID System internally generated ID to uniquely identify the person. NUMBER

EMPLOYEE

PERSON_UID

NO

NO

EMPLOYEE POSITION

POSITION_KEY Key for the position dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

REGULAR_EARNINGS Total regular for this employee. The employee earnings on the MPT_EMPLOYEE_EARNING_CY that have an EARNINGS code of Regular. This can be indicated by the EDW cleansing rules that combine groups of codes together for this purpose. NUMBER

EMPLOYEE_EARNING_CY

FUNCTION

NO

NO

EMPLOYEE POSITION

SALARIED_FTE FTE or portion of an FTE represented by the employee in all active positions and jobs, if the job is salaried (otherwise zero.) NUMBER

EMPLOYEE_POSITION

ANNUALIZED_FTE

NO

NO

EMPLOYEE POSITION

SYSTEM_LOAD_PROCESS Name of the transformation that created the row during the load activity process. VARCHAR2(30)

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

SYSTEM_LOAD_TMSTMP Most current date this record was created or updated. DATE

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

TIME_KEY Key for the time dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE POSITION

TOTAL_EARNINGS Total earnings for this employee. This will be the earnings for the employee on the MPT_EMPLOYEE_EARNING_CY for a particular position when looking at the information on the WFT_EMPLOYEE. NUMBER

EMPLOYEE_EARNING_CY

FUNCTION

NO

NO

EMPLOYEE POSITION

YEARS_OF_SERVICE Number of years the employee has been in any position with the institution. Need to look at all positions and count the number of years. NUMBER

EMPLOYEE

CURRENT_HIRE_DATE

NO

NO

EMPLOYEE POSITION

YTD_EMPLOYEE_DEDUCTION_AMOUNT Year to date employee deduction amount for all positions. NUMBER

MONTHLY_DEDUCTION

EMPLOYEE_CONTRIBUTION_YTD

NO

NO

EMPLOYEE POSITION

YTD_EMPLOYER_DEDUCTION_AMOUNT Year to date employer deduction contribution for all this employee's deductions for all positions. NUMBER

MONTHLY_DEDUCTION

EMPLOYER_CONTRIBUTION_YTD

NO

NO

EMPLOYEE POSITION

YTD_OTHER_EARNINGS Year to date total other for this employee in this position and job. This will be the sum of all the earnings for the employee on the EMPLOYEE_EARNING_CY reporting view that have an EARNINGS code of Other. This can be indicated by the EDW cleansing rules that combine groups of codes together for this purpose. NUMBER

EMPLOYEE_EARNING_CY

TOTAL_EARNINGS_YTD

NO

NO

EMPLOYEE POSITION

YTD_OVERTIME_EARNINGS Year to date total overtime for this employee in this position and job. This will be the sum of all the earnings for the employee on the EMPLOYEE_EARNING_CY reporting view that have an EARNINGS code of Overtime. This can be indicated by the EDW cleansing rules that combine groups of codes together for this purpose. NUMBER

EMPLOYEE_EARNING_CY

TOTAL_EARNINGS_YTD

NO

NO

EMPLOYEE POSITION

YTD_REGULAR_EARNINGS Year to date total regular for this employee in this position and job. This will be the sum of all the earnings for the employee on the EMPLOYEE_EARNING_CY reporting view that have an EARNINGS code of Regular. This can be indicated by the EDW cleansing rules that combine groups of codes together for this purpose. NUMBER

EMPLOYEE_EARNING_CY

TOTAL_EARNINGS_YTD

NO

NO

EMPLOYEE POSITION

YTD_TOTAL_EARNINGS Year to date total earnings for this employee. This will be the earnings for the employee on the EMPLOYEE_EARNING_CY reporting view for this particular position. NUMBER

EMPLOYEE_EARNING_CY

TOTAL_EARNINGS_YTD(SUM)

NO

NO  


Ver: 3.1