Can a fact table be used as a source for populating another fact table

View previous topic View next topic Go down

Can a fact table be used as a source for populating another fact table

Post  shaibu on Thu Aug 04, 2011 12:37 pm

Scenario:

There are two source tables :

1. SRC_INV_HDR (1 million rows in a year)
2. SRC_INV_DISCREPANCIES (1lac rows in a year)

Landing tables in ODS :
DWI_INV_HDR
DWI_DISCREPANCIES

Two fact tables :
1. DWB_Invoice_hdr
2. DWB_Discrepancy

Golden gate replication:
Extracts from SRC_INV_HDR and populate in DWI_INV_HDR
Extract from SRC_INV_DISCREPANCIES and populate in DWI_DISCREPANCIES

ETL Process1 :
Source : DWI_INV_HDR
Target: DWB_INV_HDR

ETL Process2:
Source :
1) DWB_INV_HDR (not to be read as DWI_INV_HDR)
2) DWI_Discrepancies

Target: DWB_Discrepancies

The source table DWB_INV_HDR is used to populate doc_date in DWB_Discrepancies.

Note: The reason for not using DWI_INV_HDR is because DWI_INV_HDR will be purged after loading in DWB_INV_HDR and record in SRC_INV_DISCREPANCIES may have been created after record is loaded in DWB_INV_HDR.

Query:

1. Would like to know if it is a good practice to use DWB_INV_HDR as source table for populating DWB_DISCREPANCIES?
2. The other alternate solution that we've thought is to create a table DWI_INV_CTRL in the staging layer with two columns (DOCID, DOCDATE) and populate the table as and when record is inserted in DWI_INV_HDR by creating trigger on DWI_INV_HDR. In ETL Process2 , DWI_INV_CTRL and DWI_DISCREPANCIES table can be used as source to populate DWB_Discrepancies. Records in DWI_INV_CTL table will be purged or house-kept whenever the record moves to a particular thereafter which the record is not required for populating DWB_Discrepancies. The reason for this altenative solution is to improve performance as the volume in DWB_INV_HDR will grow exponentially year after year.
3.Is there any other solution for the above problem?















shaibu

Posts : 1
Join date : 2011-08-04

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum