Introduction

With Contributions from

Shobana Kapyarath, ERP Analytics Product Manager, Oracle Analytics

Sushil Dhoot, Senior Principal Product Manager, Oracle Analytics

Oracle Fusion Data Intelligence (FDI) extends the value of Oracle Fusion Cloud Applications with packaged analytics to help monitor, align, and act on essential insights. Fusion Enterprise Resource Planning (ERP) Analytics provides numerous key metrics that help business monitor their financial performance.

FAW has General Ledger (GL) Balance Sheet and GL Profitability subject areas that provide Balance Sheet and Profit and Loss metrics. These metrics are derived from the prebuilt financial categories in ERP. It is essential to properly define the financial categories in ERP to ensure the Key Performance Indicators (KPIs) and metrics are accurately calculated. Customers have specific business needs that require them to build additional metrics. This blog describes one requirement that defines custom metrics using hierarchies defined in ERP.

Prerequisites

Ensure you have completed the prerequisite steps for ERP in FAW. See this article for complete details: https://blogs.oracle.com/analytics/post/prerequisites-for-oracle-fusion-erp-analytics

Use Case

The business must create a Gross Margin Report with Revenue, Cost of Goods Sold, and Gross Margin. The report has the following metrics, and this post walks through setting up the formula for each one. This blog focuses on creating a Gross Margin report by defining metrics using the Natural Account hierarchy defined in ERP.

Gross Margin Report

Revenue – Consulting
Revenue – Hardware
Revenue – Services
Revenue – Support
Revenue – Insurance
Revenue – Misc
Revenue – I/C
Total Revenue
Total Cost of Sales
Gross Margin

Oracle Fusion Cloud ERP (Fusion ERP) has only one financial category, “REVENUE”. The GL Profitability subject area uses this financial category to derive the Revenue metric for Profit and Loss. Customers must create custom metrics for a Gross Margin Report with revenue broken up by various revenue streams. This is achieved by creating custom metrics using custom financial categories or account hierarchies.

To create metrics based on custom financial categories, refer to the steps mentioned here: https://blogs.oracle.com/analytics/post/custom-general-ledger-metrics-in-fusion-analytics-warehouse-using-semantic-model-extensions.

To create custom financial categories for the various Revenue items, map the Natural Account codes to these custom financial categories and define the custom metrics.

The below screenshot shows the Natural Account hierarchy definition in Fusion ERP. Create a report in FAW using any GL-related subject area to see the hierarchy definitions.

Hierarchy Level Information

FAW Report Screenshot

FAW Report

Once the Hierarchy Tree and Level Code are identified for use in the report, proceed with Semantic Model Extensions.

Semantic Model Extensions

This section shows how to use Semantic Model Extensions to create metrics based on the custom financial category.

  1. Log in to FAW with the Modeler role. For information on roles, see the Oracle Fusion Data Intelligence Administration Guide.
  2. Launch the FAW Console and click Semantic Model Extensions.
  3. Click Create Branch.
  4. Provide the Name and Description and click Done.

Create Branch

5. Click Add Step and select Create a Subject Area.
6. Create the subject area based on Financials – GL Profitability.

Create SA

7.  Select all data elements and click Next.

Data Elements

8.  Click Next to proceed.

Create SA

9.  Click Finish.

10.  Click Add Step and click Add Columns.

Add Columns

Add Columns2

11.  Select the following options from the dropdowns and click Next:

Target Subject Area = Custom GL Profitability
Folder = GL Balance (AC)
Logical Table = Fact – Fins – GL Balance

SA selection

Create a Metric Column

  1. Create a Revenue – Consulting metric using the Natural Account hierarchy definition shown earlier.
  2. Enter the following into the formula:
IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency"*(-1) using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4130'),0)

Revenue Consulting

3.  Click Validate and then click Save.

Note: The Natural Account Level used in the metric definition, e.g., Natural Account Level 29 Code, and the value to be used, e.g., 4130, are based on the Hierarchy Report screenshot, shown earlier in the blog.

Create Additional Metrics

Create the following additional metrics.

Revenue – Hardware

Enter the following formula: 

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4110')*(-1),0)

Revenue Consulting

Revenue – Services

Enter the following formula:

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code"= '4145')*(-1),0)

Revenue Services

Revenue – Support

Enter the following formula:

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4120')*(-1),0)

Revenue Support

Revenue – Insurance

Enter the following formula:

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4121')*(-1) ,0)+

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4122')*(-1),0) +

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4123')*(-1),0)

Revenue Insurance

Revenue – Misc

Enter the following formula:

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4150')*(-1),0)

Revenue Misc

Revenue – I/C

Enter the following formula:

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '4160')*(-1),0)

Revenue IC

Total Revenue

Enter the following formula:

"Core"."Fact - Fins - GL Balance"."Revenue - Consulting" + "Core"."Fact - Fins - GL Balance"."Revenue - Hardware" + "Core"."Fact - Fins - GL Balance"."Revenue - Services" + "Core"."Fact - Fins - GL Balance"."Revenue - Support" + "Core"."Fact - Fins - GL Balance"."Revenue - Insurance" + "Core"."Fact - Fins - GL Balance"."Revenue - Misc" + "Core"."Fact - Fins - GL Balance"."Revenue - I/C"

Total Revenue

Total Cost of Sales

Enter the following formula:

IFNULL (FILTER ("Core"."Fact - Fins - GL Balance"."Activity Amount in Analytics Currency" using "Core"."Dim - Natural Account Hierarchy"."Natural Account Level 29 Code" = '5000'),0)

Total Cost of Sales

Gross Margin

Enter the following formula:

"Core"."Fact - Fins - GL Balance"."Total Revenue" - "Core"."Fact - Fins - GL Balance"."Total Cost of Sales"

Gross Margin

Click Save, Next, and Finish to complete the wizard.

To add the metrics to other subject areas, select them and click Finish.

Finish Metrics

Use the Reorganization wizard to make other changes to this subject area.

For more information, check this article on Customizing Subject Areas in Fusion Data Intelligence.

Publish the Model

  1. Display the Semantic Model Extensions page and click Publish Model.
  2. Select Custom GL Metrics Based on Hierarchies from the User Extensions dropdown.
  3. Click Publish.

Publish Model

Create a Report

1.  Create a report using the custom metrics.

FAW Report with Custom Metrics

2.  After validating them, click Merge to Main to merge it with the main branch.

Call to Action

For more details, see Customize Oracle Fusion Data Intelligence.