Separating your extraction from your loading and transforming routines

Extracting data from the source system can be very repetitive and time-consuming. The mappings to extract data from the source system are normally very basic with minimal transformations. Building these in an ELT or ETL tool can often take a large amount of time for very little benefit. As an alternative, a generic extraction routine which can be scheduled and executed is normally more flexible and quicker to integrate into the framework.

Getting ready

Identify all the abstraction views created in the previous recipe. Ensure you have a schema created in your staging database for staging objects.

How to do it...

1. 1. Determine which objects can be loaded incrementally and which objects require full extracts.

2. 2. Design a controlling table to register the objects that require extracting:

   CREATE TABLE "JHEATON"."DW_EXTRACT_CNTRL"
   (
   "XTR_CHANNEL" NUMBER,
   "XTR_SEQ" NUMBER,
   "XTR_ID" NUMBER,
   "SRC_SYSTEM" VARCHAR2(50),
   "SRC_DB_LINK...