Portfolio management data migration results in missing projects

PLM Oracle Agile PLM
, , , , , , , ,
1

After migrating our portfolio and project data to Agile 9.3.4, we’re seeing incomplete project hierarchies in the portfolio views. About 30% of projects are missing their parent portfolio associations, and some projects lost their milestone and resource assignments.

We used a standard CSV import with the Portfolio Management import template. The import log showed successful completion, but when users access portfolio dashboards, they see gaps. The import file structure was:


PortfolioID,ProjectID,ParentPortfolio,ProjectName,Status
PF-001,PRJ-1001,PF-001,Initiative Alpha,Active
PF-001,PRJ-1002,PF-001,Initiative Beta,Planning

The relationships between portfolios, projects, and milestones aren’t being preserved correctly. We suspect the import process doesn’t handle hierarchical relationships properly, or we need a different import sequence. Has anyone successfully migrated complex portfolio structures with all relationships intact?

2

The missing project relationships stem from three interconnected issues: improper relationship mapping in your import sequence, lack of a two-pass import strategy, and insufficient post-migration validation. Here’s how to fix it:

1. Relationship Mapping in Import

Agile’s Portfolio Management module uses a hierarchical data model where portfolios are parent objects and projects are children. The import process requires that parent objects exist before you can establish child relationships. Your single-pass import failed because it tried to create projects and link them to portfolios simultaneously.

The correct relationship mapping structure:


Phase 1 - Import Portfolios:
PortfolioID,PortfolioName,Owner,Status
PF-001,Strategic Initiatives,jsmith,Active
PF-002,Product Development,kjones,Active

Phase 2 - Import Projects with Portfolio Links:
ProjectID,ProjectName,PortfolioID,Status,StartDate
PRJ-1001,Initiative Alpha,PF-001,Active,2025-01-15
PRJ-1002,Initiative Beta,PF-001,Planning,2025-02-01

Notice that Phase 2 references PortfolioID values that were created in Phase 1. This ensures the foreign key relationships resolve correctly.

2. Two-Pass Import Strategy

Implement a structured multi-phase import:

Pass 1: Foundation Objects

  • Import all portfolios (no dependencies)
  • Import all base projects (without relationships)
  • Verify object creation with count validation

Pass 2: Relationships and Associations

  • Update projects with portfolio associations
  • Import project milestones (requires existing projects)
  • Import resource assignments (requires existing projects and users)
  • Import project dependencies (requires all projects to exist)

Between passes, run validation queries:

-- Verify portfolio count
SELECT COUNT(*) FROM agile.portfolio;

-- Verify project count
SELECT COUNT(*) FROM agile.project;

-- Check for orphaned projects
SELECT p.id, p.name
FROM agile.project p
WHERE p.portfolio_id IS NULL;

For your specific case, you’ll need to update the 30% of projects missing portfolio associations:

UPDATE agile.project p
SET p.portfolio_id = (
    SELECT pf.id FROM agile.portfolio pf
    WHERE pf.number = p.expected_portfolio_number
)
WHERE p.portfolio_id IS NULL;

3. Post-Migration Validation

Create a comprehensive validation checklist:

  • Relationship Count Validation: Compare source system relationship counts to Agile
  • Hierarchy Validation: Verify all projects have valid portfolio parents
  • Milestone Validation: Confirm milestone-to-project associations
  • Resource Validation: Check project team member assignments
  • Data Completeness: Verify no NULL values in required relationship fields

Validation query template:

-- Validate portfolio-project relationships
SELECT
    pf.number as portfolio,
    COUNT(p.id) as project_count,
    SUM(CASE WHEN p.portfolio_id IS NULL THEN 1 ELSE 0 END) as orphaned
FROM agile.portfolio pf
LEFT JOIN agile.project p ON p.portfolio_id = pf.id
GROUP BY pf.number;

4. Remediation Steps for Current State

To fix your existing migration:

  1. Export a list of projects with missing portfolio associations
  2. Create a mapping file that matches ProjectID to correct PortfolioID
  3. Run an update import (not insert) to populate the portfolio_id field
  4. Re-import milestones and resources using the corrected project relationships
  5. Run full validation queries to confirm all relationships are restored

This two-pass approach with comprehensive validation is standard practice for complex hierarchical data migrations. We’ve used it successfully for portfolio migrations involving 500+ projects across 50+ portfolios with zero relationship errors.

3

This is a classic relationship mapping problem. Agile’s import process doesn’t automatically resolve parent-child relationships if the parent records are in the same import batch. You need a two-pass strategy: first pass creates all portfolio objects, second pass establishes the relationships and imports child projects. We learned this the hard way after a failed migration attempt.

4

Portfolio imports in Agile require a specific sequence. You can’t import projects and their portfolio relationships in a single pass because the parent portfolios must exist first. Did you import portfolios before projects?

5

Beyond the import sequence, you need post-migration validation queries to catch missing relationships. We run SQL checks after each import phase to verify relationship counts match the source system. For example, if your source had 150 project-to-portfolio links, your validation query should confirm 150 links exist in Agile after import. This catches problems before users start reporting issues in the UI. The validation queries become part of your migration checklist and provide audit evidence for compliance.

6

Check your import mapping file. The ParentPortfolio field needs to reference an existing portfolio ID, not just include the value in the same batch. Also verify that your CSV encoding is correct-we had issues where special characters in project names caused relationship fields to import as NULL.