Staging > ODS > EDW

View previous topic View next topic Go down

Staging > ODS > EDW

Post  user2000 on Wed Sep 02, 2009 4:08 pm

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?

user2000

Posts : 2
Join date : 2009-08-28

View user profile

Back to top Go down

Re: Staging > ODS > EDW

Post  alex.caminals on Mon Sep 28, 2009 7:55 am

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,
avatar
alex.caminals

Posts : 15
Join date : 2009-02-25
Age : 41
Location : Barcelona (Spain)

View user profile

Back to top Go down

Re: Staging > ODS > EDW

Post  beyeguru on Tue Sep 29, 2009 1:05 am

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

View user profile

Back to top Go down

Re: Staging > ODS > EDW

Post  ngalemmo on Tue Sep 29, 2009 3:15 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Staging > ODS > EDW

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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