Blueprints → Incorta Intelligent Ingest of Oracle Cloud ERP for Azure Synapse

About Incorta Intelligent Ingest of Oracle Cloud ERP for Azure Synapse

Incorta Intelligent Ingest streamlines the integration between Oracle Cloud ERP and Microsoft Azure Synapse Analytics. Incorta Intelligent Ingest accelerates data mart deployment and automates data model design and source schema mapping with the Incorta Oracle Cloud ERP blueprint. The Incorta Cloud ERP blueprint provides a new, faster, more effective option for analyzing and understanding your data, with no data modeling or extract, transform, load (ETL) required.

Following is the high-level Incorta Intelligent Ingest process:

  • Connect Incorta to your data source with a blueprint, and load pre-existing physical schema fact and dimension tables.
  • The physical schema tables are replicated to the Parquet that resides on Azure Data Lake Storage (ADLS) Gen 2.
  • Load Synapse data marts from the parquet.
  • Visualize the data with Power BI.

Configure Oracle Cloud ERP for Azure Synapse

To configure the ingest of Oracle Cloud ERP for Azure Synapse, the following steps are required:

Prerequisite

Request the following files from Incorta Support:

  • OracleFusion_<VERSION_NUMBER>.zip
  • incorta_synapse-<VERSION>.egg
  • Synapse_schema_fusion_<VERSION>.zip
  • Synapse_business_schema_fusion_<VERSION>.zip
  • Oracle Cloud ERP PB Reports-<VERSION>.zip

Configure Intelligent Ingest

Follow the Configure Incorta Intelligent Ingest for Azure Synapse steps in these sections:

Install and configure Oracle Cloud ERP for Incorta On-Premises

Follow the Blueprints → Install and Configure Oracle Cloud ERP for Incorta On-Premises steps in these sections:

Import the Synapse schemas into Incorta

Import the Synapse physical schema file, Synapse_schema_fusion_<VERSION>.zip, as follows:

  • In the Navigation bar, select Schema.
  • In the Action bar, select + NewImport Schema.
  • In the Import Schema dialog, do one of the following:
    • Drag Synapse_schema_fusion_<VERSION>.zip to the Import Schema dialog.
    • Select Click or drag a file here to upload to select Synapse_schema_fusion_<VERSION>.zip, and then select Open.
  • Wait until the physical schema(s) and scheduled load job(s), if any, are imported.
  • In the Import Results dialog, review the status of the import operation.
  • Select Close.

Import the Synapse business schema file, Synapse_business_schema_fusion_<VERSION>.zip, as follows:

  • In the Navigation bar, select Business Schema.
  • In the Action bar, select + NewImport Business Schema.
  • In the Import Business Schema dialog, do one of the following:
    • Drag Synapse_business_schema_fusion_<VERSION>.zip to the Import Schema dialog.
    • Select Click or drag a file here to upload to select Synapse_business_schema_fusion_<VERSION>.zip, and then select Open.
  • In the Import Results dialog, review the status of the import operation.
  • Select Close.

Set a Data Destination for the Fact and Dimension physical schemas

Follow the steps in Configure Incorta Intelligent Ingest for Azure Synapse → Set a Data Destination for a physical schema to set a Synapse Data Destination for each Fact and Dimension table listed for the applicable module(s).

Business ProcessModuleFact and Dimension Physical Schemas
Procure To PayAdvanced Payables (AP)
Procurement (PO)
FUSION_COMMON_DIM_ALL
FUSION_AP_FACTS_ALL
FUSION_PO_FACTS_ALL
Order to CashAccount Receivables (AR)
Order Management (OM)
FUSION_COMMON_DIM_ALL
FUSION_AR_FACTS_ALL
FUSION_OM_FACTS_ALL
General LedgerGeneral Ledger (GL)FUSION_COMMON_DIM_ALL
FUSION_GL_FACTS_ALL
Projects (PPM)Projects (PPM)FUSION_COMMON_DIM_ALL
FUSION_PRJ_FACTS_ALL
HCM CoreHCM CoreFUSION_COMMON_DIM_ALL
FUSION_HCM_FACTS_ALL
GL Budgetary ControlBudgetary ControlFUSION_COMMON_DIM_ALL
FUSION_BUDGET_CONTROL_FACTS_ALL

Load the physical schemas

Prerequisite

Before loading the Oracle Cloud ERP Fact and Dimension schemas, and the Load_to_Synapse schemas, load the Oracle Cloud ERP base schemas as outlined in Blueprints → Install and Configure Oracle Cloud ERP for Incorta On-Premises → Perform a Full Load of the Physical Schemas.

Here are the steps to perform a full load for each physical schema:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select the Schemas tab.
  • In the list view of physical schemas, select the base schema to open it.
  • In the Schema Designer, in the Action bar, select LoadLoad nowFull.

Load the Base physical schemas

Business ProcessModulePhysical Schema Load Order
Procure To PayAdvanced Payables (AP)
Procurement (PO)
Fusion_CAL_COMMON
Fusion_Item_Common
Fusion_HR_Common
Fusion_FND_Common
Fusion_FIN_Common
Fusion_PARTY_COMMON
Fusion_AP
Fusion_PO
Fusion_INV
Order to CashAccount Receivables (AR)
Order Management (OM)
Fusion_CAL_COMMON
Fusion_Item_Common
Fusion_HR_Common
Fusion_FND_Common
Fusion_FIN_Common
Fusion_PARTY_COMMON
Fusion_AR
Fusion_ONT
Fusion_AR_SNP
Fusion_AR_MV
General LedgerGeneral Ledger (GL)Fusion_CAL_COMMON
Fusion_Item_Common
Fusion_HR_Common
Fusion_FND_Common
Fusion_FIN_Common
Fusion_PARTY_COMMON
Fusion_GL
Fusion_XLA
Fusion_XLA_MV
Projects (PPM)Projects (PPM)Fusion_CAL_COMMON
Fusion_Item_Common
Fusion_HR_Common,
Fusion_FND_Common
Fusion_FIN_Common
Fusion_PARTY_COMMON
Fusion_PRJ
Fusion_GMS
HCM CoreHCM CoreFusion_CAL_COMMON
Fusion_HR_Common
Fusion_HCM
GL Budgetary ControlBudgetary ControlFusion_CAL_COMMON
Fusion_Item_Common
Fusion_HR_Common
Fusion_FND_Common
Fusion_FIN_Common
Fusion_PARTY_COMMON
Fusion_Budgetary_Control

Load the Fact and Dimension physical schemas

Perform a load of the Fact and Dimension physical schemas for each Oracle Cloud ERP module:

Business ProcessModuleFact and Dimension Physical Schema Load Order
Procure To PayAdvanced Payables (AP)
Procurement (PO)
FUSION_COMMON_DIM_ALL
FUSION_AP_FACTS_ALL
FUSION_PO_FACTS_ALL
Order to CashAccount Receivables (AR)
Order Management (OM)
FUSION_COMMON_DIM_ALL
FUSION_AR_FACTS_ALL
FUSION_OM_FACTS_ALL
General LedgerGeneral Ledger (GL)FUSION_COMMON_DIM_ALL
FUSION_GL_FACTS_ALL
Projects (PPM)Projects (PPM)FUSION_COMMON_DIM_ALL
FUSION_PRJ_FACTS_ALL
HCM CoreHCM CoreFUSION_COMMON_DIM_ALL
FUSION_HCM_FACTS_ALL
GL Budgetary ControlBudgetary ControlFUSION_COMMON_DIM_ALL
FUSION_BUDGET_CONTROL_FACTS_ALL

Load the Load_to_Synapse physical schemas

Here are the Load_to_Synapse physical schemas for each Oracle Cloud ERP module with the load order:

Business ProcessModuleFact and Dimension Physical Schema Load Order
Procure To PayAdvanced Payables (AP)
Procurement (PO)
Load_to_Synapse_FUSION_COMMON_DIM_ALL
Load_to_Synapse_FUSION_AP_FACTS_ALL
Load_to_Synapse_FUSION_PO_FACTS_ALL
Order to CashAccount Receivables (AR)
Order Management (OM)
Load_to_Synapse_FUSION_COMMON_DIM_ALL
Load_to_Synapse_FUSION_AR_FACTS_ALL
Load_to_Synapse_FUSION_OM_FACTS_ALL
General LedgerGeneral Ledger (GL)Load_to_Synapse_FUSION_COMMON_DIM_ALL
Load_to_Synapse_FUSION_GL_FACTS_ALL
Projects (PPM)Projects (PPM)Load_to_Synapse_FUSION_COMMON_DIM_ALL
Load_to_Synapse_FUSION_PRJ_FACTS_ALL
HCM CoreHCM CoreLoad_to_Synapse_FUSION_COMMON_DIM_ALL
Load_to_Synapse_FUSION_HCM_FACTS_ALL
GL Budgetary ControlBudgetary ControlLoad_to_Synapse_FUSION_COMMON_DIM_ALL
Load_to_Synapse_FUSION_BUDGET_CONTROL_FACTS_ALL

Run the row counts schema

  • Verify if the row counts match between Incorta and Synapse

  • Import the schema zip file schema_fusion_FUSION_RowCounts.zip

  • Import the business schema zip file business_schema_fusion_FusionRowCount.zip

  • Import the dashboard dashboard_fusion_Load_Statistics_for_Ingest.zip

  • Create a SQL Server datasource called Synapse_Debug

  • In the Synapse data source, update the username, password, and connection string according your instance

  • Load the FUSION_RowCounts schema

  • Select to the Contents tab, and then select ‘Load Statistics for Ingest’ dashboard

Import the Power BI dashboards and verify the data

Additional Information

Here are the star schema models for each Oracle Cloud ERP Module.

Procure to Pay

Order to Cash

General Ledger

General Ledger Budgetary Control

Projects (PPM)

Billing and Revenue

Costing and Budgeting

HCM Core