Mechanism for updating OLAP system structure and OLTP system structure

• US 9,959,330 B2
• Filed: 05/04/2012
• Issued: 05/01/2018
• Est. Priority Date: 09/20/2011
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ng, Rondy, Zwiebach, Robert, Haimes, David, Siauw, Djiao Mei, Roth, Gregory David, Banerjee, Arunesh, Matam, Santosh Kumar, Kosuri, Venkata Ramana Murthy
• Primary Examiner(s): Reyes, Mariela
• Assistant Examiner(s): HARMON, COURTNEY N
• Application Number:
  US13/464,897
• Publication Number:
  US 20130073520A1
• Time in Patent Office: 2,188 Days
• Field of Search: 707610
• 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, comprising:

a processor;

a computer-readable storage medium;

a metadata repository configured to store structure metadata that defines one or more structures for storing data in both a relational database, and a multidimensional database;

wherein the metadata repository is further configured to store first metadata that describes a mapping between (1) cells of a table in the relational database and (2) cells of an Online Analytical Processing (OLAP) cube structure stored within the multidimensional database;

synchronization component logic configured to detect that a change has been made to the OLAP cube structure within the multidimensional database, wherein the synchronization component logic is configured to use user-interface triggers to detect the change to the OLAP cube structure within the multidimensional database;

synchronization logic to modify the first metadata to reflect the change that has been made to the OLAP cube structure within the multidimensional database in response to detecting that the change has been made to the OLAP cube structure within the multidimensional database; and

synchronization logic to modify the table in the relational database based on the modification to the first metadata that was performed in response to the change that has been made to the OLAP cube structure within the multidimensional database, wherein the detection that a change has been made to the OLAP cube structure, the modification of the first metadata to reflect the change that has been made to the OLAP cube structure, and the modification of the table in the relational database based on the modification to the first metadata, are performed within the same computing process instance.

2. The system of claim 1, further comprising structure generation logic configured to:

generate a structure in the relational database based at least in part on the first metadata; and

generate a structure in the multidimensional database based at least in part on the first metadata.

3. The system of claim 1, further comprising metadata generation logic configured to change the first metadata in response to receiving a request to change a particular structure that is defined by the structure metadata;

wherein the change comprises adding a data container to the particular structure.

4. The system of claim 3, further comprising structure generation logic configured to:

change a structure in the relational database to conform to the structure metadata; and

change a structure in the multidimensional database to conform to the structure metadata.

12. The system of claim 1, further comprising metadata generation logic configured to automatically generate the first metadata based on particular structure definition template metadata for a particular type of data selected by a user from structure template metadata for various types of data;

wherein the particular structure definition template metadata describes how to create the first metadata for the relational database and how to create the first metadata for the multidimensional database.

14. The system of claim 1, wherein the first metadata describes a mapping between cells of a first table in the relational database and cells of the OLAP cube structure within the multidimensional database;

wherein the first metadata describes a mapping between cells of a second table in the relational database and cells of the OLAP cube structure within the multidimensional database; and

wherein the first table is separate from the second table.

17. The system of claim 1, wherein the synchronization logic is further configured to:

retrieve data defining one or more parent/child relationships between data elements in the relational database;

generate a hierarchical structure based on the parent/child relationships between data elements in the relational database; and

in response to the change to the OLAP cube structure stored within the multidimensional database, use the hierarchical structure to perform the modification of the table in the relational database.

19. The system of claim 1, wherein the first metadata includes at least a first definition file defining a mapping between the table in the relational database and the OLAP cube structure stored within the multidimensional database, the first definition file comprising a plurality of dimension fields, each said dimension field corresponding to a cube structure within the multidimensional database.

5. A method, comprising:

maintaining a metadata repository that stores structure metadata that defines one or more structures for storing data in both a relational database, and a multidimensional database;

storing, in the metadata repository, first metadata that describes a mapping between (1) cells of a table in the relational database and (2) cells of an Online Analytical Processing (OLAP) cube structure stored within the multidimensional database;

detecting that a change has been made to the OLAP cube structure within the multidimensional database, wherein user-interface triggers are used to detect the change to the OLAP cube structure within the multidimensional database;

in response to detecting that the change has been made to the OLAP cube structure within the multidimensional database, modifying the first metadata to reflect the change that has been made to the OLAP cube structure within the multidimensional database; and

modifying the table in the relational database based on the modification to the first metadata that was performed in response to the change that has been made to the OLAP cube structure within the multidimensional database,

wherein detecting that the change has been made to the OLAP cube structure, modifying the first metadata to reflect the change that has been made to the OLAP cube structure, and modifying the table in the relational database are performed within the same computing process instance.

6. The method of claim 5, further comprising:

generating a structure in the relational database based at least in part on the first metadata; and

generating a structure in the multidimensional database based at least in part on the first metadata.

7. The method of claim 5, further comprising:

receiving a request to change a particular structure, wherein the structure is defined by the structure metadata;

in response to receiving the request, changing the metadata associated with the structure.

8. The method of claim 7, further comprising:

in response to receiving the request to change the particular structure;

changing a structure in the relational database to conform to the structure metadata; and

changing a structure in the multidimensional database to conform to the structure metadata.

15. The method of claim 5, wherein the first metadata describes a mapping between cells of a first table in the relational database and cells of the OLAP cube structure within the multidimensional database;

wherein the first metadata describes a mapping between cells of a second table in the relational database and cells of the OLAP cube structure within the multidimensional database; and

wherein the first table is separate from the second table.

18. The method of claim 5, further comprising:

retrieving data defining one or more parent/child relationships between data elements in the relational database;

generating a hierarchical structure based on the parent/child relationships between data elements in the relational database; and

in response to the change to the OLAP cube structure stored within the multidimensional database, use the hierarchical structure to perform the modification of the table in the relational database.

9. A computer-readable non-transitory storage medium storing a plurality of instructions executable by one or more processors, the plurality of instructions comprising:

maintaining a metadata repository that stores structure metadata that defines one or more structures for storing data in both a relational database, and a multidimensional database;

storing, in the metadata repository, first metadata that describes a mapping between (1) cells of a table in the relational database and (2) cells of an Online Analytical Processing (OLAP) cube structure stored within the multidimensional database;

detecting that a change has been made to the OLAP cube structure within the multidimensional database, wherein user-interface triggers are used to detect the change to the OLAP cube structure within the multidimensional database;

in response to detecting that the change has been made to the OLAP cube structure within the multidimensional database, modifying the first metadata to reflect the change that has been made to the OLAP cube structure within the multidimensional database; and

modifying the table in the relational database based on the modification to the first metadata that was performed in response to the change that has been made to the OLAP cube structure within the multidimensional database,

wherein detecting that the change has been made to the OLAP cube structure, modifying the first metadata to reflect the change that has been made to the OLAP cube structure, and modifying the table in the relational database are performed within the same computing process instance.

10. The computer-readable non-transitory storage medium of claim 9, wherein the instructions further include instructions that cause the one or more processors to perform:

receiving a request to change a particular structure, wherein the structure is defined by the structure metadata;

in response to receiving the request, changing the metadata associated with the structure.

11. The computer-readable non-transitory storage medium of claim 10, wherein the instructions further include instructions that cause the one or more processors to perform:

in response to receiving the request to change the particular structure;

changing a structure in the relational database to conform to the structure metadata; and

changing a structure in the multidimensional database to conform to the structure metadata.

13. The computer-readable non-transitory storage medium of claim 9, wherein the instructions cause the one or more processors to automatically generate the first metadata based on particular structure definition template metadata for a particular type of data selected by a user from structure template metadata for various types of data;

wherein the particular structure definition template metadata describes how to create the first metadata for the relational database and how to create the first metadata for the multidimensional database.

16. The computer-readable non-transitory storage medium of claim 9, wherein the first metadata describes a mapping between cells of a first table in the relational database and cells of the OLAP cube structure within the multidimensional database;

wherein the first metadata describes a mapping between cells of a second table in the relational database and cells of the OLAP cube structure within the multidimensional database;

and wherein the first table is separate from the second table.