Integrating Multiple EBS sources using OBIA 7.9.6.4 COTS product- Issue related to Data Source Numbe

user8633202

Project

Federal Health and Human Services Organization in the process of enhancing Business intelligence system(FBIS) by adding additional EBusiness Source called NIH. Current FBIS Data warehouse system built using  Oracle BI Applications (OBIA 7.9.6.4), and it stores information from Oracle EBusiness Source called UFMS. Both of these EBS sources are on the R12 platform. This integration project designed to use two federated ETL tiers to load data from UFMS and NIH source systems into the current unified FBIS Data warehouse using the existing BI Apps COTS Infrastructure.

Problem Description

Per Oracle BI Documentation BI Apps designed to source data from multiple sources. As mentioned in Oracle SR#3-12972085231, we should be able to configure a new DATASOURCE_NUM_ID in DAC for each source system and BI Apps data model should support loading data into warehouse tables from multiple sources systems using a unique DATASOURCE_NUM_ID value for each source systems.

While doing an analysis for the implementation of above mentioned NIH Integration project, following scenarios identified in BI Apps Data Model and Informatica ETLs where DATASOURCE_NUM_ID  column not used, causing serious concerns about the above mentioned NIH Integration project.

• Scenario#1  BI Apps Data model design doesn’t support to store DATASOURCE_NUM_ID column information in below tables. How we would be able to find Data Source Number specific records from these tables?

Please find below few examples for your reference-

  W_YEAR_D
W_LOV_G
W_CURRENCY_TYPE_G
W_MONTH_D
W_GEO_D
W_GL_JE_SOURCES_D
W_MCAL_CONFIG_G
W_GEO_COUNTRY_D
W_SUPPLIER_D
W_WEEK_D
W_QTR_D
W_GL_JE_CATEGORIES_D

• Scenario#2 Following ETL mappings examples doesn’t use DATASOURCE_NUM_ID in the SQL override (SQ override/LKP SQL override) to cross reference corresponding EBS records from target tables. Without having Data Source Number ID column in the filter condition, we would not be able to make sure query result belongs to respective ETL tier or not. How has oracle design to handle this scenario?

Please find below few examples for your reference-

a)      Example:#1

Folder Name: SILOS,

Mapping Name:SIL_PipelineFact
Source Qualifier: SQ_Joiner

SELECT $$Hint1 W_REVN_F.CLOSE_DT_WID, W_REVN_F.CREATED_BY_ORG_WID, W_REVN_F.CURR_SSTAGE_WID, W_REVN_F.DATASOURCE_NUM_ID, W_REVN_F.ETL_PROC_WID, W_REVN_F.INTEGRATION_ID, W_REVN_F.OPEN_DT_WID, W_REVN_F.OPTY_WID,
W_REVN_F.PR_CUSTOMER_GEO_WID, W_REVN_F.PR_CUSTOMER_WID, W_REVN_F.PR_CMPT_ACCNT_WID, W_REVN_F.PR_CONTACT_GEO_WID, W_REVN_F.PR_CONTACT_WID, W_REVN_F.PR_EMP_WID, W_REVN_F.PR_INDUST_WID, W_REVN_F.PR_OWNER_ORG_WID,
W_REVN_F.PR_PROGRAM_WID, W_REVN_F.PR_TERR_WID, W_REVN_F.PR_VIS_ORG_WID, W_REVN_F.CLOSED_REVN, W_REVN_F.EXPECT_REVN, W_REVN_F.REVN, W_REVN_F.SEGMENT_WID, W_REVN_F.PR_OFFER_WID, W_REVN_F.PR_TEAM_POS_WID,
W_REVN_F.PR_TEAM_EMP_WID, W_REVN_F.PR_PRTNR_WID, W_REVN_F.U_CLOSED_RVN, W_REVN_F.U_EXPECT_RVN, W_REVN_F.U_RVN, W_REVN_F.U_RVN_CURCY_CD, W_REVN_F.U_RVN_EXCH_DT, W_REVN_F.CREATED_BY_EMP_WID, W_REVN_F.CREATED_BY_POS_WID,
W_REVN_F.PR_TEAM_POSTN_DH_WID, W_REVN_F.VIS_PR_POS_ID, W_REVN_F.OPTY_ATTR_WID, W_REVN_F.SOURCE_WID, W_REVN_F.CAMP_LNCH_DT_WID, W_REVN_F.CAMP_ST_DT_WID, W_REVN_F.CAMP_END_DT_WID, W_REVN_F.X_CUSTOM, W_REVN_F.PR_OWNER_BU_WID,
W_REVN_F.VIS_PR_POSTN_DH_WID FROM W_REVN_F, W_OPTY_D, W_DAY_D CLOSED_V WHERE { W_OPTY_D LEFT OUTER JOIN W_DAY_D CLOSED_V ON W_OPTY_D.CLOSE_DT = CLOSED_V.DAY_DT INNER JOIN W_REVN_F ON W_OPTY_D.ROW_WID = W_REVN_F.OPTY_WID } AND W_REVN_F.SUMMARY_FLG = 'Y' AND ( (W_OPTY_D.CLOSED_FLG = 'N' AND W_OPTY_D.LOST_FLG = 'N') OR (CLOSED_V.PER_NAME_QTR = '$$CURRENT_QUARTER') ) $$Hint2

b)      Example#2

Folder Name: SILOS

Mapping Name: SIL_KPI_Fact

Source Qualifier: SQ_NEW_NUM_ACCNT
SELECT $$Hint1  W_KPI_F.ROW_WID, W_KPI_F.ACCNT_ATTRITION, W_KPI_F.TOTL_NUM_ACCNT FROM W_KPI_F

             WHERE W_KPI_F.ETL_PROC_WID = 0 $$Hint2

c)      Example#3

Folder Name:SILOS

Mapping Name:SIL_KPI_Fact

Source Qualifier: SQ_NEW_NUM_CON
Source Qualifier:SELECT $$Hint1 W_KPI_F.ROW_WID, W_KPI_F.CON_ATTRITION, W_KPI_F.TOTL_NUM_CON FROM W_KPI_F
WHERE W_KPI_F.ETL_PROC_WID = 0 $$Hint2

d)      Example#4

Folder Name: SILOS

Mapping Name:SIL_PurchaseCycleLinesFact_Load_Type

             Source Qualifier:SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END, DATASOURCE_NUM_ID FROM W_PURCH_CYCLE_LINE_F GROUP BY DATASOURCE_NUM_ID