MECHANISM FOR SYNCHRONIZING OLAP SYSTEM STRUCTURE AND OLTP SYSTEM STRUCTURE

• US 20150227597A1
• Filed: 02/16/2015
• Published: 08/13/2015
• Est. Priority Date: 06/15/2009
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ng, Rondy, Zwiebach, Robert C., Haimes, David, Siauw, Djiao Mei, Banerjee, Arunesh, Matam, Santosh Kumar, Kosuri, Venkata Ramana Murthy, Roth, Gregory David
• Granted Patent: US 10,061,827 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/275 Synchronous replication
• CPC Class Codes: G06F 16/907 Retrieval characterised by using metadata, e.g. metadata not derived from the content or metadata generated manually
• CPC Class Codes: G06F 16/9537 Spatial or temporal dependent retrieval, e.g. spatiotemporal queries

Abstract

In an embodiment, for any structural change that is made to a relational database, a corresponding change is also made to the multidimensional database. In an embodiment, the system determines that a particular structure is not implemented on the multidimensional database system. Based on metadata that defines the structure that is supposed to exist in the multidimensional database, the appropriate structure is created.

Claims

1. A system for synchronizing a two-dimensional database and a multidimensional database, the system comprising:

a metadata repository configured to store structure metadata, the structure metadata defining one or more structures for storing data in a two-dimensional database and a multidimensional database having more than two dimensions;

a processing unit comprising one or more processors; and

a database synchronization component comprising computer-readable non-transitory storage medium storing a plurality of instructions which, when executed by the processing unit, causes the processing unit to synchronize the two-dimensional database and the multidimensional database by;

determining that new data has been posted into a first cell in the two-dimensional database;

retrieving one or more records from the structure metadata of the metadata repository, the one or more records containing at least;

(a) a field identifying the first cell of the two-dimensional database; and

(b) a plurality of fields comprising indices corresponding to separate dimensions of the multidimensional database, at the intersection of which a second cell of the multidimensional database is located;

determining, based on the records retrieved from the structure metadata, that the first cell in the two-dimensional database is mapped to the second cell in the multidimensional database; and

copying the new data from the first cell in the two-dimensional database to the second cell in the multidimensional database.

2. The system of claim 1, the database synchronization component comprising further computer-readable non-transitory storage medium storing a plurality of instructions which, when executed by the processing unit, causes the processing unit to synchronize the two-dimensional database and the multidimensional database by:

identifying a particular structure within the multidimensional database that contains the second cell.

3. The system of claim 1, wherein within the structure metadata of the metadata repository, a plurality of cells in the two-dimensional database are not mapped to any cells in the multidimensional database.

4. The system of claim 1, the database synchronization component comprising further computer-readable non-transitory storage medium storing a plurality of instructions which, when executed by the processing unit, causes the processing unit to synchronize the two-dimensional database and the multidimensional database by:

after determining that new data has been posted into the first cell in the two-dimensional database, determining that no structure is implemented within the multidimensional database that contains a cell corresponding to the first cell; and

generating a new particular structure within the multidimensional database, the new particular structure containing the second cell corresponding to the first cell in the two-dimensional database.

5. The system of claim 4, the database synchronization component comprising further computer-readable non-transitory storage medium storing a plurality of instructions which, when executed by the processing unit, causes the processing unit to synchronize the two-dimensional database and the multidimensional database by:

updating the structure metadata of the metadata repository to include the new particular structure generated within the multidimensional database.

6. The system of claim 1, wherein determining that new data has been posted into the first cell in the two-dimensional database comprises:

receiving a first request to store the new data in the two-dimensional database, the first request including a requirement that the new data also be stored in the multidimensional database.

7. The system of claim 1, the database synchronization component comprising further computer-readable non-transitory storage medium storing a plurality of instructions which, when executed by the processing unit, causes the processing unit to synchronize the two-dimensional database and the multidimensional database by:

executing a structural integrity operation in accordance with a schedule, wherein the structural integrity operation performs at least a first comparison between the multidimensional database and the structure metadata, and a second comparison between the two-dimensional database and the structure metadata.

8. A method of synchronizing a two-dimensional database and a multidimensional database, the method comprising

determining that new data has been posted into a first cell in a two-dimensional database;

retrieving one or more structure metadata records from a metadata repository, the structure metadata records containing at least;

(a) a field identifying the first cell of the two-dimensional database; and

(b) a plurality of fields comprising indices corresponding to separate dimensions of a multidimensional database having more than two dimensions, at the intersection of which a second cell of the multidimensional database is located;

determining, based on the structure metadata records, that the first cell in the two-dimensional database is mapped to the second cell in the multidimensional database; and

copying the new data from the first cell in the two-dimensional database to the second cell in the multidimensional database.

9. The method of claim 8, further comprising:

identifying a particular structure within the multidimensional database that contains the second cell.

10. The method of claim 8, wherein within the metadata repository, a plurality of cells in the two-dimensional database are not mapped to any cells in the multidimensional database.

11. The method of claim 8, further comprising:

after determining that new data has been posted into the first cell in the two-dimensional database, determining that no structure is implemented within the multidimensional database that contains a cell corresponding to the first cell; and

generating a new particular structure within the multidimensional database, the new particular structure containing the second cell corresponding to the first cell in the two-dimensional database.

12. The method of claim 11, further comprising:

updating the structure metadata records of the metadata repository to include the new particular structure generated within the multidimensional database.

13. The method of claim 8, wherein determining that new data has been posted into the first cell in the two-dimensional database comprises:

receiving a first request to store the new data in the two-dimensional database, the first request including a requirement that the new data also be stored in the multidimensional database.

14. The method of claim 8, further comprising:

executing a structural integrity operation in accordance with a schedule, wherein the structural integrity operation performs at least a first comparison between the multidimensional database and the structure metadata records of the metadata repository, and a second comparison between the two-dimensional database and the structure metadata records of the metadata repository.

15. A non-transitory computer-readable memory comprising a set of instructions stored therein which, when executed by a processor, causes the processor to synchronize a two-dimensional database and a multidimensional database by:

determining that new data has been posted into a first cell in a two-dimensional database;

retrieving one or more structure metadata records from a metadata repository, the structure metadata records containing at least;

(a) a field identifying the first cell of the two-dimensional database; and

(b) a plurality of fields comprising indices corresponding to separate dimensions of a multidimensional database having more than two dimensions, at the intersection of which a second cell of the multidimensional database is located;

determining, based on the structure metadata records, that the first cell in the two-dimensional database is mapped to the second cell in the multidimensional database; and

copying the new data from the first cell in the two-dimensional database to the second cell in the multidimensional database.

16. The non-transitory computer-readable memory of claim 15, comprising further instructions stored therein which, when executed by the processor, causes the processor to synchronize the two-dimensional database and the multidimensional database by:

identifying a particular structure within the multidimensional database that contains the second cell.

17. The non-transitory computer-readable memory of claim 15, wherein within the metadata repository, a plurality of cells in the two-dimensional database are not mapped to any cells in the multidimensional database.

18. The non-transitory computer-readable memory of claim 15, comprising further instructions stored therein which, when executed by the processor, causes the processor to synchronize the two-dimensional database and the multidimensional database by:

after determining that new data has been posted into the first cell in the two-dimensional database, determining that no structure is implemented within the multidimensional database that contains a cell corresponding to the first cell; and

generating a new particular structure within the multidimensional database, the new particular structure containing the second cell corresponding to the first cell in the two-dimensional database.

19. The non-transitory computer-readable memory of claim 18, comprising further instructions stored therein which, when executed by the processor, causes the processor to synchronize the two-dimensional database and the multidimensional database by:

updating the structure metadata records of the metadata repository to include the new particular structure generated within the multidimensional database.

20. The non-transitory computer-readable memory of claim 15, comprising further instructions stored therein which, when executed by the processor, causes the processor to synchronize the two-dimensional database and the multidimensional database by:

executing a structural integrity operation in accordance with a schedule, wherein the structural integrity operation performs at least a first comparison between the multidimensional database and the structure metadata records of the metadata repository, and a second comparison between the two-dimensional database and the structure metadata records of the metadata repository.