Thursday, 30 July 2015

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


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
  • 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.

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.


  • 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.

No comments:

Post a Comment