Etl elt simplified
Download as pptx, pdf1 like575 views
The document discusses the pros and cons of staging data warehouses using Extract, Transform, Load (ETL) versus Extract, Load, Transform (ELT). Some key advantages of ETL include reducing development time by only extracting relevant data, simplifying administration through targeted data loads, and flexibility in tool selection. However, ETL can limit flexibility for future requirements and necessitate additional hardware. ELT enables project management in smaller chunks, flexibility for future needs, and risk minimization through isolated processes, but availability of mature tools is still limited as the technology is emerging.
Etl elt simplified
- 1. Staging Staging Data Warehouse Extract Transform Load Pros: 1. Development Time Designing from the output backwards ensures that only data relevant to the solution is extracted and processed, potentially reducing development, extract, and processing overhead; and therefore time. 2. Targeted data Due to the targeted nature of the load process, the warehouse contains only data relevant to the presentation. Administration Overhead Reduced warehouse content simplifies the security regime implemented and hence the administration overhead. 3. Tools Availability The prolific number of tools available that implement ETL provides flexibility of approach and the opportunity to identify a most appropriate tool. The proliferation of tools has lead to a competitive functionality war, which often results in loss of maintainability. Cons: 1. Flexibility Targeting only relevant data for output means that any future requirements, that may need data that was not included in the original design, will need to be added to the ETL routines. Due to nature of tight dependency between the routines developed, this often leads to a need for fundamental re-design and development. 2. Hardware Most third party tools utilize their own engine to implement the ETL process. Regardless of the size of the solution this can necessitate the investment in additional hardware to implement the tools ETL engine. 3. Learning Curve Implementing a third party tool that uses foreign processes and languages results in the learning curve that is implicit in all technologies new to an organization and can often lead to following blind alleys in their use due to lack of experience.
- 2. Staging Data Warehouse Extract TransformLoad Data Warehouse Pros: 1. Project Management Being able to split the warehouse process into specific and isolated tasks, enables a project to be designed on a smaller task basis, therefore the project can be broken down into manageable chunks. 2. Flexible & Future Proof In general, in an ELT implementation all data from the sources are loaded into the warehouse as part of the extract and load process. This, combined with the isolation of the transformation process, means that future requirements can easily be incorporated into the warehouse structure. 3. Risk minimization Removing the close interdependencies between each stage of the warehouse build process enables the development process to be isolated, and the individual process design can thus also be isolated. This provides an excellent platform for change, maintenance and management. 4. Utilize Existing Hardware In implementing ELT as a warehouse build process, the inherent tools provided with the database engine can be used. Alternatively, the vast majority of the third party ELT tools available employ the use of the database engines capability and hence the ELT process is run on the same hardware as the database engine underpinning the data warehouse, using the existing hardware deployed. 5. Utilize Existing Skill sets By using the functionality provided by the database engine, the existing investments in database skills are re-used to develop the warehouse. No new skills need be learned and the full weight of the experience in developing the engines technology is utilized, further reducing the cost and risk in the development process. Cons: 1. Tools: Availability of mature tools as it is an emergent technology