Content validated on 3/1/2021 with
Fusion HCM Analytics provides comprehensive workforce, turnover data sourced from Fusion HCM Cloud in a warehouse designed to support broad set of analytics use cases. In some specific use cases, Fusion HCM Analytics data can be complemented with additional Payroll, Cumulative Earning – Deduction or Costing data sets. For such data, Fusion HCM Cloud provide with an extract mechanism named HCM Extracts. HCM Extracts uses BI Publisher as the delivery mechanism for extracted data.
This blog is part of in a series covering topics on how to utilize Oracle ETL / Data Integration tools to extract data from various data sources to complement Fusion Analytics Warehouse factory data. Future blog topics will cover extracting from: Oracle SaaS Applications, Oracle on-premise applications (e.g. EBS), and other Oracle applications such as Responsys, Eloqua and third party data sources like Salesforce or Workday.
This blog walks though how to configure Oracle Data Integrator (ODI) on Oracle Cloud Marketplace to replicate data from Fusion HCM Cloud, using HCM Extracts, into an Autonomous Data Warehouse (ADW) database that is part of the FAW product.
The "Master" topic will divided in four parts :
This current article covers Part 1 - Setting up the SFTP Server using Object Storage Bucket mounted as a file system on the ODI VM.
Suggested Prerequisite Reading Material:
* Have access to an Fusion HCM Cloud instance
* Have access to an ODI Marketplace instance
* Follow the steps in the first blog for instructions on:
Policies Assignment (Step 1)
SSH key-pair Generation (Step 4-b and 4-c)
ODI Marketplace Provisioning (Step 2)
ADW Configuration (Step 5-h, 5-i, 5-j and 6-d)
The diagram below details the architecture blue print for this use case:
Figure 1 : Architecture overview
The data flow is as follow:
s3fs-fuse is being installed using yum so the installation required the ODI VM to have access to internet. To install s3fs-fuse, execute the following command
Figure 2 : s3fs-fuse installation
To configure the credentials for s3fs-fuse, one needs to generate a secret key for an OCI local user (not federated), update the s3fs-fuse password file and execute the s3fs-fuse mount command
a) Connect to the OCI console with the OCI local user
Figure 3: Login with OCI Local User
b) Create an Object Storage bucket names s3fs-fuse-sftp-bucket in the desired compartment
Figure 4: Create the Object Storage Bucket
c) Access the OCI local user details, generate a secret key and save the secret key
Figure 5 : Generate the secret key
d) Copy and save the access key of the secret key generated previously
Figure 6 : Generate the secret key and copy the access key
e) Connect to the ODI VM and generate the password file for s3fs-fuse using the access key and the secret key saved previously
Figure 7 : Generate the password file for s3fs-fuse
e) Execute the s3fs-fuse mount command to mount the Object Storage bucket to the file system
Figure 8 : Generate the password file for s3fs-fuse
f) Update the /etc/fstab file so the mount point will persist at the next ODI VM reboot.
Figure 9 : Persist bucket mount point in /etc/fstab
a) Create a file in the bucket mount point from the ODI VM and verify it is displayed in the bucket from the OCI Console
Figure 10 : Test creating a file in the bucket folder from the ODI VM
b) Create a directory in the bucket from the OCI Console and verify it is displayed in the bucket bucket mount point from the ODI VM
Figure 11 : Test creating a directory in the bucket from the OCI Console
c) Upload a file to the bucket directory from the OCI Console and verify it is available in the ODI VM folder
Figure 12 : Test creating uploading a file to the directory from the OCI Console
Creating the ssh key for Fusion BI Publisher and updating the autorized_keys file on the ODI VM
a) Create the ssh key pair bip_id_rsa and bip_id_rsa.pub for Fusion BI Publisher on the ODI VM
Figure 13 : Create the ssh key pair for Fusion BI Publisher
b) Convert the private key bip_id_rsa to DES3 format as it is the only one supported by Fusion BI Publisher and rename the private keys
Figure 14 : Convert the private to DES3 format
c) Update the authorized_keys file for opc user on the ODI VM to add the bip_id_rsa.pub public key for Fusion BIP Publisher
Figure 15 : Convert the private to DES3 format
d) Copy the private key on the client and connect to sftp server on the ODI VM
Figure 16 : Connect to SFTP Server on the ODI VM from client
Updating the BIP Fusion Publisher Server and Delivery Configuration
a) Connect to Fusion BI Publisher Upload Center in Administration and upload the private key bip_id_rsa
Figure 17 : Connect to SFTP Server on the ODI VM from client
b) Create the SFTP Delivery in BI Publisher
Access to FTP Delivery configuration
Figure 18 : Fusion BI Publisher FTP Delivery
Click on Add Server
Figure 19 : Add FTP Server
Enter a server name, the ODI VM Public IP for the host, 22 for the port. Choose Private Key authentication, select the bip_id_rsa key uploaded previously, enter opc as user and the private key passphrase in the password and Private Key Password fields. Test the connection and click apply.
Figure 20: FTP Server configuration details
Validate the FTP Delivery server is added in the BI Publisher configuration
Figure 21: SFTP Server on the ODI VM configured in BI Publisher
a) Connect to Fusion BI Publisher and Schedule a Report Job for an existing report
Figure 22: Schedule a Report Job for an existing report
b) In the output configuration, choose the CSV format. Add an FTP destination, select the ODI_VM_SFTP server, enter the folder created in the bucket mount point configuration earlier, i.e. /u01/oracle/oss/s3fs-fuse-sftp-bucket, enter a name for the Remote CSV file, leave the user name and password blank and click submit.
Figure 23: Enter BI Publisher Report job output and destination details
c) Enter a name for the Report Job
Figure 24: SFTP Server on the ODI VM configured in BI Publisher
d) Go to the report job history and monitor the report job execution
Figure 25: SFTP Server on the ODI VM configured in BI Publisher
d) Access the OCI Console and look into the bucket created in the bucket mount point configuration earlier, i.e. s3fs-fuse-sftp-bucket, and validate the CSE_ASSET_RELATIONSHIPS.csv file has been generated
Figure 26: SFTP Server on the ODI VM configured in BI Publisher
This concludes the activities in this article.
The next articles will cover :
Click here for more A-Team Oracle Data Integrator (ODI) Blogs.
Click here to sign up to the RSS feed to receive notifications for when new A-team blogs are published.
Click here to access the ODI Marketplace documentation library.
In addition to the capability described in this blog, customers have the option to load budget data in ERP Cloud from any EPM system and that data is supported in Fusion ERP Analytics using GL Budget subject area, that will be covered in a future blog post.
This article walked through the steps to configure Oracle Data Integrator (ODI) on Oracle Cloud Marketplace to replicate data from Fusion HCM Cloud, using HCM Extracts, into an Autonomous Data Warehouse (ADW) database that is part of the FAW product, particularly the Part 1 - Setting up the SFTP Server using Object Storage Bucket mounted as a file system on the ODI VM.
Bookmark this post to stay up-to-date on changes made to this blog as our products evolve.