Headcount analytics report shows incorrect totals compared to core HR data

HCM Oracle HCM Cloud
, , , , , , , ,
1

Workforce analytics headcount reports in ohcm-23c are showing incorrect totals compared to what we see in core HR data. Executive dashboard displays 1,847 active employees but core HR shows 1,923 active employees - a discrepancy of 76 employees.

We’ve checked the ETL refresh schedule and employee status mapping but can’t pinpoint the issue. The effective dating logic might be involved since the numbers were accurate until the beginning of this month. Data warehouse synchronization appears to be running but we’re getting this persistent data mismatch between analytics and transactional systems.

Query results:


Analytics Subject Area: Workforce Headcount
Active Employee Count: 1,847
As of Date: 2025-06-19

Inaccurate executive dashboards are causing major trust issues with our reporting. Anyone experienced similar ETL or effective dating problems with workforce analytics?

2

Navigate to Setup and Maintenance > Search for ‘Manage Workforce Analytics Status Mappings’. You’ll see a list of all employee statuses and which category they map to (Active, Inactive, Terminated, etc.). If ‘Extended Leave’ isn’t mapped or is mapped to ‘Inactive’, those 76 employees won’t appear in your active headcount analytics even though they’re active in core HR.

3

This sounds like an ETL lag issue. Even though the ETL is scheduled, if it’s failing or not completing fully, you’ll see stale data in analytics. Go to Scheduled Processes and check the status of the ‘Refresh Workforce Analytics Data’ job. Look for any errors or warnings in the process log. Also verify the last successful completion timestamp matches your expected refresh frequency.

4

Good point about status mapping. We did add a new ‘Extended Leave’ status on June 1st for employees on parental leave. That timing matches when the discrepancy started. How do I verify if that status is being counted in analytics? The core HR query includes it but maybe analytics doesn’t?

5

Check if your analytics subject area is filtering by a specific effective date that doesn’t match current date. The Workforce Headcount subject area has date parameters that can cause count discrepancies if set incorrectly.

6

Based on your June 1st status change timing, here’s the complete solution addressing all key areas:

Employee Status Mapping: Your 76-employee discrepancy is caused by the ‘Extended Leave’ status not being mapped in workforce analytics. When you added this status on June 1st, it wasn’t automatically included in analytics headcount calculations.

Fix the mapping:

  1. Navigate to Setup and Maintenance
  2. Search: “Manage Workforce Analytics Status Mappings”
  3. Find ‘Extended Leave’ status in the list
  4. Set Status Category to: “Active” (or “Active - On Leave” if available)
  5. Save changes

Verify the mapping:


SELECT employee_status, status_category, count(*)
FROM per_all_assignments_f
WHERE effective_end_date = '4712-12-31'
GROUP BY employee_status, status_category

You should see 76 employees with ‘Extended Leave’ status that need to be categorized correctly.

ETL Refresh Schedule: After fixing the status mapping, you must refresh the analytics warehouse to pick up the change:

  1. Verify Current ETL Schedule:

    • Go to Scheduled Processes
    • Search for: “Refresh Workforce Analytics Data”
    • Check schedule frequency (should be daily minimum)
    • Review last 10 run histories for failures

  2. Force Immediate Refresh: Run these processes in sequence:

    
1. Refresh Workforce Analytics Dimension Data
2. Refresh Workforce Analytics Fact Data
3. Refresh Workforce Analytics Aggregate Data

    Each process takes 20-45 minutes depending on data volume. Don’t run them in parallel.

  3. Monitor ETL Completion: Check process logs for these indicators:

    • “Process completed successfully”
    • Record counts processed match expected volumes
    • No warning messages about skipped records

Effective Dating Logic: The analytics engine uses specific effective date logic that differs from core HR queries:


// Pseudocode - Effective date resolution in analytics:
1. Determine report "As Of Date" parameter (default: SYSDATE)
2. For each employee, find assignment record where:
   - effective_start_date <= As Of Date
   - effective_end_date >= As Of Date
3. Apply status category filter based on mapped categories
4. Count distinct employee IDs meeting criteria
5. Handle multiple assignments (primary assignment only)
// Reference: Analytics Subject Area Technical Guide

Core HR queries often use different date logic:

  • Include future-dated changes
  • Use hire date vs. effective date
  • Count all assignments vs. primary only

Standardize your comparison queries to use identical date parameters:

Core HR Comparison Query:


SELECT COUNT(DISTINCT person_id)
FROM per_all_assignments_f paaf
WHERE assignment_type = 'E'
  AND primary_flag = 'Y'
  AND assignment_status_type IN ('ACTIVE', 'EXTENDED_LEAVE')
  AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date

Analytics Query: Use the Workforce Headcount subject area with:

  • As Of Date = Current Date
  • Assignment Status Category = ‘Active’
  • Primary Assignment Flag = ‘Yes’

Data Warehouse Synchronization: Ensure ongoing synchronization accuracy:

  1. Schedule Optimization:

    • Run full ETL refresh daily at 2 AM
    • Run incremental refresh every 4 hours during business hours
    • Avoid running during month-end processing windows

  2. Validation Queries: Create a reconciliation report that compares:

    • Core HR active employee count
    • Analytics active employee count
    • Difference and percentage variance

    Schedule this report to run after each ETL completion and alert if variance exceeds 1%.

  3. Status Dimension Maintenance: Document a process for HR admins:

    • When adding new employee statuses in core HR
    • Immediately update analytics status mappings
    • Run dimension refresh process
    • Validate counts after refresh completes

Root Cause Analysis:

The discrepancy occurred because:

  1. ‘Extended Leave’ status was added to core HR on June 1st
  2. 76 employees were moved to this status
  3. Analytics status mappings weren’t updated
  4. These employees were excluded from analytics headcount
  5. Core HR queries counted them as active, creating the 76-employee gap

Validation Steps After Fix:

  1. Wait for ETL refresh to complete (check Scheduled Processes)
  2. Run analytics report with these parameters:
    • Subject Area: Workforce Headcount
    • As Of Date: Current Date
    • Status Category: Active (should now include Extended Leave)
  3. Compare to core HR query with identical date and status filters
  4. Counts should now match within 1-2 employees (acceptable for timing differences)

Preventing Future Discrepancies:

  1. Create a change management process requiring analytics team review whenever HR adds new statuses, assignment types, or organizational units
  2. Implement automated reconciliation queries that alert when core HR and analytics counts diverge by more than 2%
  3. Document all status category mappings and review quarterly
  4. Train HR admins on the analytics impact of configuration changes

After implementing these fixes, your executive dashboards should show accurate headcount matching core HR data within acceptable tolerances.

7

Also check effective dating on employee assignments. If employees have future-dated assignment changes, the analytics might be using a different effective date than your core HR query. Analytics typically uses ‘as of today’ while some HR queries use ‘including future changes’.