Goal purge by goal ID in data retention management deletes only parent goals

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

We’re experiencing a data integrity issue with the Data Retention Management tool’s goal purge by ID functionality. When we purge parent goals using specific goal IDs, the tool successfully deletes the parent goals but leaves all child goals and sub-goals orphaned in the system. This creates referential integrity problems and incomplete data cleanup.

Our annual goal cleanup process targets completed goals from 2020-2021 that should be archived and removed. We’ve identified approximately 1,200 parent goal IDs for deletion. However, each parent has an average of 3-4 cascaded child goals, meaning we’re left with thousands of orphaned child goals after purge operations.

The goal purge configuration shows options for purge by goal ID and purge by date range, but there’s no explicit cascade deletion setting. I expected the referential integrity to be maintained automatically when deleting parent goals. Has anyone successfully configured goal purge operations to handle the complete goal hierarchy? We need a data cleanup strategy that maintains database integrity while removing obsolete goal data.

2

The referential integrity issue stems from how SuccessFactors models goal relationships. Child goals reference parent goals via the parentGoalId field, but the Data Retention Management tool treats each goal as an independent entity. Rather than purging by specific IDs, consider using the date range purge option with filters. This approach tends to handle hierarchies more consistently because it processes goals in creation date order.

3

I’ve encountered this exact issue. The goal purge by ID functionality doesn’t automatically cascade to child goals - it’s a known limitation. You need to manually identify all child goal IDs associated with each parent and include them in your purge list. We built a SQL query to extract the full goal hierarchy before running purge operations. It’s tedious but ensures complete cleanup.

4

We faced the same orphaned child goal issue last year. Our solution was a two-phase approach: first, run a query to identify all child goals associated with the parent IDs we want to purge. Export that list and combine it with the parent ID list. Second, sort the combined list so child goals are deleted before their parents. This manual process is necessary because the Data Retention tool doesn’t have built-in cascade logic. Document your process carefully because you’ll need to repeat it for future cleanup cycles.

5

Let me provide a comprehensive solution for handling goal purge operations while maintaining referential integrity and implementing proper cascade deletion.

Understanding Goal Purge by ID Limitations

The Data Retention Management tool’s goal purge by ID function operates at the individual goal level without automatic cascade logic. This is by design because SuccessFactors prioritizes data preservation - the system assumes you want explicit control over what gets deleted rather than automatic cascading that might remove data unintentionally.

When you purge a parent goal by ID:

  1. The parent goal record is deleted from the database
  2. Child goals remain with their parentGoalId field still referencing the deleted parent
  3. This creates orphaned records that show errors when accessed through UI
  4. Reporting queries that join parent-child relationships return incomplete results

Referential Integrity Strategy

To maintain database integrity during goal purges, you must explicitly handle the complete goal hierarchy. Here’s a systematic approach:

Step 1 - Extract Complete Goal Hierarchy:

Query your goal data to map the full parent-child relationship tree. Use this query structure:

SELECT
  parent.goalId AS parentGoalId,
  child.goalId AS childGoalId,
  child.parentGoalId,
  child.goalLevel
FROM goals parent
LEFT JOIN goals child ON parent.goalId = child.parentGoalId
WHERE parent.goalId IN ('your_parent_id_list')

This identifies all direct children. For multi-level hierarchies (parent > child > sub-child), run recursive queries or use the goal hierarchy export feature in Admin Center.

Step 2 - Build Deletion Sequence:

Organize goals for deletion in reverse hierarchical order:

  • Level 3 (sub-children) delete first
  • Level 2 (children) delete second
  • Level 1 (parents) delete last

This sequence prevents referential integrity violations during the deletion process.

Step 3 - Create Consolidated Purge List:

Combine all goal IDs (parents + children + sub-children) into a single purge file. Format as CSV with goalId column. For your 1,200 parent goals with average 3-4 children each, expect approximately 4,800-6,000 total goal IDs in your purge list.

Cascade Deletion Implementation

Option 1 - Manual Data Retention Tool Approach:

Use the Data Retention Management tool with your consolidated purge list:

  1. Navigate to Admin Center > Data Retention Management > Goal Purge
  2. Select ‘Purge by Goal ID’
  3. Upload your consolidated CSV file with all goal IDs (sorted in reverse hierarchical order)
  4. Run purge in test mode first to validate the deletion scope
  5. Execute actual purge during off-peak hours
  6. Monitor purge logs for any referential integrity errors

Advantages: Uses standard tool, no custom development, audit trail maintained

Disadvantages: Manual hierarchy extraction, requires careful sorting, limited error recovery

Option 2 - OData API Approach:

Implement programmatic deletion using the SuccessFactors OData API:

<!-- Pseudocode for API-based cascade deletion -->
1. Query goal hierarchy via OData:
   GET /odata/v2/Goal?$filter=parentGoalId eq 'parent_id'&$select=goalId,parentGoalId

2. Build deletion array in reverse hierarchy order

3. Delete goals iteratively:
   DELETE /odata/v2/Goal('child_goal_id_1')
   DELETE /odata/v2/Goal('child_goal_id_2')
   DELETE /odata/v2/Goal('parent_goal_id')

4. Implement error handling and rollback logic

Advantages: Automated hierarchy traversal, better error handling, can be scheduled for recurring cleanup

Disadvantages: Requires API expertise, needs authentication setup, more complex implementation

Option 3 - Goal Plan Template Configuration:

Check your goal plan template settings for cascade options:

  1. Navigate to Admin Center > Manage Goal Plan Template
  2. Select your active goal plan template
  3. Review ‘Goal Relationship Settings’ section
  4. Look for ‘Cascade Delete’ or ‘Orphan Handling’ options
  5. If available, enable ‘Delete child goals when parent is deleted’

Note: This option is template-dependent and may not be available in all SuccessFactors versions (specifically check sf-h1-2023 release notes for this feature availability).

Data Cleanup Strategy for Large-Scale Purges

For your specific scenario (1,200 parent goals from 2020-2021):

Phase 1 - Data Analysis (Week 1):

  • Export all goals from 2020-2021 target period
  • Map complete parent-child-subchild relationships
  • Identify any goals still referenced in active performance reviews or compensation plans
  • Calculate total deletion scope (expect 4,800-6,000 goals based on your averages)

Phase 2 - Pilot Purge (Week 2):

  • Select 50 parent goals (approximately 200 total goals including children)
  • Execute purge using consolidated ID list approach
  • Validate no orphaned records remain
  • Verify reporting queries return correct results
  • Check for any unexpected referential integrity errors

Phase 3 - Staged Production Purge (Weeks 3-4):

  • Divide remaining goals into 4 batches of 300 parents each (1,200 total goals per batch)
  • Execute one batch per week during maintenance windows
  • Monitor system performance during and after each batch
  • Validate data integrity between batches

Phase 4 - Verification (Week 5):

  • Run comprehensive data integrity checks
  • Verify no orphaned goal records exist
  • Confirm reporting dashboards show correct goal counts
  • Document final purge statistics and any issues encountered

Preventing Future Integrity Issues

Implement these ongoing practices:

  1. Quarterly Goal Audits: Run automated checks for orphaned goals using SQL queries that identify goals with parentGoalId values referencing non-existent parents

  2. Purge Documentation: Maintain a purge log documenting which parent goal IDs were purged, deletion date, total child goals affected, and any integrity issues encountered

  3. Automated Hierarchy Extraction: Create a scheduled job that exports goal hierarchies monthly, making future purge planning easier

  4. Pre-Purge Validation: Before any goal purge operation, run validation queries to ensure no active references exist (performance reviews, compensation plans, development plans)

This comprehensive approach ensures clean goal data removal while maintaining referential integrity throughout your SuccessFactors instance.

6

Before going the API route, check if your goal template configuration has any cascade delete settings. Some goal plan templates have options that control what happens to child goals when parents are deleted. Navigate to Admin Center > Manage Goal Plan Template and review the goal relationship settings. If cascade delete is disabled at the template level, that could explain why the Data Retention tool doesn’t handle it automatically.

7

The date range approach might work for some scenarios, but we have mixed completion dates across our goal hierarchies. Some parent goals from 2020 have child goals that were added in 2021, so date-based purging would miss parts of the hierarchy. We really need a solution that respects the parent-child relationships regardless of creation dates. Has anyone used the API approach for goal deletion to handle cascading properly?