Method and architecture for automated optimization of ETL throughput in data warehousing applications

• US 6,208,990 B1
• Filed: 07/15/1998
• Issued: 03/27/2001
• Est. Priority Date: 07/15/1998
• Status: Expired due to Term

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Informatica Corporation
• Inventors: Gautam, Jyotindra Pramathnath, DeRose, Frank Joseph, Sankaran, Mohan, Pancha, Girish, Suresh, Sankaran
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): DO, THUY T
• Application Number:
  US09/116,426
• Time in Patent Office: 986 Days
• Field of Search: 707/102, 707/10, 707/100-3, 707/104, 707/200, 707/201, 395/149, 414/786
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A computer software architecture to automatically optimize the throughput of the data extraction/transformation/loading (ETL) process in data warehousing applications. This architecture has a componentized aspect and a pipeline-based aspect. The componentized aspect refers to the fact that every transformation used in this architecture is built up with transformation components selected from an extensible set of transformation components. Besides simplifying source code maintenance and adjustment for the data warehouse users, these transformation components also provide these users the building blocks to effectively construct pertinent and functionally sophisticated transformations in a pipelined manner. Within a pipeline, each transformation component automatically stages or streams its data to optimize ETL throughput. Furthermore, each transformation either pushes data to another transformation component, pulls data from another transformation component, or performs a push/pull operation on the data. Thereby, the pipelining; staging/streaming; and pushing/pulling features of the transformation components effectively optimizes the throughput of the ETL process.

Claims

1. A computer implemented method for transforming data in a data warehousing application, comprising the steps of:

specifying at least one source containing data;

constructing a plurality of transformation components for manipulating data according to pre-determined sets of rules;

coupling the transformation components to form one or more pipelines;

specifying a target for storing data generated by one or more of the pipelines;

staging data in a first of said plurality of transformation components; and

streaming data in a second of said plurality of transformation components, wherein said staging and said streaming of data are performed automatically by software without human intervention.

2. The computer implemented method of claim 1, wherein a source transformation component, a target transformation component, an aggregation transformation component, a rank transformation component, and a joiner transformation component perform the staging step.

3. The computer implemented method of claim 2, wherein an expression transformation component, a filter transformation component, an update strategy transformation component, a sequence transformation component, a lookup transformation component, a stored procedure transformation component, an external procedure transformation component, and a normalizer transformation component perform the streaming step.

4. The computer implemented method of claim 1 further comprising the step of pushing data from a first transformation component to a second transformation component.

5. The computer implemented method of claim 1 further comprising the step of pulling data from a first transformation component to a second transformation component.

6. The computer implemented method of claim 5 further comprising the step of pushing data from a third transformation component to a fourth transformation component.

7. The computer implemented method of claim 1 further comprising the step of a first transformation component pulling data from a second transformation component and pushing data to a third transformation component.

8. The computer implemented method of claim 1 further comprising the step of executing a plurality of tasks in parallel through a plurality of the pipelines.

9. A computer-readable medium having stored thereon instructions for causing a computer to transform data in a datamart application, comprising:

a source containing original data;

a plurality of transformation components for manipulating data according to pre-determined behaviors;

a mapping which specifies an order for coupling the transformation components to form one or more pipelines;

a target for storing data generated by one or more of the pipelines;

memory for staging said data generated by a first of said plurality of transformation components;

a second of said plurality of transformation components operable to stream said data generated by said second of said plurality of transformation components; and

instructions for automatically staging or streaming of data by each of the plurality of transformation components.

10. The computer-readable medium of claim 9, wherein a source transformation component, a target transformation component, an aggregation transformation component, a rank transformation component, and a joiner transformation component stage data.

11. The computer-readable medium of claim 10, wherein an expression transformation component, a filter transformation component, an update strategy transformation component, a sequence transformation component, a lookup transformation component, a stored procedure transformation component, an external procedure transformation component, and a normalizer transformation component stream data.

12. The computer-readable medium of claim 9 further comprising instructions for pushing data from a first transformation component to a second transformation component.

13. The computer-readable medium of claim 9 further comprising instructions for pulling data from a first transformation component to a second transformation component.

14. The computer-readable medium of claim 13 further comprising instructions for pushing data from a third transformation component to a fourth transformation component.

15. The computer-readable medium of claim 9 further comprising instructions for a first transformation component pulling data from a second transformation component and pushing data to a third transformation component.

16. The computer-readable medium of claim 15 further comprising instructions for executing a plurality of tasks in parallel through a plurality of the pipelines.