Techniques for transforming and loading data into a fact table in a data warehouse

• US 8,352,458 B2
• Filed: 05/07/2008
• Issued: 01/08/2013
• Est. Priority Date: 05/07/2008
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Aggarwal, Nitin
• Primary Examiner(s): BROWN, SHEREE N
• Application Number:
  US12/116,693
• Publication Number:
  US 20090281985A1
• Time in Patent Office: 1,707 Days
• Field of Search: 707 1- 3, 707100-102, 707/616, 715/523, 715/248
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Techniques for transforming records prior to loading the records into a data warehouse in an efficient manner. In one embodiment, instead of using lookup transformations, a database operation such as an outer join operation is used to transform records stored in a fact staging table to add foreign keys to the records corresponding to the dimension tables that are associated with a fact table in a data warehouse to which the records are to be loaded. Since the outer join operation is memory intensive, a determination is made as to which dimension tables from the multiple dimension tables associated with the fact table are suitable, from a performance perspective, for the outer join operation. Only those suitable dimension tables are outer joined to the fact staging table to add the foreign keys for the outer joined dimension tables to the fact staging table records. Lookup transformations may be used to add foreign keys to the fact staging table records for the non-suitable dimension tables. The transformed fact records with the foreign keys may then be loaded into the fact table in the data warehouse.

Claims

1. A method of facilitating transformation of a set of records, the method comprising:

determining, by a computer system, a plurality of dimension tables associated with a fact table in a data warehouse;

determining, by the computer system, a set of one or more dimension tables from the plurality of dimension tables for which an outer join operation is to be used for adding foreign keys corresponding to the dimension tables to a plurality of records stored in a staging table prior to loading of the records in the fact table in the data warehouse,

wherein determining the set of dimension tables comprises;

executing, by the computer system, a query in which a first dimension table from the plurality of dimension tables is outer joined with the staging table, and

determining, by the computer system, if the first dimension table belongs to the set of dimension tables based upon information determined from execution of the query,

wherein the information comprises an indication of whether at least a portion of the staging table was written to disk in response to execution of the query;

providing, by the computer system, a query for outer joining the dimension tables in the set of dimension tables to the staging table, wherein execution of the query causes, for each dimension table in the set of dimension tables, one or more foreign keys for the dimension table to be added to the records of the staging table; and

creating, by the computer system, one or more lookup transformations for each dimension table in the plurality of dimension tables that is not included in the set of dimension tables, wherein a lookup transformation for a dimension table is usable for adding one or more foreign keys corresponding to the dimension table to the records in the staging table.

2. The method of claim 1 wherein determining the set of dimension tables further comprises:

removing, by the computer system, the first dimension table from the query and not including the first dimension table in the set of dimension tables if the information determined from execution of the query indicates that a memory is not sufficient to store a hash table for the records of all the dimension tables outer joined in the query.

3. The method of claim 1 wherein determining if the first dimension table belongs to the set of dimension tables comprises:

determining, by the computer system, if the information determined from execution of the query indicates a wait event over the staging table; and

removing, by the computer system, the first dimension table from the query upon determining that the information determined from execution of the query indicates a wait event over the staging table; and

updating, by the computer system, the query by adding a second dimension table from the plurality of dimension tables to the query upon determining that the information determined from execution of the query does not indicate a wait event over the staging table, the second dimension table storing more records that the first dimension table, wherein the second dimension table is outer joined to the staging table.

4. The method of claim 1 further comprising executing the query.

5. The method of claim 1 wherein the provided query is a SQL query.

6. The method of claim 1 further comprising:

sorting, by the computer system, the dimension tables in the plurality of dimension tables to generate a sorted list, the dimension tables sorted based upon a number of records stored by each dimension table in the plurality of dimension tables;

creating, by the computer system, a SQL query;

(i) from the one or more dimension tables in the plurality of dimension tables that have not been added to the SQL query, selecting, by the computer system, a dimension table with the smallest number of records,

(ii) adding, by the computer system, the selected dimension table to the SQL query such that the staging table is outer joined with the selected dimension table;

(iii) executing, by the computer system, the SQL query;

repeating, by the computer system, (i), (ii), and (iii) until information determined from execution of the SQL query indicates a wait event over the staging table;

wherein the providing comprises providing the SQL query with the last dimension table added to the SQL query removed from the SQL query.

7. The method of claim 6 wherein the adding comprises ensuring that the outer join of the staging table and the selected dimension table is implemented using a HASH JOIN RIGHT OUTER operation.

8. The method of claim 1 further comprising:

using, by the computer system, the one or more lookup transformations to add one or more foreign keys to the staging table records for the one or more dimension tables from the plurality of dimension tables not included in the set of dimension tables.

11. The method of claim 1, wherein the first dimension table belongs to the set of dimension tables when the information determined from execution of the query indicates that memory is sufficient to store a hash table for the records of the dimension tables outer joined in the query.

12. The method of claim 11, wherein the information determined from execution of the query indicates that a memory is sufficient to store a hash table when a trace file generated by the query does not include a wait event associated with the staging table.

13. The method of claim 12, wherein the wait event comprises a database file scattered read, a direct path read, a direct path write, or a combination thereof, over the staging table.

9. A method of facilitating transformation of a set of records, the method comprising:

determining, by a computer system, which one or more dimension tables from a plurality of dimension tables are included in a first set of dimension tables;

wherein determining comprises;

executing, by the computer system, a query in which a first dimension table from the plurality of dimension tables is outer joined with a staging table, and

determining, by the computer system, if the first dimension table belongs to the first set of dimension tables based upon information determined from execution of the query,

wherein the information comprises an indication of whether at least a portion of the staging table was written to disk in response to execution of the query; and

for the first set of dimension tables from the plurality of dimension tables associated with a fact table, providing, by the computer system, a query for outer joining the dimension tables in the first set of dimension tables to the staging table, wherein execution of the query causes, for each dimension table in the first set of dimension tables, one or more foreign keys for the dimension table to be added to records of the staging table; and

for a second set of dimension tables comprising one or more dimension tables from the plurality of dimension tables associated with the fact table that are not included in the first set of dimension tables, providing, by the computer system, one or more lookup transformations, wherein the lookup transformations are usable for adding one or more foreign keys corresponding to the dimension tables in the second set of dimension tables to the records of the staging table; and

creating, by the computer system, one or more lookup transformations for each dimension table in the plurality of dimension tables associated with the fact table that is not included in the first set of dimension tables, wherein a lookup transformation for a dimension table is usable for adding one or more foreign keys corresponding to the dimension table to the records in the staging table.

10. The method of claim 9 further comprising:

for at least one record in the staging table,

executing, by the computer system, the query to add a first set of foreign keys corresponding to the first set of dimension tables to the at least one record, and

using, by the computer system, a lookup transformation from the one or more lookup transformations to add a second set of foreign keys corresponding to the second set of dimension tables to the at least one record.

14. A system for facilitating transformation of a set of records, the system comprising:

a processor configured to;

determine a plurality of dimension tables associated with a fact table in a data warehouse;

determine a set of one or more dimension tables from the plurality of dimension tables for which an outer join operation is to be used for adding foreign keys corresponding to the dimension tables to a plurality of records stored in a staging table prior to loading of the records in the fact table in the data warehouse,

wherein to determine the set of dimension tables, the processor is configured to;

execute a query in which a first dimension table from the plurality of dimension tables is outer joined with the staging table, and

determine if the first dimension table belongs to the set of dimension tables based upon information determined from execution of the query,

wherein to determine if the first dimension table belongs to the set of dimension tables, the processor is configured to determine if the information determined from execution of the query indicates a wait event over the staging table;

provide a query for outer joining the dimension tables in the set of dimension tables to the staging table, wherein execution of the query causes, for each dimension table in the set of dimension tables, one or more foreign keys for the dimension table to be added to the records of the staging table; and

create one or more lookup transformations for each dimension table in the plurality of dimension tables that is not included in the set of dimension tables, wherein a lookup transformation for a dimension table is usable for adding one or more foreign keys corresponding to the dimension table to the records in the staging table.

15. The system of claim 14 wherein to determine the set of dimension tables the processor is further configured to:

remove the first dimension table from the query and not include the first dimension table in the set of dimension tables if the information determined from execution of the query indicates that a memory is not sufficient to store a hash table for the records of all the dimension tables outer joined in the query.

16. The system of claim 14 wherein to determine if the first dimension table belongs to the set of dimension tables the processor is further configured to:

remove the first dimension table from the query upon determining that the information determined from execution of the query indicates a wait event over the staging table; and

update the query by adding a second dimension table from the plurality of dimension tables to the query upon determining that the information determined from execution of the query does not indicate a wait event over the staging table, the second dimension table storing more records that the first dimension table, wherein the second dimension table is outer joined to the staging table.

17. The system of claim 14 wherein the processor is further configured to execute the query.

18. The system of claim 14 wherein the processor is further configured to:

sort the dimension tables in the plurality of dimension tables to generate a sorted list, the dimension tables sorted based upon a number of records stored by each dimension table in the plurality of dimension tables;

create a SQL query;

(i) from the one or more dimension tables in the plurality of dimension tables that have not been added to the SQL query, select a dimension table with the smallest number of records,

(ii) add the selected dimension table to the SQL query such that the staging table is outer joined with the selected dimension table;

(iii) execute the SQL query;

repeat (i), (ii), and (iii) until information determined from execution of the SQL query indicates a wait event over the staging table;

wherein to provide the query the processor is further configured to remove from the SQL query the last dimension table added.