Mechanism for synchronizing OLAP system structure and OLTP system structure

• US 10,061,827 B2
• Filed: 02/16/2015
• Issued: 08/28/2018
• 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, Roth, Gregory David, Banerjee, Arunesh, Matam, Santosh Kumar, Kosuri, Venkata Ramana Murthy
• Primary Examiner(s): Brooks, David T.
• Application Number:
  US14/622,993
• Publication Number:
  US 20150227597A1
• Time in Patent Office: 1,289 Days
• Field of Search: None
• 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 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;

receiving a first request to store new data within a two-dimensional database;

posting the new data into a first cell in the two-dimensional database;

in response to the posting of the new data into the first cell in the two-dimensional database, retrieving one or more records from the structure metadata of the metadata repository

determining, based on the records retrieved from the structure metadata, that the first cell in the two-dimensional database is mapped to a first dimension member within in the multidimensional database;

attempting to store the new data at the first dimension member within the multidimensional database;

determining that no structure is implemented within the multidimensional database at the first dimension member; and

in response to determining that no structure is implemented within the multidimensional database corresponding to the first dimension member;

(a) accessing the metadata repository to retrieve structural characteristics of the first dimension member;

(b) creating structure within the multidimensional database corresponding to the first dimension member, using the structural characteristics of the first dimension member retrieved from the metadata repository; and

(c) storing the new data in the created structure within the multidimensional database corresponding to the first dimension member.

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

3. The system of claim 1, wherein the plurality of instructions, when executed by the processing unit, further 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 structure created within the multidimensional database corresponding to the first dimension member.

4. The system of claim 1, wherein

the received first request to store new data within a two-dimensional database includes a requirement that the new data also be stored in the multidimensional database.

5. The system of claim 1, wherein the plurality of instructions, when executed by the processing unit, further 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.

6. The system of claim 1, wherein each of the structure metadata records retrieved from the metadata repository contains:

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

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

wherein the number of the plurality of fields comprising indices within the structure metadata records is based on the number of dimensions of the multidimensional database.

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

receiving, by a database server, a first request to store new data within a two-dimensional database;

posting, by the database server, the new data into a first cell in the two-dimensional database;

in response to the posting of the new data into the first cell in the two-dimensional database, retrieving one or more structure metadata records from a metadata repository;

determining, based on the structure metadata records, that the first cell in the two-dimensional database is mapped to a first dimension member within the multidimensional database;

attempting, by the database server, to store the new data at the first dimension member within the multidimensional database;

determining, by the database server, that no structure is implemented within the multidimensional database at the first dimension member; and

in response to determining that no structure is implemented within the multidimensional database corresponding to the first dimension member;

(a) accessing the metadata repository to retrieve structural characteristics of the first dimension member;

(b) creating structure within the multidimensional database corresponding to the first dimension member, using the structural characteristics of the first dimension member retrieved from the metadata repository; and

(c) storing the new data in the created structure within the multidimensional database corresponding to the first dimension member.

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

9. The method of claim 7 further comprising:

updating the structure metadata records of the metadata repository to include the structure created within the multidimensional database corresponding to the first dimension member.

10. The method of claim 7, wherein

the received first request to store new data within a two-dimensional database includes a requirement that the new data also be stored in the multidimensional database.

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

12. The method of claim 7, wherein each of the structure metadata records retrieved from the metadata repository contains:

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

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

wherein the number of the plurality of fields comprising indices within the structure metadata records is based on the number of dimensions of the multidimensional database.

13. The method of claim 7, wherein the posting of the new data into the first cell in the two-dimensional database, the creating of the structure within the multidimensional database corresponding to the first dimension member, and the storing of the new data in the created structure within the multidimensional database are performed via a same single process.

14. The method of claim 7, wherein the posting of the new data into the first cell in the two-dimensional database, the creating of the structure within the multidimensional database corresponding to the first dimension member, and the storing of the new data in the created structure within the multidimensional database are performed via a same single process,

wherein each of the structure metadata records retrieved from the metadata repository contains:

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

a plurality of fields comprising indices, each of the plurality of fields corresponding to a separate dimension of a multidimensional database having more than two dimensions, the indices within the plurality of fields defining the intersection at which a second cell of the multidimensional database is located, and

wherein the number of the plurality of fields comprising indices within the structure metadata records is based on the number of dimensions of the multidimensional database, and

wherein the method further comprises;

executing a structural integrity operation automatically according to a schedule, wherein the structural integrity operation (a) performs a first comparison between the multidimensional database and the structure metadata, and (b) performs a second comparison between the two-dimensional database and the structure metadata;

detecting, using the structural integrity operation, that a change has been made to a structure of a table in the two-dimensional database;

in response to detecting that the change has been made to the structure of the table in the two-dimensional database, modifying the structure metadata to reflect the change that has been made to the structure of the table; and

modifying a structure of the multidimensional database based on the modification to the structure metadata performed in response to the change that has been made to the structure of the table.

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:

receiving a first request to store new data within a two-dimensional database;

posting the new data has into a first cell in a two-dimensional database;

in response to the posting of the new data has been posted into the first cell in the two-dimensional database, retrieving one or more structure metadata records from a metadata repository;

determining, based on the structure metadata records, that the first cell in the two-dimensional database is mapped to a first dimension member within in the multidimensional database;

attempting to store the new data at the first dimension member within the multidimensional database;

determining that no structure is implemented within the multidimensional database at the first dimension member; and

in response to determining that no structure is implemented within the multidimensional database corresponding to the first dimension member;

(a) accessing the metadata repository to retrieve structural characteristics of the first dimension member;

(b) creating structure within the multidimensional database corresponding to the first dimension member, using the structural characteristics of the first dimension member retrieved from the metadata repository; and

(c) storing the new data in the created structure within the multidimensional database corresponding to the first dimension member.

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:

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.

17. The non-transitory computer-readable memory of claim 15, wherein each of the structure metadata records retrieved from the metadata repository contains:

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

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

wherein the number of the plurality of fields comprising indices within the structure metadata records is based on the number of dimensions of the multidimensional database.