Can a fact table be used as a source for populating another fact table
Page 1 of 1
Can a fact table be used as a source for populating another fact table
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?
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
Similar topics
» Populating the Bridge table (Tsql)
» Populating order fact table incrementally,
» Source for Accumulating Snapshot Fact table
» NULLs in source lead to duplicates in Fact table
» Populating the Ragged ORG Bridge table
» Populating order fact table incrementally,
» Source for Accumulating Snapshot Fact table
» NULLs in source lead to duplicates in Fact table
» Populating the Ragged ORG Bridge table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum