Most modern applications today integrate and exchange data with each other using file-based approach. This approach involves source application placing the file in a SFTP server in the format the destination application would accept and then the destination application picking up the file and processing accordingly.
Oracle ERP Cloud also has the capability to integrate with other applications. Using in-built BI Publisher tool, reports can be created and scheduled to send data files to SFTP server at the desired frequency. Let’s walkthrough the different steps using a use case of sending the journal details to data warehouse. Journals data will be generated in a CSV format and the file will be placed on an SFTP server (Detailed steps described below). From there, ETL job of Data warehouse will pick the file from SFTP server, process and load data in datawarehouse tables.
1. Create a BI report showing the data in a csv format
- Prepare the SQL and create a data model using BI Publisher
- Create the BI report on above data model and set the output to Data (CSV)
- Output of the report would like below in csv format.
2. Create an FTP Server Connection in BI Publisher
- Navigate to FTP Delivery Tab and click on Add Server from Administration Administration -> Manage BI Publisher -> Delivery -> FTP
- Enter the Server Details, Test the connection and Apply (Save)
3. Schedule the Report to send the file to FTP Server daily
- Click on Schedule of the report
- Verify if data model populated in the Report field in General Tab
- Enter the destination details and Select the FTP Server
- Enter the Schedule Frequency
- Click on Submit