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:
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.
Recommended configurations
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).
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?
- For more information about other data sources and workloads, see Data sources and workloads.
- For more information about the steps for deployment in production environments, see Cortex Framework Data Foundation deployment prerequisites.