1 / 15

GENERIC ETL DESIGN

GENERIC ETL DESIGN. VARADARAJAN VASU. varadarajan.v@polaris.co.in. SENIOR PROJECT MGR/ARCHITECT. POLARIS SOFTWARE LAB. OBJECTIVE. Application area is bifurcated as ETL / Reporting . Major Operations Select/Insert/Update/Delete

lilliec
Download Presentation

GENERIC ETL DESIGN

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. GENERIC ETL DESIGN VARADARAJAN VASU varadarajan.v@polaris.co.in SENIOR PROJECT MGR/ARCHITECT POLARIS SOFTWARE LAB

  2. OBJECTIVE • Application area is bifurcated as ETL / Reporting . • Major Operations Select/Insert/Update/Delete • To replace existing primitive methods used for ETL Design/ Automation • System should be highly intelligent to do all jobs on behalf of users • Build a comprehensive solution once and use it across verticals

  3. PERT PROCESS • PERT Stands for PROGRAM EXECUTION on REMOTE TERMINALS • Different from Program Evaluation Review Technique used by SEI • Technology used in Client/Server architecture

  4. PERT PROCESS FLOW PERT START FREE SPACE CHECK ORACLE PROCESSES CHECK

  5. EXECUTABLE PRESENCE CHECK PROCEDURE VALIDITY CHECK CHECK FOR PARELLEL RUN CHECK FOR RESTARTABILITY

  6. 1. SYSTEM INTELLIGENT CHEKS - PARAMETERISED 2. DATE CHANGE - PARAMETERISED 3. DETERMINE STAGING RUN INFORMATION - PARAMETERISED 4. STAGE REFRESH LOADER 5. GATHER FINAL REFRESH INFORMATION - PARAMETERISED 6. FINAL REFRESH LOADER 7. DATA VALIDATION CHECKS - PARAMETERISED 8. MAKE SYSTEM READY FOR NEXT DAY RUN - PARAMETERISED SUCCESS PERT END

  7. SYSTEM INTELLIGENT CHECKS - Examples • SPACE CHECK • OBJECTS VALIDITY CHECK • EXECUTABLES VALIDITY CHECK • PROCESS RUNNING CHECK • PREVENT SUCCESS RUN • PREVENT PARELLL RUN • RESTARTABILITY • HANDLE UNAVOIDABLE INTERRUPTS FROM OS

  8. OPERATION READINESS- Examples • ARCHIVE • INDEXING • COMMUNICATING WITH EXTERNAL PARTIES • MAILING • COMPILING ETL EXECUTION STATISTICS • MOVING OBJECTS TO RESPECTIVE LOCATION • ANALYZING • CLEANUP EXERCISE

  9. SALIENT FEATURES OF PERT • SPACE CHECK • PROCEDURE OBJECTS VALIDITY CHECK • EXECUTABLES VALIDITY CHECK • PREVENT SUCCESS RUN • PREVENT PARELLL RUN • RESTARTABILITY • PROVISION TO SCHEDULE FOR UPCOMING RUN FREQUENCIES • BETTER ERROR LOGGING • HANDLE UNAVOIDABLE INTERRUPTS FROM OS • Load check for staging , Final • Provision for manual run

  10. ADVANTAGES • Design is dynamic in nature • Limited time availability to plug in new facility • Avoid redundancy in coding & testing efforts • Sleeping beauty is cost effectiveness • Restart facility to start from the aborted place during data extraction and population • ETL solution can be used for other similar ETL applications.

  11. CHALLENGES • Requirements Gathering • Database Design • Performance in Execution

  12. CASE STUDY

  13. NEAR REAL TIME EDW POPULATION

  14. CASE STUDY PROCESSING

  15. THANK YOU

More Related