One of the privileges of being in the BIDW field for long enough, is to do book reviews. Currently, I am looking at the book by Packt Publishing, titled "Oracle Warehouse Builder 11g Getting Started." The author is Bob Griesemer.
This book has 9 interesting chapters, however I am jumping to Ch5 for now as it is the Extract, Transform and Load Basics. Someone with a weaker ETL background could reach this first. The interesting section of this chapter is "To Stage or not to Stage." The author talks about key considerations in ETL, such as:
> For faster movement of data, amount of source data, degree of manipulation of source data and nature of source is important; AND
> Handling failures due to connectivity and handling changes in the source data.
The author has presented options for extracting data directly into staging without worrying about computations in the source system or extract along with some manipulation from the source. He brings up a good point that creating a performance load on the source system may not be desirable compare to loading the ETL or the staging database. Another good point, that author raises is that we should look at staging as even the flat files, extracted from source, organised in certain folders, so that OWB can use them without taking space in the DW. I think this is an interesting way to look at staging.
The use of flat files is not a new concept, as often when fetching data from legacy such as main frames that do not provide direct connectivity to ETL tools, we use flat files. This way the DW team does not have to become an expert on the structures of the legacy system. Likewise, the Oracle Business Intelligence Applications, also support the concept of Universal adapters to interface with flat files when the source system is not directly supported with pre-built ETL connectors. However, the onus of change management of the source, may be little more involved.