Data Integration from Oracle, SQL Server, and Excel to ADW using ODI Marketplace

 

Objective of this use case is to To create a data integration pipeline using Oracle Data Integrator (ODI) Marketplace that extracts data from Oracle Database, SQL Server, and Microsoft Excel, and loads it into Autonomous Data Warehouse (ADW). The solution will perform a full load initially and incremental loads subsequently.

Architecture Overview

Source Systems:

1. Oracle Database
2. SQL Server
3. Microsoft Excel file (hosted on a shared file system)

Target System:

• Autonomous Data Warehouse (ADW)

Tools:

• ODI Marketplace

Implementation Steps

Step 1: Setup ODI Marketplace Environment

1. Create physical and logical schemas for the source and target systems in ODI.
2. Configure agents and ensure connectivity to Oracle, SQL Server, and Excel sources, as well as the ADW target.

Step 2: Create Models and Datastores

1. Create Models for each source (Oracle, SQL Server, Excel) and the target (ADW).
2. Reverse Engineer the tables and data structures from the source systems into ODI.
3. Create datastores representing the source and target tables.

Step 3: Design Interfaces (Mappings) in ODI

Full Load Process

1. Oracle to ADW:

   • LKM Oracle to Oracle (DBLINK)
   • IKM Oracle Incremental Update
   • CKM Oracle

2. SQL Server to ADW:

   • LKM SQL Server to Oracle (JDBC)
   • IKM Oracle Incremental Update
   • CKM Oracle

3. Excel to ADW:

   • LKM File to SQL
   • IKM Oracle Incremental Update
   • CKM Oracle

Step 4: Full Load Execution

1. Run the mappings for all sources to load data into ADW.
2. Validate the data in ADW to ensure successful full load.

Step 5: Incremental Load Process

Oracle to ADW

1. Use Last Update Timestamp to capture incremental changes.
2. Modify the mapping to use IKM Oracle Incremental Update.

SQL Server to ADW

1. Implement CDC using SQL Server's built-in CDC functionality.
2. Use LKM SQL Server to Oracle (JDBC) and IKM Oracle Incremental Update.

Excel to ADW

1. Track changes using versioning or timestamps in the Excel file.
2. Use LKM File to SQL and IKM Oracle Incremental Update for loading.

Step 6: Schedule Jobs

• Use ODI Scheduler to automate the full and incremental load processes.
• Set up email notifications for job success/failure.

ETL vs. E-LT Architectures

 ETL vs. E-LT Architectures

Understanding the ETL Bottleneck

The traditional ETL (Extract, Transform, Load) architecture often faces performance challenges due to the following factors:

1. Compute-Intensive Transformations: The ETL engine, a specialized tool, performs data transformations row-by-row. This can be inefficient, especially for large datasets.
2. Network Bottlenecks: Data is moved multiple times over the network, increasing latency and potential for errors.
3. Referential Integrity Checks: These checks can be resource-intensive, especially when data needs to be fetched from the target database for comparison.

The E-LT Paradigm: A Shift in Approach

E-LT (Extract, Load, Transform) is a newer architectural approach that addresses the limitations of ETL by shifting the transformation step to the target database. Here's how E-LT works:

1. Extract: Data is extracted from source systems.
2. Load: The extracted data is loaded into the target database.
3. Transform: Data transformations are performed using native SQL or other target database-specific languages.

Key Advantages of E-LT:

• Improved Performance:
  • By leveraging the native processing capabilities of the target database, E-LT can significantly improve performance, especially for complex transformations.
  • Reduced network traffic as data is moved only once.
• Enhanced Scalability:
  • The target database can handle large datasets and complex transformations more efficiently.
• Leveraging Existing Skills:
  • Database administrators and SQL developers can directly work on data transformations, reducing the need for specialized ETL tools and expertise.
• Flexibility:
  • Greater flexibility in customizing transformations and optimizing performance.
• Reduced Tool Dependency:
  • Less reliance on proprietary ETL tools, potentially lowering licensing costs.

However, it's important to note that E-LT is not a one-size-fits-all solution. It's best suited for scenarios where:

• The target database has powerful processing capabilities.
• The transformations are relatively simple or can be efficiently implemented using SQL.
• Data quality checks can be performed after the load, or by the source system itself.

For complex transformations and data quality requirements, a hybrid approach combining ETL and E-LT might be more suitable.

Integrating Oracle HCM with Oracle Field Service (OFS) with Oracle Integration Cloud (OIC)

 In today’s fast-paced business environment, seamless integration between Human Capital Management (HCM) and operational systems like Oracle Field Service (OFS) is critical for workforce optimization. Oracle Integration Cloud (OIC) bridges this gap, enabling real-time, automated data flows between Oracle HCM and OFS.

---

Why Integrate Oracle HCM with OFS?

Oracle HCM serves as the single source of truth for employee data, including workforce management, assignments, and roles. Oracle Field Service (OFS) optimizes field operations by managing schedules, routes, and customer appointments. Integrating these systems ensures:

• Accurate workforce assignments: Employee details, skills, and availability from HCM directly update OFS.
• Seamless onboarding and termination: New hires and terminated employees are automatically reflected in OFS schedules.
• Improved operational efficiency: Real-time updates reduce manual effort and errors.

---

OIC Architecture (Event Driven + Scheduled)

How OIC Simplifies the Integration

Oracle Integration Cloud simplifies the integration process through its prebuilt adapters and low-code interface. Key steps include:

1. Synchronizing Employee Data

• OIC HCM Adapter: Extracts employee details such as name, role, assignment, and work schedule from Oracle HCM.
• Transformation and Mapping: OIC enriches and maps HCM data to the required format for OFS.
• OFS REST API: Pushes the transformed data into Oracle Field Service.

2. Real-Time Updates

OIC’s event-driven architecture ensures:

• Immediate updates for new hires, role changes, and terminations.
• Real-time validation of data consistency across HCM and OFS.

3. Error Handling and Reporting

• Validation rules ensure data integrity.
• Errors during API calls or data mismatches are logged in OIC and sent to administrators via email notifications.

---

Key Use Cases

1. Onboarding New Employees

When a new employee is added to Oracle HCM, OIC:

• Extracts the employee’s details and validates them.
• Maps the information to OFS's workforce requirements.
• Creates a corresponding resource record in OFS.

2. Workforce Assignment Changes

When an employee’s assignment, location, or role changes in HCM, OIC automatically updates the corresponding resource details in OFS, ensuring accurate task allocation.

3. Employee Terminations

For terminated employees, OIC triggers a workflow to deactivate their profile in OFS, avoiding scheduling conflicts.

---

Benefits of Using OIC for HCM-OFS Integration

• Prebuilt Adapters: HCM and REST adapters reduce development time.
• Scalability: Handles large volumes of employee data efficiently.
• Error Handling: Built-in exception management ensures data consistency.
• Custom Workflows: Tailored to specific business needs using OIC's low-code interface.

---

BizInsight Consulting: Your Integration Partner

At BizInsight Consulting, we specialize in designing and implementing Oracle HCM and OFS integrations using OIC. With years of expertise, we ensure seamless and efficient data flows that enhance operational efficiency.

Contact us at inquiry@bizinsightinc.com to learn more or get started!

Integration of SAP SuccessFactors with Oracle HCM Using Oracle Integration Cloud (OIC)

Integration of SAP SuccessFactors with Oracle HCM Using Oracle Integration Cloud (OIC)

Objective

Integrate SAP SuccessFactors with Oracle HCM to synchronize employee data for processes like time tracking and labor costing. SAP SuccessFactors serves as the system of record, providing employee data files every three hours. Oracle Integration Cloud (OIC) facilitates seamless data transfer, validation, and processing into Oracle HCM.

---

Integration Flow Overview

1. File Ingestion and Validation

• Objective: Process employee data files from SAP SuccessFactors.
• Steps:
  1. Check for file existence in the designated FTP folder. If no file exists, terminate the process.
  2. Validate whether the file is empty. If it is, skip the file.
  3. If the file passes validations, load its data into the XX_EMP staging table in Oracle ATP.

2. Data Loading and Transformation

• OIC utilizes the DB Adapter to clear any existing data in the XX_EMP table before loading new records.
• Data transformation and enhancement are performed in OIC to ensure compatibility with Oracle HCM.

---

Key Validation Steps

A. New Employee Creation

For new employees, OIC calls the Oracle HCM REST API: /hcmRestApi/resources/11.13.18.05/workers  to create a new employee record.

B. Existing Employee Update

If the employee already exists in HCM, OIC performs the following validations and updates as necessary:

1. Work Relationship Validation

   • REST API: workRelationships api
   • Ensures the employee has an active work relationship in HCM.

2. Assignment Validation

   • REST API: assignments api
   • Validates the existence of assignments for the employee.

3. Manager Validation

   • Ensures a valid manager is associated with the employee.

C. Specific Scenarios

1. Termination
   • For terminated employees, OIC calls HCM Termination api to terminate the work relationship.
2. New Work Relationship
   • If a new work relationship is created in SAP SuccessFactors, OIC invokes workRelationships api to update HCM.
3. Assignment Changes
   • For new or updated assignments, OIC calls assignments api
4. Manager Changes
   • For manager updates, OIC invokes /hcmRestApi/resources/latest/assignments with updated manager details.

---

Error Handling and Logging

• If an error occurs while invoking HCM APIs, the integration logs the error in the following:
  1. XX_EMP Table: Updates the record with the error message.
  2. Log Tables: Captures detailed error information for debugging.

---

Final Validation and Reporting

After processing all records, OIC performs a final validation:

• Compares the total records in the file with successfully processed and error records.
• Sends a summary email to both the technical team and the SAP SuccessFactors team, including:
  • Number of records processed.
  • Successful updates.
  • Records with errors.

---

Role of Oracle Integration Cloud (OIC)

OIC simplifies this complex integration by:

1. Seamless Orchestration
   • Orchestrating data flow between SAP SuccessFactors and Oracle HCM.
2. Simplified API Calls
   • Prebuilt adapters for FTP, REST APIs, and Oracle ATP minimize development efforts.
3. Error Handling
   • Robust error capture and logging ensure data accuracy and process reliability.

---

BizInsight Consulting Expertise

BizInsight Consulting has extensive experience in building Oracle HCM integrations with OIC. Our team delivers robust, scalable, and efficient solutions tailored to complex requirements.
Contact us at inquiry@bizinsightinc.com for inquiries or assistance.