Employee_Data

satori-group-logo.gif

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.)

Idx Field Type Maximum Length Required
1 Year Alphanumeric 4 Yes
2 Employee_Code Alphanumeric 20 Yes
3 Hire_Date Date/Time   No
4 Termination_Date Date/Time   No
5 Exemption_Status Character 1 No
6 Hours Decimal 18, 3 No
7 Review_Date Date/Time   No

 

Summary

Employee_Data

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

Columns

 

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.

Documentation

 

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 Format
Type Alphanumeric
Maximum Length 4
Required Yes

Employee_Code

Employee_Code varchar(

20

) NOT NULL

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

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

Hire_Date

Hire_Date datetime NULL

Date timestamp of the hire date. Optional.

 

 

Field Load File Format
Type Date/Time
Required No

Termination_Date

Termination_Date datetime NULL

Date timestamp of hire termination date. Optional.

 

 

Field Load File Format
Type Date/Time
Required 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 Format
Type Character
Maximum Length 1
Required No

Hours

Hours decimal(

18,

3

) NULL

Current year-to-date billable hours. Optional.

 

 

Field Load File Format
Type Decimal
Maximum Length 18, 3
Required 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 Format
Type Date/Time
Required 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.