Skip to content

Data Export for IFS Asset Investment Planning

This documentation provides a step-by-step guide for exporting asset information from IFS Cloud to the IFS Asset Investment Planning (Copperleaf application) via a CSV file using the IFS Data Migration Tool. It includes predefined Migration Jobs that have been created and exported as Text (.txt) files. These files are linked within the document, allowing users to download and import them using the FNDMIG_IMPORT migration tool. By following the instructions outlined in this document, users can successfully execute the process and export the necessary data to IFS Asset Investment Planning.

Pre-Defined Migration Job

The following migration jobs are required for asset investment planning data exporting:

File Name Exported Data Download Links
AIP_Asset_and_Asset_Type_Exporter.txt Asset Types and Asset Data AIP_Asset_and_Asset_Type_Exporter.zip

Following Migration Jobs are included in above file:

  • AIP_ASSET_EXPORTER: Exports Assets (IFS Equipment Objects)
  • AIP_ASSET_TYPE_EXPORTER: Exports Asset Types (IFS Equipment Object Types)

How to Use:

  1. Download the AIP_Asset_and_Asset_Type_Exporter.zip file.
  2. Extract the archive to access the AIP_Asset_and_Asset_Type_Exporter.txt file.

Steps to Import Migration Jobs

Navigate to the Migration Job Page:

  • Go to Solution Manager > Data Management > Data Migration > Execute Job.

Search for the Migration Job:

  1. Enter FNDMIG_IMPORT as the Job ID in the search field.

  2. The migration job should now be visible.

execute job page

Upload the Migration File:

  1. Click the Load File command in the Detail tab to open the file upload window.

  2. Select the file you want to import from the file chooser.

  3. Click OK to upload the file.

file chooser

Start the Migration Job:

  • From the Start Job dropdown menu, select Online to execute the migration job.

Confirm Job Completion:

  • Once the job is completed, a confirmation popup message will appear, indicating a successful import.

import mig job success message

Steps to Execute & Generate a CSV File Using the Imported Migration Job

Navigate to the Migration Job Page:

  • Go to Solution Manager > Data Management > Data Migration > Execute Job.

Search for the Migration Job:

  1. Enter the migration job name (e.g., AIP_ASSET_EXPORTER).

  2. Alternatively, type AIP to list all migration jobs related to Asset Investment Planning.

search execute job

Start the Migration Job:

Select Online or Background from the Start Job dropdown menu based on the data size:

  • Online: Suitable for smaller datasets.
  • Background: Recommended for large datasets.

If Executed Online:

  • Once the job is completed, a popup message will confirm its completion.

If Executed as a Background Job:

1.Navigate to Solution Manager > Background Processing > Background Jobs.

background jobs page

2.Locate your background job.

check background job status

3.Once the status is Ready, return to Solution Manager > Data Management > Data Migration > Execute Job.

4.Search for the migration job using the Job ID.

Export the Data to a CSV File:

1.Click Export to File in the Detail tab to open the file export window.

2.Enter an appropriate file name with the .csv extension (e.g., exported_data.csv).

3.Click OK to generate and download the CSV file.

Manipulating the WHERE Clause in AIP_ASSET_EXPORTER Migration Job

The WHERE clause manipulation is necessary for the AIP_ASSET_EXPORTER migration job due to a limitation in Microsoft Excel's row count. Excel can store a maximum of 1,048,576 rows, which is often referred to as the "1 million row limit". To ensure that exported data does not exceed this limit, the migration job is configured to export data in batches of 1 million rows or fewer.

Navigate to the Migration Job Page:

  1. Go to Solution Manager > Data Management > Data Migration > Migration Job.

  2. Alternatively, if you are on the Execute Job page, click the Migration Job command.

Search for the Migration Job:

  • Enter the migration job name in the Job ID field.

migration job

Modify the WHERE Clause:

1.Go to the Database Information tab.

database information tab

2.Locate the WHERE Clause, where the default limit is set to 1 million rows due to limitation of excel files.

where clause

3.If you need to export more than 1 million rows, follow these steps:

  1. First Execution: Run the job as-is to export the first 1,000,000 rows.
  2. Second Execution: Modify the OFFSET value in the WHERE clause to 1000000 to skip the first million rows and fetch the second batch.
  3. The OFFSET value ensures previously exported data is skipped.
  4. The FETCH NEXT value limits the number of records retrieved in each batch.

Updated WHERE Clause Example:

asset_unique_id IN (
   SELECT asset_unique_id
   FROM equipment_object_aip_export 
   ORDER BY asset_unique_id
   OFFSET 
   1000000 -- Modify this value to skip previously exported records.
   ROWS FETCH NEXT 
   1000000 -- Modify this value to set the batch size for each export.
   ROWS ONLY
)

Save the Changes & Execute Again:

  1. Click OK, and Click Save to Save the modified WHERE clause.

  2. Run the migration job again to export the second batch.

Repeat as Needed:

  1. Adjust the WHERE clause in increments of 1,000,000 rows to extract more batches if required.

  2. This allows efficient handling of large datasets using simple SQL pagination.

Ensuring Compatibility with IFS Asset Investment Planning (Copperleaf application)

To ensure seamless integration of the exported CSV file with Copperleaf, follow these key steps:

  • Column Headers: Verify that the CSV column headers match Copperleaf application's expected field names.
  • Data Validation: Before importing into Copperleaf, confirm that the exported data is accurate, complete, and free of inconsistencies.

If further customization is required to match Copperleaf application’s expected expected format, adjust the migration job’s File Mapping and Formatting settings accordingly.

Key Considerations

  • Automation: Schedule the migration job to export asset data periodically for continuous integration with Copperleaf.
  • Error Handling: Regularly review job logs for execution issues and resolve errors promptly.
  • Support: If additional modifications are required for compatibility, contact IFS support for guidance.