Understanding balanceAAP Data Import Requirements

KEY TOPICS:

  • Reference Tables
  • Data Tables
    • Date Parameters for Pulling Data
  • Optional AAP Data by Table
    • Grade Reference Table
    • Plan Code Reference Table (Master Plan)
    • Roster and Personnel Action Tables
    • (Optional) EEO-1/VETS-4212 Reporting: Establishment Information
    • Organization-Specific Reporting
  • File Structure Details (Field names, types, and character lengths)

Additional HELP

About the AAP Data Set

Plan data are separated into two categories (and stored in two Data modules, accordingly):

  • Reference Tables — Defining and encompassing all of the permitted data entries
  • Data Tables — Containing data entries, by workforce grouping and personnel action

The information below will help you prepare the AAP data set for Data > Import.

Reference Tables

Reference tables are the "legends" for codes used in Data table entries, describing the information that each code represents. Some codes, such as Job Code, may appear on multiple Data Tables. The following Reference Tables are used in AAP data:

  • Plan (Master Plan only)
  • Race (Default codes are built into system: Reference table optional for import)
  • Gender (Default codes are built into system: Reference table not required for import)
  • Job Group (and EEO Job Category)
  • Job
  • Department
  • Applicant Disposition
  • Termination
  • Codes for optional variables (e.g., Grade, Promotion, Transfer, Drilldown, Rollup)
  • Veteran

Note: Disability status does not require a Reference table.

Data Tables

The following tables provide key information about your organization’s current workforce, prospective employees, and recent hires, respectively:

  • Roster
  • Applicant
  • New Hire

Personnel action Data tables provide detail on events that involve your organization’s prospective and current employees:

  • Termination
  • Promotion
  • Transfer

Note: Supplemental Termination Pool and Promotion Pool tables may be brought in for more in-depth analyses.

The Reference and Data tables work together to define your organization’s plan. For an example, the Applicant record for each prospective employee would include data entries, among others, for Job Code (position applied for) and Applicant Disposition (outcome). These codes are defined in the Job Code and Applicant Disposition Code Reference tables.

Date Parameters for Pulling Records

  • The Roster should contain all active employees as of your organization’s annual plan date. For example, if the plan date is January 1, 2016, the Roster should include all active employees as of January 1, 2016.
  • Personnel action data must pertain to the year preceding the plan date. For example, if the annual plan date is January 1, 2016, your personnel action data would cover the time period from January 1, 2015 to December 31, 2015.
  • For an Update plan, data should reflect a six-month period between the annual plan date and the update plan’s effective date. A July 1, 2016 update plan would consist of a Roster as of July 1 and personnel actions from January 1, 2016 to June 30, 2016.
  • Applicant data should capture only those applicants for positions that were filled or closed during the plan year, regardless of when the applicant applied. Therefore, the requisition “closed” date should be used for determining which applicants to include in the data. While tracking and reconciling applicants and new hires is best done by including requisition number, individual application dates will suffice.

Optional AAP Data by Table

“Optional” data may help you take advantage of system automation, enhance plan calculations, provide organization-specific reporting, or produce an audit plan.

Note: Because the Reference and Data tables work together, mismatches between the tables, a missing Reference table, or a missing data column are likely to produce data Errors, even if a table is listed as optional.

See the table-related information below:

Grade Reference Table

If the Workforce Analysis report is chosen for the organizational profile, Grade and numerical Salary Midpoint are used to organize the report. If grades are present in the data, Compensation analyses can be run by grade.

Roster and Personnel Action Tables

Roster — Five-digit Zip code information is useful for calculating the Availability of personnel for jobs at your organization. If the Organizational Display report has been chosen for the Organizational Profile, optional fields for supervisor (Yes/No) and supervisor’s employee ID are helpful. To run the compensation reports, include Annualized Salary.

(Master Plan only) Work location is useful for determining employees who are Annotation Only, Reports In, or Reports Out.

Applicant — Either applicant ID or first and last name is required. Home Zip code is useful as an option for calculating the Availability of external candidates for jobs at your organization.

New Hire — Hire date is required by the system if information is to be included in File Consistency checks. Hire date is also used to determine the minimum/maximum number of employees for the VETS-4212 Report.

Promotion — Promotion data are useful in determining the Internal Availability of personnel for jobs within your organization. Promotion date is required by the system if information is to be included when the system runs File Consistency checks.

Promotion ID will support Adverse Impact analysis, acting like an internal requisition number that links employees who are promoted with the pool of those considered for selection. (Promotion and Termination Pools are outlined below.)

Termination — Termination date is required by the system if information is to be included when running File Consistency checks. Termination date is also used to calculate the minimum/maximum number of employees for the VETS-4212 Report.

Termination ID will support Adverse Impact analysis. (Promotion and Termination Pools are outlined next.)

Promotion and Termination Pools — In addition to the ID entries described directly above, a Data table will be necessary for each of the two personnel actions, identifying employees who are included in the respective pools.

This functionality, producing the ADVERSE IMPACT PROMOTION or TERMINATION POOL report is typically applied:

  • To competitive promotions, where the pool of employees is easily defined. Adverse Impact reporting is generally not used for career progression promotions.
  • When the pool of employees for termination is clear, such as with a reduction in force. The analysis is generally not used for voluntary terminations.

Cross-references:

Plan Code Reference Table (Master Plan)

To view plans on the balanceAAP Dashboard (in the Map object), populate a Zip code column for all location-based plans. Zip codes will also improve the accuracy of Availability calculations.

(Optional) EEO-1/VETS-4212 Reporting: Establishment Information

Demographic data are already present in the AAP data set.

Simple additions to the AAP data set (in particular for large organizations) can aid in producing EEO-1 or VETS-4212 reporting from balanceAAP. These additions identify establishments, including:

  • DUNS number
  • NAICS code (North American Industry Classification System)
  • FEIN/Tax ID (Federal Employer Identification Number)

Note: Multi-establishment employers should ensure that all locations are identified by code. In many cases, AAP Plan Codes (from a Master Plan) can be used as establishment codes.

Cross-references:

Organization-Specific Reporting

Drilldown — For optional reports that “drill down” into plan data, using variables not shown in standard tables, a Reference table may be included. Once a Drilldown is defined, correlating data entries on the Data tables are required by the system.

Example: A Functional Unit Reference table may be added to the system. This Reference table will contain codes for units (e.g., Sales, Marketing, Manufacturing, Administration). A correlating column must then appear on all Data tables, assigning each employee to the appropriate Functional Unit.

Rollup (Master Plan only) — For optional reports that “roll up” above plan level or extend across sub-plans, a Reference table may be provided and appropriate data included in the Data tables. Rollup data issues may cause inaccurate reporting, but will not lead to data Errors.