Method and system for metadata reconciliation in a data warehouse

• US 20050015377A1
• Filed: 05/18/2004
• Published: 01/20/2005
• Est. Priority Date: 11/12/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Wan, Winnie Tak Yu
• Granted Patent: US 7,853,554 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• 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

A data warehouse system comprising a warehouse database and a data warehouse manager communicatively coupled to the database. The warehouse manager is to store data to and retrieve data from the warehouse database and perform metadata reconciliation to update metadata of objects in the data warehouse to reflect a change in metadata of a first object in the warehouse by identifying a first set of objects impacted by the change to the metadata of the first object, identifying a second set of objects on which the first object depends, identifying at least one task that has to be completed to propagate the impact of the change in the metadata of the first object to objects in the first and second sets of objects, performing the task, and dynamically computing additional tasks based on whether or not definitions of objects in the first or second sets of objects changes based upon completion of the at least one task.

Claims

1. A data warehouse system comprising:

a warehouse database; and

a data warehouse manager communicatively coupled to the database and configured to (i) store data to and retrieve data from the warehouse database and (ii) perform metadata reconciliation to update metadata of objects in the data warehouse to reflect a change in metadata of a first object in the warehouse by;

identifying a first set of objects impacted by the change to the metadata of the first object;

identifying a second set of objects on which the first object depends;

identifying at least one task that has to be completed to propagate the impact of the change in the metadata of the first object to objects in the first and second sets of objects;

performing the task; and

dynamically computing additional tasks based on whether or not definitions of objects in the first or second sets of objects changes based upon completion of the at least one task.

2. The data warehouse system set forth in claim 1 further comprising a database server operatively coupled to process queries against the warehouse database.

3. The data warehouse system set forth in claim 1 further comprising a plurality of source databases, each communicatively coupled to the data warehouse manager;

wherein the data warehouse manager is further configured to perform transformations of data stored in the plurality of database sources and conforming to a schema of one of the plurality of source databases into an object that is stored in the warehouse database that conforms to a schema of for the warehouse database.

4. The data warehouse system set forth in claim 1 wherein the data warehouse manager is further configured to perform transformations of objects in source databases coupled to the data warehouse manager into objects stored in the warehouse database.

5. The data warehouse system set forth in claim 1 wherein the data warehouse manager is further configured to manage dependencies among objects stored in the data warehouse system.

6. The data warehouse system set forth in claim 1 wherein the data warehouse manager is configured to allow a user to select an object in the data warehouse system and preview a potential impact to the data warehouse by an impending change to the definition of the object.

7. The data warehouse system set forth in claim 1 wherein the data warehouse manager is configured to generate a script of all changes needed to the warehouse system in order to reconcile the change to the metadata of the first object.

8. The data warehouse system set forth in claim 7 wherein the data warehouse manager is further configured to execute the script in response to a command from a user of the system.

9. The data warehouse system set forth in claim 1 wherein the data warehouse manager is configured to allow metadata of an object to be edited as a task that propagates the change to the metadata of the first object to other objects in the system.

10. A method for managing a data warehouse, the method comprising:

allowing a user to select an object in the data warehouse and identify a change in the definition of the selected object;

identifying a first set of objects impacted by the change to the definition of the selected object;

identifying a second set of objects on which the selected object depends;

identifying at least one task that has to be completed to propagate the impact of the change in the definition of the selected object to objects in the first and second sets of objects;

performing the task; and

dynamically computing additional tasks based on whether or not definitions of objects in the first or second sets of objects changes based upon completion of the at least one task.

11. The method of claim 10 further comprising generating a script of all changes needed to the warehouse system in order to reconcile the change to the metadata of the first object.

12. The method of claim 11 further comprising executing the script in response to a command from a user of the system.

13. The method of claim 10 further comprising displaying a graph of the first set of objects that visually depicts relationships between the selected object and objects in the first set.

14. The method of claim 13 further comprising displaying a graph of the second set of objects that visually depicts relationships between the:

selected object and objects in the second set.

15. The method of claim 10 wherein the step of identifying at least one task is part of a recursive algorithm that, for each object in the first set of objects, includes a lineage analysis within an impact analysis.

16. A computer program product for managing a data warehouse, the computer program product being embodied in a computer readable medium and comprising instructions executable by a computer to:

allow a user to select an object in the data warehouse and identify a change in the definition of the selected object;

identify a first set of objects impacted by the change to the definition of the selected object;

identify a second set of objects on which the selected object depends;

identify at least one task that has to be completed to propagate the impact of the change in the definition of the selected object to objects in the first and second sets of objects;

perform the task; and

dynamical computing additional tasks based on whether or not definitions of objects in the first or second sets of objects changes based upon completion of the at least one task.

17. The computer program product of claim 16 wherein the computer program product embodied in a computer readable medium further comprises instructions executable by a computer to generate a script of all changes needed to the warehouse system in order to reconcile the change to the metadata of the first object.

18. The computer program product of claim 17 wherein the computer program product embodied in a computer readable medium further comprises instructions executable by a computer to execute the script in response to a command from a user of the system.

19. A data system comprising:

a database; and

a data manager communicatively coupled to the database and configured to (i) store data to and retrieve data from the database and (ii) perform metadata reconciliation to update metadata of objects in the database to reflect a change in metadata of a first object in the database by;

identifying a first set of objects impacted by the change to the metadata of the first object;

identifying a second set of objects on which the first object depends;

identifying at least one task that has to be completed to propagate the impact of the change in the metadata of the first object to objects in the first and second sets of objects;

performing the task; and

dynamically computing additional tasks based on whether or not definitions of objects in the first or second sets of objects changes based upon completion of the at least one task.

21. The data system set forth in claim 22 wherein the data manager is configured to generate a script of all changes needed to the data system in order to reconcile the change to the metadata of the first object.

20. The data system set forth in claim 21 further comprising a database server operatively coupled to process queries against the database.

23. The data system set forth in claim 22 wherein the data manager is further configured to execute the script in response to a command from a user of the system.