Granted Patent B2

US 8,429,117 · App. 13/026,315 · Granted Apr 23, 2013

Abstract

Claims (39)

1. A computer-implemented method of data loading in a data warehouse, comprising:

generating two types of work tables comprising a first work table and a second work table, wherein the second work table is a temporary table for data preparation;

loading data into the first work table;

preparing the data in the first work table for loading into the data warehouse, wherein preparing the data includes:

inserting data from the first work table into the second work table;

updating legacy columns of the second work table using business key column values;

deleting duplicates within the second work table using a business key column in the second work table;

updating a surrogate key column of the second work table using business key columns in the data warehouse and the second work table; and

updating foreign key reference column of the first work table using the updated surrogate key of the second work table; and

loading the prepared data in the data warehouse.

2. The method according to claim 1 , wherein the first work table is a work table created for a base table.

3. The method according to claim 2 , wherein the second work table is a work table created for a table linked to the base table.

4. The method according to claim 3 , wherein at least one second work table is created for each table linked to the base table.

5. The method according to claim 1 , wherein preparing the data in the first work table further comprises: rounding off of time columns and populating default values for columns.

6. The method according to claim 1 , further comprising, prior to generating two types of work tables, defining a data load rule in an XML (Extensible Markup Language) file, wherein the XML file includes three sections: a table section, a references section and a column map section.

7. The method according to claim 6 , wherein the first table includes all columns of all tables included in the table section in the XML file.

8. A system, comprising:

a processor and a memory;

a data loader module, implemented by the processor, which:

generates two types of work tables comprising a first work table and a second work table, wherein the second work table is a temporary table for data preparation;

loads data into the first work table;

prepares the data in the first work table for loading into a data warehouse stored in the memory, wherein to prepare the data, the processor performs:

inserting data from the first work table into the second work table;

updating legacy columns of the second work table using business key column values;

deleting duplicates within the second work table using a business key column in the second work table;

updating a surrogate key column of the second work table using business key columns in the data warehouse and the second work table; and

updating foreign key reference column of the first work table using the updated surrogate key of the second work table; and

loads the prepared data in the data warehouse.

9. The system according to claim 8 , wherein the first work table is a work table created for a base table and the second work table is a work table created for a table linked to the base table.

10. A non-transitory machine-readable medium comprising instructions to implement a method of loading data into a data warehouse, wherein the instructions, when executed by a processor, cause the processor to perform:

generating two types of work tables comprising a first work table and a second work table, wherein the second work table is a temporary table for data preparation;

loading data into the first work table;

preparing the data in the first work table for loading into a data warehouse stored in the memory, wherein to prepare the data, the processor performs:

inserting data from the first work table into the second work table;

updating legacy columns of the second work table using business key column values;

deleting duplicates within the second work table using a business key column in the second work table;

updating a surrogate key column of the second work table using business key columns in the data warehouse and the second work table; and

updating foreign key reference column of the first work table using the updated surrogate key of the second work table; and

loading the prepared data in the data warehouse.

Assignments (13)