RETURN TO FACT TABLE REPORTS LIST
Employee Star

SGHE Enterprise Data Warehouse
Fact Table Target: WFT_EMPLOYEE

Description Employee 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 people with any employee status except 'T' (Terminated) for the time frame. The active position indicator on the EMPLOYEE Dimension table will permit the exclusion of persons with no current active assignments in the time frame.

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

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

EMPLOYEE_POSITION

ANNUAL_SALARY

NO

NO

EMPLOYEE

DEMOGRAPHIC_KEY Key for the demographic dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE

EMPLOYEE_DEDUCTION_AMOUNT Employee cost; percentage of gross pay NUMBER

MONTHLY_DEDUCTION

EMPLOYEE_CONTRIBUTION_AMOUNT

NO

NO

EMPLOYEE

EMPLOYEE_KEY Key for the employee dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE

EMPLOYER_DEDUCTION_AMOUNT Employer cost; flat amount deduction per pay NUMBER

MONTHLY_DEDUCTION

EMPLOYER_CONTRIBUTION_AMOUNT

NO

NO

EMPLOYEE

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

EMPLOYEE_POSITION

ENCUMBRANCE_AMOUNT

NO

NO

EMPLOYEE

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

LEAVE_BENEFITS_AMOUNT Amount of leave benefits to be paid for this employee for all positions. This is a calculation that uses the remaining leave and the salary information to determine the annual leave amount. NUMBER

LEAVE_BALANCE

FUNCTION

NO

NO

EMPLOYEE

MULTI_SOURCE_KEY Key for the multi source dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE

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

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

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

EMPLOYEE

PERSON_UID

NO

NO

EMPLOYEE

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

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

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

ETL GENERATED

NA

NO

NO

EMPLOYEE

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

ETL GENERATED

NA

NO

NO

EMPLOYEE

TIME_KEY Key for the time dimension. NUMBER

ETL GENERATED

NA

NO

NO

EMPLOYEE

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

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

FUNCTION

NO

NO

EMPLOYEE

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

EMPLOYEE_EARNING_CY

EMPLOYEE_CONTRIBUTION_YTD

NO

NO

EMPLOYEE

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

EMPLOYEE_EARNING_CY

EMPLOYEE_CONTRIBUTION_YTD

NO

NO

EMPLOYEE

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

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

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

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

NO

NO

EMPLOYEE



Ver: 3.1