Skip to main content

Technical Manual - Employee Data Import Process

INTRODUCTION

The Signify System is not always the primary source of employee data and information needs to be imported from other sources. This document describes the employee data import process used by Signify Software to import and maintain employee information received from different sources using a number of import methods.  

IMPORT PROCESS METHODS

The employee data received from the client, will always be imported into a staging table first and then written into the Signify system. A number of stored procedures are executed once the information is in the staging table, to complete the import process.

All the import methods mentioned below, except the Excel Spreadsheet Template Import, can be scheduled to occur daily (even more than once a day), weekly or monthly as per the client’s requirements.

1. EXCEL SPREADSHEET TEMPLATE IMPORT

  • A comma delimited CSV file that can be imported via the existing front-end import system functionality.
  • This approach is used to:
    • Receive employee data from a client in an Excel spreadsheet.
    • Do tests and validations on the correctness of the employee data received.
    • Do a once off import of employee data to confirm the data, fields etc.
    • Schedule the automatic import.
  • The client captures the information in the standard Signify Excel employee import template and sends the information to Signify.
  • The import sheet layout has to be exactly the same as defined in the detail layout of the Excel template file described in the “Excel Import Template Layout” section.

2. SIGNIFY IS PROVIDED WITH A VIEW INTO THE CLIENT'S DATABASE

  • Client’s IT to populate view or staging table on the client’s database.
  • A scheduled automated job will import the data from the client’s database into the Signify staging table.
  • The standard import process will be followed.

3. CLIENT PLACES DATA FILE ON A FTP SERVER

  • Signify to provide layout format for import data file.
  • Signify to create a secure location for FTP files.
  • Client initiates a push action and creates a data file on the FTP Server.
  • A scheduled automated job will import the data from the client’s FTP data file into the Signify staging table.
  • The standard import process will be followed.

4. CLIENT PLACES DATA DIRECTLY INTO THE SIGNIFY DATABASE

  • Signify to provide layout format for the Signify staging table.
  • Signify to assign secure access to the Signify staging table.
  • Client import data directly into the Signify staging table.
  • A scheduled automated job will import the data from Signify staging table and the standard import process will be followed.

5. SIGNIFY READS DATA FROM THE CLIENT'S ACTIVE DIRECTORY

·         Client to provide Signify with Active Directory login details.

·         Signify to verify that they can connect to Active Directory.

·         Client to specify what information should be sourced from Active Directory.

·         A scheduled automated job will import the data from the client’s Active Directory into the Signify staging table.

·         The standard import process will be followed.

TYPICAL IMPORT PROCESS DIAGRAM

The process below is a diagrammatical representation of typical processes which can be followed to import client data.

IMPORT FILE LAYOUT

The employee data import template layout is described below.

Field NameMandatory / RecommendedDefault Value if Not ProvidedFormatMax No CharactersComments / Validations
EmployeeNumberMandatory Alphanumeric50No spaces in front of the number.
This number will be used to create the employee’s username for logging into the system.
Number must be unique per employee.
Can be omitted if setting is active within Signify HR to automatically generate employee numbers.
SurnameMandatory Alphanumeric500 
FullNameMandatory Alphanumeric500 
MaidenName  Alphanumeric500 
KnownAs = FullNameAlphanumeric500This field will be displayed on most reports and screens.
Initials  Alphanumeric20 
IDRecommended Alphanumeric50Populate with either a national ID number, an employee number or ensure a passport number is provided in the passport field
TelNoW  Alphanumeric (+## ## ### #### )200Numbers not in the specified format will be imported as a whole and not split into code etc.
(+27-12-345678 format only)
TelNoH  Alphanumeric (+## ## ### #### )200
CellNo  Alphanumeric (+## ## ### #### )200
EmailRecommended Alphanumeric200 
EmpTitle  Alphanumeric500All values used here will be made part of the master data tables.  Therefore ensure these values are correct if completed.
Location  Alphanumeric500 
OrgLevel1RecommendedCOMAlphanumeric100This is the highest level in the company.
The Org Code must be unique across all org levels.
OrgLevel1_DescriptionMandatoryCompanyAlphanumeric500This is the highest level in the company, usually the group/company's name.
OrgLevel1_Region  Alphanumeric4000Values inserted in this field will also be populated in the Master Data for the Address Region.
OrgLevel1_Province  Alphanumeric500Values inserted in this field will also be populated in the Master Data for the Business Address Province.
OrgLevel2  Alphanumeric100Only org level 1 is mandatory.  Levels 2 to 9 are optional and are to be used only if needed.
For each level (1-9) in the organisation structure: An arbitrary (unique) code as well as name/description for the node of the position within which the employee is appointed.
If no code is provided, a code is automatically generated by the employee import process.
If the combination of levels do not comply to the organisation structure rules (e.g. Level 2 can exist within Level 1, Level 4 can exist within Level 3 or Level 2, etc), the record will be rejected.
The same Organisation Node Code cannot be used in multiple Organisation levels or locations - i.e. the organisation node code per node must be unique within the file
OrgLevel2_Description  Alphanumeric500
OrgLevel2_Region  Alphanumeric4000
OrgLevel2_Province  Alphanumeric500
OrgLevel3  Alphanumeric100
OrgLevel3_Description  Alphanumeric500
OrgLevel3_Region  Alphanumeric4000
OrgLevel3_Province  Alphanumeric500
OrgLevel4  Alphanumeric100
OrgLevel4_Description  Alphanumeric500
OrgLevel4_Region  Alphanumeric4000
OrgLevel4_Province  Alphanumeric500
OrgLevel5  Alphanumeric100
OrgLevel5_Description  Alphanumeric500
OrgLevel5_Region  Alphanumeric4000
OrgLevel5_Province  Alphanumeric500
OrgLevel6  Alphanumeric100
OrgLevel6_Description  Alphanumeric500
OrgLevel6_Region  Alphanumeric4000
OrgLevel6_Province  Alphanumeric500
OrgLevel7  Alphanumeric100
OrgLevel7_Description  Alphanumeric500
OrgLevel7_Region  Alphanumeric4000
OrgLevel7_Province  Alphanumeric500
OrgLevel8  Alphanumeric100
OrgLevel8_Description  Alphanumeric500
OrgLevel8_Region  Alphanumeric4000
OrgLevel8_Province  Alphanumeric500
OrgLevel9  Alphanumeric100
OrgLevel9_Description  Alphanumeric500
OrgLevel9_Region  Alphanumeric4000
OrgLevel9_Province  Alphanumeric500
BirthdayRecommended1900/01/01YYYY/MM/DD500 
Race  Alphanumeric500 
EthnicGroup  Alphanumeric500 
Gender  Alphanumeric500 
JobCodeRecommended= MAX(All numeric Job Codes) + 1Alphanumeric255An arbitrary code used to uniquely identify a job e.g. ENG001.
If not provided in the file and a job exists in the system for the JobTitle, the existing JobCode will be used.
JobTitleMandatoryLearnerAlphanumeric255If the JobCode exists in the system and the current Title differs from the Title in the import, the Title in the system is updated to the value in the import.
PositionCodeRecommended= JobCode + . + [ MAX(All numeric Position Codes for JobCode) + 1 ]Alphanumeric255An arbitrary code used to uniquely identify a position linked to a job e.g. ENG001.1.
If not provided in the file and and a position exists in the system for the PositionTitle, the existing PositionCode will be used.
PositionTitleRecommendedLearnerAlphanumeric255If the PositionCode exists in the system and the current Title differs from the Title in the import, the Title in the system is updated to the value in the import.
OccupationalCategory_ShortCode  Alphanumeric10 
OccupationalCategory_Description  Alphanumeric255 
OccupationalLevel_ShortCode  Alphanumeric10 
OccupationalLevel_Description  Alphanumeric255Choose only from the following:
· Specialist or Middle manager;
· Technical worker or Junior management;
· Semi-skilled / discretionary decider;
· Unskilled / defined decider
InternalGrading  Alphanumeric255These values MUST be unique per Job.
OFOCode  Alphanumeric50 
EmploymentType_CodeRecommendedPermanentAlphanumeric50Double-check these values.  Inconsistencies/differences between values which should be the same will cause them to be treated as separate values.
EmploymentType_DescriptionMandatoryPermanentAlphanumeric500Double-check these values.  Inconsistencies/differences between values which should be the same will cause them to be treated as separate values
LineManagerEmployeeNumberRecommended Alphanumeric100The employee number of the line manager to whom the employee reports. Mainly used for HR Event Management, not critical in LMS.
The line manager automatically acts in the place of all other managers unless the others are populated.  Note that for every employee who is used as a line manager, there has to be a separate employee record in the import sheet or the line manager should already exist as an employee in the system.
LineManagerNameRecommended Alphanumeric500 
PerformanceManagerEmployeeNumber = LineManagerEmployeeNumberAlphanumeric100The employee number of the manager to whom the employee reports for performance management purposes.
PerformanceManagerName  Alphanumeric500 
DepartmentManagerEmployeeNumber  Alphanumeric100 
DepartmentManagerName  Alphanumeric500 
TrainingManagerEmployeeNumber = LineManagerEmployeeNumberAlphanumeric100The employee number of the manager to whom the employee reports for training approval purposes.
TrainingManagerName  Alphanumeric500 
DepartmentalManagerEmployeeNumber  Alphanumeric100 
DepartmentalManagerName  Alphanumeric500 
DealerPrincipalEmployeeNumber  Alphanumeric100 
DealerPrincipalName  Alphanumeric500 
LeaveManagerEmployeeNumber = LineManagerEmployeeNumberAlphanumeric100The employee number of the manager to whom the employee reports for leave approval purposes.
LeaveManagerName  Alphanumeric500 
FAISManagerEmployeeNumber  Alphanumeric100 
FAISManagerName  Alphanumeric500 
SalaryReviewManagerEmployeeNumber = LineManagerEmployeeNumberAlphanumeric100 
SalaryReviewManagerName  Alphanumeric500 
StartDateInCompanyRecommended(1) Start Date In Group
(2) Start Date In Position
(3) Start Date In System
(4) GETDATE()
(see comments)		YYYY/MM/DD10The employee’s start date in his current company if the client has a multi-company system configuration.
If this date is not provided in the import and a position for this employee in the Job/Position and Schema exists in the system, the date from the existing position is used.
StartDateInGroup = StartDateInCompany = StartDateInPosition
StartDateInGroupRecommended(1) Start Date In Company
(2) Start Date In Position
(3) Start Date In System
(4) GETDATE()
(see comments)		YYYY/MM/DD10The employee’s initial start date in the group as a whole if the client has a multi-company system configuration.
If this date is not provided in the import and a position for this employee in the Job/Position and Schema exists in the system, the date from the existing position is used.
StartDateInGroup = StartDateInCompany = StartDateInPosition
StartDateInPositionRecommended(1) Start Date In Group
(2) Start Date In Company
(3) Start Date In System
(4) GETDATE()
(see comments)		YYYY/MM/DD10The position the employee currently holds.
If this date is not provided in the import and a position for this employee in the Job/Position and Schema exists in the system, the date from the existing position is used.
StartDateInCompany = StartDateInPosition = TerminationDate
TerminationDateRecommended9999/12/31YYYY/MM/DD10If Signify is expected to import previous/terminated employees then this field should be provided
StartDateInPosition = TerminationDate
ReasonForTermination Resignation (if TerminationDate != 9999/12/31)Alphanumeric500Should only be provided if TerminationDate is present and not 9999/12/31
Nationality  Alphanumeric500 
HomeLanguage  Alphanumeric500 
Home_Country  Alphanumeric500 
Home_Region  Alphanumeric500 
Home_CityTownVillage  Alphanumeric500 
Home_PostalCode  Alphanumeric10 
Home_UnitNumber  Alphanumeric50 
Home_Complex  Alphanumeric500 
Home_StreetNo  Alphanumeric50 
Home_Street  Alphanumeric500 
Home_Address1  Alphanumeric500 
Home_Address2  Alphanumeric500 
Home_Address3  Alphanumeric500 
HomePostal_Address1  Alphanumeric500 
HomePostal_Address2  Alphanumeric500 
HomePostal_Address3  Alphanumeric500 
HomePostal_Country  Alphanumeric500 
HomePostal_Region  Alphanumeric500 
HomePostal_CityTownVillage  Alphanumeric500 
HomePostal_PostalCode  Alphanumeric10 
Business_FloorNumber  Alphanumeric50 
Business_Address1  Alphanumeric500 
Business_Address2  Alphanumeric500 
Business_Address3  Alphanumeric500 
Business_Country  Alphanumeric500 
Business_Region  Alphanumeric500 
Business_CityTownVillage  Alphanumeric500 
Business_PostalCode  Alphanumeric10 
PassportNumber  Alphanumeric50 
MaritalStatus  Alphanumeric500 
Citizenship  Alphanumeric500 
Disability  Alphanumeric500More than one disability can be added per employee. Use the semicolon to add more than one disability.
Customfield1  Alphanumeric2000Custom Fields are only imported if enabled per customer
Customfield2  Alphanumeric2000
Customfield3  Alphanumeric2000
Customfield4  Alphanumeric2000
Customfield5  Alphanumeric2000
Customfield6  Alphanumeric2000
Customfield7  Alphanumeric2000
Customfield8  Alphanumeric2000
Customfield9  Alphanumeric2000
Customfield10  Alphanumeric2000
Customfield11  Alphanumeric2000
Customfield12  Alphanumeric2000
Customfield13  Alphanumeric2000
Customfield14  Alphanumeric2000
Customfield15  Alphanumeric2000
Customfield16  Alphanumeric2000
Customfield17  Alphanumeric2000
Customfield18  Alphanumeric2000
Customfield19  Alphanumeric2000
Customfield20  Alphanumeric2000
PasswordIfNewEmployeeRecommended= Auto generatedAlphanumeric75System will never update password for existing employees.
System will only assign password for new employees.
System will force password change at first logon
SchemaIDMandatory1Numeric4Only required if more than one schema exists

IMPORT PROCESS STAGES

The import process kicks off by executing a validation process to determine if the information in the staging table is acceptable. If a specific record fails the validation process, this record will not be imported by the actual import. All the records that passed the validation will be imported.

The import process stages are as follows:

1. PRE-IMPORT

When the Excel Spreadsheet Template Import method is used, the import sheet layout has to be exactly the same as defined in the “Excel Import Template Layout” section. The entire import file can be rejected because:

  • The number of columns are not correct.
  • The column names are not correctly defined.
  • The fields contain commas, carriage returns or other special characters.

Before any changes are made, the process makes a backup of the import process file content which will be processed.

The following data validations are done before a record is inserted or updated in the Signify System to ensure that data integrity is maintained:

  • Date validations:
    • All the dates are in the correct format (YYYY/MM/DD).
    • Start date in company is after start date in group.
    • Start Date in Position is after start date in group and after start date in company.
    • Termination date is after Start Date in position.
    • Termination date is after Start Date in company.
    • Termination date is after Start Date in group.
  • Employee Data:
    • Employee’s employee number is populated.
    • Employee’s first name is populated.
    • Employee’s surname is populated.
    • Employee number is not duplicated in the import.
  • Job codes and positions:
    • A job title is provided for each employee.
    • Multiple employees are not appointed in the same position at the same time.
    • The same job title does not exist for different job codes.
  • Employment:
    • All employees have an employment type description.
    • All employees have at least one organisation level.
  • Schema ID is populated for each employee.

2. ACTUAL IMPORT

The information is imported in the following order once the validation has been completed.  

  1. Master data:
    1. Address related master data (e.g. Countries)
    2. Employment Types
    3. Titles
    4. Ethnic Groups
    5. Genders
    6. Races
    7. Marital Status
    8. Nationality
    9. Occupational Categories (OFO-related)
    10. Occupational Levels (OFO-related)
    11. OFO Codes
    12. Languages
  2. Org Structure Master Data (Codes & Names):
    1. Validation performed:
    2. Ensure that the Org Structure nodes for each record conform to all rules for the Org Structure as configured for the Schema (e.g. Level 3 node can be child of Level 1 or Level 2, Level 2 node can be child of Level 1, etc.)
    3. Some clients prefer to manually maintain their organisation structures.  In such a case Signify will only appoint employees in an org structure nodes that already exists.
  3. Job Profiler Master Data:
    1. Job Codes & Titles
    2. Position Codes & Titles
    3. Link Positions to the correct nodes in the Org Structure.
  4. Import of Basic Employee Details per employee (New or update existing)
  5. Import of Reporting Line Details per employee
  6. Import of Appointment Related Data  in the following order:
    1. Terminations
      • I.e. an employee’s current position is terminated
    2. Transfers
      • I.e. an employee transfer from one position to another is identified
    3. Corrections
      • I.e. the basic employee or appointment data must be corrected
    4. New Appointments
      • I.e. the employee and position is entirely new
  7. Further actions performed after employee import process has been completed:
    1. Create User Account if new employee and link to employee account (using an SP)
    2. Disable user accounts for terminated employees
    3. Update flat Org Structure for all Org Nodes (using an SP)
    4. Subgroups are created for managers and org structure levels.
  8. Further Customer Specific Actions:
    1. Import of Custom Field Details per employee  only if enabled per customer.
    2. Create/update all Employee Subgroups as needed
    3. Update FAIS Licence Codes, etc.
    4. Update Subsidiary Codes, etc.

Any records failing validation are excluded from the import and a validation log entry is created for these records  to populate exception reports from the import process.

Excel Spreadsheet Template Import method

1. PREPARING THE DATA

The following steps assume you have already populated and verified the data in the Signify Software Employee Data Import spreadsheet.  For a full discussion on populating the spreadsheet, please see the addendum later in this document.

2. IMPORTING THE DATA

After you have successfully populated the Employee Data Import spreadsheet, you can start with the import by following these steps.

1. Save the Excel spreadsheet as a Comma Delimited (CSV) file.

2. Open the Signify system, logging in as an Administrator.

 

3. Select System Administration from the Administration Menu

4. Highlight and open Import Data under System Administration | Import Data

5. Search for the relevant import template and click on the link in the Action column. Select the Import File link:

6. Once the Import File option is chosen, the below screen will be displayed:

image-1616952913724.png

When using a preconfigured import template, most of the import steps would be configured correctly.

  • Click Choose File and then navigate to your newly saved CSV file.
  • Click Save & Preview.
  • Tick “I verify that the preview of the below data is correct”.
  • Click Execute Mock Import to preview the imported information.
  • View the Mock Import results.

image-1616952921563.png

  • If the mock import results are correct, click on Execute Import.

image-1616952928236.png

  • Once you are satisfied with the results, click on Close.

4. HOW TO DOWNLOAD A SAMPLE FILE

  • A sample file will provide the exact data sets required for the import process to go through smoothly.
  • On the Home page select System Administration:
  • At the top menu on the System Administration tab, select the Import Data from the drop-down list.
  • Click on the link in the Action column and then select the Download sample file link.
  • Once the file has finished download the file can be opened and saved.

NOTE: the downloaded file will always be in CSV format.

5. EXCEPTION MANAGEMENT

An audit trail is kept for all records imported, updated and rejected and is available via the Signify report module. The audit records are only available for a few months after the import and are then automatically deleted.

The following are examples of exceptions messages that should be resolved before a record can be successfully imported and saved:

  • Incorrect date format(s)
  • StartDateInGroup ({0}) cannot be AFTER StartDateInCompany ({1})
  • StartDateInCompany ({0}) cannot be AFTER StartDateInPosition ({1})
  • StartDateInPosition ({0}) cannot be AFTER TerminationDate ({1})
  • Employee number duplicates in the import file.
  • There are different Job Titles using the same Job Code
  • Position is not unique, another employee in the import has the same position code
  • Appointment record overlaps another appointment record for employee OR another employee is appointed in the same position overlapping the time period specified. Start and End Date in a position is determined by Start Date In Position and Termination Date respectively.
  • LineManagerEmployeeNumber - 4779 - does not exist in the system or in the import file
  • DealerPrincipalEmployeeNumber - 4779 - does not exist in the system or in the import file
  • DepartmentManagerEmployeeNumber - CM01586 - does not exist in the system or in the import file
  • PerformanceManagerEmployeeNumber - CM01586 - does not exist in the system or in the import file
  • FAISManagerEmployeeNumber - 9442 - does not exist in the system or in the import file
  • TrainingManagerEmployeeNumber - CM03397 - does not exist in the system or in the import file
  • LeaveManagerEmployeeNumber - 9442 - does not exist in the system or in the import file
  • Appointment overlaps existing appointment for employee
  • Employee cannot be transferred to another schema with the same start date in position
  • An unexpected scenario or anomaly has occurred. Please review the appointment information for further information

Below find the exception reports available for the import process.

6. EMPLOYEE IMPORT SUMMARY AND DRILLDOWN REPORT

This is a drilldown report whereby the user can drill down on the number of ‘Exceptions’ (rejections) and the total number of ‘Records in Import’ per day.

The user can click on the hyperlinks in the summary report to see the detail information per employee.

image-1616952938975.png

7. EMPLOYEE IMPORT VALIDATION LOG E-MAIL

Currently the employee import process running at one of the Signify customers automatically sends an email to one of their employees containing the results of the import process, after the import process has completed.  This enables them to respond to any errors in the import file to correct the information when imported again.  See below an example of such import file.

FREQUENTLY ASKED QUESTIONS

1.       When does the Signify process, which picks up data from the client’s staging table, run?

The process can be scheduled according to the client’s needs and specifications e.g. every morning at 5:00 AM.

 

2.       How is this process initiated, is it a batch process or manual run?

A SQL job initiates the scheduled task.

 

3.       What does the process do exactly e.g. populate tables, perform any data validations?

·         Clear the Signify import table.

·         Copy all data from the client’s staging table to the Signify staging table.

·         Data validations are done before a record is inserted or updated in the Signify System to ensure that data integrity is maintained.

·         An audit trail is kept for all records imported, updated and rejected and is available via the Signify report module, since the amount of data generated, the audit is only available for a few months after the import date.

 

4.       What if there is no data in the client’s staging table, does it notify anyone that the process was unsuccessful or is no action taken?

Nobody will be notified that the client’s staging table contains no data. The SQL job will start as normal, but no data will result in no changes in the Signify system.

 

5.       If there are no changes found when comparing to the existing data within Signify to the new imported client data, what action is taken?

No update is done in the Signify system.

 

6.       If there are updates for existing records, does it delete the old record and insert a new record, or merely update the existing record with the new values?

Depending on the type of change, personnel information only updates the existing record, while a position change creates a new record.

 

7.       For new data, is it as simple as inserting the new record/s?

Yes, a new record is created for each new employee.

 

Back to top