METHODS AND SYSTEMS FOR LOADING DATA INTO A TEMPORAL DATA WAREHOUSE

• US 20120150791A1
• Filed: 04/08/2011
• Published: 06/14/2012
• 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,688,622 B2
• Time in Patent Office: Days
• US Class Current: 707/600
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

A system disclosed includes a temporal data warehouse and a platform independent temporal data warehouse load application operable to run on the system. The load application uses timestamp data from incoming data in conjunction with 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 loads the identified and sequenced net changes into the data warehouse with relatively little intrusion into normal operation of the data warehouse. Optimizations, including but not limited to, distinct partitioning of the workload into parallel streams are selectable via metadata.

Claims

1. A system for use in loading an incoming data set into a temporal data warehouse, said system comprising:

a storage device including a temporal data warehouse and an incoming data set; and

a processor unit coupled to said storage device and programmed to;

divide the incoming data set into a plurality of partitions including a first partition and a second partition, wherein each partition of the plurality of partitions includes a plurality of data records;

import the first partition into a pre-load table;

import the second partition into the pre-load table; and

apply the pre-load table to the temporal data warehouse.

2. A system in accordance with claim 1, wherein said processor unit is programmed to divide the incoming data set into the plurality of partitions at least in part by applying a hash function to a primary key associated with at least one data record to produce a hash value corresponding to the at least one data record.

3. A system in accordance with claim 1, wherein said processor unit is further programmed to import the second partition into the pre-load table after the first partition is pre-loaded into the table.

4. A system in accordance with claim 1, wherein said processor unit is further programmed to import the second partition into the pre-load table while the first partition is being imported into the pre-load table.

5. A system in accordance with claim 4, wherein said processor unit is programmed to import the second partition into the pre-load table while the first partition is being imported into the pre-load table based on determining that a current quantity of parallel imports is less than a predetermined maximum quantity of parallel imports.

6. A system in accordance with claim 1, wherein said processor unit is programmed to import at least one of the first partition and the second partition at least in part by:

import of the data records of the partition into a volatile table corresponding to the partition; and

copy of the data records from the volatile table to the pre-load table.

7. A system in accordance with claim 1, wherein said processor is further programmed to:

identify data records in the first partition that include a plurality of fields other than a timestamp that are equal to non-key fields of a previously imported data record; and

exclude the identified data records when importing the first partition into the pre-load table.

8. A system in accordance with claim 1, wherein the incoming data includes a snapshot of data from a source database, and said processor is further programmed to:

detect that an active data record in a temporal data warehouse is not associated with a data record in the incoming data set; and

execute an implicit delete of the active data record based on said detecting.

9. A system in accordance with claim 8, wherein said processor is further programmed to:

determine an earliest source timestamp associated with a first data record in the incoming data set; and

identify a set of primary keys that represent;

a data record in the temporal data warehouse associated with a source timestamp immediately prior to the earliest source timestamp; and

one or more data records in the temporal data warehouse that are associated with a source timestamp later than the earliest source timestamp; and

import the first partition and the second partition based on the identified set of primary keys.

10. A method for use in loading a plurality of data records into a temporal data warehouse, said method comprising:

dividing, by a computing device, the data records into a plurality of partitions including a first partition and a second partition;

importing, by the computing device, the first partition into a pre-load table;

importing, by the computing device, the second partition into the pre-load table; and

applying, by the computing device, the pre-load table to the temporal data warehouse.

11. A method in accordance with claim 10, wherein the first partition and the second partition are imported in parallel.

12. A method in accordance with claim 10, further comprising determining that a current quantity of parallel imports is less than a predetermined maximum quantity of parallel imports, wherein the first partition and the second partition are imported in parallel based on said determining.

13. A method in accordance with claim 10, further comprising determining that the current quantity of parallel imports is greater than or equal to a predetermined maximum quantity of parallel imports, wherein the first partition and the second partition are imported sequentially based on said determining.

14. A method in accordance with claim 10, wherein dividing, by a computing device, the data into the plurality of partitions comprises:

applying a hash function to at least one data record to create a hash value associated with the at least one data record; and

applying a modulus operator to the hash value based on a predetermined quantity of partitions to determine a partition number corresponding to and associated with the at least one data record.

15. A method in accordance with claim 10, further comprising:

identifying the data records in the first partition that include a plurality of fields other than a timestamp that are equal to non-key fields of a previously imported data record; and

excluding the identified data records when importing the first partition into the pre-load table.

16. A computer program product comprising a non-transitory computer readable medium having embodied thereon computer-executable instructions for loading a data warehouse with net change data, wherein when executed by at least one processor, the computer-executable instructions cause the processor to:

divide an incoming data set into a plurality of partitions including a first partition and a second partition, wherein at least one partition of the plurality of partitions includes a plurality of data records;

import the first partition into a pre-load table;

import the second partition into the pre-load table; and

apply the pre-load table to the data warehouse.

17. A computer program product in accordance with claim 16, wherein the computer-executable instructions further cause the at least one processor to import the first partition and the second partition in parallel to each other.

18. A computer program product in accordance with claim 16, wherein the computer-executable instructions further cause the at least one processor to:

compare a current quantity of parallel imports to a predetermined maximum quantity of parallel imports;

when the current quantity is less than the maximum quantity of parallel imports, import the second partition in parallel with importing the first partition; and

when the current quantity is greater than or equal to the maximum quantity of parallel imports, import the second partition after import of the first partition.

19. A computer program product in accordance with claim 16, wherein the computer-executable instructions cause the at least one processor to import the first partition and the second partition at least in part by:

importing the data records of the first partition into a first volatile table with correspondence to the first partition;

importing the data records of the second partition into a second volatile table with correspondence to the second partition; and

copying the data records of the first volatile table and the second volatile table to the pre-load table.

20. A computer program product in accordance with claim 16, wherein the incoming data set includes a snapshot of data from a source database, and the computer-executable instructions further cause the at least one processor to:

detect an active data record in the data warehouse is not associated with a data record in the incoming data set; and

execute an implicit delete of the active data record responsive to said detection.