QUALITY-DRIVEN ETL DESIGN OPTIMIZATION

• US 20110047525A1
• Filed: 08/18/2009
• Published: 02/24/2011
• Est. Priority Date: 08/18/2009
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Castellanos, Maria G., Simitsis, Alkiviadis, Dayal, Umeshwar, Wilkinson, William K.
• Granted Patent: US 8,719,769 B2
• Time in Patent Office: Days
• US Class Current: 717/104
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 9/4494 data driven

Abstract

A method for quality objective-based ETL pipeline optimization is provided. An improvement objective is obtained from user input into a computing system. The improvement objective represents a priority optimization desired by a user for improved ETL flows for an application designed to run in memory of the computing system. An ETL flow is created in the memory of the computing system. The ETL flow is restructured for flow optimization with a processor of the computing system. The flow restructuring is based on the improvement objective. Flow restructuring can include application of flow rewriting optimization or application of an algebraic rewriting optimization. The optimized ETL flow is stored as executable code on a computer readable storage medium.

Claims

1. A method for Extract Transform Load (ETL) process optimization on a computing system, comprising:

obtaining an improvement objective from user input into the computing system, the improvement objectives representing a priority optimization desired by a user for improved ETL flows for an application configured to run in memory of the computing system;

creating an ETL flow in memory of the computing system;

restructuring the ETL flow for flow optimization using a processor of the computing system by applying an algebraic rewriting process, wherein the flow restructuring is based on the improvement objective; and

storing the optimized ETL flow as executable code on a computer readable storage medium.

2. A method in accordance with claim 1, wherein restructuring the ETL flow further comprises applying a flow rewriting optimization.

3. A method in accordance with claim 2, further comprising determining computing resources available for processing ETL flows, and wherein the flow restructuring is based on the improvement objective and the available computing resources.

4. A method in accordance with claim 1, wherein the improvement objective comprises at least one of system reliability, system maintainability, data freshness, flow recoverability, system scalability, system availability, system flexibility, system robustness, system affordability, system and data consistency, data traceability, and data auditability.

5. A method in accordance with claim 1, wherein applying an algebraic rewriting optimization further comprises creating a graph of algebraic operators with data flow between the operators, the graph being usable by the processor in determining optimal flow rewriting.

6. A method in accordance with claim 1, further comprising optimizing data flows including structured and unstructured data, and incorporating the structured and unstructured data into a common data warehouse or into a common database on the computer readable storage medium.

7. A method in accordance with claim 6, wherein optimizing data flows and incorporating data for structured and unstructured data further comprises using an integrated structured and unstructured pipeline to load data into the data warehouse.

8. A method in accordance with claim 1, further comprising translating quality objectives into quality metrics using the processor, and representing the ETL flow as a graph wherein the quality metrics are represented as graph properties on the graph.

9. A method for Extract Transform Load (ETL) process optimization on a computing system, comprising:

obtaining an improvement objective from user input into the computing system, the improvement objective representing a priority optimization desired by a user for improved ETL flows for an application configured to run in memory of the computing system;

determining available computing components for processing ETL flows;

creating an ETL flow in memory of the computing system, the ETL flow being implemented on the computing components;

restructuring the ETL flow for flow optimization with a processor of the computing system by applying flow rewriting optimization, wherein the flow rewriting is based on the improvement objective and the available computing components; and

storing the optimized ETL flow as executable code on a computer readable storage medium.

10. A method in accordance with claim 9, wherein restructuring the ETL flow further comprises applying an algebraic rewriting technique.

11. A method in accordance with claim 9, wherein the improvement objective comprises at least one of system reliability, system maintainability, data freshness, flow recoverability, system scalability, system availability, system flexibility, system robustness, system affordability, system and data consistency, data traceability, and data auditability.

12. A method in accordance with claim 9, wherein applying the flow rewriting technique further comprises at least one of collapsing long sequential flow chains, eliminating conditional branching nodes, factorizing common operations, parallelizing flows and adding recovery points.

13. A method in accordance with claim 9, wherein the ETL flow comprises one of an Extract Transform Load flow, an Extract Load Transform flow, and an Extract Transform Load Transform flow.

14. A method for integration of quality objectives into an Extract Transform Load (ETL) flow on a computing system comprising:

gathering quality objectives for ETL data flows configured to run in memory of the computing system;

entering the quality objectives into the computing system;

creating a conceptual model of ETL data flows including dependencies and constraints among the flows using the memory and a processor;

restructuring ETL data flows using the conceptual model and the quality objectives to form a first optimized ETL data flow stored in the memory;

creating a logical model of the first optimized ETL data flow using the memory and the processor by expressing the conceptual model as a graph of algebraic operators with data flow between the operators;

restructuring ETL data flows with the processor using the logical model and the quality objectives to form a second optimized ETL data flow stored in the memory; and

generating a code model of the second optimized ETL data flow including executable code stored on the computer readable storage medium.

15. A method in accordance with claim 14, wherein the quality objectives comprise at least one of system reliability, system maintainability, data freshness, flow recoverability, system scalability, system availability, system flexibility, system robustness, system affordability, system and data consistency, data traceability, and data auditability.

16. A method in accordance with claim 14, further comprising executing the code model stored on the computer readable storage medium using the processor.

17. A method in accordance with claim 14, wherein gathering quality objectives and entering the quality objectives is performed by the computing system using information stored in memory or on a computer readable storage medium.

18. A method in accordance with claim 14, wherein creating the conceptual model further comprises creating a quality objective interdependency graph and wherein restructuring ETL data flows further comprises using the quality objective interdependency graph in guiding the selection of the flow restructuring optimizations.

19. A method in accordance with claim 14, wherein optimizing ETL processes further comprises generating an ETL flow for an entire set of ETL transformations based on the conceptual and logical models using the computing system.

20. A method in accordance with claim 14, further comprising repeating at least one of creating a conceptual, logical, or code model, or restructuring the ETL flow to further optimize the ETL flow.