Distributed extract, transfer, and load (ETL) computer method

• US 7,051,334 B1
• Filed: 04/27/2001
• Issued: 05/23/2006
• Est. Priority Date: 04/27/2001
• Status: Expired due to Fees

• Current Assignee: Sprint Communications Company LP (Deutsche Telekom AG)
• Original Assignee: Sprint Communications Company LP (Deutsche Telekom AG)
• Inventors: Porter, Duane L., Swanholm, David E.
• Primary Examiner(s): Thomson, William
• Assistant Examiner(s): ANYA, CHARLES E
• Application Number:
  US09/843,682
• Time in Patent Office: 1,852 Days
• Field of Search: 707 1- 10, 707101-1041, 709200-253, 719311-317
• US Class Current: 719/313
• 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/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: H04L 12/66 Arrangements for connecting between networks having differing types of switching systems, e.g. gateways

Abstract

The present invention is a distributed extract, transform and load (ETL) method for delivering information within a computing environment, comprising extracting information from an information source and transforming the extracted information. The transformed information is isolated by wrapping the transformed information into a message envelope having a standard format. The message envelope is routed to at least one information target, unwrapped to reveal the received information, preferably transformed again, and loaded into the information target. The extraction, transformation, and adaptation steps on the source side are isolated from the routing step such that the extraction, transformation, and adaptation steps on the source side may be executed simultaneously for a plurality of information sources distributed across the computing environment to produce a plurality of message envelopes. The routing, unwrapping, mapping, transformation, and loading steps on the target side are repeated for each of the plurality of message envelopes.

Claims

1. A method for delivering information within a computing environment, comprising:

a) extracting information from an information source;

b) transforming the extracted information;

c) wrapping the transformed information into a message envelope having a standard format;

d) routing the message envelope to at least one information target;

e) unwrapping the message envelope to reveal the information received;

f) mapping the received information to a format required by the information target;

g) transforming the received information; and

h) loading the received information into the information target,

wherein the extracting, transforming, and wrapping steps (a)–

(c), respectively, are isolated from the routing step (d) such that the extracting, transforming, and wrapping steps may bc executed simultaneously for a plurality of information sources distributed across the computing environment to produce a plurality of message envelopes and wherein the routing, unwrapping, mapping, transforming, and loading steps (d)–

(h), respectively, are repeated for each of the plurality of message envelopes.

2. The method of claim 1 wherein the information is pulled from the source during the extracting step (a).

3. The method of claim 1 wherein the information is pushed from the source during the extracting step (a).

4. The method of claim 1 wherein the information extracted during step (a) comprises content changes to the source information at the time step (a) is performed as compared to the source information at a previous point in time.

5. The method of claim 1 wherein transforming the extracted information during step (b) further comprising applying one or more business rules to modify the extracted information.

6. The method of claim 1 wherein the message envelope further comprises an identification of the information source, a content definition identification and the content of the transformed information.

7. The method of claim 6 wherein the content definition identification is used to retrieve the content definition from a metadata repository.

8. The method of claim 6 wherein wrapping the message envelope further comprising retrieving content definition from a metadata repository and applying the content definition to the transformed information to produce a message envelope.

9. The method of claim 6 further comprising placing the message envelope into an inbox queue to a router component for routing according to step (d).

10. The method of claim 9 wherein the information sources publish the message envelope to the inbox queue and the router component subscribes to the inbox queue.

11. The method of claim 10 further comprising retrieving the message envelope from the inbox queue, looking up the address of the information target in a cross-reference table, and transmitting the message envelope to the information target.

12. The method of claim 11 wherein looking up the address of the information target is cross-referenced by the identity of the information source.

13. The method of claim 12 wherein the cross-reference table resides in local memory within the router component.

14. The method of claim 11 wherein transmitting the message envelope comprises placing the message envelope into an information target queue.

15. The method of claim 14 wherein the router component publishes the message envelope to the outbox queue and the information target subscribes to the outbox queue.

16. The method of claim 15 wherein the message envelope is retrieved from the outbox queue prior to unwrapping the message envelope.

17. The method of claim 8 wherein unwrapping the message envelope further comprising retrieving content definition from the metadata repository and applying the content definition to the message envelope to reveal the transformed information.

18. The method of claim 1 further comprising after unwrapping the message envelope, filtering the transformed information prior to loading the transformed information.

19. The method of claim 1 further comprising after unwrapping the message envelope, aggregating a plurality of transformed information and loading the aggregation of transformed information into the information target as a batch.

20. The method of claim 1 wherein the information target comprises a data warehouse and a data mart.

21. The method of claim 1 wherein the method for delivering information is executed on a plurality of computing platforms within the computing environment.

22. The method of claim 21 wherein the plurality of computing platforms comprise information domains for an enterprise.

23. A computer system for delivering information within a computing environment, comprising a method for:

a) extracting information from an information source;

b) transforming the extracted information;

c) isolating the transformed information by wrapping the transformed information into a message envelope having a standard format;

d) routing the message envelope to at least one information target;

e) unwrapping the message envelope to reveal the transformed information;

f) mapping the received information to a format required by the information target;

g) transforming the received information; and

h) loading the transformed information into the information target;

wherein the routing step occurs at a different resource than the extracting, the transforming, and the loading steps (a)–

(c) such that the extracting, transforming, and wrapping steps may be executed simultaneously for a plurality of information sources distributed across the computing environment to produce a plurality of message envelopes and wherein the routing, unwrapping, transforming and loading steps (d)–

(h) are repeated for each of each of the plurality of message envelopes.

24. The computer system of claim 23 wherein the extracting, transforming, and isolating steps occur at the information source.

25. The computer system of claim 24 wherein the unwrapping and loading steps occur at the information target.

26. The computer system of claim 25 wherein the transforming step occurs prior to the isolating step.

27. The computer system of claim 26 wherein the extracting, transforming, and isolating steps occur prior to the routing step.