How to add new fact tables in Oracle BI Apps 11.1.1.8.1

It is often seen that certain key business requirements are not covered by the pre-built contents (OOTB - Out Of The Box). For example, the OOTB Data Warehouse may not include some reporting metrics and you need to source it from the primary data source (OLTP database) or external secondary data stores (i.e. file, RDBMS, XML, and OLAP). There are number of situations where there is a need to modify the OOTB contents. This article describes how to do customizations in case we are required to build new Fact tables for the BI Apps 11.1.1.8.1.

In these cases it is important to follow certain guidelines and techniques when developing non-standard BI elements. The idea is to follow the same design techniques and guidelines that BI Apps uses to build OOTB contents for customization. This approach would be helpful to maintain the Oracle’s high standard development practices. 

How to add new fact to the Oracle Business Analytics Warehouse?

1. Create custom fact datastores

Create custom Fact Staging and Fact tables in the database. Name all the newly created tables as WC_. These datastores include 2 following required columns:

• INTEGRATION_ID: Stores the primary key or the unique identifier of a record as in the source table.
• DATASOURCE_NUM_ID: Stores the data source from which the data is extracted.

In this document, we use WC_MASAN_PROC_FST_FS and WC_MASAN_PROC_FST_F which are the custom datastores of Procurement Analytics as sample templates.

Create 2 these custom tables in DW database, then import into ODI using the BI Apps RKM.

Note that the specific submodel that a table belongs to drives the table maintenance behavior. For example, tables in the ‘Fact Stage’ submodel will always be truncated during each ETL run while tables in the ‘Fact’ submodel are only truncated during a Full ETL run.

2. Create custom SDE and SIL tasks

Before creating the custom SDE and SIL tasks, you should create new SDE and SIL Adaptor folders named as CUSTOM_<Original Folder Name> (e.g. CUSTOM_SDE_ORAR122_Adaptor) and CUSTOM_SILOS then you can put the custom SDE and SIL tasks into.

In ODI Studio, create the SDE and SIL task in the Custom SDE and SIL adaptor folders. In this document, we use SDE_ORA_MasanProcForecastPriceFact and SIL_MasanProcForecastPriceFact as sample templates, as shown below:

These tasks include the logic required to populate the data to the custom columns.

Dummy Interface

If you just use the Procedures to create the tasks and no need to use the Interfaces, then you should create a Dummy Interface which the Source and Target Datastore are the same and added the filter as shown below

The purpose of creating this Interface is OBI Apps will include automatically the Pre-Steps (Initialize) and Post-Steps (Finalize) (i.e. gather stats) in the generated Load Plan as shown below and adding filter is to not populate the data the target table using this interface.

Put the Interface is followed by the Procedure in the Package as following example:

Generate the custom scenario

Once you done the custom packages creation, generate the scenarios for those tasks named as <Folder Name>_<Package Name>

(e.g. SDE_ORAR122_ADAPTOR_SDE_ORA_MASANPROCFORECASTPRICEFACT and SILOS_SIL_MASANPROCFORECASTPRICEFACT).

3. Add foreign key constraint to all dimensions associated with custom fact

Add a foreign key constraint to all dimension tables associated with this fact. The foreign key constraint ensures the Dimension SIL task is included in the generated load plan. The Dimension SDE task will be included in the generated load plan because it populates the staging table that is used as a source for the Dimension SIL task.

• Drill into the Fact datastore.
• Right-click the ‘Constraints’ subfolder below the Fact datastore and select New Reference. The naming convention is FK_<Fact Table>_<Dimension Table><numeric suffix> (e.g. FK_WC_MASAN_PROC_FST_F_W_PRODUCT_D).
• Set the Type to ‘User Reference’, select the dimension from the Table drop-down list and in the Column subtab, add new column, then select the custom WID column in the fact table and the ROW_WID column in the dimension table.

Include the custom facts step automatically in the Generated Load Plan

Add the SDE step to ‘3 SDE Fact X_CUSTOM_FG <Product Line Version Code>’ Load Plan Component:

• In Designer, navigate to Load Plans and Scenarios -> BIAPPS Load Plan -> Load Plan Dev Components à SDE -> <Product Line Version Code> and double-click the ‘3 SDE Fact X_CUSTOM_FG <Product Line Version Code>’ Load Plan Component.
• Select the ‘X_CUSTOM_FG’ step.
• Drag the custom scenarios from left pane to the right pane and arrange in serial or parallel steps
• Provide the Scenario Version should be -1 (latest version) and Step Name should match the Task name (e.g. SIL_MasanProcForecastPriceFact). Set the Restart Type to ‘Restart from failed step’.

Add the SIL step to ‘3 SIL Fact X_CUSTOM_FG’ Load Plan Component:

• In Designer, navigate to Load Plans and Scenarios -> BIAPPS Load Plan -> Load Plan Dev Components -> SIL and double-click the ‘3 SDE Fact X_CUSTOM_FG’ Load Plan Component.
• Select the ‘X_CUSTOM_FG’ step.
• Drag & Drop the custom scenarios from left pane to the right pane and arrange in serial or parallel steps
• Provide the Scenario Version should be -1 (latest version) and Step Name should match the Task name (e.g. SIL_MasanProcForecastPriceFact). Set the Restart Type to ‘Restart from failed step’.

Oracle Business Intelligence (BI) Applications are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence throughout an organization. These solutions enable organizations to gain more insight and greater value from a range of data sources and applications including Oracle E-Business Suite, PeopleSoft, JD Edwards, Siebel, and third party systems such as SAP.