1 / 53

ETL Workflows: From Formal Specification to Optimization

ETL Workflows: From Formal Specification to Optimization. Timos Sellis National Technical University of Athens (joint work with Alkis Simitsis, IBM Almaden Research Center, Panos Vassiliadis, Univ. of Ioannina and Dimitris Skoutas, NTUA). Data Warehouse Environment.

olisa
Download Presentation

ETL Workflows: From Formal Specification to Optimization

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 Workflows: From Formal Specification to Optimization Timos Sellis National Technical University of Athens (joint work with Alkis Simitsis, IBM Almaden Research Center, Panos Vassiliadis, Univ. of Ioannina and Dimitris Skoutas, NTUA)

  2. Data Warehouse Environment Timos Sellis - ADBIS 2007

  3. Extract-Transform-Load (ETL) Timos Sellis - ADBIS 2007

  4. Motivation • ETL and Data Cleaning tools cost • 30% of effort and expenses in the budget of the DW • 55% of the total costs of DW runtime • 80% of the development time in a DW project • ETL market: a multi-million market • IBM paid $1.1 billion dollars for Ascential • ETL tools in the market • software packages • in-house development • No standard, no common model • most vendors implement a core set of operators and provide GUI to create a data flow Timos Sellis - ADBIS 2007

  5. Problems • The key factors underlying the main problems of ETL processes are: • vastness of the data volumes • quality problems, since data is not always clean and has to be cleansed • performance, since the whole process has to take place within a specific time window • evolution of the sources and the data warehouse can eventually lead, even to daily maintenance operations Timos Sellis - ADBIS 2007

  6. Outline • Conceptual Model • Logical Model • Architecture Graph • Operational Semantics • Quality Metrics (will not touch this) • Conceptual to Logical • Optimization of ETL Workflows • Research Challenges Timos Sellis - ADBIS 2007

  7. Outline • Conceptual Model • Logical Model • Architecture Graph • Operational Semantics • Quality Metrics (will not touch this) • Conceptual to Logical • Optimization of ETL Workflows • Research Challenges Timos Sellis - ADBIS 2007

  8. Conceptual Model • Design goals • we need a simple model, sufficient for the early stages of the data warehouse design • we need convenient means of communication among different groups of people involved in the DW project (e.g., dba’s and business managers) • we need to be able to model what our sources “talk” about • Semantic goals • we need richer semantics to • describe sources • reason about them Timos Sellis - ADBIS 2007

  9. Conceptual Model Timos Sellis - ADBIS 2007

  10. Conceptual Model • Key idea • an ontology-based approach to facilitate the conceptual design • An ontology • is a “formal, explicit specification of a shared conceptualization” • describes the knowledge in a domain in terms of classes, properties, and relationships between them • machine processable • formal semantics • reasoning mechanisms • Method • construct an appropriate application vocabulary • annotate the data sources • generate the application ontology • apply reasoning techniques to select relevant sources and identify required transformations Timos Sellis - ADBIS 2007

  11. Conceptual Model Paris/Rome/Athens software/hardware Above 200 Euros prices in Dollars software Paris/Rome/Athens hardware only software products From 500 to 1500 Euros Rome/Athens Timos Sellis - ADBIS 2007

  12. Conceptual Model • Application vocabulary • Datastore mappings • Datastore annotation Timos Sellis - ADBIS 2007

  13. The class hierarchy Definition for class DS1_Products Conceptual Model Timos Sellis - ADBIS 2007

  14. Conceptual Model • Reasoning on the mappings s(location, city, street) c(street, number, street) Timos Sellis - ADBIS 2007

  15. Conceptual Model • Reasoning on the definitions f(pid, Source_Pid, DW_Pid) σ(price, From_500_To_1500) nn(quantity) σ(type, {software}) Timos Sellis - ADBIS 2007

  16. Outline • Conceptual Model • Logical Model • Architecture Graph • Operational Semantics • Quality Metrics (will not touch this) • Conceptual to Logical • Optimization of ETL Workflows • Research Challenges Timos Sellis - ADBIS 2007

  17. Logical Model DSA DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE DS.PSNEW2 DS.PSNEW2.PKEY, DS.PSOLD2.PKEY QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ DIFF2 A2EDate SK2 $2€ DS.PSOLD2 rejected rejected rejected rejected Log Log Log Log DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE DS.PSNEW1 DS.PSNEW1.PKEY, DS.PSOLD1.PKEY COST DATE=SYSDATE PKEY,DATE DS.PS1 U PK NotNULL AddDate SK1 DIFF1 DS.PSOLD1 rejected rejected rejected Log Log Log PKEY, DAY MIN(COST) S2.PARTS DW.PARTS FTP2 V1 Aggregate1 PKEY, MONTH AVG(COST) DW.PARTSUPP.DATE, DAY S1.PARTS TIME FTP1 V2  Aggregate2 Sources DW Timos Sellis - ADBIS 2007

  18. Logical Model • Main question: What information should we put inside a metadata repository to be able to answer questions like: • what is the architecture of my DW back stage? • which attributes/tables are involved in the population of an attribute? • what part of the scenario is affected if we delete an attribute? Timos Sellis - ADBIS 2007

  19. Architecture Graph DSA DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE DS.PSNEW2 DS.PSNEW2.PKEY, DS.PSOLD2.PKEY QTY,COST COST DATE SOURCE DS.PS2 AddAttr2 γ A2EDate $2€ DIFF2 SK2 DS.PSOLD2 rejected rejected rejected rejected Log Log Log Log DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE DS.PSNEW1 DS.PSNEW1.PKEY, DS.PSOLD1.PKEY COST DATE=SYSDATE PKEY,DATE DS.PS1 U PK NotNULL AddDate SK1 DIFF1 DS.PSOLD1 rejected rejected rejected Log Log Log PKEY, DAY MIN(COST) S2.PARTS DW.PARTS FTP2 V1 Aggregate1 PKEY, MONTH AVG(COST) DW.PARTSUPP.DATE, DAY S1.PARTS TIME FTP1 V2  Aggregate2 Sources DW Timos Sellis - ADBIS 2007

  20. Architecture Graph Example 2 Timos Sellis - ADBIS 2007

  21. Architecture Graph Example 2 Timos Sellis - ADBIS 2007

  22. Semantics • We provide graph modeling techniques for several kinds of activities: • update (INS, UPD, DEL) activities • aggregates • rules employing negation and aliases • functions Timos Sellis - ADBIS 2007

  23. Logical Model • Question revisited What information should we put inside a metadata repository to be able to answer questions like: • what is the architecture of my DW back stage? • it is described as the Architecture Graph • which attributes/tables are involved in the population of an attribute? • what part of the scenario is affected if we delete an attribute? • follow the appropriate pathin the Architecture Graph Timos Sellis - ADBIS 2007

  24. Outline • Conceptual Model • Logical Model • Architecture Graph • Operational Semantics • Metrics • Conceptual to Logical • Optimization of ETL Workflows • Research Challenges Timos Sellis - ADBIS 2007

  25. Conceptual to Logical • Similarities • conceptsandattributes recordsetsandattributes • part-of and provider relationships Timos Sellis - ADBIS 2007

  26. Conceptual to Logical • Discrepancies Transformations vs. Activities Operational Semantics of SK : Timos Sellis - ADBIS 2007

  27. Conceptual to Logical • Correctness • our methodology is a semi-automatic procedure • the designer should examine, complement or change the outcome of this methodology • Optimization • the constraints in the determination of the execution order require only that the placement of activities should be semantically correct • the logical workflow produced by this methodology is not the only possible logical workflow • the final choice of an ETL workflow depends on other parameters, e.g., • the quality of the design of an ETL workflow • the execution cost of an ETL workflow Timos Sellis - ADBIS 2007

  28. Conceptual to Logical • Execution order… which is the proper execution order? Timos Sellis - ADBIS 2007

  29. Conceptual to Logical • Execution order… order equivalence? SK,f1,f2 orSK,f2,f1 or ... ? Timos Sellis - ADBIS 2007

  30. Outline • Conceptual Model • Logical Model • Architecture Graph • Operational Semantics • Metrics • Conceptual to Logical • Optimization of ETL Workflows • Research Challenges Timos Sellis - ADBIS 2007

  31. Optimization of ETL Workflows • Common settlement • ad-hoc optimization based on the experience of the designer • execute ETL workflow as it is; hopefully, the optimizer of the DBMS would improve the performance • An ETL workflow is NOT a big query • ETL is very procedural in nature, each ETL operator is a low-level operator in procedural languages like PL/SQL • Traditional query optimization techniques are not enough • existence of functions • where it is allowed to push an activity before/after a function? • existence of black-box activities • unknown semantics • can not interfere in their interior • naming conflicts Timos Sellis - ADBIS 2007

  32. Optimization of ETL Workflows • How can we improve an ETL workflow in terms of execution time? • We model the ETL processes optimization problem as a state search problem • we consider each ETL workflow as a state • we construct the search space • the optimal state is chosen according to our cost model’s criteria, in order to minimize the execution time of an ETL workflow Timos Sellis - ADBIS 2007

  33. Optimization of ETL Workflows • Transition from one state to the other • SWA: interchange two activities of the workflow • FAC: replace homologous tasks in parallel flows with an equivalent task over a flow to which these parallel flows converge • DIS: divide tasks of a joint flow to clones applied to parallel flows that converge towards the joint flow • MER / SPL: merge / split group of activities a,a1,a2: homologousactivities Timos Sellis - ADBIS 2007

  34. Optimization of ETL Workflows DSA DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE DS.PSNEW2 DS.PSNEW2.PKEY, DS.PSOLD2.PKEY QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ DIFF2 A2EDate SK2 $2€ DS.PSOLD2 rejected rejected rejected rejected Log Log Log Log DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE DS.PSNEW1 DS.PSNEW1.PKEY, DS.PSOLD1.PKEY COST DATE=SYSDATE PKEY,DATE DS.PS1 U PK NotNULL AddDate SK1 DIFF1 DS.PSOLD1 rejected rejected rejected Log Log Log PKEY, DAY MIN(COST) S2.PARTS DW.PARTS FTP2 V1 Aggregate1 PKEY, MONTH AVG(COST) DW.PARTSUPP.DATE, DAY S1.PARTS TIME FTP1 V2  Aggregate2 Sources DW Timos Sellis - ADBIS 2007

  35. Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE DW.PARTS U Log Log Log Log PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE rejected COST DATE=SYSDATE DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Timos Sellis - ADBIS 2007

  36. Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE Log Log Log DW.PARTS Log U PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE rejected DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Timos Sellis - ADBIS 2007

  37. Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE Log Log Log DW.PARTS Log U PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE rejected DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Timos Sellis - ADBIS 2007

  38. DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE DW.PARTS U Log Log Log Log PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE rejected COST DATE=SYSDATE DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing AddAttr $2€ SK2 A2EDate in out in out in out in out source source source Adate Adate Adate Edate skey pkey pkey pkey € $ € € skey Timos Sellis - ADBIS 2007

  39. DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE DW.PARTS U Log Log Log Log PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE rejected COST DATE=SYSDATE DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing ? ? AddAttr A2EDate SK2 $2€ in out in out in out in out source source source Adate Edate Adate Adate skey pkey pkey pkey € € € $ skey Timos Sellis - ADBIS 2007

  40. DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE DW.PARTS U Log Log Log Log PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE rejected COST DATE=SYSDATE DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing x  AddAttr A2EDate SK2 $2€ in out in out in out in out source source source Adate Edate Εdate Εdate skey pkey pkey pkey € $ $ $ skey Timos Sellis - ADBIS 2007

  41. SK2 U SK1 Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE Log Log Log DW.PARTS Log U PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE rejected DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Timos Sellis - ADBIS 2007

  42. Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE Log Log Log DW.PARTS Log U PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE rejected DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log SK1,2 U Timos Sellis - ADBIS 2007

  43. Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE Log Log Log DW.PARTS Log U PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE rejected DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log PK U U Timos Sellis - ADBIS 2007

  44. PK2 U PK1 Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE DS.PS2 AddAttr2 γ A2EDate SK2 $2€ rejected rejected rejected rejected PKEY,DATE Log Log Log DW.PARTS Log U PK DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE rejected DS.PS1 Log NotNULL AddDate SK1 rejected rejected Log Log Timos Sellis - ADBIS 2007

  45. Optimization of ETL Workflows Pre-Processing Phase I: SWA’s in local groups Phase II: FAC’s homologous Phase III: DIS’s Phase IV: SWA’s in local groups Post-Processing DS.PS1.PKEY, LOOKUP_PS.SKEY, SOURCE QTY,COST SOURCE COST DATE PKEY,DATE DS.PS2 AddAttr2 γ PK2 A2EDate SK2 $2€ rejected rejected rejected rejected rejected Log Log Log Log DW.PARTS Log U DS.PS2.PKEY, LOOKUP_PS.SKEY, SOURCE COST DATE=SYSDATE PKEY,DATE DS.PS1 PK1 NotNULL AddDate SK1 rejected rejected rejected Log Log Log Timos Sellis - ADBIS 2007

  46. Optimization of ETL Workflows • Algorithms • exhaustive (1) • heuristic (2) • greedy (3) • in phases I & IV perform only those transitions that drive to a better state • Results • algorithms (2) and (3) improve the performance of ETL workflows over70% (avg)during a satisfactory for DW’s period of time (in a time range of sec..10min) Timos Sellis - ADBIS 2007

  47. Optimization of ETL Workflows • Physical optimization • several physical operators implement the same semantic operation • e.g., a logical join can be performed in more than one way: nested loops, sort-merge join, hash-join • Solution • employ different alternatives for the physical execution of each logical-level activity • take into consideration • the effects of possible system failures to the workflow operation • the introduction of sorter activities in the physical design • Algorithms • exhaustive search of the search space • state-space pruning based on experimental observations and the benefits of sorter introduction given by the formula: Timos Sellis - ADBIS 2007

  48. Outline • Conceptual Model • Logical Model • Architecture Graph • Operational Semantics • Metrics • Conceptual to Logical • Optimization of ETL Workflows • Research Challenges Timos Sellis - ADBIS 2007

  49. Research Challenges • A unified way to represent ETL processes • an algebra or a declarative language • a benchmark for ETL processes • useful for evaluating optimization techniques, implementation algorithms for specific ETL activities, and so on(a first attempt was presented in QDB’07, in conj. w/ VLDB’07) • Extension of the ETL mechanisms for non-traditional data • XML/HTML, spatial, biomedical data, … • Apply the techniques described to similar environments • e.g., Active DWs • meet the high demand of applications for up-to-date information • are refreshed on-line and achieve a higher consistency between the stored information and the latest data updates Timos Sellis - ADBIS 2007

  50. Real time ETL Timos Sellis - ADBIS 2007

More Related