Data Applications → Example Data Delivery of Oracle EBS for Data Destinations

Customize Incorta Data Delivery or Oracle EBS

There may be customizations that you would like to make to support your specific business needs. Here are some common customizations along with detailed implementation steps:

Add new columns to existing fact or dimension tables
Add a new dimension to an existing fact table
Add a new fact table
Add a new data source and create facts and dimensions

Add New Columns to Existing Fact or Dimension Tables

You can add new columns to existing fact or dimension tables. Following are the steps for both types of tables:

Add the New Column to the Physical Schema as Needed

Determine if the new column exists in the corresponding Incorta physical schema. For example, to add a new column to the DimSuppliers dimension table, determine if the column already exists in EBS_PARTY_COMMON.AP_Suppliers. If the column exists, continue to the next step. If it does not exist, refer to the Apply Optional Customizations to the Oracle EBS data applications section of Data applications → Install and Configure Oracle EBS for Incorta Cloud to add the column to the Incorta physical schema.

Add the New Column to the Business Schema

Add the column to the appropriate data mart business schema. The name of the business schema has a suffix of DM, the abbreviation for data mart. Within each DM business schema, there is a business view for each fact and dimension table. Here are the steps to add the column to the business view for the example:
- In the Navigation bar, select Business Schema.
- In the Business Schema Manager, select the CommonDimensionsDM business schema to open it.
- In the Business Schema Designer, for the DimSuppliers business view, select More Options (⋮ vertical ellipsis).
- Select Edit.
- To add the new column, drag and drop it from the Data panel to the business schema view.
- In the Action bar, select Done.

Add the New Column to the Materialized View

Add the new column to the corresponding fact or dimension materialized view in the appropriate physical schema. The name of the physical schema will end in _ALL. Here are the steps to add the column to the materialized view for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_COMMON_DIM_ALL schema to open it.
- In the Schema Designer, in the Tables section, select the DimSuppliers table to open it.
- In the Table Editor, select the Materialized icon to open the Data Source dialog.
- In the Data Source dialog, in Script, select Edit Query. Add the new column to the end of the SELECT statement.
- Select Validate.
- Select Done.

Load the Physical Schemas

If you added the new column to the physical schema in the first step, perform a full load of the physical schema. Here are the steps to perform a full load of the physical schema for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_PARTY_COMMON schema to open it.
- In the Schema Designer, in the Action bar, select Load → Full Load.
Perform a full load of the physical schema containing the materialized view. This will also load to the Data Destination. Here are the steps to perform a full load of the materialized view physical schema for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_COMMON_DIM_ALL schema to open it.
- In the Schema Designer, in the Action bar, select Load → Full Load.

Check the Load Status

Check the status of the data load with the following steps:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the _ALL schema of interest to open it.
- In the Schema Designer, in the Tables section, select the desired table to open it.
- In the Columns section, select the Preview Data link.
- In the Preview dialog, review the rows_status column.

Add a New Dimension to an Existing Fact Table

You can add a new dimension to an existing fact table. Following are the steps:

Design and Model the New Dimension

Identify the base EBS source tables and views, and the key column, or composite key, you will use to join the new dimension to the existing fact table.

Add the Source Tables to the Physical Schema as Needed

Determine if the EBS source tables for the new dimension exist in the Incorta physical schema. For example, if you want to add a new dimension called DimPartners, identify the EBS source tables that contain the related data. If the EBS source tables exist, continue to the next step. If they do not exist, refer to the Apply Optional Customizations to the Oracle EBS data applications section of Data applications → Install and Configure Oracle EBS for Incorta Cloud to add the tables to the Incorta physical schema.

Add the Dimension Table to the Business Schema

Add the dimension table to the appropriate data mart business schema. The name of the business schema has a suffix of DM, the abbreviation for data mart. Within each DM business schema, there is a business view for each fact and dimension table. Here are the steps to add the dimension table to the business schema for the example:
- In the Navigation bar, select Business Schema.
- In the Business Schema Manager, select the CommonDimensionsDM business schema to open it.
- In the Business Schema Designer, in the Action bar, select + New → Add New View.
- Name the view DimPartners.
- To add new columns, drag and drop them from the Data panel to the business schema view.
  - Add the LAST_UPDATE_DATE from each of the EBS source tables. If there are multiple last update date columns, add a unique suffix, such as LAST_UPDATE_DATE_PO_HDR. Add logic for null dates: (ifNull(EBS_PO.RCV_SHIPMENT_HEADERS.LAST_UPDATE_DATE,timestamp("1980-01-01 00:00:00.000"))
  - Add the ID column(s) that will be used as the key to join to the same ID in the fact table.
- In the Action bar, select Done.

Create a New Materialized View for the Dimension

Create a new Incorta PostgreSQL materialized view in the corresponding _ALL physical schema. Here are the steps for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_COMMON_DIM_ALL schema to open it.
- In the Schema Designer, in the Action bar, select + New → Derived Table → Materialized View.
  - For Language, select Incorta PostgreSQL.
  - Enable Incremental.
  - Select Edit Query for Script and Incremental Script, and enter the SQL. See below for tips on how to generate the SQL and add logic to the incremental script using the last update date.
  - In properties, select Add Property, and enter the following:
    Key Value
    spark.executor.cores 1
    spark.executor.memory 1G
    spark.driver.memory 3G
    spark.cores.max 1
For Language, select Incorta PostgreSQL.
- Select Validate.
- For Table Name, enter DimPartners.
- In the Table Editor for the columns that are keys in the materialized view, in the Function column, select key.
- Select Done.
  Tip → How to generate a PostgreSQL SELECT statement with the Schema Wizard
  - You can use the Schema Wizard to generate the PostgreSQL with the following steps:
    In the Navigation bar, select Schema.
    In the Action bar, select + New → Schema Wizard.
    In step 1, Choose a Source,
    In Name, enter a physical schema name.
    For Select a Datasource, select Postgres.
    In step 2, Manage Tables,
    Select a data source and a table.
    Select Customize SQL.
    Select Format.
    Copy the SQL and paste it into the Incorta PostgreSQL materialized view Script property.

Key	Value
spark.executor.cores	1
spark.executor.memory	1G
spark.driver.memory	3G
spark.cores.max	1

Tip → How to add last update date logic to the Incremental Script

For the Incremental Script, add logic to get all rows that were modified after the maximum last update date of the dimension materialized view underlying tables.

Load the Physical Schemas

If you added a new table to the physical schema in the first step, perform a full load of the physical schema. Here are the steps to perform a full load for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_PARTY_COMMON schema to open it.
- In the Schema Designer, in the Action bar, select Load → Full Load.
Perform a full load of the schema that contains the new Incorta PostgreSQL materialized view. This will also load the desired Data Destination. Here are the steps to perform a full load of the materialized view physical schema for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_COMMON_DIM_ALL schema to open it.
- In the Schema Designer, in the Action bar, select Load → Full Load.

Check the Load Status

Check the status of the load with the following steps:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the _ALL schema of interest to open it.
- In the Schema Designer, in the Tables section, select the desired table to open it.
- In the Columns section, select the Preview Data link.
- In the Preview dialog, review the rows_status column.

Add a New Fact Table

You can add one or more new fact tables that may involve new dimensions. Following are the steps:

Design and Model the New Fact Table

Identify the base EBS source tables and views, and the key column, or composite key, you will use to join the new fact table to new and existing dimensions.

Add the Source Tables to the Physical Schema as Needed

Determine if the EBS source tables for the new fact table exist in the Incorta physical schema. For example, if you want to add a new fact called FactBulkOrder, identify the EBS source tables that contain the related data. If the EBS source tables exist, continue to the next step. If they do not exist, refer to the Apply Optional Customizations to the Oracle EBS data applications section of Data applications → Install and Configure Oracle EBS for Incorta Cloud to add the tables to the Incorta physical schema.
Add the fact table to the appropriate data mart business schema. The name of the business schema has a suffix of DM, the abbreviation for data mart. Within each DM business schema, there is a business view for each fact and dimension table. Here are the steps to add the fact table to the business schema for the example:
- In the Navigation bar, select Business Schema.
- In the Business Schema Manager, select the PurchaseOrderDM business schema to open it.
- In the Business Schema Designer, in the Action bar, select + New → Add New View.
- Name the view FactBulkOrder.
- To add new columns, drag and drop them from the Data panel to the business schema view.
  - Add the LAST_UPDATE_DATE from each of the EBS source tables. If there are multiple last update date columns, add a unique suffix. such as LAST_UPDATE_DATE_PO_HDR. Add logic for null dates: (ifNull(EBS_PO.RCV_SHIPMENT_HEADERS.LAST_UPDATE_DATE,timestamp("1980-01-01 00:00:00.000"))
  - Add the ID column(s) that will be used as the key to join each dimension table to the new fact table.
- In the Action bar, select Done.

Create a New Materialized View for the Fact Table

Create a new Incorta PostgreSQL materialized view in the corresponding _ALL physical schema. Here are the steps for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_PO_FACTS_ALL schema to open it.
- In the Schema Designer, in the Action bar, select + New → Derived Table → Materialized View.
  - For Language, select Incorta PostgreSQL.
  - Enable Incremental.
  - Select Edit Query for Script and Incremental Script, and enter the SQL. See below for tips on how to generate the SQL and add logic to the incremental script using the last update date.
  - In properties, select Add Property, and enter the following:
    Key Value
    spark.executor.cores 2
    spark.executor.memory 2G
    spark.driver.memory 3G
    spark.cores.max 4
For Language, select Incorta PostgreSQL.
- Select Validate.
- For Table Name, enter DimPartners.
- In the Table Editor for the columns that are keys in the materialized view, in the Function column, select key.
- Select Done.
  Tip → How to generate a PostgreSQL SELECT statement with the Schema Wizard
  - In the Navigation bar, select Schema.
  - In the Action bar, select + New → Schema Wizard.
  - In step 1, Choose a Source,
    In Name, enter a physical schema name.
    For Select a Datasource, select Postgres.
  - In step 2, Manage Tables,
    Select a data source and a table.
    Select Customize SQL.
    Select Format.
    Copy the SQL and paste it into the Incorta PostgreSQL materialized view Script property.

Key	Value
spark.executor.cores	2
spark.executor.memory	2G
spark.driver.memory	3G
spark.cores.max	4

Tip → How to add last update date logic to the Incremental Script

For the Incremental Script, add logic to get all rows that were modified after the maximum last update date of the dimension materialized view underlying tables.

* Perform a full load of the physical schema you added a new table to in the first step. Here are the steps to perform a full load for the example: * In the Navigation bar, select **Schema**. * In the Schema Manager, select the **Schemas** tab. * In the list view of physical schemas, select the **EBS_PO** schema to open it. * In the Schema Designer, in the Action bar, select **Load** → **Full Load**.

Load the Physical Schemas

Perform a full load of the schema that contains the new Incorta PostgreSQL materialized view. This will also load the Data Destination. Here are the steps to perform a full load of the materialized view physical schema for the example:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the EBS_PO_FACTS_ALL schema to open it.
- In the Schema Designer, in the Action bar, select Load → Full Load.

Check the Load Status

Check the status of the data load with the following steps:
- In the Navigation bar, select Schema.
- In the Schema Manager, select the Schemas tab.
- In the list view of physical schemas, select the _ALL schema of interest to open it.
- In the Schema Designer, in the Tables section, select the desired table to open it.
- In the Columns section, select the Preview Data link.
- In the Preview dialog, review the rows_status column.

Add a New Data Source and Create Facts and Dimensions

You can add one or more new fact or dimension tables from data sources other than Oracle EBS. Following are the steps:

Design and model the fact table and related dimensions, if they do not already exist. Identify the data source tables and views, and the key column, or composite key, you will use to join the new fact table to new and existing dimensions.
Create a new external data source in Incorta. Refer to References → Connectors for a list of available connectors and detailed instructions on how to connect to your external data source.
Create a new physical schema or add to an existing physical schema to create the tables from the new data source. Refer to Tools → Schema Manager for detailed instructions on how to create or modify a physical schema.
Perform a full load of the new or modified existing physical schema.
Refer to the previous customization sections for steps on how to add new columns, dimensions, and facts.

Additional Information

Troubleshooting

For large tables, to limit full data loads, add a SQL clause to the table query: WHERE CREATION_DATE > <DATE>. You may need to contact Incorta Support to increase your cluster size.
Some of the formula columns will be incompatible. In this situation, the formula columns can be created in Power BI.
A data type mismatch may occur if the parquet data types are not mapped properly to the MS SQL data types.

Support for Source System Row Deletion

Incorta supports the deletion of rows in the source system through use of security filters. For more information, refer to Discussions on the Incorta Community.

About Entity Object Naming Conventions for the EBS Data Application

Oracle EBS physical schemas have a prefix of EBS.
Business schemas with facts and dimensions have a suffix of DM, the abbreviation for data mart.
Dimension business views and materialized views have a prefix of Dim.
Fact business views and materialized views have a prefix of Fact.