Method and apparatus for accessing multidimensional data

• US 6,317,750 B1
• Filed: 10/26/1998
• Issued: 11/13/2001
• Est. Priority Date: 10/26/1998
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Hyperion Solutions Corporation (Oracle Corporation)
• Inventors: Nouri, Koorosh M., Tortolani, Thomas R.
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Ly, Anh
• Application Number:
  US09/178,059
• Time in Patent Office: 1,114 Days
• Field of Search: 707/3, 707/4, 707/5, 707/101, 707/103, 707/104, 707/503, 706/11, 345/335, 345/355
• US Class Current: 1/1
• 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/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

Retrieving multidimensional data from a data source and displaying the data in a familiar and pre-existing user interface automatically propagates user-created formulas thereby eliminating the need for users to re-enter formulas. A data representation of the multidimensional data is sent to a query processor which creates row and column structures. These structures are manipulated based on a user action, such as zoom-in, and a multidimensional data output tree showing a hierarchy of the multidimensional data. Also created is a blueprint containing instructions on insertions and deletions to be carried out by the program associated with the pre-existing user interface, such as a spreadsheet program. Once the blueprint is interpreted by the program, typically through a data representation manipulator or common spreadsheet layer, the user interface is configured to accommodate the returned multidimensional data. Once the user interface is populated with the data, the program, such as the spreadsheet program, adjusts the user-created formula cell designations to reflect the new configuration.

Claims

1. A method of presenting data including a formula using a cell-based user interface, the method comprising:

retrieving an initial data representation of data in the cell-based user interface, the initial data representation including a representation of a user navigation operation;

determining whether the data representation contains a pre-existing, user-created formula computing a first value having a meaning in a first dimension of a multidimensional database;

creating a set of instructions to be performed by a cell-based data manipulation program associated with the cell-based user interface containing the user operation and the pre-existing, user-created formula;

creating a second data representation including updated data from a data source to be displayed in the cell-based user interface and the set of instructions to be performed by the cell-based data manipulation program; and

executing the set of instructions thereby allowing the pre-existing, user-created formula to automatically replicate and propagate, such that values from the pre-existing, user-created formula are displayed in the cell-based user interface, wherein a replicated formula computes a second value having a meaning in a second dimension of the multidimensional database, wherein the first value and the second value are related.

2. A method as recited in claim 1 wherein retrieving an initial data representation of data in the cell-based user interface further comprises converting data in the cell-based user interface and the user navigation operation to a compact binary representation.

3. A method as recited in claim 2 wherein the compact binary representation is a binary spreadsheet table format.

4. A method as recited in claim 1 further comprising creating the initial data representation of data in the cell-based user interface by utilizing spreadsheet add-in modules to transmit data to a spreadsheet data and user navigation operation formatter.

5. A method as recited in claim 4 wherein the spreadsheet data and user navigation operation formatter is a common spreadsheet layer.

6. A method as recited in claim 1 wherein determining whether the data representation contains a preexisting, user-created formula further comprises detecting whether cells in the cell-based user interface contain one of either data or non-data values.

7. A method as recited in claim 1 wherein creating a set of instructions to be performed by a cell-based data manipulation program further comprises creating a row structure containing a plurality of row nodes and a column structure containing a plurality of column nodes.

8. A method as recited in claim 7 wherein each one of the plurality of row nodes contains first position data of each string at the beginning of each row in the cell-based user interface and each one of the plurality of column nodes contains second position data of each string at the beginning of each column in the cell-based user interface.

9. A method as recited in claim 8 further comprising adjusting the first position data and the second position data after examining an outline of multidimensional data.

10. A method as recited in claim 9 wherein the outline of multidimensional data contains a hierarchy of data in the form of parent-child relationships.

11. A method as recited in claim 10 wherein the first position data and the second position data for a particular node are adjusted based on whether a parent-child relationship exists for that particular node.

12. A method as recited in claim 1 wherein creating a second data representation including updated data from a data source to be displayed in the cell-based user interface and the set of instructions to be performed by the cell-based data manipulation program further comprises converting data from the data source and the set of instructions to be performed by the cell-based data manipulation program to a compact binary representation.

13. A method as recited in claim 12 wherein the set of instructions to be performed by the cell-based manipulation program is derived from a blueprint of operations needed to configure the cell-based user interface to accommodate the data.

14. A method as recited in claim 13 wherein the data is multidimensional data and the data source is a multidimensional database.

15. A method as recited in claim 1 wherein performing the set of instructions further comprises invoking add-in modules associated with the cell-based data manipulation program to enable execution of the set of instructions by the cell-based data manipulation program.

16. A method as recited in claim 1 further comprising populating the cell-based data manipulation program with multidimensional data thereby displaying it through a newly configured cell-based user interface.

17. A computer readable medium containing programmed instructions arranged to present data including a formula using a cell-based user interface, the computer readable medium including programmed instructions for:

retrieving an initial data representation of data in the cell-based user interface, the initial data representation including a representation of a user navigation operation;

determining whether the data representation contains a pre-existing, user-created formula computing a first value having a meaning in a first dimension of a multidimensional database;

creating a set of instructions to be performed by a cell-based data manipulation program associated with the cell-based user interface containing the user operation and the pre-existing, user-created formula;

creating a second data representation including updated data from a data source to be displayed in the cell-based user interface and the set of instructions to be performed by the cell-based data manipulation program; and

executing the set of instructions thereby allowing the pre-existing, user-created formula to automatically replicate and propagate, such that values from the pre-existing, user-created formula are displayed in the cell-based user interface, wherein a replicated formula computes a second value having a meaning in a second dimension of the multidimensional database, wherein the first value and the second value are related.

18. A computer readable medium as recited in claim 17 wherein the programmed instructions for creating a set of instructions to be performed by a cell-based data manipulation program further comprises programmed instructions for creating a row structure containing a plurality of row nodes and a column structure containing a plurality of column nodes.

19. A computer readable medium as recited in claim 18 further comprising programmed instructions such that each one of the plurality of row nodes contains first position data of each string at the beginning of each row in the cell-based user interface and each one of the plurality of column nodes contains second position data of each string at the beginning of each column in the cell-based user interface.

20. A computer readable medium as recited in claim 18 further comprising programmed instructions for adjusting the first position data and the second position data after examining an outline of multidimensional data.

21. A computer readable medium as recited in claim 20 further comprising programmed instructions such that the outline of multidimensional data contains a hierarchy of data in the form of parent-child relationships.

22. A computer readable medium as recited in claim 21 further comprising programmed instructions such that the first position data and the second position data for a particular node is adjusted based on whether a parent-child relationship exists for that particular node.

23. A computer-implemented method for automatically propagating pre-existing, user-created formulas following a user navigation through a database capable of being viewed in a multi-dimensional manner, said computer-implemented method comprising:

(a) displaying a view of a first data set from the database, the first view of the data set containing numeric data, and at least a portion of the numeric data results from a pre-existing, user-created formula in a first dimension of a database;

(b) receiving a user navigation request with respect to the view of the first data set;

(c) determining a view of a second data set from the database, the view of the second data set containing numeric data, and at least a portion of the numeric data resulting from propagating and replicating the pre-existing, user-created formula of the view of the first data set to locations within the view of the second data set wherein a propagated formula is in a second dimension of the database and is related to the pre-existing, user-created formula; and

(d) displaying the view of the second data set.

24. A computer-implemented method as recited in claim 23, wherein the first and second data sets are multi-dimensional.

25. A computer-implemented method as recited in claim 23, wherein the user navigation request is one of a zoom-in navigation request.

26. A computer-implemented method as recited in claim 23, wherein said displaying a view of a first data set from the database operates to display the first view in a grid format, and said displaying the view of the second data set operates to display the second view in a grid format.

27. A computer-implemented method as recited in claim 26, wherein the grid format is a spreadsheet grid.

28. A computer-implemented method as recited in claim 23, wherein said determining a view of a second data set from the database comprises:

forming a first data representation describing the first view and the user navigation request;

forwarding the first data representation to a data view manager;

converting, at the data view manager, the first data representation into row and column structures;

modifying, at the data view manager, the row and column structures based on the user navigation request;

determining, at the data view manager, formula propagation information for the view of the second data set with respect to the first data set; and

forming a return data representation based on the modified row and column structures and the formula preservation information, the return data representation describing the view of the second data set.

29. A computer-implemented method as recited in claim 28, wherein said displaying the view of the second data set comprises:

constructing the view of the second data set in accordance with the return data representation; and

populating the view of the second data set with data from the database.

30. A computer-implemented method as recited in claim 28, wherein said modifying, at the data view manager, the row and column structures, makes use of a data outline that describes the multi-dimensional nature of the database.

31. A computer-implemented method as recited in claim 28, wherein the formula preservation in formation indicates at least copy and paste operations for the pre-existing, user-created formula.

32. A computer-implemented method as recited in claim 28, wherein a spreadsheet application provides the views of the database to a user,

wherein said displaying a view of a first data set from the database operates to display the first view in a spreadsheet grid format, and said displaying the view of the second data set operates to display the second view in a spreadsheet grid format, and

wherein the formula preservation information are spreadsheet instructions carried-out by the spreadsheet application.

33. A method for displaying data from a multidimensional database, said method comprising the operations of:

displaying a first grid of data on a display device, the data within the first grid of data including numeric data, and at least a portion of the numeric data results from a user-created formula created in a spreadsheet program;

receiving a user navigation request with respect to the first grid of data, the user navigation request requesting a related but different dimensional view of the data displayed in the first grid of data; and

displaying an altered grid of data on the display device, the altered grid of data being related to the first grid of data, the data within the altered grid of data including at least numeric data, and at least a portion of the numeric data results from propagating the user-created formula associated with the first grid of data to a plurality of different cells within the altered grid of data to provide the different dimensional view of the data wherein a propagated formula is derived according to the user-created formula.

34. A method as recited in claim 33, wherein the user navigation request is a zoom-in navigation request.

35. A method as recited in claim 33, wherein the computer program is a spreadsheet program.

36. A computer readable medium containing programmed instructions arranged to automatically propagate pre-existing, user-created formulas following a user navigation through a database capable of being viewed in a multi-dimensional manner, the computer readable medium including programmed instructions for:

(a) displaying a view of a first data set from the database, the first view of the data set containing numeric data, and at least a portion of the numeric data results from a pre-existing, user-created formula in a first dimension of a database;

(b) receiving a user navigation request with respect to the view of the first data set;

(c) determining a view of a second data set from the database, the view of the second data set containing numeric data, and at least a portion of the numeric data resulting from propagating and replicating the pre-existing, user-created formula of the view of the first data set to locations within the view of the second data set wherein a propagated formula is in a second dimension of the database and is related to the pre-existing, user-created formula; and

(d) displaying the view of the second data set.

37. A computer readable medium as recited in claim 36 further comprising programmed instructions such that said determining a view of a second data set from the database further comprises programmed instructions for:

forming a first data representation describing the first view and the user navigation request;

forwarding the first data representation to a data view manager;

converting, at the data view manager, the first data representation into row and column structures;

modifying, at the data view manager, the row and column structures based on the user navigation request;

determining, at the data view manager, formula propagation information for the view of the second data set with respect to the first data set; and

forming a return data representation based on the modified row and column structures and the formula preservation information, the return data representation describing the view of the second data set.

38. A computer readable medium containing programmed instructions arranged to display data from a multidimensional database, the computer readable medium including programmed instructions for:

displaying a first grid of data on a display device, the data within the first grid of data including numeric data, and at least a portion of the numeric data results from a user-created formula created in a spreadsheet program;

receiving a user navigation request with respect to the first grid of data, the user navigation request requesting a related but different dimensional view of the data displayed in the first grid of data; and

displaying an altered grid of data on the display device, the altered grid of data being related to the first grid of data, the data within the altered grid of data including at least numeric data, and at least a portion of the numeric data results from propagating the user-created formula associated with the first grid of data to a plurality of different cells within the altered grid of data to provide the different dimensional view of the data wherein a propagated formula is derived according to the user-created formula.