Method and apparatus for pipelining the transformation of information between heterogeneous sets of data sources

• US 6,449,619 B1
• Filed: 06/23/1999
• Issued: 09/10/2002
• Est. Priority Date: 06/23/1999
• Status: Expired due to Term

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: Datamirror Corporation (International Business Machines Corporation)
• Inventors: Colliat, George, Scott, Jonathan, Lyon, Steve, Sieradzki, Stefan, DʼCruz, Marina
• Primary Examiner(s): Metjahic, Safet
• Assistant Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US09/342,155
• Time in Patent Office: 1,175 Days
• Field of Search: 707/1, 707/4, 707/5, 707/9, 707/101.1, 707/200, 707/100, 707/101, 707/102, 707/103
• US Class Current: 707/604
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/956 Hierarchical
• CPC Class Codes: Y10S 707/959 Network
• CPC Class Codes: Y10S 707/962 Entity-attribute-value
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

The invention provides a method and apparatus for pipelining the transformation of information between heterogeneous sets of data sources. The invention avoids intermediate data staging by constructing metadata that allow the acquisition of data in an order that matches the order in which the target data must be delivered. In a further feature of the invention, the acquisition, transformation, and delivery stages of an interface between two applications are pipelined as three processes that execute simultaneously on a continuous flow of data. Pipelining is a well-known technology that has been in use in operating systems, such as UNIX pipes and more recently by Torrent systems, to optimize the usage of multiprocessor machines. Uniquely, the invention applies pipelining technology to data transformation between heterogeneous data sources.

Claims

1. A method for reducing processing time between acquisition, transformation, and delivery of data during the transformation of information between heterogeneous sets of data sources, comprising the steps of:

subdividing a target into candidate hierarchies;

for each target attribute, building transformation rules with a mapping module to construct a target from a source;

identifying with a source view, source entities and attributes required by said mapping module;

identifying source view hierarchies that match target hierarchies;

identifying source hierarchies that match source view hierarchies; and

constructing metadata describing the structure of said source entities, attributes, and relationships, hierarchies of said source view, said mapping module, the structure of said target entities, attributes, and relationships.

2. A method for creating metadata to facilitate the transformation and exchange of data between heterogeneous data sources comprising the steps of:

creating metadata that defines one or more delivery hierarchical data structures and includes delivery order information such that data organized according to the one or more delivery hierarchical data structures when delivered to the target data source in the delivery order meet the referential integrity constraints of the target data source;

creating metadata that defines the transformations required to construct data organized according to each of the one or more delivery hierarchical data structures from source data elements including transformation order information that results in source data elements being transformed into data organized according to the one or more delivery hierarchical data structures in the delivery order; and

creating metadata that defines one or more acquisition data structures and includes acquisition order information such that data organized according to the one or more acquisition data structures contains all of the source data elements required to create data organized according to the one or more delivery hierarchical data structures and data organized according to the one or more acquisition data structures acquired from the source data source in the acquisition order can be transformed in the transformation order.

3. The method of claim 2 wherein the one or more delivery hierarchical data structures represent a decomposition of a network data structure defining the target data source.

4. The method of claim 2 wherein the source data source is in table format and the acquisition order matches the foreign key/primary key relationship.

5. The method of claim 2 wherein the source data source is a structured file and the acquisition order matches the order of data in the structured file.

6. The method of claim 2 wherein the source data is a denormalized file or table and the acquisition order corresponds to a relationship that is linked to the denormalized attributes.

7. The method of claim 2 wherein the source data source is a combination of zero or more table format data sources, zero or more structured files and zero or more denormalized files or tables and the acquisition order matches the foreign/primary key relationship for each table format data source, matches the order of data in each structured file and corresponds to a relationship that is linked to the denormalized attributes in each denormalized file or table.

8. A method for transforming and exchanging data between heterogeneous data sources comprising the steps of:

creating metadata that defines one or more delivery hierarchical data structures, the delivery order in which data organized according to the one or more delivery hierarchical data structures is delivered to the target data source such that the referential integrity constraints of the target data source are met, one or more acquisition data structures, transformations to transform data organized according to the one or more acquisition data structures to data organized according to the one or more delivery hierarchical data structures, the transformation order in which such transformations are performed in order to ensure that data organized according to the one or more delivery hierarchical data structures is created in the delivery order, and the acquisition order in which data organized according to the one or more acquisition data structures is acquired in order to ensure that transformations can be performed in the transformation order;

acquiring acquisition data organized according to the one or more acquisition data structures from the source data source in the acquisition order;

transforming acquisition data to delivery data according to the transformations; and

delivering the delivery data to the target data source in the delivery order.

9. The method of claim 8 in which the acquiring step, the transforming step and the delivering step are run as three separate threads or processes.