ETL Architecture

View previous topic View next topic Go down

ETL Architecture

Post  tropically on Fri Feb 05, 2010 12:04 pm

Hi
My company is currently planning on building a datawarehouse. We are an oracle shop and are considering the approach for data migration from client servers to the datamart.

Thoughts we have
1) Asynchronous CDC . I believe oracle now is replacing this with Golden Gate.
2) Export / Import
3) Db Links
4) Oracle data pump. (Similar to export / import)

Questions
Anyone have any other option. Not sure if ETL tools are used for this. My thoughts have been that the ETL tools are used to transform data from staging tables to the Star Schema.

Any thoughts would be appreciated.
Thank you for your time in advance.

tropically

Posts : 13
Join date : 2009-05-12

View user profile

Back to top Go down

Re: ETL Architecture

Post  BoxesAndLines on Fri Feb 05, 2010 1:15 pm

I think what you will find is all of the above. Each data source presents its own unique set of challenges. I agree with your take on ETL. ETL normally takes over once the data is staged. The majority of our data arrives in text files. Our ETL tool takes over from there, loading to Oracle staging tables and then transforming to the dimensional models.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: ETL Architecture

Post  ngalemmo on Fri Feb 05, 2010 3:53 pm

ETL tools are commonly used for source extraction to staging as well (after all, that is what the E stands for...). It's just that, in your case, you have a more homogenous environment, so you can make use of Oracle features that are not available in a mixed database environment.

However, good data warehouse design should assume that legacy data sources will change, but the data warehouse (to a large extent) will not. This philosophy needs to trickle down to how the staging and loading of data takes place. Some level of transformation needs to occur in the staging process (such as abstracting natural keys) so that the staging table represents a standard, stable interface to the load processes. This allows the more complex transformation and load processes to remain stable should data sources (handled by simpler staging processes) change.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: ETL Architecture

Post  Sponsored content


Sponsored content


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