TECHNIQUES FOR TRANSFORMING AND LOADING DATA INTO A FACT TABLE IN A DATA WAREHOUSE

• US 20090281985A1
• Filed: 05/07/2008
• Published: 11/12/2009
• 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
• Granted Patent: US 8,352,458 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 a plurality of dimension tables associated with a fact table in a data warehouse;

determining 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; and

providing 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.

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

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

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

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

removing 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.

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

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

removing 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 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.

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

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

7. The method of claim 1 further comprising:

sorting 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 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 a dimension table with the smallest number of records,

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

(iii) executing the SQL query;

repeating (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.

8. The method of claim 7 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.

9. The method of claim 1 further comprising:

creating 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.

10. The method of claim 9 further comprising:

using 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. A method of facilitating transformation of a set of records, the method comprising:

for a first set of dimension tables from a plurality of dimension tables associated with a fact table, providing a query for outer joining the dimension tables in the first set of dimension tables to a 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 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.

12. The method of claim 11 further comprising:

for at least one record in the staging table,

executing 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 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.

13. The method of claim 11 further comprising determining which one or more dimension tables from the plurality of dimension tables are included in the first set of dimension tables.