BI APPS Customization Process

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.