Skip to content.

Sections
Home » World-Class Training » World-Class Data Warehouse Training » ETL Architectures – Concepts and Implementation

ETL Architectures – Concepts and Implementation

ETL Architectures is an in-depth, technical course that teaches the concepts for designing and implementing the appropriate architectures to use in managing the extraction, transformation and loading (ETL) of data for:

  • High performance decision support environments (data warehouses, dimensional data marts, Operational Data Stores (ODS), etc)
  • Master Data Management hubs (Customer Data Integration (CDI), Product Information Management (PIM), etc)
  • General data integration (e.g. Service Oriented Architectures (SOA))

This course will review these architectures and concepts with the primary focus on the concepts and techniques that apply to various approaches to ETL. Participants will learn when to use certain techniques, based on their technical and business requirements. With hands-on workshops, attendees will study different ETL products and methodologies for implementation in today’s heterogeneous system environments.

Benefits To Your Company

By learning the best way to design ETL architectures, architects and ETL developers will be able to implement the appropriate tools and techniques to satisfy business requirements and relate them to the supporting data structures. They will:

  • Understand the concepts of extraction, transformation and loading in decision support systems, master data management systems, SOA environments.
  • Understand the various forms of data architectures and how to apply ETL techniques to these
  • Understand sophisticated techniques for more complicated ETL solutions (real-time, high volume, etc)
  • Construct ETL architectures that are flexible to support changing business and technical requirements
  • Learn about the most common ETL products and their strengths and weaknesses.

Who Should Attend

  • Data Warehouse Architects
  • Enterprise Architects (Data, Technical)
  • ETL Developers
  • Data Architects
  • Business Intelligence designers
  • Database designers
  • Database administrators (DBA)

What Makes This Certified Course Unique

This ICCP-certified course provides participants with practical, in-depth understanding of how to create appropriate ETL architectures for decision support and data integration solutions. Hands-on workshops throughout the course will reinforce the learning experience and provide the attendees with concrete results that can be utilized in their organizations.

Course Outline

  • Review common system architectures
    • Transaction Processing
    • Decision Support
    • Master Data Management
    • Service Oriented Architecture
  • ETL Concepts
    • General principles
    • Design and plan for reuse
    • Design for error handling
    • Design for performance
    • Design for maintainability
    • ETL Standards
    • ETL and Meta Data
    • ETL Tool Usage
  • ETL for Decision Support
    • ETL for the Data Warehouse
      1. Data Sourcing / Changed Data Capture
      2. Data Transport
      3. Data Staging
      4. Changed Data Determination
      5. Loading normalized warehouse structures
    • ETL for the Data Mart
      1. Surrogate key lookup and assignment
      2. Slowly Changing Dimensions - Types 1,2, 3 & 6
      3. Denormalization and impact on ETL
      4. Populating “junk” dimensions using a Cartesian product
      5. Aggregation
    • ETL for the ODS
      1. Real/near time approaches
      2. Data Modeling differences
    • Row level security
    • Closing the loop
  • ETL for Master Data Management (MDM) and Service Oriented Architectures (SOA)
    • Customer Data Integration (CDI)
    • Product Information Management (PIM)
    • Integrating ETL and SOA environments
    • Integrating ETL with Data Quality tools
    • Integration with OLTP systems
  • ETL Tools
    • Leading ETL tool vendors
    • ETL tool strengths / weaknesses
    • Choosing the correct ETL tool
  • High performance ETL
    • Indexing (b-tree, bitmap, join indexes, etc)
    • Forms of Parallelism
    • RDBMS tuning and ETL
    • Massively Parallel Processing (MPP) platforms vs. Symmetrical Multiprocessing (SMP) platforms
    • ETL query optimization
  • Workshop conclusion
    • Summary, additional exercises, sources for further reading, etc.

Standard Duration

  • 3 days

To learn more about how EWSolutions can provide our World-Class Training for your company or to request a quote, please feel free to contact David Marco, our Director of Education at DMarco@EWSolutions.com or call him at 630.920.0005 ext. 103.

LEARN MORE ABOUT Data Warehousing