Data Applications → 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 data application. The Incorta Cloud ERP data application 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 data application, 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:

• Configure Incorta Intelligent Ingest
• Install and configure Oracle Cloud ERP for Incorta On-Premises
• Import the Synapse schemas into Incorta
• Set a Data Destination for the Fact and Dimension physical schemas
• Load the physical schemas
• Import the Power BI dashboards and verify the data

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:

• Enable Incorta Intelligent Ingest
• Configure Spark for Intelligent Ingest
• Configure Your Azure Synapse Analytics SQL Pool
• Create an Azure Synapse Data Destination

Install and configure Oracle Cloud ERP for Incorta On-Premises

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

• Install the Oracle Cloud ERP data application for Incorta On-Premises, which provides the details for downloading and installing the Oracle Cloud ERP tenant
• Configure the Oracle Cloud ERP data application

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 + New → Import 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 + New → Import 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).

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 Data applications → 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 Load → Load now → Full.

Load the Base physical schemas

Load the Fact and Dimension physical schemas

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

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:

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

• Verify the data in the Synapse SQL Pool. Refer to Data applications → Configure Incorta Intelligent Ingest for Azure Synapse.
• Import the Power BI dashboard file, Oracle Cloud ERP PB Reports-<VERSION>.zip, to the Power BI client.
• Configure the dashboard to point to the SQL pool.
• Verify the dashboard 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