Simplified interaction with complex database

• US 9,411,874 B2
• Filed: 06/14/2012
• Issued: 08/09/2016
• Est. Priority Date: 06/14/2012
• Status: Active Grant

• Current Assignee: Melaleuca Incorporated
• Original Assignee: Melaleuca Incorporated
• Inventors: Hasner, Jason Steven
• Primary Examiner(s): Gofman, Alex
• Assistant Examiner(s): Mian, Umar
• Application Number:
  US13/523,502
• Publication Number:
  US 20130339291A1
• Time in Patent Office: 1,517 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A computer-implemented method for processing data in a database using a data cube is discussed and involves interacting with a user of a computing device, using a computer application, to obtain meta data about a database and obtain user-defined selections of options concerning the database; creating a data cube definition by using the selected options to identify characteristics of columns in the database; publishing to an OLAP engine a definition of a data cube that corresponds to the data base; generating a simplified flat-file model of the data in the data cube; and providing the simplified flat-file model in a form that can be reviewed and directly manipulated by the user.

Claims

1. A computer-implemented method for processing data in a database using a data cube, the method comprising:

receiving, at an application executing on a computer system, a request from a user for a report relating to a group of data in a database;

querying the database with the application to obtain meta data about the group of data;

identifying, by the application and based on the obtained meta data, field descriptors of one or more fields in the group of data;

providing, by the application, the field descriptors for review by the user;

receiving, from the user and by the application, selections based on the provided field descriptors;

querying the database with the application and based on the received selected field descriptors to identify aggregable columns in the group of data to be included in the requested report;

creating, by the application, measure groups of aggregable columns in the group of data to be included in the requested report, wherein the measure groups are identified by the application based on the aggregable columns identified by the application;

creating, by the application, a data cube definition based on the selected field descriptors and the measure groups created by the application;

publishing, with the application, the data cube definition and other information about the group of data to an online analytical processing (OLAP) engine that is separate from the application;

receiving, at the application and from the OLAP engine, a data cube based on the data cube definition and comprising data obtained from the database relating to the group of data;

generating, in response to receiving the data cube by the application, a simplified flat-file model of the group of data based on the received data cube, wherein the simplified flat-file is generated without additional user input to the application; and

providing, by the application, the generated simplified flat-file model as the requested report in a form that can be reviewed and directly manipulated by the user.

2. The computer-implemented method of claim 1, wherein the simplified flat-file model comprises a computer spreadsheet to which the user is given access.

3. The computer-implemented method of claim 1, further comprising, after receiving the selected field descriptors from the user, notifying the user that the simplified flat-file model will be provided in the future, and allowing the user to interact with the application while the simplified flat-file model is generated.

4. The computer-implemented method of claim 1, wherein generating the simplified flat-file model comprises mapping data from the data cube to a spreadsheet pivot table.

5. The computer-implemented method of claim 1, wherein

providing, by the application, the field descriptors for review by the user comprises providing fields from a report to the user, and

receiving, from the user and by the application, selections based on the provided field descriptors comprises receiving one or more commands from the user to filter the fields for ranges identified by the user.

6. The computer-implemented method of claim 1, wherein querying the database with the application to identify aggregable columns in the group of data to be included in the requested report comprises identifying columns in the database as being aggregatable as one or more of an average, a count, and a sum.

7. The computer-implemented method of claim 1, further comprising:

querying the database with the application and based on the received selections to identify columns in the database having a finite number of values; and

creating, by the application, cube dimensions for columns identified as having a finite number of values.

8. The computer-implemented method of claim 7, wherein publishing the data cube definition and other information comprises:

creating relationships between the group of data and a data source for the dimensions; and

creating one or more measures for the cube.

9. The computer-implemented method of claim 8, further comprising identifying by the application dimensions that are preexisting on a server system and re-using dimensions that are identified as being preexisting on the server rather than recreating the preexisting dimensions.

10. A computer program stored in a non-transitory computer readable storage device, the computer program comprising instructions that when executed by a processing device cause the processing device to perform operations comprising:

receiving, at the processing device, a request from a user for a report relating to a group of data in a database;

querying the database by the computer program to obtain meta data about the group of data;

identifying, by the computer program and based on the obtained meta data, field descriptors of one or more fields in the group of data;

providing, by the computer program, the field descriptors for review by the user;

receiving, from the user and by the computer program, selections based on the provided field descriptors;

querying the database with the computer program and based on the received selected field descriptors to identify aggregable columns in the group of data to be included in the requested report;

creating, by the computer program, measure groups of aggregable columns in the group of data to be included in the requested report, wherein the measure groups are identified by the application based on the aggregable columns identified by the computer program;

creating, by the computer program, a data cube definition based on the selected field descriptors and the measure groups created by the computer program;

publishing, by the computer program, the data cube definition and other information about the group of data to an online analytical processing (OLAP) engine that is separate from the computer program;

generating, in response to receiving the data cube by the computer program, a simplified flat-file model of the group of data based on the received data cube, wherein the simplified flat-file model is generated without additional user input to the application; and

providing, by the computer program, the generated simplified flat-file model as the requested report in a form that can be reviewed and directly manipulated by the user.

11. The computer program of claim 10, wherein the simplified flat-file model comprises a computer spreadsheet to which the user is given access.

12. The computer program of claim 10, wherein querying the database with the application to identify aqgregable columns in the group of data to be included in the requested report comprises identifying columns in the database as being aggregatable as one or more of an average, a count, and a sum.

13. The computer program of claim 10, the operations further comprising:

querying, by the computer program, the database based on the received selections to identify columns in the database having a finite number of values; and

creating, by the computer program, a cube dimension for columns identified as having a finite number of values.

14. The computer program of claim 13, wherein publishing the data cube definition comprises:

creating relationships between the group of data and a data source for the dimensions; and

creating one or more measures for the cube.

15. The computer program of claim 14, the operations further comprising identifying by the computer program dimensions that are preexisting on a server system and re-using dimensions that are identified as being preexisting on the server rather than recreating the preexisting dimensions.

16. A computer-implemented automatic data manipulation system, comprising:

a data processing apparatus;

a data storage device storing;

a relational database storing data in a plurality of interrelated tables of data; and

an online analytical processing (OLAP) engine arranged to communicate with the relational database and process data from the relational database into a data cube; and

a memory device storing instructions, separate from the database and the OLAP engine, that when executed by data processing apparatus cause the data processing device to perform operations to;

receive a request from a user for a report relating to a group of data in a database;

query the database to obtain meta data about the group of data;

identify, based on the obtained meta data, field descriptors of one or more fields in the group of data;

provide the field descriptors for review by the user;

receiving, from the user, selections based on the provided field descriptors;

query the database based on the received selected field descriptors to identify aggregable columns in the group of data to be included in the requested report;

create measure groups of aggregable columns in the group of data to be included in the requested report, wherein the measure groups are identified based on the identified aqgregable columns;

create a data cube definition based on the selected field descriptors and the created measure groups;

publish the data cube definition and other information about the group of data to the OLAP engine;

receive, from the OLAP engine, a data cube based on the data cube definition and comprising data obtained from the database relating to the group of data;

generate, in response to receiving the data cube, a simplified flat-file model of the group of data based on the received data cube, wherein the simplified flat-file is generated without additional user input to the data processing apparatus; and

provide the generated simplified flat-file model as the requested report in a form that can be reviewed and directly manipulated by the user.

17. The computer-implemented system of claim 16, wherein the simplified flat-file model comprises a computer spreadsheet to which the user is given access.

18. The computer-implemented system of claim 16, wherein querying the database to identify aqgregable columns in the group of data to be included in the requested report comprises identifying columns in the database as being aggregatable as one or more of an average, a count, and a sum.

19. The computer-implemented system of claim 16, further comprising:

querying the database based on the received selections to identify columns in the database having a finite number of values; and

creating a cube dimension for columns identified as having a finite number of values.

20. The computer-implemented system of claim 19, wherein publishing the data cube definition comprises:

creating relationships between the group of data and a data source for the dimensions; and

creating one or more measures for the cube.

21. The computer-implemented system of claim 20, further comprising identifying database dimensions that are preexisting on a server system and re-use dimensions that are identified as being preexisting on the server rather than recreating the preexisting dimensions.