Staging > ODS > EDW
4 posters
Page 1 of 1
Staging > ODS > EDW
I have a basic query on Staging > ODS > EDW architecture.
how the incremental data would be populated in EDW?
Assume i have a "Customer Addess" Staging table which selects the delta from source system, then it updates the "Customer Addess" ODS table.
how the incremental data would be fed from ODS to "Customer Addess" Dimension which is located in EDW with SCD policy?
how the incremental data would be populated in EDW?
Assume i have a "Customer Addess" Staging table which selects the delta from source system, then it updates the "Customer Addess" ODS table.
how the incremental data would be fed from ODS to "Customer Addess" Dimension which is located in EDW with SCD policy?
user2000- Posts : 2
Join date : 2009-08-28
Re: Staging > ODS > EDW
While loading the ODS you need to flag the rows loaded in the current ETL run. That will help you to identify the rows to be loaded to the dimension table. I am assuming that you are using the customer address dimension as a SCD type 2 as per your comments. In this case, while treating a new row, you will need to insert the latest value in the address and update the previous current dimension row (depending on your design you will set the "current flag" to false or will update the effective end date).
Please let me know if you need more clarification.
Best regards,
Please let me know if you need more clarification.
Best regards,
alex.caminals- Posts : 15
Join date : 2009-02-25
Age : 48
Location : Barcelona (Spain)
Re: Staging > ODS > EDW
Typically, having a field like an UpdateTimeStamp is helpful in determining the records that got update and/or inserted in the ODS. To extract the most recent records then you would query on UpdateTimeStamp and extract all the records with an UpdateTimeStamp of the previous day. These would then get loaded into the EDW foillowing the SCD strategy you have implemented.
beyeguru- Posts : 5
Join date : 2009-08-03
Re: Staging > ODS > EDW
There are a lot of ways to do this, the best one depends on your situation. You can stage the data for the DW at the same time you are updating the ODS, or you can have triggers on the ODS tables to generate deltas for the DW. You can simply reapply the same data feed into the DW, or update both at the same time...
You can even design your ODS to use the DW's dimension tables, so, at least for dimensional data, you only update in one place.
You can even design your ODS to use the DW's dimension tables, so, at least for dimensional data, you only update in one place.
Similar topics
» Data in Staging area
» Staging Activities
» Start Schema vs Snow flake schema
» Staging Strategy and CDC
» Staging area.. is it a must?
» Staging Activities
» Start Schema vs Snow flake schema
» Staging Strategy and CDC
» Staging area.. is it a must?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum