1 / 26

ETL Engine Review April 13, 2011

ETL Engine Review April 13, 2011. Introduction Concept Implemented Extracts Implemented Transforms Implemented Loads Features Screenshots. Introduction. Purpose

ericanderson
Download Presentation

ETL Engine Review April 13, 2011

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ETL EngineReviewApril 13, 2011

  2. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  3. Introduction • Purpose • To make Schneider Electric projects successful by reducing the development effort required to create new data adapter applications. • To help us meet the common project requirement of integrating data from disparate data sources, and to do so in a flexible way. • Description • The ETL Engine: A flexible, highly-reusable, field-configurable, and low-effort interoperability tool.

  4. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  5. Concept • “ETL” stands for “Extract, Transform, Load” • Extract data from a particular system or data source • Transform the data in some way • Load the data into another system • Each part can replaced with a different component, creating a new data adapter. • New data adapters can be assembled using the existing toolset, or new parts can be created.

  6. Concept - Jobs • A Job is the highest level container in the ETL Engine. • Jobs contain two or more ETL Tasks, and some additional properties. • ETL Jobs are responsible for... • executing constituent tasks • persisting state between job executions (via XML serialization)

  7. Concept - Tasks • ETL Tasks are worker classes that perform one specific function. • Tasks fall into three main categories: • Extract Tasks read data from a particular data source and convert it into a format the ETL Engine can understand. • Transform Tasks modify data in some way. • Load Tasks write data into a particular system. • ETL jobs must contain one extract task, zero or more transform tasks, and one load task

  8. Concepts – Data Flow Transform Extract Load Alias, Scale System 1 System 2

  9. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  10. Extracts Generic • Delimited File (CSV): various formats of CSV • ODBC SQL • CMEP Data Collection/SCADA • ION EEM* • ION Enterprise • PI (OSI Soft)* • SMS (AR,NonAR) • PrimeRead* BAS • JCI Metasys • TAC Vista (in progress) • TAC Continuum (in progress) Device Formats • EGX 300 On-line Systems • EPO/EVO • NOAA Weather Data Environment • Canada Weather Data * Uses generic ODBC query

  11. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  12. Transforms • Time shift • Interval derivation

  13. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  14. Loads • EnergyCAP • ION EEM • ION Enterprise 6 • CMEP (used by EPO/EVO) • HTML • ION MeterM@il Pushmessaging Email format • Vizelia

  15. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  16. Features • Multithreaded Task Execution • Improves performance • Built into the ETL Engine to save task developers effort • Logging using log4Net • Error Log • Trace Log • Data Trace Log • Windows event log • Email Notifications

  17. Introduction • Concept • Implemented Extracts • Implemented Transforms • Implemented Loads • Features • Screenshots

  18. Tactical Solutions admin Tool • Create, edit, delete and control a job

  19. Job • Name a job, set the sleep time between the execution when run as a service

  20. Task Setup • Add tasks to a job and configure the task settings Any “E” can be connected to any “L” task Properties are defined programmatically for any task, values configured by user

  21. Mappings • Maps names in source system to names in destination system

  22. Position Counter Setup • User can reset the positions for individual source measurement pairs by initializing the value

  23. Logging • Contains logging options for Data Trace, Error, custom, Windows Event logs and Email notifications

  24. Advanced • Control over low level application operation settings

  25. Control • User controls the execution of a job

  26. Questions ?

More Related