Cortex Framework: integration with Oracle EBS

The Oracle EBS (E-Business Suite) integration supports Order to Cash data models with data ingestion using Incorta. Incorta uses a hosted or private instance to ingest the data from Oracle into a BigQuery CDC dataset and handles the CDC processing. From there, Cortex Framework transforms and materializes the CDC data into reporting assets using Cloud Composer to orchestrate BigQuery jobs.

The following diagram describes how Oracle EBS data is available through the Oracle EBS operational workload:

Oracle EBS data source

Figure 1. Cortex Framework-Oracle EBS-Incorta Integration Overview .

Deployment configuration

The following table shares the parameters for configuring the Oracle EBS workload: The config.json file configures the settings required to transfer data from any data source, including Oracle EBS. This file contains the following parameters for Oracle EBS:

Parameter Meaning Default value Description Matching Oracle source field
OracleEBS.itemCategorySetIDs Item category sets [1100000425] List of sets to use for categorizing items. MTL_ITEM_CATEGORIES.CATEGORY_SET_ID
OracleEBS.currencyConversionType Currency conversion type "Corporate" Type of currency conversion to use in aggregate tables. GL_DAILY_RATES.CONVERSION_TYPE
OracleEBS.currencyConversionTargets Currency conversion targets ["USD"] List of target currencies to include in aggregate tables. GL_DAILY_RATES.TO_CURRENCY
OracleEBS.languages Languages ["US"] List of languages to present translations of fields like item descriptions. FND_LANGUAGES.LANGUAGE_CODE
OracleEBS.datasets.cdc CDC dataset - CDC dataset. -
OracleEBS.datasets.reporting Reporting dataset "REPORTING_OracleEBS" Reporting dataset. -

Data Ingestion

Reach out to an Incorta representative and refer to Oracle EBS for Google Cortex Setup Guide for details on ingesting data from Oracle to BigQuery.

Although Incorta supports scheduling data ingestion jobs at various intervals, for optimal performance and data freshness, we recommend scheduling Incorta data ingestion jobs to run daily. If your use case requires handling deleted data, make sure to enable those by following the instructions in the Incorta documentation, Handling source deletes.

Reporting configurations

This section outlines the necessary reporting configurations for your environment.

Cloud Composer Airflow connection

Create a BigQuery Airflow connection named oracleebs_reporting_bq that will be used by the BigQuery operator to perform reporting transforms. See more details in the Manage Airflow connections documentation.

Materializer settings

Find materialization settings in src/OracleEBS/config/reporting_settings.yaml. By default the dimension, header, and aggregate tables are materialized daily. The reporting layer tables are also date partitioned. Partitions and clustering can be customized if needed. For more information, see Cluster Settings and Table Partition.

Data Model

This section describes the Oracle EBS Order to Cash Logical Data Model. Each subsection explains the following Oracle EBS Entity Relationship Diagram (ERD).

Oracle EBS ERD

Figure 2. Oracle EBS: Entity Relationship Diagram.

Base fact views

These are the blue objects in the ERD and are views on CDC tables with no transforms other than some column name aliases.

Dimension tables

These are the purple objects in the ERD and contain the relevant dimensional attributes used by the reporting tables. By default, these dimensions are filtered down based on the deployment configuration parameter values where applicable. This integration also uses the Cortex K9 Gregorian calendar dimension for date attributes, which is deployed by default.

Header tables

These are the green objects in the ERD and contain the joined facts and dimensions that describe business entities like orders and invoices at the header level. The header tables are partitioned by a primary event date corresponding to each entity, for example ORDERED_DATE or INVOICE_DATE.

Nested and repeated Lines

The SalesOrders and SalesInvoices tables contain nested repeated fields named LINES. These fields group the various order lines and invoice lines under their associated headers. To query these nested fields, use the UNNEST operator to flatten the elements into rows as shown in the provided sample scripts (src/OracleEBS/src/reporting/ddls/samples/).

Nested and repeated attributes

Some tables contain additional nested repeated fields such as ITEM_CATEGORIES or ITEM_DESCRIPTIONS where multiple values of the same attribute may apply to the entity. If unnesting these repeated attributes, be sure to filter down to a single attribute value to avoid over counting measures.

Applied Receivables

SalesAppliedReceivables is a unique table in that the entities can reference either invoices on their own, or an invoice with a cash receipt. As such there are nested (but not repeated) INVOICE and CASH_RECEIPT fields, where the CASH_RECEIPT field is only populated when APPLICATION_TYPE = 'CASH'.

Aggregate tables

These are the red objects in the ERD and aggregate from the header tables up to daily measures. Each of these tables are also partitioned by a primary event date. The aggregate tables only contain additive measures (for example, counts, sums) and don't include measures like averages and ratios. This means that users must derive the non-additive measures to ensure they can be derived appropriately when aggregating up to a higher grain, such as monthly. See sample scripts like src/OracleEBS/src/reporting/ddls/samples/SalesOrderAggMetrics.sql.

Currency conversion Amounts

Each aggregate table uses the CurrencyRateMD dimension to create a nested repeated field of AMOUNTS containing currency measures converted into each of the target currencies specified in the deployment configuration. When using these measures, make sure to filter to a single target currency or group on target currencies for reporting to avoid over counting. This can also be seen in the sample scripts like src/OracleEBS/src/reporting/ddls/samples/SalesOrderAggMetrics.sql.

Nested line attributes and measures

The SalesOrdersDailyAgg table contains a nested repeated field named LINES to differentiate between line level attributes and measures (for example, ITEM_CATEGORY_NAME and AMOUNTS) versus header level attributes and measures (for example, BILL_TO_CUSTOMER_NAME and NUM_ORDERS). Take care to query these grains separately to avoid over counting.

Although invoices also have a notion of headers versus lines, the table SalesInvoicesDailyAgg only contains measures at the line level, so it doesn't follow the same structure as SalesOrdersDailyAgg.

What's next?