Interfacing with a relational database for multi-dimensional analysis via a spreadsheet application

• US 10,140,352 B2
• Filed: 07/17/2014
• Issued: 11/27/2018
• Est. Priority Date: 07/17/2014
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Hariharan, Sreeram, Uppala, Swathi, Shirkol, Mohammed Hussain, Arora, Usha, Pathak, Ashish, Suleman, Kashif, Raman, Ramchand
• Primary Examiner(s): Owyang, Michelle N
• Application Number:
  US14/334,556
• Publication Number:
  US 20160019281A1
• Time in Patent Office: 1,594 Days
• Field of Search: 715212, 707705-780
• CPC Class Codes: G06F 16/13 File access structures, e.g. distributed indices arrangements of input from, or output to, record carriers G06F3/06
• CPC Class Codes: G06F 16/14 Details of searching files based on file metadata
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/284 Relational databases
• 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 40/18 of spreadsheets form-filling G06F40/174

Abstract

Systems, methods, and computer-readable mediums are presented that may provide for an interface to a relational database. A request may be received for data stored in a relationship database, wherein the request is received from a spreadsheet application. In response to the request for data stored in the relational database, a plurality of tables in the relational database may be accessed to retrieve the data indicated in the request. The retrieved data may be translated from the plurality of tables of the relational database into a format for output to the spreadsheet application. The translated data may be output to the spreadsheet application.

Claims

1. A system for providing an interface to a relational database, the system comprising:

a relational interface web server system, comprising;

one or more processors; and

a memory communicatively coupled with and readable by the one or more processors and having stored therein processor-readable instructions which, when executed by the one or more processors, cause the one or more processors to;

receive a first eXtensible Markup Language (“

WL”

)-based message from a spreadsheet application, the first XML-based message defining a layout, the layout identifying a plurality of attributes of the relational database and the layout identifying a mapping of the plurality of attributes to a plurality of columns in a spreadsheet of the spreadsheet application;

in response to receiving the first WL-based message defining the layout, generate and submit a first database query to fetch metadata associated with the plurality of attributes from the relational database;

receive, from the relational database in response to the first database query, the metadata comprising a data type value for at least one of the plurality of attributes;

transmit the metadata to the spreadsheet application for the spreadsheet application to format the spreadsheet of the spreadsheet application in accordance with the metadata;

after transmitting the metadata to the spreadsheet application, receive, from the spreadsheet application, a second XML-based message comprising a request for data stored in the relational database;

in response to receiving the second XML-based message comprising the request, generate and submit a second database query to retrieve the data indicated in the request from a plurality of tables in the relational database;

receive, from the relational database in response to the second database query, a query response comprising the data indicated in the request;

translate the query response in accordance with the layout and the metadata; and

output the translated query response to the spreadsheet application for the spreadsheet application to interpret and present via the spreadsheet.

2. The system for providing the interface to the relational database of claim 1, the system further comprising:

a user computer system configured to;

execute the spreadsheet application, wherein the spreadsheet application uses a software extension to communicate with the relational interface web server system; and

present, via a first plurality of cells of the spreadsheet, at least a portion of the translated query response, wherein the user computer system communicates with the relational interface web server system via a first network connection.

3. The system for providing the interface to the relational database of claim 1, the system further comprising:

a relational database server system configured to;

store data using the plurality of tables, wherein the relational database server system is accessible by the relational interface web server system.

4. The system for providing the interface to the relational database of claim 1, wherein the metadata comprises, for each of the plurality of attributes, an attribute data type and an attribute label.

5. The system for providing the interface to the relational database of claim 1, wherein the processor-readable instructions of the relational interface web server system, when executed, further cause the one or more processors to:

receive a validation request for an authorization and an authentication of a user from the spreadsheet application;

in response to the validation request for the authorization and the authentication of the user, perform the authorization and the authentication of the user; and

provide an authorization response and an authentication response to the spreadsheet application.

6. The system for providing the interface to the relational database of claim 2, wherein the user computer system is further configured to:

present, via a second plurality of cells of the spreadsheet, data retrieved from a multidimensional database distinct from the relational database, wherein the first plurality of cells and the second plurality of cells are presented concurrently.

7. The system for providing the interface to the relational database of claim 1, wherein the processor-readable instructions of the relational interface web server system, when executed, further cause the one or more processors to:

obtain an available connection with the relational database from a connection pool.

8. The system for providing the interface to the relational database of claim 1, wherein the processor-readable instructions of the relational interface web server system, when executed, further cause the one or more processors to:

receive, from the spreadsheet application, an indication of a selection of a cell from within the spreadsheet;

determine that the cell corresponds to hierarchical data;

retrieve, from the relational database, the hierarchical data; and

output the hierarchical data to the spreadsheet application.

9. A method for providing an interface to a relational database, the method comprising:

receiving, by a server system, a first eXtensible Markup Language (“

XML”

)-based message from a spreadsheet application, the first XML-based message defining a layout, the layout identifying a plurality of attributes of the relational database and the layout identifying a mapping of the plurality of attributes to a plurality of columns in a spreadsheet of the spreadsheet application;

in response to receiving the first XML-based message defining the layout, generating and submitting, by the server system, a first database query to fetch metadata associated with the plurality of attributes from the relational database;

receiving, by the server system from the relational database in response to the first database query, the metadata comprising a data type value for at least one of the plurality of attributes;

transmitting, by the server system, the metadata to the spreadsheet application for the spreadsheet application to format the spreadsheet of the spreadsheet application in accordance with the metadata;

after transmitting the metadata to the spreadsheet application, receiving, by the server system from the spreadsheet application, a second WL-based message comprising a request for data stored in the relational database;

in response to receiving the second XML-based message comprising the request, generating and submitting, by the server system, a second database query to retrieve the data indicated in the request from a plurality of tables in the relational database;

receiving, by the server system from the relational database in response to the second database query, a query response comprising the data indicated in the request;

translating, by the server system, the query response in accordance with the layout and the metadata; and

outputting, by the server system, the translated query response to the spreadsheet application for the spreadsheet application to interpret and present via the spreadsheet.

10. The method for providing the interface to the relational database of claim 9, the method further comprising:

executing, by a user computer system, the spreadsheet application, wherein the spreadsheet application uses a software extension to communicate with the server system; and

presenting, by the user computer system via a first plurality of cells of the spreadsheet, at least a portion of the translated query response, wherein the user computer system communicates with the server system via a first network connection.

11. The method for providing the interface to the relational database of claim 9, the method further comprising:

storing, using a relational database server system, data using the plurality of tables, wherein the relational database server system is accessible by the server system.

12. The method for providing the interface to the relational database of claim 9, wherein the metadata comprises, for each of the plurality of attributes, an attribute data type and an attribute label.

13. The method for providing the interface to the relational database of claim 9, the method further comprising:

receiving, by the server system, a validation request for an authorization and an authentication of a user from the spreadsheet application;

in response to the validation request for the authorization and the authentication of the user, performing, by the server system, the authorization and the authentication of the user; and

providing, by the server system, an authorization response and an authentication response to the spreadsheet application.

14. The method for providing the interface to the relational database of claim 10, the method further comprising:

presenting, by the user computer system via a second plurality of cells of the spreadsheet, data retrieved from a multidimensional database distinct from the relational database.

15. The method for providing the interface to the relational database of claim 14, wherein the first plurality of cells and the second plurality of cells are presented concurrently.

16. The method for providing the interface to the relational database of claim 9, the method further comprising:

receiving, by the server system from the spreadsheet application, an indication of a selection of a cell from within the spreadsheet;

determining, by the server system, that the cell corresponds to hierarchical data;

retrieving, by the server system, from the relational database, the hierarchical data; and

outputting, by the server system, the hierarchical data to the spreadsheet application.

17. A non-transitory processor-readable medium for providing an interface to a relational database, comprising processor-readable instructions configured to cause one or more processors to:

receive a first eXtensible Markup Language (“

XML”

)-based message from a spreadsheet application, the first XML-based message defining a layout, the layout identifying a plurality of attributes of the relational database and the layout identifying a mapping of the plurality of attributes to a plurality of columns in a spreadsheet of the spreadsheet application;

in response to receiving the first XML-based message defining the layout, generate and submit a first database query to fetch metadata associated with the plurality of attributes from the relational database;

receive, from the relational database in response to the first database query, the metadata comprising a data type value for at least one of the plurality of attributes;

transmit the metadata to the spreadsheet application for the spreadsheet application to format the spreadsheet of the spreadsheet application in accordance with the metadata;

after transmitting the metadata to the spreadsheet application, receive, from the spreadsheet application, a second XML-based message comprising a request for data stored in the relational database;

in response to receiving the second XML-based message comprising the request, generate and submit a second database query to retrieve the data indicated in the request from a plurality of tables in the relational database;

receive, from the relational database in response to the second database query, a query response comprising the data indicated in the request;

translate the query response in accordance with the layout and the metadata; and

output the translated query response to the spreadsheet application for the spreadsheet application to interpret and present via the spreadsheet.

18. The non-transitory processor-readable medium for providing the interface to the relational database of claim 17, wherein the metadata comprises, for each of the plurality of attributes, an attribute data type and an attribute label.

19. The non-transitory processor-readable medium for providing the interface to the relational database of claim 17, wherein the processor-readable instructions are further configured to cause the one or more processors to:

receive a validation request for an authorization and an authentication of a user from the spreadsheet application;

in response to the validation request for the authorization and the authentication of the user, perform the authorization and the authentication of the user; and

provide an authorization response and an authentication response to the spreadsheet application.

20. The non-transitory processor-readable medium for providing the interface to the relational database of claim 17, wherein the processor-readable instructions are further configured to cause the one or more processors to:

receive, from the spreadsheet application, an indication of a selection of a cell from within the spreadsheet;

determine that the cell corresponds to hierarchical data;

retrieve, from the relational database, the hierarchical data; and

output the hierarchical data to the spreadsheet application.