ADW Version Oracle Database 19c Enterprise Edition Release – Production Version 19.11.0.0.0
Oracle Cloud Application 22C (11.13.22.07.0)
Fusion Analytics Warehouse Application Version 22.R4.P2
Background
Oracle Fusion Analytics provides analytics for Oracle Cloud applications, powered by Autonomous Data Warehouse and Oracle Analytics. More specifically:
Fusion ERP Analytics provides accounting data sourced from ERP Cloud in a warehouse designed to support broad set of analytics use cases.
Fusion HCM Analytics provides comprehensive workforce, turnover data sourced from Fusion HCM Cloud.
Fusion SCM Analytics give customers the ability to gain visibility into Supply Chain performance, correlate Supply Chain processes with business goals and detect, understand and predict Supply Chain issues.
Making the right business decisions is intrinsinclty tied to the system of records data accuracy of a company. In many specific use cases, Fusion Analytics data can be complemented with additional data from external source such as On Premise, Legacy applications, Non Fusion SaaS application, OCI source, Third party cloud applications, and so on…
To support these data enrichment and consilidation requirements, Fusion Analytics is introducing a series of connector to leverage the Fusion Analytics Oracle Managed data pipeline infrastructure to extract data (full and incremental) from these external sources following a defined schedule, potentially reducing custom data extraction code, infrastructure and platform costs.
This blog describes how to utilize Fusion Analytics (FAW) Data Augmentation feature to replicate transaction data from Amazon Web Services (AWS) Cloud Storage, aka AWS S3 into ADW to extend the Data Layer of FAW. This blog will also detail the steps to extend the semantic model to ultimately allow customers to answer business questions and gaining business insight and augmented FAW factory data set with AWS S3 data.
* Have access to an Fusion Analytics Instance (OAC, ADW) with Administrative privileges
* Have access to a Salesforce Instance
* Have configured SQL level access to Fusion Analytics ADW, as described in Krithika and Gunaranjan blog post here.
Architecture Overview
FAW Data Augmentation allows customers to leverage the FAW data pipeline to augment reports with datasets created :
By adding a new dimension in the target instance,
By adding a new fact in the target instance.
The diagram below details the architecture for the AWS S3 Connector :
Figure 1: FAW Data Augmentation flow with AWS S3 Connector
The high level configuration flow is as follow:
AWS Configuration
Create an S3 bucket
Create a main S3 folder (Remote extract folder) and a sub folder to contain the source file
Upload csv or xlsx source file to sub folder
Create an access key and save it with its paired secret
FAW Configuration
Enable AWS S3 feature
Create AWS connection
Test Connection
Create metadata files and upload them to their respective folders in AWS S3 bucket
Refresh metadata for AWS S3 connection
Create AWS S3 data augmentation
Save and Schedule the data augmentation pipeline jobs
FAW Data Pipeline processes the data augmentation pipeline jobs, extract the data from Salesforce tables and load it into ADW, queryable via synonyms starting with DW_S3_X
Augmented data can be then queried directly using OAC Data set or a SQL client like SQL developer, through the database synonym named after the table name give in the first step
Semantic model is automatically be extended by the data augmentation in a new subject area
Creating FAW Data Augmentation with AWS S3 Connector
Set up SQL Developer Access to Fusion Analytics Warehouse Data with OAX_USER
Follow the steps described in the CEAL Team blog here to enable SQL Developer access to FAW augmented data: this will allow user to execute SQL queries against FAW delivered data model and FAW augmented data for any Oracle SQL Client (sqlplus, sqlcl, SQL Developer…).
Create the Connection to AWS S3 in Data Augmentation and Create Data Augmentation
The following video will walk you through how to :
Set the connectivity prerequisites:
Deploy the Configuration Steps
Let’s watch the video now
Figure 2: FAW – Augmenting FAW Data with AWS S3
Want to Learn More?
Click here to sign up to the RSS feed to receive notifications for when new A-team blogs are published.
Summary
This blog described how to utilize Fusion Analytics (FAW) Data Augmentation Managed Pipelines to replicate transactions data from AWS S3 Cloud Storage into Autonomous Data Warehouse (ADW) to extend the Data Layer of FAW. This blog also detailed the steps to extend the semantic model to ultimate allow customers to answer business questions and gaining business insight.
Bookmark this post to stay up-to-date on changes made to this blog as our products evolve.
Authors
Matthieu Lombard
Consulting Solution Architect
The Oracle A-Team is a central, outbound, highly technical team of enterprise architects, solution specialists, and software engineers.
The Oracle A-Team works with external customers and Oracle partners around the globe to provide guidance on implementation best practices, architecture design reviews, troubleshooting, and how to use Oracle products to solve customer business challenges.
I focus on data integration, data warehousing, Big Data, cloud services, and analytics (BI) products. My role included acting as the subject-matter expert on Oracle Data Integration and Analytics products and cloud services such as Oracle Data Integrator (ODI), and Oracle Analytics Cloud (OAC, OA For Fusion Apps, OAX).
