Modularized extraction, transformation, and loading for a database

• US 20050187974A1
• Filed: 02/20/2004
• Published: 08/25/2005
• Est. Priority Date: 02/20/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gong, Yu
• Granted Patent: US 8,311,974 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

Techniques exporting data and metadata from a source database environment to a target database environment are provided. The techniques include the steps of analyzing metadata that describes one or more items, the data for which is in a source database, where the one or more items include at least one of a view, a sequence, a dimension, a cube, an ETL mapping, and any database object for which the metadata is stored outside of the source and target databases. The data for each item resides in a data file associated with the source database. The data for each item is incorporated into the target database based on the metadata by providing the target database access to an incorporated data file, where the incorporated data file is the data file or a copy thereof. Techniques are also provided for exporting database data from the source database. The techniques include extracting metadata that describes one or more items, the data for which is in the source database, where the one or more items include at least one of the structures described above. The data for each item resides in a data file associated with the source database. After the exporting, a database server that manages the target database is provided access to an incorporated data file, where the incorporated data file is the data file or a copy thereof.

Claims

1. A method for loading database data into a target database, comprising the machine-implemented steps of:

analyzing metadata that describes one or more items, the data for which is in a source database, wherein the one or more items include at least one of

a view,

a sequence,

a dimension,

a cube,

an ETL mapping,

a queue,

an external table,

a stored procedure, and

a database object, wherein the metadata for the database object is stored outside of the source database and the target database;

wherein data for said one or more items resides in a data file associated with said source database;

incorporating the data for said one or more items into the target database by providing said target database access to an incorporated data file, wherein said incorporated data file is said data file or a copy thereof; and

accessing in the target database the data for said one or more items based on the metadata that defines said one or more items.

2. The method of claim 1, wherein said one or more items include one or more database views from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more database views.

3. The method of claim 1, wherein said one or more items include one or more database sequences from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more database sequences.

4. The method of claim 1, wherein said one or more items include one or more database dimensions from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more database dimensions.

5. The method of claim 1, wherein said one or more items include one or more database cubes from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more database cubes.

6. The method of claim 1, wherein said one or more items include one or more ETL mappings from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more ETL mappings.

7. The method of claim 1, wherein said one or more items include one or more queues from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more queues.

8. The method of claim 1, wherein said one or more items include one or more external tables from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more external tables.

9. The method of claim 1, wherein said one or more items include one or more stored procedures from the source database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more stored procedures.

10. The method of claim 1, wherein said one or more items include one or more database objects from the source database, wherein the metadata for the one or more database objects is stored outside of the source database and the target database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database comprises the step of analyzing metadata that describes said one or more database objects.

11. The method of claim 1, wherein the target database stores data in a data files that have a first format, the incorporated data file is in a second format that is different from the first format, and wherein a database server that manages the target database accesses the one or more items from said incorporated data file.

12. The method of claim 1, wherein a source database server manages data from said source database, a target database server manages data from said target database, and the step of analyzing metadata that describes one or more items, the data for which is in a source database is performed at least in part by a process that is separate from said source database server and said target database server.

13. The method of claim 1, wherein at least a portion of said metadata is read from a source repository separate from said source database.

14. The method of claim 13, wherein a source database server manages data from said source database, a target database server manages data from said target database, and said source repository is a repository associated with an application separate from both said source database server and said target database server.

15. The method of claim 14, wherein said application is an extraction, transformation and loading application.

16. The method of claim 1, the step of analyzing is performed by one or more processes and the method further comprises the step of the one or more processes obtaining over a network said metadata that describes said one or more items from said source database.

17. The method of claim 1, wherein the step of analyzing is performed by one or more processes and the method further comprises said one or more processes reading at least a portion of the metadata from a machine-readable medium while said machine-readable medium resides at a first location, wherein the metadata about said items was recorded to the machine-readable medium at a second location.

36. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

37. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

38. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

39. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

40. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

41. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

42. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

43. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

44. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

45. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

46. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 11.

47. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 12.

48. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 13.

49. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 14.

50. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 15.

51. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 16.

52. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 17.

18. A method for exporting database data from a source database, comprising the machine-implemented steps of:

extracting metadata that describes one or more items, the data for which is in the source database, wherein the one or more items include at least one of

a view,

a sequence,

a dimension,

a cube,

an ETL mapping,

a queue,

an external table,

a stored procedure, and

a database object, wherein the metadata for the database object is stored outside of the source database and a target database,

wherein data for said one or more items resides in a data file associated with said source database; and

providing to a database server that manages the target database access to an incorporated data file, wherein said incorporated data file is said data file or a copy thereof.

19. The method of claim 18, wherein said one or more items include one or more database views from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more database views.

20. The method of claim 18, wherein said one or more items include one or more database sequences from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more database sequences.

21. The method of claim 18, wherein said one or more items include one or more database dimensions from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more database dimensions.

22. The method of claim 18, wherein said one or more items include one or more database cubes from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more database cubes.

23. The method of claim 18, wherein said one or more items include one or more ETL mappings from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more ETL mappings.

24. The method of claim 18, wherein said one or more items include one or more queues from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more queues.

25. The method of claim 18, wherein said one or more items include one or more external tables from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more external tables.

26. The method of claim 18, wherein said one or more items include one or more stored procedures from the source database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more stored procedures.

27. The method of claim 18, wherein said one or more items include one or more database objects from the source database, wherein the metadata for the one or more database objects is stored outside of the source database and the target database, and the step of extracting metadata that describes one or more items, the data for which is in a source database comprises the step of extracting metadata that describes said one or more database objects.

28. The method of claim 18, wherein the target database stores data in a data files that have a first format, the incorporated data file is in a second format that is different from the first format, and wherein a database server that manages the target database accesses the one or more items from said incorporated data file.

29. The method of claim 18, wherein a source database server manages data from said source database, a target database server manages data from said target database, and the step of extracting metadata that describes one or more items, the data for which is in a source database is performed at least in part by a process that is separate from said source database server and said target database server.

30. The method of claim 18, wherein at least a portion of said metadata is read from a source repository separate from said source database.

31. The method of claim 30, wherein a source database server manages data from said source database, a target database server manages data from said target database, and said source repository is a repository associated with an application separate from both said source database server and said target database server.

32. The method of claim 31, wherein said application is an extraction, transformation and loading application.

33. The method of claim 18, the step of extracting is performed by one or more processes and the method further comprises the step of sending over a network said metadata that describes one or more items, the data for which is in a source database.

34. The method of claim 33, wherein the step of sending over a network further comprising the step of sending the metadata using the File Transfer Protocol.

35. The method of claim 18, wherein the step of extracting is performed by one or more processes and the method further comprises said one or more processes writing to a machine-readable medium said metadata that describes said one or more items from the source database.

53. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 18.

54. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 19.

55. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 20.

56. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 21.

57. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 22.

58. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 23.

59. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 24.

60. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 25.

61. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 26.

62. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 27.

63. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 28.

64. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 29.

65. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 30.

66. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 31.

67. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 32.

68. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 33.

69. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 34.

70. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 35.