Category 1 customizations add columns from the source systems that have pre-packaged adapters and load the data into the existing Oracle Business Analytics Warehouse tables.
Category 1 customizations involve extracting additional columns from the source systems for which pre-packaged adapters are included (for example, Oracle E-Business Suite) and loading the data into the existing Oracle Business Analytics Warehouse tables. For Category 1 customizations, data can also come from non-packaged sources, but this section assumes that you have already mapped the sources with a Universal adapter and you need to extend to capture additional columns. The initial mapping of a Universal adapter is considered a Category 3 customization. For information, see Category 3 Customizations: Adding New Data.
To see additional columns in the Oracle Business Analytics Warehouse, you must pass the columns first through the ETL process. The existing mappings and tables are extensible. Oracle BI Applications provides a methodology to extend preconfigured mappings to include these additional columns and load the data into the existing tables.
Oracle BI Applications recognizes two types of customization: extension and modification. The supported extension logic allows you to add to the existing objects. For example, you can extract additional columns from a source, pass them through the existing mappings, and populate new columns added to an existing table. Generally, Oracle BI Applications does not allow you to modify the existing logic or columns. You should not change the existing calculations to use different columns, and you should not remap the existing columns to be loaded from different sources.
For example, if you want to calculate revenue differently from the existing logic, create a new column (for example, X_REVENUE) and populate it with a custom mapping expression. You can then remap the Oracle Business Intelligence repository to point to the new X_REVENUE column.
Most datastores have a single placeholder column named X_CUSTOM. Each ETL task has mapping expressions to populate this column. These serve as templates for customizing ODI datastores and interfaces. When creating new custom columns, follow the naming convention of including the X_ prefix to help distinguish custom columns.
In the figure, the preconfigured logic is shaded in gray. Do not modify anything contained within these objects. Add customizations to the existing objects rather than creating new packages and interfaces, which allows them to run in parallel to the existing logic.
The most common reason for extending the Oracle Business Analytics Warehouse is to extract the existing columns from a source system and map them to an existing Oracle Business Analytics Warehouse table (either fact or dimension). This type of change typically requires you to extend the interfaces within a SIL package.
If the data is coming from a packaged source, then you must extend the interfaces within an appropriate SDE adapter package. If the data is coming from a non-packaged source, then you must use a Universal adapter package. If an appropriate package does not already exist, you must create a Universal adapter package with interfaces.
To extend an ODI package in the Oracle Business Analytics Warehouse:
This working example describes adding columns from source systems that have pre-packaged adapters and loading the data into the existing Oracle Business Analytics Warehouse tables (known as a Category 1 customization).
In this example, a company has identified additional fields in a source system table HZ_CUST_ACCOUNTS that must be added to the Oracle Business Analytics Warehouse table W_ORG_D. Data is passed from an existing source table to an existing target table, known as a category 1 customization. The company uses an extension field to capture information related to organizations referred to as ACCOUNT_LOG. In addition, the company wants to include the name of the person who last updated the record as an attribute of the organization.
In this example, you want to extract information from the following two fields that are not extracted by the out-of-the-box application:
HZ_CUST_ACCOUNTS.ATTRIBUTE1
ATTRIBUTE1 is currently not extracted from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS.
HZ_CUST_ACCOUNTS.LAST_UPDATE_LOGIN
LAST_UPDATE_LOGIN is currently extracted from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS, but is not loaded into the staging table W_ORG_DS.
The following diagram shows the two fields ATTRIBUTE1 and LAST_UPDATE_LOGIN as they pass from the source system table to the target table via the tables: HZ_CUST_ACCOUNTS to ODI_SQ_BCI_CUSTOMERS to W_ORG_DS to ODI_Sq_W_ORG_DS to W_ORG_D.
The customization is done in two parts:
SDE Processing that extracts data from the source system and loads it into the staging area. See Example of Extracting Data from an Oracle EBS 11.5.10 Data Packaged Source into the ODI Staging Area.
SIL Processing that extracts data from the staging area and loads it into the target table. See Example of Loading Data from the Staging Area into an Existing Target Table.
This section shows how data is extracted from an existing source table into the staging area.
The following diagram shows the new ETL mappings that to load the new data into the staging area, and the ODI Interfaces to modify.
Note:
The new ETL mappings diagram only shows the incremental interfaces.To customize the ETL process for loading these two fields into the staging area:
Extract the HZ_CUST_ACCOUNTS.ATTRIBUTE1 value from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS using the Interfaces SQ_BCI_CUSTOMERS and SQ_BCI_CUSTOMERS_FULL.
Then, load the ODI_SQ_BCI_CUSTOMERS.ATTRIBUTE1 value from the temporary table ODI_SQ_BCI_CUSTOMERS into the X_ACCOUNT_LOG field in the staging table W_ORG_DS using the Interfaces ORG_DS and ORG_DS_FULL.
Load the SQ_BCI_CUSTOMERS.LAST_UPDATE_LOGIN value from the temporary table ODI_SQ_BCI_CUSTOMERS into the X_LAST_LOGIN field in the staging table W_ORG_DS using the Interfaces ORG_DS and ORG_DS_FULL.
Note:
Remember that LAST_UPDATE_LOGIN value is already extracted from the source table HZ_CUST_ACCOUNTS into the temporary table ODI_SQ_BCI_CUSTOMERS, but is not loaded into the staging table W_ORG_DS.To extract data from an Oracle EBS 11.5.10 Data Packaged Source:
Now that you have set up the ETL process for extracting and staging the data, you must load the new data into the data warehouse. See Example of Loading Data from the Staging Area into an Existing Target Table.
This section shows how data is loaded from the staging area into an existing target table.
The following diagram shows the new ETL mappings to load the new data from the staging area into the target table and the ODI Interfaces to modify.
To customize the ETL process to load these two fields into the staging area:
Load the X_ACCOUNT_LOG value and X_LAST_LOGIN value from the staging table W_ORG_DS into the temporary table SQ_W_ORG_DS using the Interfaces Sq_W_ORG_DS and Sq_W_ORG_DS_FULL.
Load the X_ACCOUNT_LOG value and X_LAST_LOGIN value from the temporary table ODI_Sq_W_ORG_DS into the Target table W_ORG_D using the Interfaces ORG_D, ORG_D_FULL, and ORG_D_UNSPC.
To extract data from an Oracle EBS 11.5.10 Data Packaged Source:
Follow these tips to modify the SQ_BCI_Interface:
Make sure that you define a new source table on the right side of a LEFT OUTER join syntax with the existing source tables. Using an INNER join or a RIGHT OUTER join might result in loss of records.
Make sure that you define joins to match on a unique set of values. If you do not define a join that ensures a unique relationship, you might get a Cartesian product, which changes the granularity and results in duplicate errors.
If it is not possible to define a unique join, then do the following:
These types of customization require special handling.
The Oracle BI Applications ETL process supports Type I and Type II slowly changing dimension behavior. Some dimensions are enabled only for Type I behavior while other dimensions are enabled to also support Type II behavior.
Of those dimensions that support Type II behavior, different dimension attributes have different Slowly Changing behavior including some attributes being treated as Type I.
Note:
Modifying the Type-II tracking logic is the only change that you should make to shipped logic.To modify a Category 2 SCD Trigger:
You can modify which columns are treated as Type I or Type II in a dimension that is configured to support Type II behavior. If a dimension is configured to support only Type I behavior, the following changes have no effect as all columns are treated as Type I.
You can add a dimension to an existing fact, adding a dimension and dimension staging datastores as well as associated SDE and SIL processes, which also requires extending the fact and fact staging tables to reflect the association with the new dimension.
This use case is similar to adding a regular Dimension to a fact but in this case, a Date dimension is used. There are several Date related dimension, each representing dates in a different manner (fiscal, enterprise, and so on) and different granularities (day, week, month, etc.).
Joins between a fact and Date dimension table are performed on a Date specific WID column. The Date WID column is a smart key value that represents the date in YYYYMMDD
format. You must not perform a lookup to resolve the ROW_WID of the Date dimension, rather you pass the Date column through the ETL process and convert it to this format.
Each fact table has exactly one canonical Date specific WID column. This is the primary date used to drive various date-related calculations. There is no particular metadata to identify this column, but lookups to effective dated tables use this column in the ETL and various date-related expressions in the repository also use this column. All packaged fact tables have a single canonical date already identified. When creating custom fact tables, nominate one Date WID column as the canonical date and use consistently.
Follow the same steps as adding a dimension to a fact with the following changes. You must not create a custom SDE as you use the existing Date dimension.
You must extend the facts related datastores, W_Fact Name_FS and W_Fact Name_F and tasks to reflect the new dimension.
To extend the facts related datastores and tasks: