FACT_EMPLOYEE_COUNT
This fact table supports reporting on the evolution of worker counts and capacity, e.g. headcount, fte count, joiners, and leavers.
This table contains one row per employee per month until the end of the next year.
Main relational fields
PERIOD_ID | Relate to DIM_PERIOD to identify the month. The Format is YYYYMM. Put a condition on this column to restrict the time range that is retrieved. |
EMPLOYEE_PK | Relate to DIM_EMPLOYEE to identify the historical properties of the employee. |
EMPLOYEE_NONHIST_PK | Relate to DIM_EMPLOYEE to identify properties of the employee at a given point in time. When using the relation based on this foreign key, include a restriction on DIM_EMPLOYEE using START_DT and END_DT or CURRENT_FL. |
EMPLOYER_PK | Relate to DIM_EMPLOYER. |
PAYROLL_ADMIN_ID | Identifier of the payroll partition for when you are using multiple entities |
Counters
These counters indicate the value at the end of the given month (identified with PERIOD_ID).
EMPLOYEE_AGE_NB | Age (in Years) of the employee at the end of the given month |
HEADCOUNT_NB | Values 1 or 0. Indicates whether the employee is included in the headcount |
IN_CNT_NB | Values 1 or 0. Indicates whether the employee is a new hire in the given month |
OUT_CNT_NB | Values 1 or 0. Indicates whether the employee is a leaver in the current month. When employees leave the company at the last day of the month, they are considered to be a leaver in the next month. |
NET_IN_CNT_NB | Values 1 or 0. Similar to IN_CNT_NB, but 0 indicates employees that joined and left in the same month. |
NET_OUT_CNT_NB | Values 1 or 0. Similar to OUT_CNT_NB, but 0 indicates employees that joined and left in the same month. |
OUT_1ST_MONTH_CNT_NB | Values 1 or 0. Similar to OUT_CNT_NB, but 1 only indicates if the employee left within the first month of employment. |
OUT_1ST_YEAR_CNT_NB | Values 1 or 0. Similar to OUT_CNT_NB, but 1 only indicates if the employee left within the first year of employment. |
FTE_CNT_NB | Values between 0 and 1. Similar to HEADCOUNT_NB, but it is expressed in full time equivalents. These values can be summed together to retrieve total FTE capacity in a given month. Be careful when using a sum because the table gives values on a monthly basis. Summing across multiple months (period_id) should be interpreted as capacity in "man-months". |
IN_FTE_CNT_NB | Values between 0 and 1. Similar to IN_CNT_NB, but expressed in full time equivalents percentage. |
OUT_FTE_CNT_NB | Values between 0 and 1. Similar to OUT_CNT_NB, but expressed in full time equivalents percentage. |
HEADCOUNT_FTE_NB | Deprecated and replaced by FTE_CNT_NB. Values between 0 and 100. Similar to HEADCOUNT_NB, but expressed in full time equivalents. These values can be summed to retrieve total FTE% capacity in a given month. Be careful when using a sum because the table gives values on monthly basis. Summing across multiple months (period_id) should be interpreted as capacity in "man-months". |
IN_FTE_NB | Deprecated and replaced by IN_FTE_CNT_NB. Values between 0 and 100. Similar to IN_CNT_NB, but expressed in full time equivalents percentage. |
OUT_FTE_NB | Deprecated and replaced by OUT_FTE_CNT_NB. Values between 0 and 100. Similar to OUT_CNT_NB, but expressed in full time equivalents percentage. |
