Extract, transform, and load application complexity management framework

• US 8,688,625 B1
• Filed: 12/30/2011
• Issued: 04/01/2014
• Est. Priority Date: 12/31/2010
• Status: Active Grant

• Current Assignee: United Services Automobile Association
• Original Assignee: United Services Automobile Association
• Inventors: Clark, Larry W., Hendry, Jason P., Steen, Mark
• Primary Examiner(s): Vy, Hung T
• Application Number:
  US13/341,433
• Time in Patent Office: 823 Days
• Field of Search: 707/602, 707/607, 717/104
• US Class Current: 707/602
• CPC Class Codes: G06F 11/1446 Point-in-time backing up or restoration of persistent data
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2474 Sequence data queries, e.g. querying versioned data
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06Q 10/00 Administration; Management

Abstract

Extract, transform, and load application (ETL) complexity management framework systems and methods are described herein. The present disclosure describes systems and methods that reduce the complexity in managing ETL flow and correcting errant data that is subsequently identified. One or more methods include defining an ETL job definition, defining a data asset definition, defining a data asset dependency definition, receiving an ETL flow to provide execution of one or more ETL flow steps, providing retrieval of data from a source data asset, applying a data control to the source asset data, and producing an ETL job registration, a data asset status, a latest asset available date, a data asset consumer identifier, and a target data asset based on at least one of the ETL job definition, the data asset definition, the data dependency definition, and the source asset data.

Claims

1. A computer-implemented method of managing one or more processes providing extract, transform, and load (ETL) application complexity management comprising:

defining an ETL job definition;

defining a data asset definition;

defining a data asset dependency definition;

receiving an ETL flow to provide execution of one or more ETL flow steps;

retrieving data from a source data asset;

applying a data control to the source asset data;

producing an ETL job registration and a data asset consumer identifier, based on at least one of the ETL job definition, the data asset definition, the data asset dependency definition, and the source asset data, wherein the data asset consumer identifier includes a roll back plan to remove the effect of one or more errant source data assets; and

updating a data asset status, a latest asset available date, and a target data asset; and

recognizing when it is a necessary to restart and re-initialize target data assets based on the execution of the ETL flow steps.

2. The method of claim 1, wherein applying the data control causes at least one of a file identity check, an audit count check, a threshold data check, and automatic archiving of source data.

3. The method of claim 1, wherein the method includes storing data asset consumer information about particular ETL job runs in an electronic storage media.

4. The method of claim 1, wherein executing at least two ETL flow steps is accomplished in parallel.

5. The method of claim 1, wherein executing the ETL flow steps is accomplished in parallel and is synchronized based on ETL flow scripts.

6. A non-transitory machine readable medium having a set of machine readable instructions stored thereon for execution by a processor to cause a machine to perform a method of managing one or more processes providing extract, transform, and load (ETL) application complexity management comprising:

receiving one or more data asset incidents associated with a particular data asset, one or more data asset consumers associated with the particular data asset, and one or more data asset statuses associated with the particular data asset;

producing a roll back plan to remove an effect of one or more errant source data assets from one or more target data asset consumers, wherein the removal of the effects of errant data recalculates totals and counts within one or more data sets;

executing the roll back plan and a roll forward plan; and

replacing one or more subsets of errant target data assets, each containing at least one data item, with corrected data.

7. The method of claim 6, wherein the roll back plan is reviewed prior to execution.

8. The method of claim 6, wherein the method includes replacing one or more subsets of target data assets, each containing at least one data item, with corrected data removes the effects of errant data.

9. The method of claim 6, wherein the removal of the effects of errant data is accomplished by providing corrected data to the target data asset to replace a subset of data in one or more consuming target data assets.

10. The method of claim 9, wherein providing corrected data to the target data asset to replace a subset of data in one or more consuming target data assets is accomplished by the recalculating totals and counts within one or more data sets.

11. The method of claim 9, wherein providing corrected data to the target data asset to replace a subset of data in one or more consuming target data assets is accomplished by inserting the corrected data into one or more consuming target data assets.

12. The method of claim 11, wherein the consuming target assets are consumed by one or more subsequent consuming target data assets.

13. The method of claim 12, wherein providing corrected data to the target data asset to replace a subset of data in one or more consuming target data assets is accomplished by inserting the corrected data into one or more consuming target data assets corrects all subsequent consuming target assets.