Many data warehouse efforts struggle with determining the best methodology and implementation strategy for performing extract, transform and load (ETL) processing for their particular enterprise. Once the business requirements for the warehouse project have been defined, the source systems analyzed and a physical data model has been developed, you are ready to begin designing your ETL processes. A systematic approach needs to be applied for designing your ETL processes. This column will describe the use of a staged approach for designing ETL process flows. Each process is directed toward loading of a particular target table or cluster of tables depending on the business requirements and technical design considerations.

A staged transformation approach is used to optimize data acquisition and transformation from source systems. This method provides increased flexibility and extensibility to ETL developers. The various stages provide a single processing method for initial and successive loads to the data warehouse. The five stages provide an adaptable transformation process for the target table(s) that can adapt easily to changes in the source systems or warehouse model design. The combined use of meta data tags, sometimes referred to as operational meta data, in the warehouse schema design and ETL transformation processes allows for improved capabilities in loading and maintenance designs. Not all stages will be used in every target table case depending on the business requirements and complexity of business rules needed in the transformation.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access