EFFICIENTLY UPDATING ROWS IN A DATA WAREHOUSE

• US 20120011096A1
• Filed: 07/08/2010
• Published: 01/12/2012
• Est. Priority Date: 07/08/2010
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Aggarwal, Nitin
• Granted Patent: US 8,868,484 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

An aspect of the present invention facilitates efficient updating of rows in a data warehouse. In one embodiment, a sequence of identifiers is allocated to rows of data elements received earlier, and then inserted along with the received data elements in a table according to the defined by the sequence of identifiers. On receiving data elements as updates to the rows having associated identifiers, the received data elements are sorted according to the order of the associated identifiers and updates of the rows are performed successively in the sorted order of identifiers. According to another aspect, the above features are performed when (target) fact/dimension tables in the data warehouse are sought to be updated based on transformations/computations performed on data in other (source) fact/dimension tables.

Claims

1. A method of updating rows in a data warehouse, said method comprising:

receiving a first plurality of data elements suitable for storing in the form of a plurality of rows of a target table in said data warehouse;

allocating respective ones of a sequence of identifiers to each of said plurality of rows;

inserting in said target table said plurality of rows, including the allocated identifier, according to an order defined based on said sequence of identifiers;

receiving a second plurality of data elements as updates to respective rows, each of said second plurality of data elements being associated with one of said identifiers;

sorting said second plurality of data elements according to said order of the associated identifiers; and

updating successively said plurality of rows of said target table with said second plurality of data elements sorted in said order.

2. The method of claim 1, wherein said sequence of identifiers comprises values of a primary key of said target table.

3. The method of claim 2, further comprising:

retrieving a second plurality of rows from a source table, wherein each of said second plurality of rows includes an associated identifier as a foreign key matching said primary key; and

computing said second plurality of data elements based on the data in the second plurality of rows.

4. The method of claim 3, wherein said source table is a fact table and said target table is a dimension table,

and wherein said computing aggregates at least some of the data elements in said fact table to generate said second plurality of data elements,

whereby said updating updates the data elements of a column in said dimension table based on data in said fact table.

5. The method of claim 4, wherein said retrieving, said computing and said sorting are performed by executing an Structured Query Language (SQL) query.

6. The method of claim 2, further comprising:

retrieving a second plurality of rows from a source table which does not have a foreign key matching said primary key;

performing a join of said second plurality of rows with corresponding rows of said target table to collate the corresponding identifiers to respective ones of said second plurality of rows, wherein said join is performed on a common column present in both said source table and said target table; and

computing said second plurality of data elements based on the data in the second plurality of rows.

7. The method of claim 6, wherein said source table is a first fact table and said target table is a second fact table.

8. The method of claim 7, wherein said retrieving, said performing said join, said computing and said sorting are performed by executing an Structured Query Language (SQL) query.

9. The method of claim 1, wherein said second plurality of data elements are received after said inserting of said first plurality of data elements.

10. A machine readable medium storing one or more sequences of instructions for causing a system to update rows in a data warehouse, wherein execution of said one or more sequences of instructions by one or more processors contained in said system causes said system to perform the actions of:

receiving a first plurality of data elements suitable for storing in the form of a plurality of rows of a target table in said data warehouse;

allocating respective ones of a sequence of identifiers to each of said plurality of rows;

inserting in said target table said plurality of rows, including the allocated identifier, according to an order defined based on said sequence of identifiers;

receiving a second plurality of data elements as updates to respective rows, each of said second plurality of data elements being associated with one of said identifiers;

sorting said second plurality of data elements according to said order of the associated identifiers; and

updating successively said plurality of rows of said target table with said second plurality of data elements sorted in said order.

11. The machine readable medium of claim 10, wherein said sequence of identifiers comprises values of a primary key of said target table.

12. The machine readable medium of claim 11, further comprising one or more instructions for:

retrieving a second plurality of rows from a source table, wherein each of said second plurality of rows includes an associated identifier as a foreign key matching said primary key; and

computing said second plurality of data elements based on the data in the second plurality of rows.

13. The machine readable medium of claim 12, wherein said source table is a fact table and said target table is a dimension table,

and wherein said computing aggregates at least some of the data elements in said fact table to generate said second plurality of data elements,

whereby said updating updates the data elements of a column in said dimension table based on data in said fact table.

14. The machine readable medium of claim 11, further comprising one or more instructions for:

retrieving a second plurality of rows from a source table which does not have a foreign key matching said primary key;

performing a join of said second plurality of rows with corresponding rows of said target table to collate the corresponding identifiers to respective ones of said second plurality of rows, wherein said join is performed on a common column present in both said source table and said target table; and

computing said second plurality of data elements based on the data in the second plurality of rows.

15. The machine readable medium of claim 14, wherein said source table is a first fact table and said target table is a second fact table.

16. A data warehouse comprising:

a processor;

a random access memory (RAM);

a non-volatile storage to store a plurality of tables, said plurality of tables comprising a target table storing a first plurality of data elements in the form of a plurality of rows, wherein each of said plurality of rows is stored including a corresponding identifier allocated from a sequence of identifiers, wherein said plurality of rows is stored according to an order defined based on said sequence of identifiers;

a machine readable medium to store a first set of instructions which when retrieved into said RAM and executed by said processor forms an extract-transform-load (ETL) tool, said ETL tool designed to performs the actions of;

receiving a second plurality of data elements as updates to respective rows of said target table, each of said second plurality of data elements being associated with one of said identifiers;

sorting said second plurality of data elements according to said order of the associated identifiers; and

updating successively said plurality of rows of said target table with said second plurality of data elements sorted in said order.

17. The data warehouse of claim 16, wherein said target table is a dimension table and said sequence of identifiers comprises values of a primary key of said dimension table, wherein said ETL tool further performs the actions of:

retrieving a second plurality of rows from a fact table, wherein each of said second plurality of rows includes an associated identifier as a foreign key matching said primary key, said plurality of tables comprising said fact table; and

computing aggregates of at least some of the data elements in said second plurality of rows to generate said second plurality of data elements,

wherein said updating updates the data elements of a column in said dimension table based on data in said fact table.

18. The data warehouse of claim 17, wherein said retrieving, said computing and said sorting are performed by executing an Structured Query Language (SQL) query.

19. The data warehouse of claim 16, wherein said target table is a first fact table and said sequence of identifiers comprises values of a primary key of said first fact table, wherein said ETL tool further performs the actions of:

retrieving a second plurality of rows from a second fact table which does not have a foreign key matching said primary key, said plurality of tables comprising said second fact table;

performing a join of said second plurality of rows with corresponding rows of said first fact table to collate the corresponding identifiers to respective ones of said second plurality of rows, wherein said join is performed on a common column present in both said first fact table and said second table; and

computing said second plurality of data elements based on the data in the second plurality of rows.

20. The data warehouse of claim 19, wherein said retrieving, said performing said join, said computing and said sorting are performed by executing an Structured Query Language (SQL) query.