BI APPS Customization Process
Scenario: Currently
organization’s reporting model is based on Views based on EBS. It needs to be
replaced with BI Apps model
Approach:
Perform GAP Analysis on views, performing it for
a column
- Select a column from the view
e.g. ‘Transaction_Date’
- Analyze the view script and identify
the source column and table for this col
- The source column is ‘TRANSACTION_DATE_ENTERED’
from ’FA_TRANSACTION_HEADERS’
- Open ETL Lineage document for
BI Apps that has the source and target details. Look for
FA_TRANSACTION_HEADERS in source table and TRANSACTION_DATE_ENTERED in
source column.
- Since we have four warehouse
columns for the same source column, we need to identify the exact
warehouse column by checking the logic in ODI.
- Find the SDE and SIL interfaces that are loading the
warehouse tables ‘W_FA_XACT_F’ and ‘W_FIXED_ASSET_D’ using the below queries
in warehouse schema.
Select * FromW_ETL_LOAD_DATES
Where (Target_Table_Name like
'W_FA_XACT_F%' or Target_table_name like 'W_FIXED_ASSET_D%')
and Target_table_name not like '%TL';
- Check the SIL
of W_FIXED_ASSET_D and backtrack the column SRC_EFF_FROM_DT
- Go to Designer
àBIApps Project à Mappings à SILOS à SIL _FIXEDASSETDIMENSION. Check the source column for
SRC_EFF_FROM_DT. In this case the source is W_FIXED_ASSET_DS.SRC_EFF_FROM_DT
- Now, open the
SDE for the same dimension. Go to Designer àBIApps Project à Mappings --> SDE_ORAR1213_Adaptor àSDE_ORAR1213_ADAPTOR_SDE_ORA_FIXEDASSETDIMENSION. Open
the interface and backtrack the column. It is TRANSACTION_DATE_ENTERED column
from FA_TRANSACTION_HEADERS
Even though we are
getting the TRANSACTION_DATE_ENTERED column as SRC_EFF_FROM_DT in
W_FIXED_ASSET_D, the join condition through which FA_TRANSACTION_HEADERS is
joined to other tables is different between Query and ODI interface for
W_FIXED_ASSET_D.
In ODI:
- So, we will follow the steps 8 and 9 again to check the source for XACT_DT_WID column in W_FA_XACT_F.
- After checking, we have found that the source column is coming correct in Retirement folder and Utilization folder of W_FIXED_ASSET_DS. But, it is incorrect in Depreciation folder(See screenshot below).
- So, finally we have decided that this column is a GAP and we have taken the entry as shown below.
In this way, we have identified the GAPs between the Custom Queries and Out of Box BIApps.
- As we already have the FA_TRANSACTION_HEADERS as a source table in W_FA_XACT_F, we have decided to develop the new column in W_FA_XACT_F. For that, we have created new folders for all our customizations as shown below by including a word which is specific to client before the actual name.
- As we don’t want to modify the existing interfaces, we have copied the existing interfaces and pasted them in the custom folders and changed the names as “X_” which we follow for all the customizations.
- Now, as we have decided on our approach to add this column, we have prepared Functional Design Document for all the GAPs in a particular Custom View explaining the high level implementation steps. Find below a sample FDD
- The Development steps as mentioned in the above document include the following steps in a brief
• Add column X_XACT_DT_WID in physical table
• Add the column in Models in ODI
• Map the target column from source table/column.
• Apply the CALCULATE_DT_WID_DFLT function and calculate the WID and map it to X_XACT_DT_WID.
• Import the column in the physical layer of RPD.
• Create a new alias for W_DAY_D and join it to W_FA_XACT_F based on
X_XACT_DT_WID = ROW_WID
• Expose the column to presentation layer as “Transaction Date”
• Compare the data between Custom Query and BIApps and add the Test Case in Design Document.
This way we develop all the GAPs and make them available for Reporting.
This way we develop all the GAPs and make them available for Reporting.