Succession planning analytics export times out when pulling multi-BU data

HCM SAP SuccessFactors
, , , , , , , ,
1

We’re experiencing consistent timeouts when exporting succession planning analytics across our 12 business units (approximately 45,000 employees total). The Analytics Engine export job fails after 30 minutes when pulling data from People Intelligence dashboards.

The query includes standard succession readiness metrics, talent pools, and development plans. We’ve noticed the timeout occurs specifically when including multi-business unit data segmentation filters:

SELECT emp.userId, succession.readinessLevel,
       talent.poolAssignment, dev.planStatus
FROM EmployeeData emp
JOIN SuccessionPlan succession ON emp.userId = succession.employeeId
WHERE emp.businessUnit IN ('BU01','BU02',...'BU12')

This blocks our quarterly succession planning review cycle. Exports for single business units complete in under 5 minutes, but consolidated reporting is critical for executive dashboards. Has anyone optimized similar large-scale analytics exports or implemented batch scheduling approaches?

2

I had similar issues with 8 BUs. The problem was actually in how we structured the data segmentation logic - we were using IN clauses with 12 values which forces sequential evaluation. Try restructuring with a BU mapping table and JOIN instead. Also check if your SuccessionPlan table has proper partitioning by business unit - this allows the query optimizer to eliminate partitions early in execution.

3

Thanks for the suggestions. We’re currently pulling 64 fields including all talent pool assignments and historical development plan data. The batch scheduling idea makes sense - would splitting by BU groups require separate export configurations in Analytics Engine, or can this be automated through the scheduling interface?

4

You can automate batch scheduling through the Analytics Engine job scheduler. Create separate export jobs for each BU group with staggered start times (30-minute intervals). Use the job chaining feature to trigger a consolidation script after all batches complete. Also, consider implementing incremental exports if you’re running these quarterly - only pull changed records since last export rather than full dataset each time. This dramatically reduces query complexity and timeout risk.

5

I’ve seen this exact pattern before. The issue is likely your query is doing full table scans across all BUs simultaneously. Try adding composite indexes on businessUnit + userId columns in your succession planning tables. Also check if your People Intelligence dashboard has proper indexing on the readinessLevel field - that’s a common bottleneck.

6

Let me provide a comprehensive solution addressing all the optimization areas:

Query Optimization and Indexing: First, create composite indexes to eliminate full table scans:

CREATE INDEX idx_emp_bu_user ON EmployeeData(businessUnit, userId);
CREATE INDEX idx_succession_emp_ready ON SuccessionPlan(employeeId, readinessLevel);
CREATE INDEX idx_talent_pool ON TalentPool(employeeId, poolAssignment);

Rewrite your query to leverage these indexes and use a mapping table approach:

SELECT emp.userId, succession.readinessLevel,
       talent.poolAssignment, dev.planStatus
FROM EmployeeData emp
JOIN BusinessUnitMap bum ON emp.businessUnit = bum.buCode
JOIN SuccessionPlan succession ON emp.userId = succession.employeeId
WHERE bum.exportGroup = 'GROUP_1'  -- Process in batches

Batch Export Scheduling: In Analytics Engine, configure three separate export jobs:

  • Job 1: BU01-BU04 (starts 00:00)
  • Job 2: BU05-BU08 (starts 00:45)
  • Job 3: BU09-BU12 (starts 01:30)

Use the job chaining feature to trigger a consolidation job after all three complete. Set up email notifications for each batch completion.

Multi-Business Unit Data Segmentation: Implement table partitioning on your succession planning tables by business unit. This allows the query optimizer to perform partition pruning and only scan relevant data segments. Configure this in your database settings:

ALTER TABLE SuccessionPlan
PARTITION BY LIST(businessUnit)
(PARTITION p_bu01_04 VALUES IN ('BU01','BU02','BU03','BU04'),
 PARTITION p_bu05_08 VALUES IN ('BU05','BU06','BU07','BU08'),
 PARTITION p_bu09_12 VALUES IN ('BU09','BU10','BU11','BU12'));

People Intelligence Dashboard Integration: Critical step: Configure your People Intelligence dashboards for scheduled refresh rather than real-time. In Dashboard Settings:

  • Set refresh schedule to daily at 02:30 (after all batch exports complete)
  • Enable dashboard caching with 24-hour TTL
  • Create materialized views for key succession metrics:
CREATE MATERIALIZED VIEW mv_succession_summary AS
SELECT businessUnit, readinessLevel,
       COUNT(*) as employeeCount,
       AVG(performanceRating) as avgPerformance
FROM SuccessionPlan sp
JOIN EmployeeData emp ON sp.employeeId = emp.userId
GROUP BY businessUnit, readinessLevel
REFRESH COMPLETE ON DEMAND;

Additional Optimizations:

  1. Reduce field count from 64 to essential metrics (aim for 25-30 fields)
  2. Implement incremental exports using lastModifiedDate filters for quarterly runs
  3. Add query hints to force index usage: `/*+ INDEX(emp idx_emp_bu_user) */
  4. Monitor Analytics Engine job logs for execution plan details
  5. Consider archiving historical development plan data older than 2 years to separate tables

This approach reduced our export time from 30+ minutes (timeout) to 12 minutes total across all three batches for 50,000 employees across 15 BUs. The key is combining proper indexing, batch scheduling, and dashboard optimization together rather than addressing just one area.

7

We had the same problem last year with 15 BUs. Two things helped: First, implement batch export scheduling by splitting your 12 BUs into 3 groups of 4, running them sequentially instead of all at once. Second, review your data segmentation approach - are you really pulling all fields or can you limit to key succession metrics? We reduced our export from 87 fields to 23 essential ones and cut processing time by 60%. The Analytics Engine handles smaller, focused queries much better than one massive pull.