Project: Regional Project Data Integration

by

Project Description

The Regional Project Data Integration aims to integrate legacy project data from Deltek Vision systems in the APAC, NA, and CAN regions into the Data Warehouse. This integration will enable automated business reporting before the GMIS ERP system is implemented in these regions.

The main objective is to ensure that historical and active project data is available in a structured and standardised format, aligning with the UK Project DataMarts. The integration will follow a defined data model, anticipating the creation of the following views:

  • Project.Details
  • Project.Details_Plus
  • Project.Cost
  • Project.Revenue
  • Project.Dates
  • Project.Values

Each region presents unique challenges due to variations in Deltek Vision deployments:

  • APAC: Uses a dedicated DataVerse instance with restricted access.
  • NA: Uses a SaaS-hosted Deltek Vision version.
  • CAN: Uses a local SQL Server instance of Deltek Vision.

The Deltek Vision schema is not well understood, adding complexity to the extraction, transformation, and loading (ETL) process. Additionally, the order in which regions will migrate to the new Cloud ERP system (GMIS) is uncertain and could happen within the next three months.

Risks & Issues

Key Risks

  1. Limited Understanding of Deltek Vision Schema
    • The table structures and relationships within Deltek Vision are unclear, increasing the risk of data misalignment or missing fields.
    • Mitigation: Engage with technical teams or consultants who have experience with Deltek Vision; attempt schema discovery via metadata extraction.
  2. Access Restrictions in APAC
    • APAC’s DataVerse has restricted access, which may delay extraction and require special permissions or workarounds.
    • Mitigation: Early engagement with APAC IT teams to facilitate access approvals or explore alternative extraction methods.
  3. Different Deployment Models Across Regions
    • NA (SaaS), CAN (SQL Server), and APAC (DataVerse) have different infrastructure and access methods.
    • Mitigation: Develop region-specific ETL strategies to handle differences in extraction, authentication, and data retrieval.
  4. Unknown Timeline for ERP Migration
    • The regions may migrate to GMIS in the next 3 months, which could impact the relevance of this integration.
    • Mitigation: Ensure the integration process is agile, focusing on short-term wins (e.g., interim reporting) while aligning with future ERP migration plans.
  5. Data Mapping Complexity
    • Existing UK Project DataMarts mappings may not fully align with legacy Deltek Vision structures.
    • Mitigation: Conduct data profiling for each region to identify gaps, anomalies, and required transformations.
  6. Data Quality Issues
    • Historical data may contain inconsistencies, missing fields, or duplicate records.
    • Mitigation: Implement data validation rules and work with regional teams to resolve discrepancies.
  7. Performance & Scalability
    • Extracting large datasets from different infrastructures (especially the SaaS version in NA) may lead to latency issues.
    • Mitigation: Plan for incremental data loads and optimise query performance.

Main Tasks & Deliverables

1. Discovery & Planning

  • Understand Deltek Vision Schema (Extract metadata, relationships, primary keys).
  • Confirm access permissions for APAC, NA, and CAN.
  • Engage with regional stakeholders to align on data expectations.
  • Define data mapping between Deltek Vision and the UK Project DataMarts.

2. Data Extraction & Integration Strategy

  • Develop region-specific ETL strategies for:
    • APAC (DataVerse extraction)
    • NA (SaaS API-based extraction or alternative methods)
    • CAN (SQL-based extraction)
  • Define incremental load process to capture updates efficiently.

3. Data Transformation & Standardisation

  • Create transformation logic to align Deltek Vision data with the UK DataMart model.
  • Develop cleansing rules for missing values, duplicates, and data inconsistencies.
  • Establish data validation processes before loading into the Data Warehouse.

4. DataMart Implementation

  • Build standardised Project DataMart Views:
    • Project.Details
    • Project.Details_Plus
    • Project.Cost
    • Project.Revenue
    • Project.Dates
    • Project.Values
  • Validate against UK models to ensure consistency.

5. Reporting & Testing

  • Develop test cases for validation (completeness, accuracy, consistency).
  • Compare outputs with UK reports to ensure alignment.
  • Gather regional feedback and refine mappings.

6. Deployment & Transition

  • Implement in the Data Warehouse and ensure reports are available.
  • Monitor performance & resolve issues.
  • Coordinate with GMIS team to align the integration with the future ERP.

Next Steps

  1. Immediate Priority: Schema discovery for Deltek Vision.
  2. Confirm Access & Extraction Feasibility in each region.
  3. Define Data Mapping & Transformation Rules.
  4. Develop ETL pipelines for initial data loads.
  5. Validate Data Quality & Ensure Consistency with UK Reports.

Leave a Comment