Employee_Data

Used as the core source for loading the Employee Data fact table and the Xchange Employee Data cube in Planning.  This file contains the current population of fee earners and administrative staff for loading of the budget.  The hire and termination dates should be provided in SQL date time format (e.g., 1985-09-16).

The system does not treat hourly employees differently.  Their full annual compensation for the year should be calculated and entered into the system. They will be budgeted for in the same way as other employees.

Hours should be provided as of the “month end” as specified in the configuration parameters in the Planning Application.

For exemption status, currently the only status that in use is 'E', which is used to drive the calculation of overtime in the Planning Application.  (If the field is not = 'E', then it multiplies the Salary times the overtime percentage rate as defined in the Planning Application.)

IdxFieldTypeMaximum LengthRequired1Year2Employee_Code3Hire_Date4Termination_Date5Exemption_Status6Hours7Review_Date
Alphanumeric 4 Yes
Alphanumeric 20 Yes
Date/Time   No
Date/Time   No
Character 1 No
Decimal 18, 3 No
Date/Time   No

 

Summary

Employee_Data

Columns

Documentation

Used as the core source for loading the Employee Data fact table and the Xchange Employee Data cube in Planning.

 

Year

Year associated with the data values.   Required.

Employee_Code

Employee code associated to the employee; must match entry in EmployeeRequired.

Hire_Date

Date timestamp of the hire date. Optional.

Termination_Date

Date timestamp of hire termination date. Optional.

Exemption_Status

Indicate value of ‘E’ if employee is exempt from receiving overtime for calculating the budget. Optional.

Hours

Current year-to-date billable hours. Optional.

Review_Date

Date timestamp of employee’s review date. Optional.

 

Business Rules

These rules govern creation of this table and cover what the column does and how it may be calculated.

Sample CSV Load File

This load file is a small sample of an Employee Data load.

Sample CSV Load File

This load file is a small sample of an Employee Data load.

Filename: Employee_Data.csv

2007,00032,1985-09-03,,E,916.50,2008-09-03 2007,00006,1980-01-01,,,10.0,2008-01-01 2007,00046,1987-01-01,,E,1947.25,2008-01-01 2007,00075,1985-09-16,,E,974.75,2008-09-16 2007,00084,1988-01-01,,E,1923.70,2008-01-01 2007,00094,1966-01-01,,E,146.00,2008-01-01 2007,00076,1982-01-01,,E,317.80,2008-01-01

Columns

Year

Year varchar(

4

) NOT NULL

Year associated with the data values.   Required.

 

 

Field Load File FormatTypeMaximum LengthRequired

Alphanumeric
4
Yes

Employee_Code

Employee_Code varchar(

20

) NOT NULL

Employee code associated to the employee; must match entry in EmployeeRequired.

 

 

Field Load File FormatTypeMaximum LengthRequired

Alphanumeric
20
Yes

Hire_Date

Hire_Date datetime NULL

Date timestamp of the hire date. Optional.

 

 

Field Load File FormatTypeRequired

Date/Time
No

Termination_Date

Termination_Date datetime NULL

Date timestamp of hire termination date. Optional.

 

 

Field Load File FormatTypeRequired

Date/Time
No

Exemption_Status

Exemption_Status char(

1

) NULL

Indicate value of ‘E’ if employee is exempt from receiving overtime for calculating the budget. Optional.

 

 

Field Load File FormatTypeMaximum LengthRequired

Character
1
No

Hours

Hours decimal(

18,

3

) NULL

Current year-to-date billable hours. Optional.

 

 

Field Load File FormatTypeMaximum LengthRequired

Decimal
18, 3
No

Review_Date

Review_Date datetime NULL

Date timestamp of employee’s review date. Optional.

 

10/22/2008: Placed this at the end of the definition to support older file formats

 
 

 

Field Load File FormatTypeRequired

Date/Time
No

 

Business Rules

These rules govern creation of this table and cover what the column does and how it may be calculated.

Calculated Columns

Hours

Total year to date number of billable hours for the employee.

Exemption_Status

If employee is exempt from receiving overtime then ‘E’ otherwise leave blank.

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk