Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
1 Solution
Accepted Solutions
Let me take a step back and describe how your job could be set up to do this purely in Talend.
+ First update all your dimension tables with data from your source system (SCD1, SCD2 etc...)
+ Select data from your source system that includes all your business keys (so every row has business keys and measures (for the fact table)
+ Using tMap, look up the current row from each Dimension table; since they're SCD2, you'll have an active flag that shows what row is the current.
+ From the row for each dimension table, get the corresponding Surrogate Key; for example, CustID MAG7983 could be mapped to Surrogate Key 1123. Get that key and add to your output.
+ In that tMap, look up all your Dimensions, and make sure you use 'Left Join' to that main data flow and select 'Unique' records from the input 'table'.
+ Coming out of the tMap, you'll have all your Surrogate Keys and your measures, ready to be inserted / updated into the Fact Table.
+ Write this to a Staging Table or output file.
See screenshots... You will notice that the initial load of the Staging table uses a Process_Type field, which we set to 1. This will be the default for all new records from the source system.
Now when you have the data in the staging table, you need to determine what records in the Fact Table need to be updated. We do this by joining the Staging table to the Fact Table (identical schemas at this point) and using an Inner Join. We only join on as few keys as we expect to always have the first load. In this case, I don't expect us to have ShipDate, Receipt Date etc... So we don't join on them. Any row that matches completely based on SK matches is assigned a Process_Type of 2. The output from this step is used to update the same Staging Table. At this point, rows that should be used for update will have Process_Type 2, and those for Insert, Process_Type 1.
Finally, read the Staging table and update or insert (see screenshots for details).
+ First update all your dimension tables with data from your source system (SCD1, SCD2 etc...)
+ Select data from your source system that includes all your business keys (so every row has business keys and measures (for the fact table)
+ Using tMap, look up the current row from each Dimension table; since they're SCD2, you'll have an active flag that shows what row is the current.
+ From the row for each dimension table, get the corresponding Surrogate Key; for example, CustID MAG7983 could be mapped to Surrogate Key 1123. Get that key and add to your output.
+ In that tMap, look up all your Dimensions, and make sure you use 'Left Join' to that main data flow and select 'Unique' records from the input 'table'.
+ Coming out of the tMap, you'll have all your Surrogate Keys and your measures, ready to be inserted / updated into the Fact Table.
+ Write this to a Staging Table or output file.
See screenshots... You will notice that the initial load of the Staging table uses a Process_Type field, which we set to 1. This will be the default for all new records from the source system.
Now when you have the data in the staging table, you need to determine what records in the Fact Table need to be updated. We do this by joining the Staging table to the Fact Table (identical schemas at this point) and using an Inner Join. We only join on as few keys as we expect to always have the first load. In this case, I don't expect us to have ShipDate, Receipt Date etc... So we don't join on them. Any row that matches completely based on SK matches is assigned a Process_Type of 2. The output from this step is used to update the same Staging Table. At this point, rows that should be used for update will have Process_Type 2, and those for Insert, Process_Type 1.
Finally, read the Staging table and update or insert (see screenshots for details).
Let me take a step back and describe how your job could be set up to do this purely in Talend.
+ First update all your dimension tables with data from your source system (SCD1, SCD2 etc...)
+ Select data from your source system that includes all your business keys (so every row has business keys and measures (for the fact table)
+ Using tMap, look up the current row from each Dimension table; since they're SCD2, you'll have an active flag that shows what row is the current.
+ From the row for each dimension table, get the corresponding Surrogate Key; for example, CustID MAG7983 could be mapped to Surrogate Key 1123. Get that key and add to your output.
+ In that tMap, look up all your Dimensions, and make sure you use 'Left Join' to that main data flow and select 'Unique' records from the input 'table'.
+ Coming out of the tMap, you'll have all your Surrogate Keys and your measures, ready to be inserted / updated into the Fact Table.
+ Write this to a Staging Table or output file.
See screenshots... You will notice that the initial load of the Staging table uses a Process_Type field, which we set to 1. This will be the default for all new records from the source system.
Now when you have the data in the staging table, you need to determine what records in the Fact Table need to be updated. We do this by joining the Staging table to the Fact Table (identical schemas at this point) and using an Inner Join. We only join on as few keys as we expect to always have the first load. In this case, I don't expect us to have ShipDate, Receipt Date etc... So we don't join on them. Any row that matches completely based on SK matches is assigned a Process_Type of 2. The output from this step is used to update the same Staging Table. At this point, rows that should be used for update will have Process_Type 2, and those for Insert, Process_Type 1.
Finally, read the Staging table and update or insert (see screenshots for details).
+ First update all your dimension tables with data from your source system (SCD1, SCD2 etc...)
+ Select data from your source system that includes all your business keys (so every row has business keys and measures (for the fact table)
+ Using tMap, look up the current row from each Dimension table; since they're SCD2, you'll have an active flag that shows what row is the current.
+ From the row for each dimension table, get the corresponding Surrogate Key; for example, CustID MAG7983 could be mapped to Surrogate Key 1123. Get that key and add to your output.
+ In that tMap, look up all your Dimensions, and make sure you use 'Left Join' to that main data flow and select 'Unique' records from the input 'table'.
+ Coming out of the tMap, you'll have all your Surrogate Keys and your measures, ready to be inserted / updated into the Fact Table.
+ Write this to a Staging Table or output file.
See screenshots... You will notice that the initial load of the Staging table uses a Process_Type field, which we set to 1. This will be the default for all new records from the source system.
Now when you have the data in the staging table, you need to determine what records in the Fact Table need to be updated. We do this by joining the Staging table to the Fact Table (identical schemas at this point) and using an Inner Join. We only join on as few keys as we expect to always have the first load. In this case, I don't expect us to have ShipDate, Receipt Date etc... So we don't join on them. Any row that matches completely based on SK matches is assigned a Process_Type of 2. The output from this step is used to update the same Staging Table. At this point, rows that should be used for update will have Process_Type 2, and those for Insert, Process_Type 1.
Finally, read the Staging table and update or insert (see screenshots for details).
And as far as faster and error free, I've used this simple design pattern (1 or 2...) many times across different projects. But don't take my word for it
. Put it to the test and let us know...
. Put it to the test and let us know...
