METHODS AND SYSTEMS FOR METADATA DRIVEN DATA CAPTURE FOR A TEMPORAL DATA WAREHOUSE

• US 20090299987A1
• Filed: 10/22/2008
• Published: 12/03/2009
• Est. Priority Date: 06/02/2008
• Status: Active Grant

• Current Assignee: The Boeing Co.
• Original Assignee: The Boeing Co.
• Inventors: Willson, Ian Alexander
• Granted Patent: US 8,271,430 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

A system is described that includes a data warehouse and a platform independent data warehouse load application operable to run on the system. The load application includes a sequencing unit configured to utilize timestamp data from incoming data to be stored in the data warehouse and a relational algebra of set operators to identify and sequence net changes between the incoming data and data previously stored within the data warehouse. The load application is configured to non-intrusively load the identified and sequenced net changes into the data warehouse.

Claims

1. A system comprising:

a data warehouse; and

a platform independent data warehouse load application operable to run on said system, said load application comprising a sequencing unit configured to utilize timestamp data from incoming data to be stored in said data warehouse and a relational algebra of set operators to identify and sequence net changes between the incoming data and data previously stored within said data warehouse, said load application configured to non-intrusively load the identified and sequenced net changes into said data warehouse.

2. The system of claim 1 wherein said load application further comprises a compression unit configured to compress contiguous data into a minimum number of time periods.

3. The system of claim 1 wherein said load application further comprises:

an autocoder unit configured to dynamically generate code operable to perform data warehouse insertions and updates; and

an execution unit to execute generated code.

4. The system of claim 3 wherein said autocoder unit is configured to dynamically generate ANSI-complaint SQL code based upon a table definition and a primary key using only ANSI-compliant SQL.

5. The system of claim 1 wherein to identify and sequence net changes between the incoming data and data previously stored within said data warehouse, said load application is configured to analyzing a set of incoming data with respect to itself and existing data within said data warehouse.

6. The system of claim 1 wherein said load application is configured to generate and maintain nanosecond level sequences within unique timestamps to ensure that all candidate rows with unique non-key attribution are loaded into said data warehouse.

7. The system of claim 1 wherein said load application is configured to utilize only set-structured query language to analyze interactions within and between incoming data and data existing within said data warehouse.

8. The system of claim 1 wherein said load application is configured to operate in a mini-batch mode, the mini batch mode invoked serially with respect to a target table as needed to support multiple data latencies and process with super-linear scalability a variable quantity of input data.

9. The system of claim 1 wherein said system comprises a massively parallel processing (MPP) architecture, said system is configured to leverage said MPP architecture and provide super-linear scalability utilizing said architecture.

10. The system of claim 1 wherein to identify and sequence net changes between the incoming data and data previously stored within said data warehouse, said sequencing unit is configured to query against a database catalog associated with the data warehouse, at run-time, for column name, data type information, and a primary key metadata table.

11. A method of loading a data warehouse, said method comprising:

analyzing a set of incoming data with respect to itself and an existing data warehouse;

identifying and sequencing any net change data between the incoming data and the existing data warehouse using a relational algebra set of operators;

normalizing said net changes with respect to a primary key within a table and a time period that varies with the sequences of rows within the primary key; and

applying any net change data as an update to the data warehouse.

12. The method of claim 11 further comprising:

dynamically generating code to apply the net change data to the data warehouse; and

executing the generated code to insert the net change data into the data warehouse.

13. The method of claim 12 wherein dynamically generating code comprises dynamically generating ANSI-complaint SQL code based upon a table definition and a primary key using only ANSI-compliant SQL.

14. The method of claim 11 wherein applying any net change data as an update to the data warehouse comprises compressing contiguous net change data into a minimum number of time periods.

15. The method of claim 11 wherein applying any net change data as an update to the data warehouse comprises generating and maintaining nanosecond level sequences within unique timestamps to ensure that all candidate rows of net change data with unique non-key attribution are loaded into the data warehouse.

16. The method of claim 11 wherein identifying and sequencing any net change data comprises utilizing only set-structured query language to analyze interactions within and between the set of incoming data and data existing within the data warehouse.

17. The method of claim 11 wherein applying any net change data comprises non-intrusively loading the net change data into the data warehouse thereby allowing continual query access.

18. The method of claim 11 wherein identifying and sequencing any net change data between the incoming data and the existing data warehouse comprises querying against a database catalog associated with the data warehouse, at run-time, for column name, data type information, and a primary key metadata table.

19. A computer program embodied on a computer readable medium for loading a data warehouse with net change data, said program comprising a code segment that:

utilizes an autocoder to dynamically generate code to analyze a set of incoming data with respect to itself and an existing data warehouse;

identifies and sequences net changes between the incoming data and data previously stored within the data warehouse; and

loads the identified and sequenced net changes into the data warehouse.

20. A computer program according to claim 19 further comprising a code segment that dynamically generates ANSI-complaint SQL code based upon a table definition and a primary key using only ANSI-compliant SQL, the generated code applying the net change data to the data warehouse.

21. A computer program according to claim 19 further comprising a code segment that utilizing only set-structured query language to analyze interactions within and between the set of incoming data and data existing within the data warehouse.