Why do we have to add new fact
table?
It is often seen that certain key business requirements are
not covered by the pre-built contents (Out Of The Box). For example, the OTTB
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.
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.
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.
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’.
