Introduction_of_ETL_Atomation_V25(内部资料).ppt

Introduction of ETL AutomationVersion 2.5 By Jet Wu, Consultant NCR Copyright? 2001-2002 What is ETL Automation? In Data Warehouse, ETL means Extraction, Transformation, Loading Extraction Extract data from the source database into a flat data file. It occurs in external source environment. Transformation Transform data from flat data file to different format or from one table to another table in different data type. It may occur in external source or in DW, usually in DW environment. Loading Load the data from flat data file into Data Warehouse. It occurs in DW environment. Sometimes, it is called ELT. Automation Scheme for ETL How do we make it automatically? ETL Automation Structure ETL Automation – Multiple Servers Support Jobs at one server can trigger jobs at another server. For example, once a group of data loading jobs were done at loading server, the cube job will be triggered at another OLAP server by ETL Automation automatically. ETL Automation – Multiple Servers Support ETL Automation Process Directory Tree in ETL Automation Server /ETL |--/APP |-----/$SYS |---------/$JOB |-------------/bin |-------------/ddl |--/DATA |-----/complete |-----/fail |--------/bypass |--------/corrupt |--------/duplicate |--------/error |--------/unknown |-----/process |-----/queue |-----/receive |--/LOG |-----/$SYS |--------/$DATE |--/bin |--/etc |--/lock |--/tmp ETL Automation Server Program ETL Automation Server Programs are written in Perl etlagent.pl etlclean.pl etlmaster.pl etlmsg.pl etlrcv.pl etlslave_unix.pl/etlslave_nt.pl etl_unix.pm/etl_nt.pm Daemon Program in ETL Automation etlagent.pl etlclean.pl etlmaster.pl etlmsg.pl etlrcv.pl Daemon Program – etlagent.pl This daemon program will listen to a socket port to service the requests from GUI front-end administration program. Get Log File Get Script File Put Script File Invoke Job Force Start Job Query Status Daemon Program – etlclean.pl This daemon program will do the house keeping job for ETL Automation. Clean up e