Fusion Analytics Warehouse – Extensibility Reference Architecture

February 27, 2020 | 11 minute read
Matthieu Lombard
Consulting Solution Architect
Text Size 100%:

Co-authors:

Satnam Singh, Vice President, Product Management

Profile photo for Balaji Krishnan

Balaji Krishnan, Product Management Architect, Analytics Apps

 

Content validated on 11/18/2020 with

  • ODI Version 12.2.1.4.200304.2238

  • FAW 20.R2.P1

  • ADW Version Oracle Database 19c Enterprise Edition Release - Production Version 19.5.0.0.0

 

/content/published/api/v1.1/assets/CONT6491D03E514E469C8DBF10B4C75FFCD8/Medium?cb=_cache_ab8e&channelToken=12f676b76bf44b4e9b22e6b36ebfe358&format=jpg Background

Fusion Analytics Warehouse ( aka FAW) is an optimized analytics solution that provides rapid time to insights for multifarious lines of business through Oracle’s feature-rich production-ready business intelligence software for Fusion Cloud Apps.

FAW incorporates the robust combination of Oracle Analytics Cloud (OAC) and Oracle Autonomous Data Warehouse (ADW) delivering ready-to-use dashboards, industry KPIs, and machine learning models that leverage Oracle’s sophisticated Cloud architecture.

FAW is fully extensible and customizable; allowing customers to import external data into ADW, expand the base semantic model, and add additional content to OAC.

This blog is a prologue to a series of forthcoming topics on how to utilize Oracle ETL and Data Integration tools to extract data from various sources and extend Oracle’s delivered Analytics offerings.

These blogs are a collaboration effort between various Oracle teams: FAW Product Management, the CEAL Team, and the A-Team.

Future blog topics will include:

  • Data Extensibility:  extracting data from Oracle SaaS Applications, Oracle on-premise applications (e.g. EBS), other Oracle applications (e.g. Responsys & Eloqua), and third-party data sources (e.g. Salesforce & Workday).

  • Semantic Layer Extensibility:  adding new/extending dimensions, defining new presentation hierarchies, adding calculations, extending Oracle Facts, creating new subject areas, and customizing the presentation layer (re-ordering columns, hiding / showing columns, leveraging translations).

  • Content Extensibility:  creating new KPIs, and creating new Cards and Decks.

See below “Figure 1: FAW Extensibility Use Cases”:

The FAW Oracle-managed components shown in red on the left are immutable and cannot be modified.

Oracle-managed components comprise of:

  • Fusion SaaS Applications source

  • ETL service to extract Fusion data, transform and load it to the Factory schema

  • Prepackaged semantic model covering the FAW subject areas (GL balances, GL Journals, Account Payables, Account Receivables, etc.)

  • Prepackaged content (KPIs, visualizations, machine learnings models.)

The Customer-managed components shown in blue on the right represent customizable elements.
Customizations may include: 

  •  Using independent custom extractions to onboard additional data to one or more custom schemas.

  • Extending the semantic layer to map schemas to the presentation layer.

  • Creating custom content on semantic layer extensions.

 

Figure 1: FAW extensibility use cases

This blog is broken into three main chapters:

  • Data Extensibility Use Cases

  • Semantic Extensibility Use Cases

  • Content Extensibility Use Cases

 

Suggested Prerequisite Reading Material:

* FAW Product Page

* FAW Documentation Library

* Using Oracle Data Integrator on Oracle Cloud Marketplace

/content/published/api/v1.1/assets/CONT7B6397EA137542C7B81E57AB15C19BDD/Medium?cb=_cache_ab8e&channelToken=12f676b76bf44b4e9b22e6b36ebfe358&format=jpg Reference Architecture - Data Extensibility Use Cases

Overview

FAW comprises of three main components:

1) OAC the foundation for FAW Analytics.

2) ADW to house the data warehouse.

3) Pre-packaged dashboards, visualizations, KPIs, content, and data pipeline.

Customers have disparate systems, applications that generate and store data critical for analytical decision-making. Customers can create schemas to host additional data sources not available out-of-the-box in FAW and to have all business-critical data collated into Oracle ADW.

Use Cases

Use Case 1 – Replicate Oracle Fusion SaaS data in ADW using Oracle Data Integrator (ODI) Marketplace

Figure 2: Fusion SaaS data replication use case

Scenario: Fusion View Objects (VOs) such as Tax or Cash Management VOs are not in the FAW roadmap; however, they are often required for custom analytics.

Solution: Leverage the transformation/orchestration capabilities of ODI Market Place to extract the VOs or additional attributes from ERP and load them into Customer Managed Schemas within the same ADW instance.

Methodology: In this blog the A-Team walks you through how to replicate Oracle Fusion SaaS VOs data into ADW using ODI Marketplace and BI Cloud Connector (BICC). Topics Include:

  • Provisioning ODI Marketplace

  • Fulfill OCI, ODI and ADW prerequisites

  • Configure BICC to write to object storage

  • Configuring Oracle Object Storage and BICC ODI Topology

  • Create ODI Models

  • Create ODI Project and Mapping

  • Run the ODI Mapping

Use Case 2 – Replicate Salesforce data in data in ADW using ODI Marketplace

Scenario: Customer would like to extract data from Oracle Non-Fusion sources such as Salesforce.

Solution(s):

(a) Use ODI Marketplace to extract data using the source extract capabilities.

(b) Use alternative ETL methods such as REST API calls, Web Service calls, JDBC/ODBC drivers, etc…

(c) Use FSCM, HCM & CRM Extract

Figure 3: Non-Fusion SaaS or Third-Party data replication use case

Methodology: In this blog, the A-Team walks you through how to replicate Salesforce data into ADW using ODI Marketplace. Topics Include:

  • Reset Salesforce Security Token

  • Set up Salesforce Topology in ODI Marketplace

  • Create ODI Models and Reverse Engineer Salesforce Datastores

  • Update ADW diagram and create target ADW Datastore

  • Create and run ODI Mapping

Use Case 3 – Replicate Taleo Recruiting Cloud data in data in ADW using ODI Marketplace

Scenario: Customer would like to extract data from Oracle Non-Fusion sources such as Taleo Recruiting Cloud.

Solution(s):

(a) Use ODI Marketplace to extract data using the Taleo client connect utility

(b) Use alternative ETL methods such as REST API calls, Web Service calls, JDBC/ODBC drivers, etc…

 

Figure 4: Taleo Recruiting Cloud data replication use case

Methodology: In this blog, the A-Team walks you through how to replicate Taleo Recruting Cloud data into ADW using ODI Marketplace. Topics Include:

  • Set up Taleo Client Connect in ODI Marketplace

  • Create ODI Models and Reverse Engineer Taleo Extract Datastores

  • Update ADW diagram and create target ADW Datastore

  • Create and run ODI Mapping

/content/published/api/v1.1/assets/CONT312AEE8B6E95451C83CE994DF7C06119/Medium?cb=_cache_ab8e&channelToken=12f676b76bf44b4e9b22e6b36ebfe358&format=jpg  Reference Architecture - Semantic Extensibility Use Cases

Overview

A semantic model is a business representation of physical data that helps business users understand and access data through commonly understood business terms.  

The OAC Semantic model consists of three layers:

  • Physical – Maps to our Physical Data Model

  • Logical – Mapping or a transformation layer where complex calculations are defined

  • Presentation – End user access layer

Using the semantic model has the following advantages:

  • Security and Governance

  • Performance and Scalability

  • Clean way to obfuscate complex physical model, joins, filters, calculations etc.

Figure 4: Semantic Layer Details

The FAW Semantic Model is immutable: the out of the box subject areas are preconfigured to map the FAW star schema tables. However, FAW offers capabilities for extending the semantic model through Extension Wizards. Extension wizards are customer-managed and accessed through the FAW Console.

Figure 5: FAW Console for Semantic Model Extensions

Extension Wizard Capabilities:

  • Add new dimensions.

  • Extend an existing dimension.

  • Add a new Fact

  • Define a new presentation hierarchy.

  • Add a column

  • Add a Session Variable

  • Create a new subject area or modify an existing one

  • Customize the presentation layer.

Figure 6: FAW Semantic Layer Extension Wizards

Use Cases

**** At the time of updating this blog – September,30 2020 –  in the FAW current version (v4.1.0), only the use cases not greyed above are available. ****

Use Case 1 – Extend an existing dimension

Scenario: Customer has onboarded an additional data element (column) from one of the Fusion SaaS data store and want to add this column to one of the FAW delivered dimension.

Solution(s): Use the Extend a Dimension Wizard to onboard the new column and expose it to the presentation layer.

Methodology: In this blog <Incoming – Stay tuned!>, the CEAL Team will walk you through how to enrich the FAW delivered dimension with custom attributes and enrich the semantic layer with a new dimension.

Figure 7: FAW Semantic Layer Extension Wizard – Extend a Dimension

 

Use Case 2 – Add a new dimension to the semantic layer and define a new hierarchy in the semantic layer

Scenario: Customer has onboarded a new dimension (column) and corresponding hierarchy from one of Fusion SaaS data stores and want to add this dimension and hierarchy to the FAW presentation layer.

Solution(s): Use the Add a Dimension and Define a Hierarchy Wizards to onboard the new column and expose it to the presentation layer.

Methodology: In this blog, the CEAL Team will walk you through how to enrich the FAW delivered semantic model with a new hierarchy.

Use Case 3 – Add a new Fact in the semantic layer

Scenario: Customer wants to create a derived calculation and add it to an existing FAW fact table.

Solution(s): Use the Add a Calculation and Extend and Oracle Fact Wizards to onboard the new calculation and expose it to the presentation layer.

Methodology: In this blog, the CEAL Team will walk you through how to enrich the FAW delivered semantic model with a new hierarchy.

Use Case 4 – Credit a new subject area in the semantic layer

Scenario: Customer has onboarded data elements (dimension and facts) into a custom schema and wants to be able map the physical tables to the presentation layer so users can build reports on this new subject area.

Solution(s): Use the Create a Subject Area Wizard to organize the new data elements into a subject area and expose it to the presentation layer.

Methodology: In this blog , the CEAL Team will walk you through how to enrich the FAW delivered semantic model with a new hierarchy.

Use Case 5 – Customizing the presentation layer of the semantic layer

Scenario: Customer has created a new subject area and wants to reorganize its content.

Solution(s): Use the Reorder Columns and Show/Hide Columns Wizards to reorganize the data elements in the custom subject area.

Methodology: In this blog , the CEAL Team will walk you through how to customize the FAW delivered presentation layer, by reordering columns and hiding/showing columns.

 

/content/published/api/v1.1/assets/CONT6DA6A71FD43D4943BDD1C9B8F9BFE0ED/Medium?cb=_cache_ab8e&channelToken=12f676b76bf44b4e9b22e6b36ebfe358&format=jpg Reference Architecture - Content Extensibility Use Cases

Overview

FAW offers a new set of analytics objects:

  • Homepage – Collect individual, personal favourites across whole app

  • Decks - Collections of cards that show status of organization at a glance with simple filtering to illuminate status. Sharable sets to service shorter and longer-term needs.

  • Cards / Large Cards – Provide personalizable instantiations of core KPIs. Serve as a location for collaboration, and present adjacent metrics for context or tangential investigation. 

  • KPIs – Capture shared business concepts – core set created/managed by analyst.

  • Detail Analysis – Projects deliver detailed analysis, and bridge to ad hoc exploration.

  • Dataset – Incorporate non-subject area data and support joins for mashups.

  • Subject Areas – Provide Semantic Model

  • Data Pipeline – Support data movement and transformations

  • Data – Oracle Fusion SaaS or other

Figure 8: FAW Semantic Layer Extension Wizards

Use Cases

Across the series of blog, the following use cases will be covered:

Use Case 1 – Create a new KPI in FAW

In this blog <Incoming – Stay tuned!>, the A-Team will walk you through how to create a new FAW KPI.

Use Case 2 – Create a new Card in FAW

In this blog <Incoming – Stay tuned!>, the A-Team will walk you through how to create a new FAW Card and attach the previously created KPI.

Use Case 3 – Create a new Deck in FAW

In this blog <Incoming – Stay tuned!>, the A-Team will walk you through how to create a new FAW Deck and attach the previously created Card.

/content/published/api/v1.1/assets/CONTA7252E5F764F45268C71D61531961AB1/Medium?cb=_cache_ab8e&channelToken=12f676b76bf44b4e9b22e6b36ebfe358&format=jpg Want to Learn More?

Click here for more detail around Oracle Fusion Analytics Warehouse

Click here for more CEAL Team Blogs

Click here for more A-Team Oracle Data Integrator (ODI) Blogs

Click here for more detail around the ODI Salesforce integration capabilities

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 

/content/published/api/v1.1/assets/CONTA00084B96F134CAC92866942DEC1CB71/Medium?cb=_cache_ab8e&channelToken=12f676b76bf44b4e9b22e6b36ebfe358&format=jpg Summary

This article walked through multiple extensibility use cases for Oracle Fusion Analytics  ( aka FAW) covering:

* Data Extensibility Use Cases

* Semantic Extensibility Use Cases

* Content Extensibility Use Cases

This blog is a prologue to a series of forthcoming topics on how to utilize Oracle ETL and Data Integration tools to extract data from various sources and extend Oracle’s delivered Analytics offerings. Stay tuned for more to come!

These blogs are a collaboration effort between various Oracle teams: FAW Product Management, the CEAL, and the A-Team.

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

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


Previous Post

Collecting diagnostics for troubleshooting of UI performance issues in Fusion Apps

Ingolf Loboda | 14 min read

Next Post


Using Python and the Metering API to Produce Custom Cost Reports

Stefan Hinker | 9 min read