Extraction, transformation and loading constitutes more than 70 percent of the overall execution effort in an enterprise data warehousing project. While typical projects spend considerable time and energy finalizing the overall data warehousing architecture, designing the ETL architecture detail has not received the attention and research it deserves. Several technologies and architectures exist in this area; however these are based on the architectÂ’s experience with prior projects and the approach is purely based on what seems more practical from an architectural viewpoint in a given situation satisfying the triple project management constraints. This article outlines a more objective and analytical approach to the ETL and data flow architecture selection based on a set of variables with an objective to enhance the reliability of the overall data warehousing solution.

The reliability of the overall data flow is very important for making sure the data warehouse meets the service level agreement it was designed to satisfy. While data validation efforts are required to make sure the data is right and/or within the acceptable quality threshold, it is very important to consider the factors influencing the overall reliability of data flow from source systems all the way up to and including the presentation layer so that appropriate steps can be taken during the ETL detailed design phase.  Consider the typical (5*5) data warehouse architecture as shown in Figure 1. Data moves from source systems to staging to the operational data store and then to the data warehouse before getting to the data mart and finally the presentation layer. There could be up to five stages of ETL before the data gets to the user. Reliability at each stage is important, since each of these stages is like a link within a big chain. 

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