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.
- Drag
- 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.
- Drag
- 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 Process | Module | Fact and Dimension Physical Schemas |
---|---|---|
Procure To Pay | Advanced Payables (AP) Procurement (PO) | FUSION_COMMON_DIM_ALL FUSION_AP_FACTS_ALL FUSION_PO_FACTS_ALL |
Order to Cash | Account Receivables (AR) Order Management (OM) | FUSION_COMMON_DIM_ALL FUSION_AR_FACTS_ALL FUSION_OM_FACTS_ALL |
General Ledger | General Ledger (GL) | FUSION_COMMON_DIM_ALL FUSION_GL_FACTS_ALL |
Projects (PPM) | Projects (PPM) | FUSION_COMMON_DIM_ALL FUSION_PRJ_FACTS_ALL |
HCM Core | HCM Core | FUSION_COMMON_DIM_ALL FUSION_HCM_FACTS_ALL |
GL Budgetary Control | Budgetary Control | FUSION_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 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
Business Process | Module | Physical Schema Load Order |
---|---|---|
Procure To Pay | Advanced 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 Cash | Account 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 Ledger | General 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 Core | HCM Core | Fusion_CAL_COMMON Fusion_HR_Common Fusion_HCM |
GL Budgetary Control | Budgetary Control | Fusion_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 Process | Module | Fact and Dimension Physical Schema Load Order |
---|---|---|
Procure To Pay | Advanced Payables (AP) Procurement (PO) | FUSION_COMMON_DIM_ALL FUSION_AP_FACTS_ALL FUSION_PO_FACTS_ALL |
Order to Cash | Account Receivables (AR) Order Management (OM) | FUSION_COMMON_DIM_ALL FUSION_AR_FACTS_ALL FUSION_OM_FACTS_ALL |
General Ledger | General Ledger (GL) | FUSION_COMMON_DIM_ALL FUSION_GL_FACTS_ALL |
Projects (PPM) | Projects (PPM) | FUSION_COMMON_DIM_ALL FUSION_PRJ_FACTS_ALL |
HCM Core | HCM Core | FUSION_COMMON_DIM_ALL FUSION_HCM_FACTS_ALL |
GL Budgetary Control | Budgetary Control | FUSION_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 Process | Module | Fact and Dimension Physical Schema Load Order |
---|---|---|
Procure To Pay | Advanced 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 Cash | Account 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 Ledger | General 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 Core | HCM Core | Load_to_Synapse_FUSION_COMMON_DIM_ALL Load_to_Synapse_FUSION_HCM_FACTS_ALL |
GL Budgetary Control | Budgetary Control | Load_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
- 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.