Validation

Content validated on 12/22/2022 with

• 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

• Salesforce Developer Edition

 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 new Connectors to leverage the Fusion Analytics Oracle 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.

We have cover in this previous blog how to use Oracle Analytics Cloud (OAC) to extract data from Salesforce to Autonomous Data Warehouse (ADW)  and in this one how to use Oracle Data Integrator (ODI) to achieve the same goal.

This blog describes how to utilize Fusion Analytics (FAW) Data Augmentation feature to replicate transaction data from Salesforce Cloud Applications 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 such as :

• Opportunity conversion: As open sales opportunities move through the stages of sales, a sales executive wants to track the progress so that he/she can guide their team members
• Bookings and Revenue by Customers and/or Channels or Regions
• Forecast accuracy : As a sales/business executive, compute and manage open opportunities to bookings
• Average resolution time : Compute and manage/improve resolution of customer issues 
• Customer retention: An executive in customer support or customer experience organization needs to track and manage customer satisfaction leading to customer retention, in many industries such as retail, banking, financial services, etc.
• Lead generation : Gaining the interest of potential customers in order to increase future sales

Suggested Prerequisite Reading Material:

* Data Augmentation with FAW 

* Data Replication with OAC 

* FAW Semantic Model Customization

Overall prerequisites

* 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.

* Have the Salesforce connector enabled for your Fusion Analytics tenant through Service Request with Oracle Support

 Architecture Overview

FAW Data Augmentation allows customers to leverage the FAW data pipeline to augment reports with datasets created :

1. By adding a new dimension in the target instance,
2. By adding a new fact in the target instance.

The diagram below details the architecture for the Connectors :

Figure 1: FAW Data Augmentation flow with Salesforce Connectors

The high level configuration flow is as follow:

• FAW Data Augmentation

  • Create the Salesforce connection

  • Create Dimensions and Fact Data Augmentation on Salesforce source tables

  • Choose Attributes for the Data Augmentation

  • Select dimensions to be mapped with Fact columns

  • 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_SA_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 can be extended by customizing an existing subject area or addition of a new subject area to allow reporting on augmented data extracted in ADW. This will require the creation of database objects (views and grants). We will cover the basics here to be able to build a visualization against the extracted Salesforce Data.

 Creating FAW Data Augmentation with Salesforce 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 Salesforce in Data Augmentation and Create Salesforce Data Augmentation

The following video will walk you through how to :

• Set the connectivity prerequisites:
  • Salesforce user / password and security token
  • Connected App in Salesforce administration (Client ID and Client Secret)
• Deploy the Configuration Steps:

1. Create SALESFORCE_REST connection in Fusion Analytics Data Configuration Console
2. Test Connection to SALESFORCE_REST 
3. Refresh Metadata for SALESFORCE_REST connection
4. Create a data augmentation SALESFORCE_REST 
5. Validate the data has been extracted by issuing SQL queries

A subsequent section of the blog below will describe how to build semantic model extensions for Salesforce replicated data.

The objective is to be able to extract a Sales schema from Saleforce into Fusion Anaytics ADW, as per the Salesforce Data model documented here. In this exercise, we will focus on a subset of the Sales schema with three entitied :

• Opportunity being our Fact Table
• Account being one of our Dimension Table
• Campaign being our second dimension

Figure 2: Subset of the Sales Data model targeted for extract

Let's watch the video now

Repeat steps in the video  to create the Campaign Dimension

Figure 3: FAW  - Campaign Dimension table created and extracted

i) Create the Data Augmentation for the Opportunity Fact: enter Create Fact as Augmentation Type, Supplementad Data Source Dataset Type, Salesforce pillar, System provided Source Table type and choose the OPPORTUNITY source table. Click Next

Figure 4: Opportunity Fact Salesforce Data Augmentation

j) Select all attributes, ensure a Primary and Incremental Key are selected and click next

Figure 5: Opportunity Fact Attributes

k) Update the ACCOUNTID and CAMPAIGNID to be Treated as Dimensions, EXPECTEDREVENUE as Measure with Sum Aggregation rule, review the Primary and Incremental Key. Click Next

Figure 6: Opportunity Fact Column Options

l) Select the Dimension Keys to CAMPAIGN and ACCOUNT Dimensions as per the below screen. Click Next once done.

Figure 7: Opportunity Fact Dimension Key

m) Enter the Opportunity Fact details schedule and click Finish.

Figure 8: Opportunity Fact Schedule and Save

n) Check the Request History and confirm the Activation is created and then after a while Completed. The synonym can now be queried from SQL Developer

Figure 9: Opportunity Fact table created and extracted

 Extending the FAW Semantic Model with Salesforce Subject Area 

Now that the Salesforce tables have been extracted into ADW, we can use the Semantic Model extension wizards to create a Salesforce Subject Area

Create the required database objects for the Salesforce extracted tables

In order for FAW to be able to read the Salesforce tables, the recommended approach is to create database views based on the synonym definitions under the OAX_USER schema and to grant the OAX$OAC user access to these views.

a)  In SQL Developer, connect to ADW with OAX_USER and create a view and grants with the following SQL queries for Account Dimension.

CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SA_X_ACCOUNT_DIM"  AS
SELECT * FROM oax$oac.DW_SA_X_ACCOUNT_DIM;

GRANT SELECT ON "OAX_USER"."V_DW_SA_X_ACCOUNT_DIM" TO "OAX$OAC";

Figure 10: Account Dimension required database objects

b) Create a view and grants with the following SQL queries for Campaign Dimension.

CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SA_X_CAMPAIGN_DIM"  AS
SELECT * FROM oax$oac.DW_SA_X_CAMPAIGN;

GRANT SELECT ON "OAX_USER"."V_DW_SA_X_CAMPAIGN_DIM" TO "OAX$OAC";

Figure 11: Campaign Dimension required database objects

b) Create a view and grants with the following SQL queries for Campaign Dimension.

CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SA_X_OPPORTUNITY_FACT"  AS
SELECT * FROM oax$oac.DW_SA_X_OPPORTUNITY_FACT;

GRANT SELECT ON "OAX_USER"."V_DW_SA_X_OPPORTUNITY_FACT" TO "OAX$OAC";

Figure 12: Opportunity Fact required database objects

Extend the Semantic model

a)  In the Console pane, click Semantic model extensions and create a new Branch for Salesforce Subject Area

Figure 13: Salesforce Semantic model extension

b) Add Steps to create a new Salesforce subject area named Custom SFDC

Figure 14: Add New Subject Area step

c) Add two Dimension steps for Account dimension and Campaign Dimension:

* Enter a Step name and Descritpion

* Choose the Custom - SFDC target subject area and click Next. 

* Chose the OAX_USER schema, the dimension database view created earlier. Rename the Dimension, select all columns and click Next

* Skip the joins and click Next

* Add a hierarchy to the dimension

* Choose a target subject area and finish

Figure 15: Add dimension steps

d) Add the Opportunity Fact step 

* Enter the Opportunity Fact details and target subject area

*  Select the facts, keys and degenerated attributes

* Define the joins to the Campaign and Account dimension

* Define the aggregation rules for the measures and aggregation levels

* Choose a target subject area and finish

Figure 16: Add Fact step

f) IfThe Account Dimension and the Campaign Dimensions steps will have warnings as they have been applied before any fact was created. Reapply these steps and validate that all the steps are applied succesfully without warnings once complete.

Figure 17: New SA, Dimensions and Fact steps applied

Publish the Semantic model extension and Merge with the Main Branch

a)  Back in Semantic model extensions, under User extensions click on Publish Model

Figure 18: Publish Model

b) Select the SFDC user extension and click Publish

Figure 19: Select Salesforce Subject Area and Publish

c) Check the Activity and validate that the SFDC semantic model extension is published

Figure 20: Validate Salesforce Subject Area Publishing

d) Click on the SFDC Branch and select Merge With Main Branch

Figure 21: Merge with Main Branch

e) Confirm Merge. Check the Main Brnach steps and validate that the Custom - SFDC semantic model extension merge is done

Figure 22: Confirm and Validate Merge

 Creating an OAC Workbook to Gain Business Insight with Salesforce Data

Now that we have seen how to use FAW Data Augmentation Connector extract Salesforce Data and the FAW semantic model extension to build a new Salesforce Subject area, we can create visualization in OAC. 

a) Go back to FAW Home page and click on Projects

Figure 23: Projects

b) In OAC, click on Create Workbook

Figure 24: Create Workbook

c) Select the Custom - SFDC Subject area and click Add to Workbook.

Figure 25: Add Custom SFDC Workbook

c) Select Columns from the dimensions and fact presentation tables to build a new Visualization and review the Results

Figure 26: SFDC OAC Visualization

This concludes the activities in the blog.

 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 Connector to replicate transactions data from Salesforce Cloud Applications 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 such as :

• Opportunity conversion: As open sales opportunities move through the stages of sales, a sales executive wants to track the progress so that he/she can guide their team members
• Bookings and Revenue by Customers and/or Channels or Regions
• Forecast accuracy : As a sales/business executive, compute and manage open opportunities to bookings
• Average resolution time : Compute and manage/improve resolution of customer issues 
• Customer retention: An executive in customer support or customer experience organization needs to track and manage customer satisfaction leading to customer retention, in many industries such as retail, banking, financial services, etc.
• Lead generation : Gaining the interest of potential customers in order to increase future sales

Bookmark this post to stay up-to-date on changes made to this blog as our products evolve.