Extract, transform and load using metadata

• US 8,442,935 B2
• Filed: 03/30/2011
• Issued: 05/14/2013
• Est. Priority Date: 03/30/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Cottle, Aaron D., Peri, Jagan M., Burugapalli, Srinivasa R., Koya, Veerendra K.
• Primary Examiner(s): LE, HUNG D
• Application Number:
  US13/076,291
• Publication Number:
  US 20120254103A1
• Time in Patent Office: 776 Days
• Field of Search: 707/602, 707/756, 707/761, 707/802, 707/809, 707/975
• US Class Current: 707/602
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

The extract and transformation operation performed on multiple data sources. In the extract operation, a computing system periodically performs an extraction operation on the data sources, perhaps as defined by metadata. The transformation operation is performed on at least some the extracted data, and is periodically performed, perhaps also in a manner that is defined by metadata. When performing each iteration of the transformation operation, the computing system performs a transformation operation on valid data, marks other data as suspect (due to a dependency on data that is not present), and completes transformation on yet other data that had previously been marked as suspect or perhaps reaffirms its suspect status if the needed data is still not available.

Claims

1. A computer program product comprising one or more physical computer storage device having thereon computer-executable instructions that are structured such that, when executed by one or more processors of a computing system, cause the computing system to perform a method for performing an extract and transform of data from a plurality of data sources, the method comprising:

an act of the computing system repeatedly performing an extraction operation on a plurality of data sources;

an act of the computing system periodically performing a transformation operation on the extracted data, wherein the act of periodically performing a transformation operation for each of at least one iteration of the transformation operation comprises;

for at least some of the extracted data that are valid, an act of performing a transformation operation on the valid data;

for at least some of the extracted data that are dependent on other data that is not yet extracted, and an act of identifying the extracted data element as suspect; and

for at least some extracted data that had been marked as suspect in a prior interaction of the transformation operation, an act of completing transformation of the previously-marked suspect data.

2. The computer program product in accordance with claim 1, wherein the iteration of the transformation operation includes the following for the at least some of the extracted data that are dependent on other data that is not yet extracted:

an act of partially performing a transformation operation on the extracted data.

3. The computer program product in accordance with claim 2, wherein the iteration of the transformation operation includes the following for the least some of the extracted data that had been marked as suspect in a prior interaction of the transformation operation:

an act of completing transformation on the previously-marked suspect data that had been partially transformed in the prior transformation.

4. The computer program product in accordance with claim 1, wherein the period for extraction may differ depending on the identity of the data source.

5. The computer program product in accordance with claim 4, wherein the period of extraction is variable from one period to the next and is defined by editable data.

6. The computer program product in accordance with claim 4, wherein the period of extraction is variable one data source to the next and is defined by editable data.

7. The computer program product in accordance with claim 1, wherein the transformation operation is defined by editable data and may differ by data source.

8. The computer program product in accordance with claim 1, wherein the transformation operation is defined by editable data and may differ by component within a data source.

9. The computer program product in accordance with claim 1, wherein the computer-executable instructions are further structured such that the method further comprises:

an act of performing a load operation on the transformed data.

10. The computer program product in accordance with claim 1, wherein the transformation operation includes a sequence of transformations.

11. The computer program product in accordance with claim 1, wherein the transformation operation includes at least one branch operation.

12. The computer program product in accordance with claim 1, wherein the transformation operation includes at least one merge operation.

13. The computer program product in accordance with claim 1, wherein the load operation is defined by editable data and may differ by component within a data source.

14. A method for a computing system to perform an extract and transform of data from a plurality of data sources, the computing system comprising one or more physical processors, the method comprising:

an act of the computing system repeatedly performing an extraction operation on a plurality of data sources, wherein the period for extraction may differ depending on the identity of the data source, wherein the period and manner of extraction are defined by metadata;

an act of the computing system periodically performing a transformation operation on the extracted data, wherein the transformation operation is also defined by metadata, and wherein the act of periodically performing a transformation operation for each of at least one iteration of the transformation operation comprises;

for at least some of the extracted data that are valid, an act of performing a transformation operation on the valid data;

for at least some of the extracted data that are dependent on other data that is not yet extracted, and an act of identifying the extracted data element as suspect; and

for at least some extracted data that had been marked as suspect in a prior interaction of the transformation operation, an act of completing transformation of the previously-marked suspect data.

15. The method in accordance with claim 14, wherein the iteration of the transformation operation includes the following for the at least some of the extracted data that are dependent on other data that is not yet extracted:

an act of partially performing a transformation operation on the extracted data.

16. The method in accordance with claim 15, wherein the iteration of the transformation operation includes the following for the least some of the extracted data that had been marked as suspect in a prior interaction of the transformation operation:

an act of completing transformation on the previously-marked suspect data that had been partially transformed in the prior transformation.

17. The method in accordance with claim 14, further comprising:

an act of performing a load operation on the transformed data.

18. The method in accordance with claim 14, wherein the transformation operation includes a plurality of transformations.

19. A computer program product comprising one or more physical computer storage device having thereon computer-executable instructions that are structured such that, when executed by one or more processors of a computing system, cause the computing system to perform a method for performing an extract and transform of data from a plurality of data sources, the method comprising:

an act of periodically performing an extraction operation on a plurality of data sources, wherein the period for extraction may differ depending on the identity of the data source and is performed in a manner that is defined by editable data;

an act of periodically performing a transformation operation on the extracted data, wherein the act of periodically performing the transformation operation is performed in a manner that is defined by the editable data, wherein the act of periodically performing a transformation operation for one iteration of the transformation operation comprises;

for at least some of the extracted data that are valid, an act of performing a transformation operation on the valid data;

for at least some of the extracted data that are dependent on other data that is not yet extracted, an act of partially performing a transformation operation on the extracted data, and an act of identifying the extracted data element as suspect and thus subject to further transformation in a subsequent iteration of the transformation operation; and

for at least some extracted data that had been subject to partial transformation in a prior interaction of the transformation operation, an act of completing transformation of that partially transformed data.