Spreadsheet to SQL translation

• US 20050039114A1
• Filed: 07/15/2004
• Published: 02/17/2005
• Est. Priority Date: 07/16/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Naimat, Aman, Witkowski, Andrew, Waingold, Allison, Roth, Martin
• Granted Patent: US 7,299,223 B2
• Time in Patent Office: Days
• US Class Current: 715/234
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

Users can create computational models in a spreadsheet application and automatically apply the model to data stored in a relational database. By importing a sample of the data from a database table into the spreadsheet application, users can build spreadsheet models that perform analysis and computations on the sample data. Once the model is complete, the spreadsheet model is translated into an SQL format model understood by the database. The SQL model can operate on the entire data set in the database, rather than just the sample data used to construct the model. The SQL model and its associated data are stored in the database, and the model can be executed on a different sets of data. A web browser based front-end allows model users to access the SQL model via a web browser, eliminating the need for model users to have a spreadsheet application.

Claims

1. A method for creating a model for analyzing data, the method comprising:

importing a set of sample data from a database into a spreadsheet application;

associating a set of spreadsheet operations comprising a spreadsheet model with the set of sample data;

translating the set of spreadsheet operations into a model database query, such that the model database query performs the set of spreadsheet operations on a full data set in the database.

2. The method of claim 1, wherein the sample data is a copy of a portion of the full data set.

3. The method of claim 2, wherein translating comprises matching the set of sample data to a second database query defining the full data set within the database.

4. The method of claim 1, further comprising creating a web page from the results of the model database query on the database.

5. The method of claim 1, wherein importing the set of sample data comprises:

receiving a database query specifying the full data set in the database;

receiving a sample size value; and

querying the database to retrieve the set of sample data corresponding to a portion of the full data set and having a size of the sample size value.

6. The method of claim 5, further comprising:

receiving a name for the set of sample data; and

associating the name with the set of sample data.

7. The method of claim 6, wherein the set of sample data includes a plurality of sample data subsets, each sample data subset corresponding to a subset of the full data set.

8. The method of claim 7, wherein associating the name with the set of sample data includes associating the name and a subset name derived from the corresponding subset of the full data set with each of the plurality of sample data subsets.

9. The method of claim 6, wherein, in associating the set of spreadsheet operations with the set of sample data, the set of spreadsheet operations includes references to the name associated with the set of sample data.

10. The method of claim 9, wherein translating comprises matching the set of sample data to the database query defining the full data set within the database using the name associated with the set of sample data for each of the set of operations.

11. A system for implementing a model, the system comprising:

a modeling module adapted to interface with a spreadsheet application and to retrieve a set of sample data from a database, wherein the set of sample data corresponds with a portion of a full data set within the database;

a persistence module adapted to interface with the spreadsheet application and to store a set of spreadsheet operations referencing the set of sample data in the database; and

a translation module adapted to translate the set of spreadsheet operations into a model database query, such that the model database query performs the set of spreadsheet operations on a full data set in the database.

12. The system of claim 11, wherein the translation module is further adapted to store the model database query in the database.

13. The system of claim 11, wherein the modeling module is adapted to associate a name and a database query specifying the full data set within the database with the set of sample data.

14. The system of claim 11, further comprising:

an instance generator adapted to create an instance of the model from the model database query and to store the instance of the model and a set of model input data in the database.

15. The system of claim 13, wherein the set of spreadsheet operations include a reference to name associated with the set of sample data;

and

wherein the translation module is further adapted to match the name associated with the set of sample data with the database query specifying the full data set and to create a model database query applying the set of operations to the full data set specified by the database query.

16. The system of claim 11, wherein the model database query is an SQL MODEL rule.

17. The system of claim 14, wherein the instance generator is adapted to store a set of output data resulting from the execution of the model database query on the set of model input data in the database.

18. The system of claim 17, wherein the instance generator is adapted to receive the set of model input data from a model user application.

19. The system of claim 18, further comprising:

a web front-end adapted to create a web page from the results of the model database query on the database; and

wherein the model user application is a web browser.

20. The system of claim 19, wherein the web page includes a user interface adapted to collect the set of model input data from a model user.