Core Employee Information

• Core.Employee: Central employee table containing:

  • Demographics
  • Contact information
  • Employment status
  • Current position details
  • Service dates
  • Basic compensation information

• Core.Dependent: Information about employee dependents and spouses:

  • Relationship type
  • Basic demographics
  • Benefit eligibility status

Position and Organization

• Position.Position: Detailed position information:

  • Job titles and codes
  • Department assignments
  • Position status
  • Compensation ranges
  • Position effective dates

• Position.Supervisor: Reporting relationships:

  • Direct supervisor assignments
  • Reporting hierarchies
  • Supervisor history

• Position.ServiceHistory: Employment milestones:

  • Position changes
  • Promotions
  • Service recognition dates
  • Historical position records

• Position.Skills: Employee qualifications:

  • Skills and competencies
  • Certifications
  • Licenses
  • Expiration dates

Benefits (Initial Release)

• Benefits.BenefitPlan: Available benefit plans:

  • Plan types and codes
  • Coverage levels
  • Provider information
  • Plan effective dates

• Benefits.Benefit: Employee benefit enrollments:

  • Current elections
  • Coverage levels
  • Enrollment dates
  • Basic cost information

Currently Available

1. Core.EmployeePersonalDataView

   • Consolidated employee personal information
   • Contact details
   • Dependent counts
   • Emergency contacts

2. Position.PositionOrgDataView

   • Current position details
   • Reporting structure
   • Department information
   • Skills and certifications

3. Position.EmployeeHistoryView

   • Service history
   • Position changes
   • Historical assignments

4. Core.EmployeeSummaryView

   • High-level employee dashboard
   • Key metrics and status

Payroll Data (Phase 2)

• Detailed payroll transactions
• Earnings history
• Direct deposit information
• Tax withholding details
• Payroll-related benefit deductions

Additional Benefits Features (Phase 2)

• Detailed benefit deductions
• Historical enrollment information
• Cost and contribution tracking
• Benefit-related payroll transactions

Time Off Tracking (Phase 3)

• Leave requests
• Attendance records
• PTO balances
• Time off patterns

Performance Data (Phase 3)

• Performance evaluations
• Goals and objectives
• Development plans

Available Roles

• [Schema]_Reader: Read-only access to specific schema data

  • Benefits_Reader
  • Core_Reader
  • Position_Reader
  • Payroll_Reader

• Employee_Admin: Full access to all data and functions

Requesting Access

1. Determine which role(s) you need based on your reporting requirements
2. Submit access request through your supervisor
3. Include justification for access level requested

1. Employee Count Validation

-- Compare active employee counts
SELECT COUNT(*) FROM Core.Employee WHERE IsActive = 1;
SELECT COUNT(DISTINCT EmployeeID) FROM Position.Position WHERE IsActive = 1;

These counts should match or have explainable differences.

2. Position Data Checks

-- Check for employees without positions
SELECT e.EmployeeID, e.EmployeeNumber, e.FirstName, e.LastName
FROM Core.Employee e
LEFT JOIN Position.Position p ON e.EmployeeID = p.EmployeeID
WHERE e.IsActive = 1 AND p.EmployeeID IS NULL;

3. Supervisor Relationship Validation

-- Identify employees without supervisors
SELECT e.EmployeeID, e.EmployeeNumber, e.FirstName, e.LastName
FROM Core.Employee e
LEFT JOIN Position.Supervisor s ON e.EmployeeID = s.EmployeeID
WHERE e.IsActive = 1 AND s.SupervisorID IS NULL;

4. Benefits Enrollment Verification

-- Check benefit enrollment counts
SELECT BenefitType, COUNT(*) as EnrollmentCount
FROM Benefits.Benefit
WHERE IsActive = 1
GROUP BY BenefitType;

5. Data Completeness Checks

For key reports, verify:

• No missing required fields
• Date ranges are appropriate
• No unexpected NULL values
• Reasonable value distributions

6. Historical Data Validation

For service history:

• Verify no gaps in employment dates
• Check for overlapping position assignments
• Validate promotion sequences

7. Pentegra Census Specific Checks

-- Verify all required fields are populated
SELECT COUNT(*) as MissingData
FROM Core.Pentegra_Census_View
WHERE Division IS NULL
   OR Department IS NULL
   OR JobTitle IS NULL;

Common Data Issues to Watch For

1. Employee Status Mismatches

   • Active employees without positions
   • Terminated employees showing as active
   • Missing hire or termination dates

2. Position Anomalies

   • Multiple active positions for one employee
   • Missing supervisor assignments
   • Invalid department codes

3. Benefits Data

   • Duplicate benefit enrollments
   • Missing coverage levels
   • Invalid effective dates

4. Service History

   • Gap in position history
   • Incorrect service dates
   • Missing position changes

Reconciliation Process

1. Run the validation queries provided above
2. Document any discrepancies found
3. Compare results with source systems when possible
4. Report issues to the HR Data Team for investigation
5. Document any known exceptions or legitimate differences