Systems and methods for visualizing multi-dimensional data in spreadsheets and other data structures

• US 6,707,454 B1
• Filed: 10/12/1999
• Issued: 03/16/2004
• Est. Priority Date: 07/01/1999
• Status: Expired due to Term

• Current Assignee: WSOU Investments, LLC (WSOU Holdings, LLC)
• Original Assignee: Lucent Technologies, Inc. (Nokia Corporation)
• Inventors: Mirel, Barbara R., Garity, Brenda A., Hackborn, Dianne K., Eick, Stephen G., Burkwald, Susan K., Barg, Timothy A., Tatelman, Michael S., Swanson, William C.
• Primary Examiner(s): BRIER, JEFFERY A
• Application Number:
  US09/415,923
• Time in Patent Office: 1,617 Days
• Field of Search: 345/440, 345/440.2, 707/503, 707/903, 705/10, 715/503, 715/903
• US Class Current: 345/440
• CPC Class Codes: G06F 18/40 Software arrangements specially adapted for pattern recognition, e.g. user interfaces or toolboxes therefor
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06T 11/206 Drawing of charts or graphs
• CPC Class Codes: G06V 10/945 User interactive design; Environments; Toolboxes

Abstract

The data visualization systems and methods of this invention provide a powerful tool for visualizing multi-dimensional databases. In one exemplary embodiment, the data visualization systems and methods of this invention visualize pivot tables using two novel visual metaphors: a single measure perspective consisting of linked bar charts and a multiscape landscape visualization; and a anchored measures perspective for displaying several measures simultaneously. The data visualization systems and methods of this invention usefully display much larger pivot tables than is possible using conventional interfaces and provide the ability to navigate through complex cubes to identify important structures. In various exemplary embodiments, the data visualization systems and methods of this invention attach to MS Excel™ as an add-on, access pivot tables directly from SQL Server 7.0, and integrate with MS Office.

Claims

1. A method for visually analyzing data of a multi-dimensional data structure having a plurality of dimensions and at least one measure, comprising:

generating at least one perspective, each perspective having a dimensional view portion and a multi-dimensional view portion, the dimensional view portion having at least one dimensional view, each dimensional view displaying one of the at least one measure against one of the plurality of dimensions, the multi-dimensional view portion having a multi-dimensional view displaying data items of the multi-dimensional data structure corresponding to at least one of the at least one measure and at least two of the plurality of dimensions;

displaying one of the at least one generated perspective;

displaying a multi-dimensional analysis toolbar, wherein the toolbar comprises;

a replace selection mode button that causes a new selection data set to replace the previous selection data set;

an intersect selection mode button that combines the previous and current data sets to form a new, intersecting, data set; and

an add selection mode button that extends the previous selection data set by forming a new effective selection set as a union with the previous selection data set and the new selection data set;

selecting a portion of the data being displayed by selecting a portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective; and

altering, using the multi-dimensional analysis toolbar, the selected portion of the data being displayed.

2. The method of claim 1, wherein generating at least one perspective comprises:

determining a data schema of the multi-dimensional data structure, the data schema defining the dimensions and the measures of the multi-dimensional data structure and at least one relationship between the dimensions, between the measures, or between the dimensions and the measures;

selecting a plurality of dimensions of the multi-dimensional data structure;

creating a data table for each selected dimension;

selecting at least one measure of the multi-dimensional data structure;

creating a data table for each selected measure; and

creating a layout of each at least one perspective based on the selected dimensions and at least one selected measure.

3. The method of claim 2, wherein selection a plurality of dimensions of the multi-dimensional data structure comprises selecting a column dimension or a row dimension of the multi-dimensional data structure.

4. The method of claim 3, wherein creating the data table for a selected column or row dimension comprises:

setting at least one of dimensional view data, weighting data and color by data to corresponding data table fields of a data table for that selected dimension;

adding the selected dimension to a list of available dimensions; and

adding the selected dimension to a list of available color-by fields.

5. The method of claim 3, wherein creation a layout of each at least one perspective based on a selected column or row dimension comprises:

creating at least one dimensional view for the selected dimension; and

adding each created dimensional view for the selected dimension to at least one of the at least one perspective.

6. The method of claim 2, wherein selecting a plurality of dimensions of the multi-dimensional data structure comprises selecting a page dimension of the multi-dimensional data structure.

7. The method of claim 6, wherein creating the data table for a selected page dimension comprises:

adding the selected page dimension to a list of available dimensions; and

adding the selected page dimension to a list of available color-by fields.

8. The method of claim 6, wherein creating a layout of each at least one perspective based on a selected page dimension comprises:

adding a combo box control to each at least one perspective if the at least one perspective does not already contain the combo box control;

adding the selected page dimension to a combo box list of the combo box control.

9. The method of claim 2, wherein creating the data table for each selected measure comprises:

adding the selected measure to a list of available measures; and

adding the selected measure to a list of weighting fields; and

adding the selected measure to a list of available color-by fields.

10. The method of claim 1, wherein generating the at least one perspective comprises generating at least one of at least a single measure perspective and a multiple measures perspective.

11. The method of claim 10, wherein generating the single measure perspective comprises:

determining a single measure for the single measure perspective;

determining at least two column or row dimensions for the single measure perspective;

generating a dimensional view for each determined column or row dimension based on the determined single measure;

determining at least one page dimension for the single measure perspective;

generating a combo box control for the determined at least one page dimension;

determining axis fields for the determined at least two column or row dimensions; and

generating a single measure multi-dimensional view based on the determined axis fields and the determined measure.

12. The method of claim 11, wherein displaying the single measure perspective comprises:

displaying the dimensional view for each determined column or row dimension in the dimensional view portion of the single measure perspective;

displaying the combo box control for the determined at least one page dimension in the single measure perspective;

displaying the single measure multi-dimensional view in the multi-dimensional view portion; and

weighting the single measure multi-dimensional view based on a selected one of the determined dimensions.

13. The method of claim 12, wherein displaying the multiple measures perspective comprises:

displaying the dimensional view for each determined column or row dimension in the dimensional view portion of the multiple measures perspective;

displaying the combo box control for the determined at least one page dimension in the multiple measures perspective;

displaying the multiple measures multi-dimensional view in the multi-dimensional view portion;

weighting the multiple measures multi-dimensional view based on a selected one of the determined dimensions.

14. The method of claim 11, wherein generating a dimensional view for each determined column or row dimension based on the determined single measure comprises generating one of at least a bar chart, a pie chart, a line chart or a bubble plot as the dimensional view for each determined column or row dimension.

15. The method of claim 10, wherein generating the multiple measures perspective comprises:

determining a plurality of measures for the multiple measures perspective;

determining at least two column or row dimensions for the multiple measures perspective;

generating a dimensional view for each determined column or row dimension;

determining at least one page dimension for the multiple measures perspective;

generating a combo box control for the determined at least one page dimension; and

generating a multiple measures multi-dimensional view based on the determined column, row and page dimensions and the determined measures.

16. The method of claim 15, wherein the multiple measures perspective is one of a box plot multiple measures perspective and a scatterplot multiple measures perspective.

17. The method of claim 16, wherein, when multiple measures perspective is the box plot multiple measures perspective, generating the multiple measures multi-dimensional view based on the determined column, row and page dimensions and the determined measures comprises generating one box plot for each determined measure.

18. The method of claim 16, wherein, when multiple measures perspective is the scatter plot multiple measures perspective, generating the multiple measures multi-dimensional view based on the determined column, row and page dimensions and the determined measures comprises generating a scatter plot for two of the determined measures, each point of the scatter plot weighted based on the selected dimension.

19. The method of claim 10, wherein generating at least one perspective further comprises generating an anchored measures perspective.

20. The method of claim 19, wherein generating the anchored measures perspective comprises:

determining a plurality of measures for the multiple measures perspective;

determining at least two column or row dimensions for the multiple measures perspective;

generating a set of bubble plots for each determined column or row dimension;

determining at least one page dimension for the multiple measures perspective;

generating a combo box control for the determined at least one page dimension; and

generating a parabox view based on the determined column, row and page dimensions and the determined measures, the parabox view comprising one box plot for each determined measure.

21. The method of claim 20, wherein displaying the anchored measures perspective comprises:

displaying the set of bubble plots for each determined column or row dimension in the dimensional view portion of the multiple measures perspective;

displaying the combo box control for the determined at least one page dimension in the multiple measures perspective;

displaying the parabox view in the multi-dimensional view portion;

weighting the bubble plots and the parabox view based on a selected one of the determined dimensions.

22. The method of claim 1, wherein displaying the at least one generated perspective comprises displaying one of at least a single measure perspective and a multiple measures perspective.

23. The method of claim 22, wherein displaying the at least one generated perspective further comprises displaying an anchored measures perspective.

24. The method of claim 1, wherein altering an appearance of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective based on each data item of the multi-dimensional data structure that corresponds to the selected portion comprises:

determining a selection mode;

determining the data items in the multi-dimensional data structure corresponding to the selected portion based on the determined selection mode; and

updating an appearance of each dimensional view and each multi-dimensional view of each at least one perspective based on the determined data items.

25. The method of claim 1, further comprising writing back data to one of the multi-dimensional data structure and a new multi-dimensional data structure based on the selected portion.

26. The method of claim 1, wherein displaying one of the at least one generated perspective comprises:

receiving at least one user input identifying the one of the at least one generated perspective to be displayed;

hiding a currently displayed one of the at least one generated perspective; and

displaying the identified one of the at least one generated perspective.

27. The method of claim 1, wherein displaying one of the at least one generated perspective comprises:

receiving at least one user input identifying a measure to be visualized of the at least one measure;

altering the multi-dimensional view portion of each at least one perspective based on the identified measure to display data items of the multi-dimensional data structure corresponding to the identified measure; and

altering each dimensional view of the dimensional view portion of each at least one perspective based on the identified measure.

28. The method of claim 1, further comprising:

receiving a user input identifying a measure to be visualized in the multi-dimensional view and the at least one dimensional view of each of the at least one perspective; and

updating the multi-dimensional view and the at least one dimensional view of each of the at least one perspective to visualize data items in the multi-dimensional data structure corresponding to the identified measure.

29. The method of claim 1, wherein displaying one of the at least one generated perspective comprises:

receiving at least one user input identifying a page dimension to be visualized;

altering the multi-dimensional view portion of each at least one perspective based on the identified measure to display data items of the multi-dimensional data structure corresponding to the identified page dimension; and

altering each dimensional view of the dimensional view portion of each at least one perspective based on the identified page dimension.

30. The method of claim 1, wherein, when the dimensional views of the dimensional view portion are bar chart views, selecting a portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective comprises receiving a user input identifying at least one bar of one of the bar chart views.

31. The method of claim 30, wherein altering the appearance of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective based on the selected portion, when the received user input identifies at least one bar of one of the bar chart views, comprises setting portions of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective corresponding to the at least one selected bar to a selected state based on an active selection mode.

32. The method of claim 1, wherein generating the at least one perspective comprises:

determining a number of values of the data items in the multi-dimensional data structure corresponding to the at least one measure and the at least one dimension;

assigning rendering values to the values of the data items in the multi-dimensional data structure corresponding to the at least one measure and the at least one dimension based on the determined number and a predetermined range of rendering values.

33. The method of claim 1, further comprising:

a textual query selection mode button that activates a text string measure selection graphical user interface.

34. The method of claim 1, further comprising:

a toggle selection mode to unselect those data items which were previously selected while those data items which were previously unselected become selected.

35. The method of claim 1, further comprising an unselect-all mode which causes all data entries to be unselected.

36. The method of claim 1, wherein each perspective dimensional view portion and each multi-dimensional view portion comprise the same type of chart.

37. The method of claim 36, wherein the type of chart is a bar-chart.

38. A method for visually generating a query related to data on a multi-dimensional data structure having a plurality of dimensions and at least one measure, comprising:

generating at least one perspective, each perspective having a dimensional view portion and a multi-dimensional view portion, the dimensional view portion having at least one dimensional view, each dimensional view displaying one of the at least one measure against one of the plurality of dimensions, the multi-dimensional view portion having a multi-dimensional view displaying at least one of the at least one measure against at least two of the plurality of dimensions;

displaying one of the at least one generated perspective;

displaying a multi-dimensional analysis toolbar, wherein the toolbar comprises;

a replace selection mode button that causes a new selection data set to replace the previous selection data set;

an intersect selection mode button that combines the previous and current data sets to form a new, intersecting, data set; and

an add selection mode button that extends the previous selection data set by forming a new effective selection set as a union with the previous selection data set and the new selection data set;

selecting a portion of the data being displayed by selecting a portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective;

querying the multi-dimensional data structure based on data items of the multi-dimensional data structure corresponding to the selected portion; and

altering, using the multi-dimensional analysis toolbar, an appearance of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective based on results of the query.

39. The method of claim 38, wherein querying the multi-dimensional data structure based on data items of the multi-dimensional data structure corresponding to the selected portion comprises:

determining a selection mode;

determining the data items in the multi-dimensional data structure for querying the multi-dimensional data structure based on the selected portion and the determined selection mode; and

querying the multi-dimensional data structure based on the determined data items of the multi-dimensional data structure, and further wherein determining the data items in the multi-dimensional data structure for querying the multi-dimensional data structure based on the selected portion and the determined selection mode further comprises determining the data items in the multi-dimensional data structure for querying the multi-dimensional data structure based on the selected portion, at least one other portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective and the determined selection mode.

40. The method of claim 38, further comprising writing back data to one of the multi-dimensional data structure and a new multi-dimensional data structure based on the results of the query.

41. The method of claim 40, wherein writing back data to the data source, comprising:

creating a write-back form;

displaying the write-back form to a user to select a name for the data to be written-back;

determining whether the user has indicated that the current state of the write-back form is acceptable;

allowing the user to change the name and subset of the data to be written-back;

determining if the write-back process is complete; and

permitting a user to operate on the written back data.

42. A visual analysis system for visually analyzing data of a multi-dimensional data structure having a plurality of dimensions and at least one measure, comprising:

a memory that stores the data items of the multi-dimensional data structure;

a rendering system capable of rendering at least one perspective, each perspective having a dimensional view portion and a multi-dimensional view portion, the dimensional view portion having at least one dimensional view, each dimensional view displaying one of the at least one measure against one of the plurality of dimensions, the multi-dimensional view portion having a multi-dimensional view displaying data items of the multi-dimensional data structure corresponding to at least one of the at least one measure an at least one of the plurality of dimensions;

a display device capable of displaying the at least one perspective and a multi-dimensional analysis toolbar, wherein the toolbar comprises;

replace selection mode button that causes a new selection data set to replace the previous selection data set;

an intersect selection mode button that combines the previous and current data sets to form a new, intersecting, data set; and

an add selection mode button that extends the previous selection data set by forming a new effective selection set as a union with the previous selection data set and the new selection data set;

a selection device capable of selecting at least one selection mode button of the multi-dimensional analysis toolbar and a portion of the data being displayed by selecting a portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective.

43. The visual analysis system of claim 42, wherein the rendering system comprises:

a display controller; and

a data/color/selection state table, and further wherein the display controller comprises;

means for rendering at least one dimensional view;

means for rendering at least one single measure view; and

means for rendering at least one multiple measures view.

44. The visual analysis system of claim 43, wherein the rendering system alters an appearance of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective based on the selected portion.

45. The visual analysis system of claim 43, wherein the at least one perspective comprises at least one of at least a single measure perspective and a multiple measures perspective.

46. The visual analysis system of claim 45, wherein, when the displayed perspective is the single measure perspective, the rendering system renders the multi-dimensional view as a three-dimensional graph plotting at least one dimension along each of a first axis and a second axis, and a single measure along a third axis.

47. The visual analysis system of claim 45, wherein, when the displayed perspective is the multiple measures perspective, the rendering system renders the multi-dimensional view as a two-dimensional scatter plot with one measure plotted along each of a first axis and a second axis, an appearance of the points of the scatter plot based on a selected dimension.

48. The visual analysis system of claim 45, wherein, when the displayed perspective is the multiple measures perspective, the rendering system renders the multi-dimensional view as a set of box plots, the box plot multi-dimensional view displaying data corresponding to a plurality of measures, each box plot corresponding to one of the plurality of measures.

49. The visual analysis system of claim 45, wherein the at least one perspective further comprises at least one an anchored measures perspective.

50. The visual analysis system of claim 49, wherein, when the displayed perspective is the anchored measures perspective, the rendering system renders the multi-dimensional view as a set of box plots, the box plot multi-dimensional view displaying data corresponding to a plurality of measures, each box plot corresponding to one of the plurality of measures.

51. The visual analysis system of claim 50, wherein, when the displayed perspective is the anchored measures perspective, the rendering system renders each dimensional view as number of sets of bubble plots, each set of bubble plots corresponding to one of a plurality of dimensions.

52. The visual analysis system of claim 43, wherein, when the displayed perspective is the single measure perspective or the multiple measures perspective, the rendering system renders each dimensional view as a bar chart.

53. The visual analysis system of claim 42, wherein the rendering system renders an appearance of the displayed multi-dimensional view based on one of at least one displayed dimension, and further wherein the rendering system further renders an appearance of the displayed multi-dimensional view based on a displayed measure.

54. The visual analysis system of claim 42, wherein, when a portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective is selected, the rendering system alters an appearance of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective based on each data item of the multi-dimensional data structure that corresponds to the selected portion.

55. The visual analysis system of claim 54, wherein the rendering system alters the appearance of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective further based on a selection mode associated with selected portion.

56. The visual analysis system of claim 55, wherein the rendering system determines the data items in the multi-dimensional data structure corresponding to the selected portion based on the selection mode associated with selected portion.

57. The visual analysis system of claim 55, wherein, when a data item of the multi-dimensional data structure is selected, the rendering systems renders the appearance of corresponding portions of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective based on values of the selected measure, and when a data item of the multi-dimensional data structure is unselected, the rendering systems renders the appearance of corresponding portions of the multi-dimensional view and the at least one dimensional view of each of the at least one perspective in a neutral state.

58. A graphical user interface for visually analyzing data of a multi-dimensional data structure having a plurality of dimensions and at least one measure, comprising:

a perspectives portion, comprising;

a dimensional views portion containing a plurality of dimensional views; and

a multi-dimensional view portion,

wherein an appearance of a portion of the data being displayed in at least one of the plurality of dimensional views and the multi-dimensional view portion is selected and altered, while original values of the data are preserved; and

a multi-dimensional analysis toolbar, wherein the toolbar comprises;

a replace selection mode button that causes a new selection data set to replace the previous selection data set;

an intersect selection mode button that combines the previous and current data sets to form a new, intersecting, data set; and

an add selection mode button that extends the previous selection data set by forming a new effective selection set as a union with the previous selection data set and the new selection data set.

59. The graphical user interface of claim 58, wherein the perspectives portion is capable of displaying one of at least a single measures perspective and a multiple measures perspective.

60. The graphical user interface of claim 59, wherein, when the perspectives portion displays the single measure perspective, the multi-dimensional view portion displays a three-dimensional graph plotting at least one dimension along each of a first axis and a second axis, and a single measure along a third axis, and the dimensional views portion displays a number of dimensional views equal to the number of dimensions plotted along the first and second axes, each dimensional view plotting one of the dimensions plotted along the first and second axes against the single measure.

61. The graphical user interface of claim 59, wherein, when the perspectives portion displays the multiple measures perspective, the multi-dimensional view portion displays a two-dimensional scatter plot with one measure plotted along each of a first axis and a second axis, an appearance-of the points of the scatter plot based on a selected dimension, and the dimensional views portion displays a plurality of dimensional views, each dimensional view plotting a dimension of the multi-dimensional data structure against one of the measures plotted along the first and second axes.

62. The graphical user interface of claim 59, wherein, when the perspectives portion displays the multiple measures perspective, the multi-dimensional view portion displays at least one box plot, each box plot corresponding to one measure of the multi-dimensional data structure, and the dimensional views portion displays a plurality of dimensional views, each dimensional view plotting a dimension of the multi-dimensional data structure against one of the measures displayed in the multi-dimensional view portion, each box plot plotting one of the plurality of measures against a selected one of the plurality of dimensions.

63. The graphical user interface of claim 58, wherein the perspectives portion is further capable of displaying one of an anchored measures perspective.

64. The graphical user interface of claim 63, wherein, when the perspectives portion displays the anchored measures perspective, the multi-dimensional view portion displays at least one box plot and the dimensional views portion displays at least one set of bubble plots, each set of bubble plots plotting one of a plurality of dimensions against a selected one of the plurality of measures, each box plot plotting one of a plurality of measures against a selected one of the plurality of dimensions.

65. The graphical user interface of claim 58, wherein the toolbar further comprises:

a select perspective control portion;

a select color by field control portion; and

a select measure control portion.

66. The graphical user interface of claim 58, wherein the toolbar further comprises:

a select all button that causes all portions of the displayed perspective to be selected;

an unselect all button that causes all portions of the displayed perspective to be unselected; and

a toggle selection button that causes all currently-unselected portions of the displayed perspective to be selected and that causes all currently-selected portions of the displayed perspective to be unselected.

67. The graphical user interface of claim 58, wherein the toolbar further comprises:

an exclude button that causes the unselected items to be removed from the at least one perspective when the at least one perspective is displayed; and

an restore button that causes any previously excluded items to be restored to the at least one perspective when the at least one perspective is displayed.

68. The graphical user interface of claim 58, wherein the toolbar further comprises a write-back button that causes a new multi-dimensional data structure to be created based on the data items of the multi-dimensional data structure that correspond to the selected portions of the displayed perspective.

69. A method for visually analyzing data of a multi-dimensional data structure having a plurality of dimensions and at least one measure, comprising:

generating at least one perspective, each perspective having a dimensional view portion and a multi-dimensional view portion, the dimensional view portion having at least one dimensional view, each dimensional view displaying one of the at least one measure against one of the plurality of dimensions, the multi-dimensional view portion having a multi-dimensional view displaying data items of the multi-dimensional data structure corresponding to at least one of the at least one measure and at least two of the plurality of dimensions;

displaying one of the at least one generated perspective;

displaying a multi-dimensional analysis toolbar, wherein the toolbar comprises at least one of;

a replace selection mode button that causes a new selection data set to replace the previous selection data set;

an intersect selection mode button that combines the previous and current data sets to form a new, intersecting, data set; and

an add selection mode button that extends the previous selection data set by forming a new effective selection set as a union with the previous selection data set and the new selection data set;

selecting a portion of the data being displayed by selecting a portion of one of the multi-dimensional view and the at least one dimensional view of the displayed perspective; and

altering, using the multi-dimensional analysis toolbar, the selected portion of the data being displayed; and

simultaneously generating three perspectives, including a single measure perspective, a multiple measures perspective and an anchored measures perspective, each perspective having a dimensional view portion and a multi-dimensional view portion, the dimensional view portion having at least one dimensional view, each dimensional view displaying one of the at least one measure against one of the plurality of dimensions, the multi-dimensional view portion having a multi-dimensional view displaying data items of the multi-dimensional data structure corresponding to at least one of the at least one measure and at least two of the plurality of dimensions.