Data integration system with programmatic source and target interfaces

• US 8,108,534 B2
• Filed: 06/30/2003
• Issued: 01/31/2012
• Est. Priority Date: 05/08/2003
• Status: Expired due to Fees

• Current Assignee: Blue Yonder Group, Inc. (Panasonic Holdings Corporation)
• Original Assignee: JDA Software Group Incorporated (RedPrairie Holding Incorporated)
• Inventors: Carroll, Andrew J., Rishel, Jeremy D.
• Primary Examiner(s): LEE, PHILIP C
• Application Number:
  US10/611,779
• Publication Number:
  US 20040225671A1
• Time in Patent Office: 3,137 Days
• Field of Search: 709/204, 709/230, 709/219
• US Class Current: 709/230
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

In one embodiment, a system is provided for executing bulk data transfers between persistent data stores in connection with an enterprise-level business workflow. A data integration server is coupled to one or more stores. Programmatic source interfaces are each associated with a source store, defined according to a source interface specification, and exposed within the server during a transfer in connection with an enterprise-level business workflow to enable the server to extract from its source store data entities for loading into any selected target stores during the transfer. Programmatic target interfaces are each associated with a target store, defined according to a target interface specification, and exposed within the server during a transfer in connection with an enterprise-level business workflow to enable the server to load into its target store data entities extracted from any selected source stores during the transfer. Each programmatic interface: (1) provides to its store an abstraction of transfer operations within the server such that custom code need not be developed in connection with its store to enable transfers between its store and any other particular stores; and (2) isolates from the server specific details associated with its store such that custom code need not be developed in connection with the server to enable transfers between its store and any other particular data stores.

Claims

1. A computer-implemented system, comprising:

a data integration server coupled with one or more data stores, the data integration server executing bulk data transfers between the one or more data stores according to an enterprise-level business workflow, the data integration server comprising;

a plurality of programmatic source interfaces each coupled with one or more source data stores, defined according to a common programmatic source interface specification, and exposed within the data integration server during the bulk data transfer;

a plurality of programmatic target interfaces, each coupled with one or more target data stores, defined according to a common programmatic target interface specification, and exposed within the data integration server during the bulk data transfer; and

a plurality of relational interfaces used as alternatives to the plurality of programmatic source interfaces or the plurality of programmatic target interfaces,

wherein each of the plurality of programmatic source interfaces extracts from the one or more source data stores one or more data entities for loading into any of the one or more target data stores during the bulk data transfer;

wherein each of the plurality of programmatic target interfaces loads into the one or more target data stores the one or more data entities extracted from the one or more source data stores during the bulk data transfer; and

wherein the data integration server determines whether to use the plurality of relational interfaces or the plurality of programmatic source interfaces and the plurality of programmatic target interfaces based on file processing time and one or more performance requirements.

2. The system of claim 1, wherein the data integration server exposes bulk data transfer operations as services to applications or other systems within an enterprise-level infrastructure and executes a bulk data transfer operation in response to a request from one or more of the applications or other systems.

3. The system of claim 1, wherein the programmatic source interfaces and the programmatic target interfaces comprise JAVA interfaces.

4. The system of claim 1, wherein:

a programmatic interface is exposed within the data integration server as an industry standard interface supporting the bulk data transfers according to an industry standard protocol; and

the data integration server;

receives a request from a client indicating that the client is extracting data from or loading data into a data store in accordance with the industry standard protocol;

creates the programmatic interface to extract the data from or loading of the data into the data store; and

for data extraction, as the programmatic source interface produces the data extracted from the data store, sends the data to the client in accordance with the industry standard protocol;

or

for data loading, as the data arrives from the client in accordance with the industry standard protocol, sends the data to the programmatic target interface for loading into the data store.

5. The system of claim 4, wherein the data integration server allows a client supporting the industry standard protocol for the bulk data transfers to perform the bulk data transfers with respect to an existing data store using the programmatic interface whether or not the existing data store or an associated existing application supports the bulk data transfers in accordance with the industry standard protocol.

6. The system of claim 1, wherein:

a programmatic source interface is exposed within the data integration server as an industry standard File Transfer Protocol (FTP) interface supporting the bulk data transfers according to an FTP industry standard protocol; and

the data integration server;

allows an FTP client to open an FTP connection informing the data integration server that the FTP client is downloading a stream of data from the one or more source data stores;

creates the programmatic source interface to extract the stream of data from the one or more source data stores; and

as the programmatic source interface produces the stream of data extracted from the one or more source data stores, sends the outgoing stream of data to the FTP client in accordance with the FTP.

7. The system of claim 6, wherein at least one of the FTP clients comprise a commercially available Extract-Transform-Load (ETL) tool supported within the data integration server.

8. The system of claim 1, wherein:

a programmatic target interface is exposed within the data integration server as an industry standard File Transfer Protocol (FTP) interface supporting the bulk data transfers according to an FTP industry standard protocol; and

the data integration server;

allows an FTP client to open an FTP connection informing the data integration server that the FTP client is uploading a stream of data to the one or more target data stores;

creates the programmatic target interface to enable loading of the stream of data into the one or more source data stores; and

as the stream of data arrives from the FTP client in accordance with the FTP, sends the stream of data to the programmatic target interface for loading into the one or more target data stores.

9. The system of claim 8, wherein at least one of the FTP clients comprise a commercially available Extract-Transform-Load (ETL) tool supported within the data integration server.

10. The system of claim 1, wherein a particular data store is a source data store or a target data store for a particular bulk data transfer depending on whether the one or more data entities are extracted from the particular data store or loaded into the particular data store during the particular bulk data transfer.

11. The system of claim 1, wherein loading the one or more data entities comprises inserting, updating, or deleting the one or more data entities.

12. The system of claim 1, wherein:

each of the plurality of programmatic source interfaces and the plurality of programmatic target interfaces comprise one or more resources representing the one or more data entities contained in the one or more data stores are defined; and

the data integration server, in response to a request to execute the bulk data transfer involving one or more resources contained in the one or more data stores, creates each of the programmatic interfaces within which at least one of the resources is defined.

13. The system of claim 1, wherein the data integration server supports a commercially available Extract-Transform-Load (ETL) tool to:

connect directly to the one or more data stores with which the ETL tool is compatible to extract the one or more data entities directly from and load the one or more data entities directly into the one or more data stores; and

connect to the one or more data stores, whether or not the ETL tool is compatible with the one or more data stores, using the programmatic source interfaces and the programmatic target interfaces to extract the one or more data entities from and load the one or more data entities into the one or more data stores.

14. The system of claim 13, wherein the data integration server uses the programmatic source interfaces and the programmatic target interfaces to support compatibility between any of the commercially available ETL tools and any of the one or more data stores.

15. The system of claim 14, wherein the data integration server supports a controller to execute individual bulk data transfers using the programmatic source interfaces and the programmatic target interfaces where either:

the ETL tool is not present;

or

the ETL tool is present but capabilities of the ETL tool are not needed to transform the one or more data entities extracted from the one or more source data stores, using one or more of the plurality of programmatic source interfaces, before the extracted data entities are loaded into the one or more target data stores, using one or more of the plurality of programmatic target interfaces.

16. The system of claim 1, further comprising one or more transformation interfaces exposed within the data integration server, each of the one or more transformation interfaces:

comprising one or more of the plurality of programmatic source interfaces and one or more of the plurality of programmatic target interfaces defined within the transformation interface;

comprising custom transformation logic applied to the one or more data entities extracted from one or more source data stores in the bulk data transfer, using one or more of the plurality of programmatic source interfaces, before the extracted data entities are loaded into one or more target data stores in the bulk data transfer, using one or more of the plurality of programmatic target interfaces; and

isolating custom transformation logic from the one or more defined programmatic interfaces; and

the data integration server further, in connection with creating the defined programmatic interfaces, creates each of the transformation interfaces within which at least one of the programmatic interfaces is defined for application of the custom transformation logic in the bulk data transfer.

17. The system of claim 16, further comprising a controller supported within the data integration server to use the transformation interface in executing an individual bulk data transfer without using a commercially available Extract-Transform-Load (ETL) tool in connection with the bulk data transfer.

18. A computer-implemented method, comprising:

providing, by a server, a data integration server coupled with one or more data stores, the data integration server executing bulk data transfers between the one or more data stores according to an enterprise-level business workflow;

providing, by the server, a plurality of programmatic source interfaces, each coupled with one or more source data stores, defined according to a common programmatic source interface specification, and exposed within the data integration server during the bulk data transfer;

providing, by the server, a plurality of programmatic target interfaces, each coupled with one or more target data stores, defined according to a common programmatic target interface specification, and exposed within the data integration server during a bulk data transfer;

providing, by the server, a plurality of relational interfaces used as alternatives to the plurality of programmatic source interfaces or the plurality of programmatic target interfaces;

extracting, by the server, from the one or more source data stores one or more data entities for loading into any of the one or more target data stores during the bulk data transfer; and

loading, by the server, into the one or more target data stores the one or more data entities extracted from any of the one or more source data stores during the bulk data transfer,

wherein the data integration server determines whether to use the plurality of relational interfaces or the plurality of programmatic source interfaces and the plurality of programmatic target interfaces based on file processing time and one or more performance requirements.

19. The method of claim 18, wherein the data integration server exposes bulk data transfer operations as services to applications or other systems within an enterprise-level infrastructure and executes a bulk data transfer operation in response to a request from one or more of the applications or other systems.

20. The method of claim 18, wherein the programmatic source interfaces and the programmatic target interfaces comprise JAVA interfaces.

21. The method of claim 18, wherein:

a programmatic interface is exposed within the data integration server as an industry standard interface supporting the bulk data transfers according to an industry standard protocol; and

the method further comprises;

receiving a request from a client indicating that the client is extracting data from or loading data into a data store in accordance with the industry standard protocol;

creating the programmatic interface to extract the data from or loading of the data into the data store; and

for data extraction, as the programmatic source interface produces the data extracted from the data store, sending the data to the client in accordance with the industry standard protocol;

or

for data loading, as the data arrives from the client in accordance with the industry standard protocol, sending the data to the programmatic target interface for loading into the data store.

22. The method of claim 21, further comprising allowing a client supporting the industry standard protocol for the bulk data transfers to perform the bulk data transfers with respect to an existing data store using the programmatic interface whether or not the existing data store or an associated existing application supports the bulk data transfers in accordance with the industry standard protocol.

23. The method of claim 18, wherein:

a programmatic source interface is exposed within the data integration server as an industry standard File Transfer Protocol (FTP) interface supporting the bulk data transfers according to an FTP industry standard protocol; and

the method further comprises;

allowing an FTP client to open an FTP connection informing the data integration server that the FTP client is downloading a stream of data from the one or more source data stores;

creating the programmatic source interface to extract the stream of data from the one or more source data stores; and

as the programmatic source interface produces the stream of data extracted from the one or more source data stores, sending the outgoing stream of data to the FTP client in accordance with the FTP.

24. The method of claim 23, wherein at least one of the FTP clients comprise a commercially available Extract-Transform-Load (ETL) tool supported within the data integration server.

25. The method of claim 18, wherein:

a programmatic target interface is exposed within the data integration server as an industry standard File Transfer Protocol (FTP) interface supporting the bulk data transfers according to an FTP industry standard protocol; and

the method further comprises;

allowing an FTP client to open an FTP connection informing the data integration server that the FTP client is uploading a stream of data to the one or more target data stores;

creating the programmatic target interface to enable loading of the stream of data into the one or more source data stores; and

as the stream of data arrives from the FTP client in accordance with the FTP, sending the stream of data to the programmatic target interface for loading into the one or more target data stores.

26. The method of claim 25, wherein at least one of the FTP clients comprise a commercially available Extract-Transform-Load (ETL) tool supported within the data integration server.

27. The method of claim 18, wherein a particular data store is a source data store or a target data store for a particular bulk data transfer depending on whether the one or more data entities are extracted from the particular data store or loaded into the particular data store during the particular bulk data transfer.

28. The method of claim 18, wherein loading the one or more data entities comprises inserting, updating, or deleting the one or more data entities.

29. The method of claim 18, wherein:

each of the plurality of programmatic source interfaces and the plurality of programmatic target interfaces comprise one or more resources representing the one or more data entities contained in the one or more data stores are defined; and

the method further comprises, in response to a request to execute the bulk data transfer involving one or more resources contained in one or more data stores, creating each of the programmatic interfaces within which at least one of the resources is defined.

30. The method of claim 18, wherein the data integration server supports a commercially available Extract-Transform-Load (ETL) tool to:

connect directly to the one or more data stores with which the ETL tool is compatible to extract the one or more data entities directly from and load the one or more data entities directly into the one or more data stores; and

connect to the one or more data stores, whether or not the ETL tool is compatible with the one or more data stores, using the corresponding programmatic source interfaces and the programmatic target interfaces to extract the one or more data entities from and load the one or more data entities into the one or more data stores.

31. The method of claim 30, further comprising the data integration server using the programmatic source interfaces and the programmatic target interfaces to support compatibility between any of the commercially available ETL tools and any of the one or more data stores.

32. The method of claim 31, further comprising providing a controller within the data integration server to execute individual bulk data transfers using the programmatic source interfaces and the programmatic target interfaces where either:

the ETL Extract Transform Load (ETL) tool is not present;

or

the ETL tool is present but capabilities of the ETL tool are not needed to transform the one or more data entities extracted from the one or more source data stores, using one or more of the corresponding plurality of programmatic source interfaces, before the extracted data entities are loaded into the one or more target data stores, using one or more of the corresponding plurality of programmatic target interfaces.

33. The method of claim 18, further comprising:

providing one or more transformation interfaces exposed within the data integration server, each of the one or more transformation interface;

comprising one or more of the plurality of programmatic source interfaces and one or more of the plurality of programmatic target interfaces defined within the transformation interface;

comprising custom transformation logic applied to the one or more data entities extracted from one or more source data stores in the bulk data transfer, using one or more of the corresponding plurality of programmatic source interfaces, before the extracted data entities are loaded into one or more target data stores in the bulk data transfer, using one or more of the corresponding plurality of programmatic target interfaces; and

isolating custom transformation logic from the one or more defined programmatic interfaces; and

the data integration server, in connection with creating the defined programmatic interfaces, creating each of the transformation interfaces within which at least one of the programmatic interfaces is defined for application of the custom transformation logic in the bulk data transfer.

34. The method of claim 33, further comprising providing a controller supported within the data integration server to use the transformation interface in executing an individual bulk data transfer without using a commercially available Extract-Transform-Load (ETL) tool in connection with the bulk data transfer.

35. A non-transitory computer readable medium embodied with software executing bulk data transfers between data stores according to an enterprise-level business workflow, the software when executed using one or more computers is configured to:

provide a data integration server coupled with one or more data stores;

provide a plurality of programmatic source interfaces, each coupled with one or more source data stores, defined according to a common programmatic source interface specification, and exposed within the data integration server during the bulk data transfer;

provide a plurality of programmatic target interfaces, each coupled with one or more target data stores, defined according to a common programmatic target interface specification, and exposed within the data integration server during the bulk data transfer;

provide a plurality of relational interfaces used as alternatives to the plurality of programmatic source interfaces or the plurality of programmatic target interfaces;

extract from the one or more source data stores one or more data entities for loading into any of the one or more target data stores during the bulk data transfer; and

load into the one or more target data stores the one or more data entities extracted from any of the one or more source data stores during the bulk data transfer,

wherein the data integration server determines whether to use the plurality of relational interfaces or the plurality of programmatic source interfaces and the plurality of programmatic target interfaces based on file processing time and one or more performance requirements.

36. The computer readable medium of claim 35, wherein the software is further configured to expose bulk data transfer operations of the data integration server as services to applications or other systems within an enterprise-level infrastructure and to execute a bulk data transfer operation in response to a request from one or more of the application or other system.

37. The computer readable medium of claim 35, wherein the programmatic source interfaces and the programmatic target interfaces comprise JAVA interfaces.

38. The computer readable medium of claim 35, wherein:

a programmatic interface is exposed within the data integration server as an industry standard interface supporting the bulk data transfers according to an industry standard protocol; and

the software is further configured to;

receive a request from a client indicating that the client is extracting data from or loading data into a data store in accordance with the industry standard protocol;

create the corresponding programmatic interface to extract the data from or loading of the data into the data store; and

for data extraction, as the programmatic source interface produces the data extracted from the data store, send the data to the client in accordance with the industry standard protocol;

or

for data loading, as the data arrives from the client in accordance with the industry standard protocol, send the data to the programmatic target interface for loading into the data store.

39. The computer readable medium of claim 38, wherein the software is further configured to allow a client supporting the industry standard protocol for the bulk data transfers to perform the bulk data transfers with respect to an existing data store using the programmatic interface whether or not the existing data store or an associated existing application supports the bulk data transfers in accordance with the industry standard protocol.

40. The computer readable medium of claim 35, wherein:

a programmatic source interface is exposed within the data integration server as an industry standard File Transfer Protocol (FTP) interface supporting the bulk data transfers according to an FTP industry standard protocol; and

the software is further configured to;

allow an FTP client to open an FTP connection informing the data integration server that the FTP client is downloading a stream of data from the corresponding source data store;

create the programmatic source interface to extract the stream of data from the corresponding source data store; and

as the programmatic source interface produces the stream of data extracted from the corresponding source data store, send the outgoing stream of data to the FTP client in accordance with the FTP.

41. The computer readable medium of claim 40, wherein at least one of the FTP clients comprise a commercially available Extract-Transform-Load (ETL) tool supported within the data integration server.

42. The computer readable medium of claim 35, wherein:

a programmatic target interface is exposed within the data integration server as an industry standard File Transfer Protocol (FTP) interface supporting the bulk data transfers according to an FTP industry standard protocol; and

the software is further configured to;

allow an FTP client to open an FTP connection informing the data integration server that the FTP client is uploading a stream of data to the corresponding target data store;

create the programmatic target interface to enable loading of the stream of data into the corresponding source data store; and

as the stream of data arrives from the FTP client in accordance with the FTP, send the stream of data to the programmatic target interface for loading into the corresponding target data store.

43. The computer readable medium of claim 42, wherein at least one of the FTP clients comprise a commercially available Extract-Transform-Load (ETL) tool supported within the data integration server.

44. The computer readable medium of claim 35, wherein a particular data store is a source data store or a target data store for a particular bulk data transfer depending on whether the one or more data entities are extracted from the particular data store or loaded into the particular data store during the particular bulk data transfer.

45. The computer readable medium of claim 35, wherein loading the one or more data entities comprises inserting, updating, or deleting the one or more data entities.

46. The computer readable medium of claim 35, wherein:

each of the plurality of programmatic source interfaces and the plurality of programmatic target interfaces comprise one or more resources representing the one or more data entities contained in the corresponding data store are defined; and

the software is further configured to, in response to a request to execute the bulk data transfer involving one or more resources contained in the one or more data stores, create each of the programmatic interfaces within which at least one of the resources is defined.

47. The computer readable medium of claim 35, wherein the software is further configured to support a commercially available Extract-Transform-Load (ETL) tool configured to:

connect directly to the one or more data stores with which the ETL tool is compatible to extract the one or more data entities directly from and load the one or more data entities directly into the one or more data stores; and

connect to the one or more data stores, whether or not the ETL tool is compatible with the one or more data stores, using the corresponding programmatic source interfaces and the programmatic target interfaces to extract the one or more data entities from and load the one or more data entities into the one or more data stores.

48. The computer readable medium of claim 47, wherein the software is further configured to use the programmatic source interfaces and the programmatic target interfaces to support compatibility between any of the commercially available ETL tools and any of the one or more data stores.

49. The computer readable medium of claim 48, wherein the software is further configured to support a controller configured to execute individual bulk data transfers using the programmatic source interfaces and the programmatic target interfaces where either:

the ETL tool is not present;

or

the ETL tool is present but capabilities of the ETL tool are not needed to transform the one or more data entities extracted from the one or more source data stores, using one or more of the corresponding plurality of programmatic source interfaces, before the extracted data entities are loaded into the one or more target data stores, using one or more of the corresponding plurality of programmatic target interfaces.

50. The computer readable medium of claim 35, wherein the software is further configured to:

provide one or more transformation interfaces exposed within the data integration server, each of the one or more transformation interfaces;

comprising one or more of the plurality of programmatic source interfaces and one or more of the plurality of programmatic target interfaces defined within the transformation interface;

comprising custom transformation logic applied to the one or more data entities extracted from one or more source data stores in the bulk data transfer, using one or more of the corresponding plurality of programmatic source interfaces, before the extracted data entities are loaded into one or more target data stores in the bulk data transfer, using one or more of the corresponding plurality of programmatic target interfaces; and

isolating custom transformation logic from the one or more defined programmatic interfaces; and

creating each of the transformation interfaces, in connection with creating the defined programmatic interfaces, within which at least one of the programmatic interfaces is defined for application of the custom transformation logic in the bulk data transfer.

51. The computer readable medium of claim 50, wherein the software is further configured to provide a controller configured to use the transformation interface in executing an individual bulk data transfer without using a commercially available Extract-Transform-Load (ETL) tool in connection with the bulk data transfer.

52. A computer-implemented system for executing bulk data transfers between data stores according to an enterprise-level business workflow, comprising:

a data integration server coupled with one or more data stores, the data integration server exposes bulk data transfer operations as services to applications or other systems within an enterprise-level infrastructure and executes a bulk data transfer operation in response to a request from one or more of the applications or other systems, the data integration server comprising;

a plurality of programmatic source interfaces, each associated with a corresponding source data store, defined according to a common programmatic source interface specification, and exposed within the data integration server during the bulk data transfer according to an enterprise-level business workflow to enable the data integration server to extract from the corresponding source data store one or more data entities for loading into any one or more selected target data stores during the bulk data transfer;

a plurality of programmatic target interfaces, each being associated with a corresponding target data store, defined according to a common programmatic target interface specification, and exposed within the data integration server during the bulk data transfer according to an enterprise-level business workflow to enable the data integration server to load into the corresponding target data store the one or more data entities extracted from any one or more selected source data stores during the bulk data transfer;

a plurality of relational interfaces used as alternatives to the plurality of programmatic source interfaces or the plurality of programmatic target interfaces based on file processing time and one or more performance requirements;

wherein each of the plurality of programmatic source interfaces and the plurality of programmatic target interfaces;

provide to the corresponding source data store and the corresponding target data store an abstraction of bulk data transfer operations within the data integration server such that custom code need not be developed in connection with the corresponding source data store and the corresponding target data store to enable the bulk data transfers between the corresponding source data store and the corresponding target data store; and

isolate from the data integration server specific details associated with the corresponding source data store and the corresponding target data store, wherein custom code need not be developed in connection with the data integration server to enable the bulk data transfers between the corresponding source data store and the corresponding target data store;

one or more transformation interfaces exposed within the data integration server, each of the one or more transformation interfaces;

comprising one or more of the plurality of programmatic source interfaces and one or more of the plurality of programmatic target interfaces defined within the transformation interface;

comprising custom transformation logic applied to the one or more data entities extracted from the one or more source data stores in the bulk data transfer, using one or more of the corresponding plurality of programmatic source interfaces, before the extracted data entities are loaded into the one or more target data stores in the bulk data transfer, using one or more of the corresponding plurality of programmatic target interfaces; and

isolating custom transformation logic from the one or more defined programmatic interfaces;

the data integration server further, in connection with creating the defined programmatic interfaces, create each of the transformation interfaces within which at least one of the programmatic interfaces is defined for application of the custom transformation logic in the bulk data transfer; and

a controller supported within the data integration server to use the one or more transformation interfaces in executing an individual bulk data transfer without using a commercially available Extract-Transform-Load (ETL) tool in connection with the bulk data transfer,

wherein the data integration server determines whether to use the plurality of relational interfaces or the plurality of programmatic source interfaces and the plurality of programmatic target interfaces based on file processing time and one or more performance requirements.