Understanding balanceAAP Data Import Requirements

EXCEL TEMPLATES

For standard plans:

• Single Plan Data Collection
• Master Plan Data Collection

Required template fields are shown in bold.

For plans with Drilldowns or Master Plan Rollups:

• Single Plan with Enhanced Features Data Collection
• Master Plan with Enhanced Features Data Collection

Template fields necessary for Drilldown and Rollup reporting are shown in green.

For plans with EEO-1/VETS Reporting:

• Master Plan Data Collection with EEO-VETS

DOWNLOAD GUIDE FOR USE OUTSIDE balanceAAP:

• BALANCEaap Data Requirements Guide

About Reference and Data Tables

Plan data in balanceAAP are separated into two categories:

• Reference Tables (that define data codes)
• Data Tables (that house data entries)

The information below will help you prepare the AAP data set for Import into balanceAAP.

Additional HELP

• For descriptions of fields, field types, and character lengths by table, see: File Structure Details.

• For Help on how to edit records and work with other balanceAAP tools after data are imported, see: Reference Tables and Data Tables.

About Reference Tables

Reference tables house codes used in Data table entries and describe 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 Plans 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
• Optional variables (e.g., Grade, Promotion, Transfer, Drilldown, Rollup)
• Veteran

Note: Disability status does not require a Reference table.

About Data Tables

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

• Roster
• Applicant
• New Hires

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

• Terminations
• Promotions
• Termination and Promotion Pools (Optional)
• Transfers

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, 2014, the Roster should include all active employees as of January 1, 2014.
• Personnel action data must pertain to the year preceding the plan date. For example, if the annual plan date is January 1, 2014, your personnel action data would cover the time period from January 1, 2013 to December 31, 2013.
• 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, 2014 update plan would consist of a Roster as of July 1 and personnel actions from January 1, 2014 to June 30, 2014.
• 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

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

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:

Plan Code Reference Table (Master Plan only) – To view plans on the BALANCEhub DASH, include a Zip code for all location-based plans. Zip codes will also improve the accuracy of External Availability calculations.

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 – Five-digit zip code information is useful for calculating the External 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 Plans 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 External Availability of personnel for jobs at your organization.

New Hires – 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 EEO-1/VETS-100A reporting.

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.

Terminations – 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 EEO-1/VETS-100A reports.

Quick Tip: Templates for the optional reporting described below are linked at the top of this page.

(Optional) EEO-1 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 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 can be used as establishment codes.

Cross-reference For more information on tools for developing EEO-1 reporting, refer to:EEO-1 .

Each physical location with less than 50 employees should have a unique establishment code. Please review the current Federal regulations.

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.

For

example,Example: aA 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.

Rollups (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.