Template-driven approach to extract, transform, and/or load

• US 7,681,185 B2
• Filed: 10/12/2005
• Issued: 03/16/2010
• Est. Priority Date: 10/12/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Jain, Sandhya D, Kapoor, Rahul
• Primary Examiner(s): Zhen; Wei Y
• Assistant Examiner(s): Nguyen; Phillip H
• Application Number:
  US11/248,632
• Publication Number:
  US 20070083850A1
• Time in Patent Office: 1,616 Days
• Field of Search: 717/106
• US Class Current: 717/140
• CPC Class Codes: G06F 9/44521 Dynamic linking or loading; Link editing at or after load time, e.g. Java class loading

Abstract

System(s) and/or method(s) (“tools”) are described that enable multiple executable ETL (Extract, Transform, and Load) packages to be built based on a single template. The tools also enable multiple executable ETL packages to be altered by altering a single template and/or set of data parameters. In some embodiments the tools provide many types of templates that are capable, once instantiated with data parameters, of performing many different types of processes useful in extracting, transforming, and/or loading data from databases to a data warehouse. In so doing, the tools can help users to more easily, accurately, and/or robustly build ETL packages.

Claims

1. A computer-implemented method for building executable ETL (Extract, Transform, and Load) packages, the method comprising:

enabling a selector to select a template, the template comprising a data-independent, coordinated sequence of tasks;

receiving the selected template;

enabling a selection of data parameters, the data parameters comprising;

a source table in which data is stored in a source database;

a target table to store data in a data warehouse; and

a mapping between the source table and the target table;

receiving the selection of data parameters;

inferring additional data parameters required for execution of the template, the inferring based on the selection of received data parameters;

instantiating the data parameters to tasks of the template based on the received source table, target table, and mapping between the source table and the target table, and the inferred additional data parameters;

generating an executable ETL package based on the template and the data parameters; and

executing the executable ETL package.

2. The method of claim 1, further comprising receiving a second template having a second coordinated sequence of tasks and wherein the act of generating generates the executable ETL package based on the first coordinated sequence of tasks, the second coordinated sequence of tasks, and the set of data parameters.

3. The method of claim 1, further comprising maintaining the set of data parameters using shredded metadata.

4. The method of claim 1, further comprising enabling selection one or more templates each having a coordinated sequence of tasks to provide a selected template and wherein the template received as part of the act of receiving is the selected template.

5. The method of claim 1, further comprising receiving an additional set of data parameters and wherein the act of generating generates an additional executable ETL package based on the coordinated sequence of tasks and the additional set of data parameters.

6. The method of claim 5, further comprising altering the executable ETL package and the additional executable ETL package responsive to a received change to the template or the set of data parameters.

7. The method of claim 1, wherein the template comprises instantiated data parameters and wherein the act of generating replaces the instantiated data parameters with the set of data parameters.

8. The method of claim 1, wherein the act of generating comprises instantiating the coordinated sequence of tasks with the set of data parameters.

9. One or more computer readable media storing processor-executable instructions that, when executed, cause one or more processors to perform a method, the method comprising:

enabling a selector to select a template having a computer-readable, data independent, coordinated sequence of tasks capable of instantiation with data parameters effective to create a portion of an executable Extract, Transform, and Load (ETL) package with an executable form of the coordinated sequence of tasks;

receiving the selected template;

enabling a selection of data parameters, the data parameters comprising;

a source table in which data is stored in a source database;

a target table to store data in a data warehouse; and

a mapping between the source table and the target table;

receiving the selection of data parameters;

inferring additional data parameters required for execution of the template, the inferring based on the selection of received data parameters;

instantiating the data parameters to tasks of the template based on the received source table, target table, and mapping between the source table and the target table, and the inferred additional data parameters;

generating an executable ETL package based on the template and the data parameters; and

executing the executable ETL package.

10. The media of claim 9, wherein the template is further capable of instantiation with multiple sets of data parameters effective to create a portion of multiple and different executable ETL packages.

11. The media of claim 9, wherein the portion of the executable ETL package, when executed, is effective to perform one or more of the following processes:

a dimension load process;

a fact load process;

a dimension load with auditing process;

a fact load with auditing process;

a data cleansing process; and

an initial load process.

12. The media of claim 9, wherein execution of the portion of the executable ETL package is effective to perform one or more of the following processes:

an extract from source process;

a create physical objects process;

a delta process;

a surrogate key generation process;

a schema mapping process;

a deduping process;

an entity association process; and

a load to target process.

13. A computer-implemented method for constructing executable ETL (Extract, Transform, and Load) packages, the method comprising:

enabling a selector to select a template, the template comprising a data-independent, coordinated sequence of tasks;

receiving a change to a first ETL package or a template used to build the first ETL package;

enabling a selection of data parameters, the data parameters comprising;

a source table in which data is stored in a source database;

a target table to store data in a data warehouse; and

a mapping between the source table and the target table;

receiving the selection of data parameters;

inferring additional data parameters required for execution of the template, the inferring based on the selection of received data parameters;

instantiating the data parameters to tasks of the template based on the received change to the first ETL package or the template used to build the first ETL package, the source table, the target table, and mapping between the source table and the target table, and the inferred additional data parameters;

altering, responsive to the change, the first ETL package and a second ETL package, each of the first and second ETL packages having first and second task sequences and where the first and second task sequences or a portion of each of the first and second task sequences are identical or where the first and second ETL packages are governed at least in part by the template;

generating an executable first ETL package and an executable second ETL package based on the received change to the first ETL package or template used to build the first ETL package and the received data parameters; and

executing the executable first ETL package and the executable second ETL package.

14. The method of claim 13, wherein the act of altering is performed without user interaction.

15. The method of claim 13, wherein the act of receiving receives the change to the template and the template comprises a sequence of tasks contained in both the first and second ETL packages, and the act of altering comprises altering or recreating the first ETL package and the second ETL package to reflect the change to the template.

16. The method of claim 15, wherein the act of altering is performed without user interaction.