The present invention relates to information processing and more particularly to database access and reporting systems and methods relating to information processing.
Data access and reporting has long played an essential role in enterprise management. Without the ability to adequately access, summarize, and manipulate raw data the efficiency of an enterprise suffers. Traditionally, dedicated application programs perform specific data access and reporting.
Many of these database application programs are complex and unwieldy to use. A user often needs specialized knowledge to perform even basic tasks, and many simply cannot use prior solutions to build usable reports that provide answers to real or sophisticated questions. In large part, prior solutions are too complex, unwieldy and difficult to use because they provide field selection methods and systems that require a sophisticated understanding of the source data and how it is organized.
Prior solutions typically address field selection through the use of general programs that group fields according to predetermined logical groupings. These logical groupings greatly simply the field selection process by minimizing the number of fields displayed to the user at a given time. However, because prior solutions were not developed to extract what the user wants or what context the groupings and fields have to what the user wants, they are too difficult to use and may lead to inaccurate reports.
Prior solutions also have attempted to address field selection through the use of simplified interfaces known as program “wizards.” These wizards greatly simplify the field selection process by minimizing the number of options and/or inputs needed to create a report. However, because designers of these wizards presume that each wizard will be used to manipulate a particular type of data or a particular set of data, the simplified interfaces are inflexible and may lead to inaccurate or incomprehensible reports when applied to different data types or data sets.
Additionally, because dedicated database application programs, general report generator programs with limited field picking capabilities, and field selection wizards require extensive knowledge of underlying data structures, they are often prohibitively expensive. With prices in the hundreds or thousands of dollars per copy, and tens of thousands of dollars for an enterprise license, data access and reporting may be prohibitively expensive. This is especially true in the case of a small or start-up business.
Thus, there is a general need for a data access method and system that interactively and iteratively displays appropriate fields based upon what the user wants and thus quickly and efficiently constrains field selection in a simplified manner that is easily comprehended by a casual or novice user. There is also a need for a data access method and system that is inexpensive and affordable to individuals or small companies.
According to the present invention, a method and system for displaying a relational abstraction of a data store is provided. The method and system empower novice or casual computer users easily and iteratively to select fields of the data store to create useful reports.
According to one embodiment, the present invention is implemented through a distributed application that runs on multiple computers but is displayed on a graphical user interface (“GUI”). This GUI, combined with common input devices such as a mouse and keyboard, minimizes the learning curve of applications made in accordance with the present invention. Thus, even a novice or casual user may quickly and easily understand and apply the present invention to access and build reports from a data store.
The present invention provides a simple-to-use application that displays fields associated with a data store. The fields associated with a data store are defined in a relational abstraction of the data store. The present invention provides an easily comprehended means of interactively and iteratively selecting fields defined in the relational abstraction, according to the user's desires in response to simple and efficient input commands. Using the invention, a user selects an initial view associated with the relational abstraction. This view, referred to herein as the base view, becomes the entry point into the relational abstraction and is used to constrain which fields are displayed for selection by the user. Selecting the base view is inherently understandable by users because the base view comprises the answer to what the user selects as the basis for a report. Based upon the base view, the user may select fields associated with any view of the relational abstraction, and may follow relations within the relational abstraction to select additional fields. As the user follows relations within the relational abstraction, the fields displayed are constrained by the base view and the relation path from the base view. Users thus have fields displayed for selection that are appropriate to answer the question associated with the base view.
The present invention defines a data store in terms of a relational abstraction. The relational abstraction generally parallels the entity-relationship inherent in a well designed transactional relational database. Doing so preserves the business logic associated with such transactional systems for use by users of the invention. One skilled in the art will readily recognize that an entity-relationship abstraction may also be applied to data storage systems that are not in the genre of traditional relational database management systems.
The relational abstraction includes views associated with a data store, scalar or aggregate fields associated with views and relations between views. View definitions identify tabular structures of rows and columns in the data store. Field definitions describe columns of data accessible in a particular view. Relation definitions describe associations between various views. Typically such definitions are associated with one or more tables and columns of a conventional relational database management system. However, one skilled in the art will recognize that any means of providing an entity-relationship view on data may be used as part of the invention.
The present invention permits a user to display only those fields that are logically available based upon the base view selected by the user and the relation path sequence followed by the user from the base view. If a relation path sequence contains only to-one relations, scalar fields are displayed. If a relation path sequence contains a to-many relation, aggregate fields are displayed. If a relation path sequence contains a to-many relation followed by a one-to-one relation, distinct aggregates are displayed.
Another benefit of the present invention comes from displaying fields with additional information indicating ways in which the fields may be used by the user. Aggregate fields can be displayed with information indicating that they can be used to generate totals. Fields predefined to be meaningful for grouping can be displayed as grouping fields. One skilled in the art will recognize that a field may be displayed any number of ways in accordance with the present invention.
Another benefit of the present invention comes from displaying default ways in which fields may be constrained. A default display of aggregate fields may be constrained to generate totals. A default display of groups meaningful for grouping may be constrained to grouping. One skilled in the art will recognize that the present invention permits the display of any number of defaults for display of a field.
Another benefit of the present invention comes from displaying the nature of relation paths from one view to another. Relation paths may be displayed as to-many relations where the relation path to a destination view contains a to-many relation sequence. Relation paths may be displayed as to-one where the relation path to a destination view contains a to-one relation sequence. Relation paths from a base view to a destination view may be displayed in the same manner. Relation paths containing a sequence of relations may also be displayed with additional information such as the existence of duplicate items in a destination view. One skilled in the art will recognize that any number of means to display relation paths and relation sequences may be included as part of the invention.
Another benefit of the present invention comes from providing the flexibility to display the relational abstraction of a data store in one or more ways. A relational abstraction may be displayed in a tree structure. A relational abstraction may be displayed in a list. One skilled in the art will recognize that any number of means may be used to display a relational abstraction in a hierarchical manner as part of the invention.
Another benefit of the present invention comes from displaying a sequence of relations that has been followed. By displaying the sequence of relations, the method and system apprises the user of the context associated with fields and relations available for selection. The sequence of relations may be displayed in a tree. The sequence of relations may be displayed in a list. The sequence of relations may be displayed using a natural language description. One skilled in the art will recognize that any number of means may be used to display a sequence or relations.
Another benefit of the present invention comes from providing means to help the user select fields appropriate to a particular purpose. Selectable fields may be limited to detail fields through the selection of a drop area. Selectable fields may be limited to measure fields through the selection of a button. Selectable fields may be limited to grouping fields through the selection of a menu item. One skilled in the art will recognize that any number of means may be used to display fields useful for particular purposes.
Other features and advantages of the present invention will be apparent from the following Detailed Description taken in conjunction with the accompanying Drawings, in which:
The present invention may be embodied in a computer database access and reporting system that displays selected database data based upon base views, and the fields and relations associated with those base views. Selected database data is displayed on a display surface according to row, column, summary, group and filter criteria chosen by a user. The display surface is typically an active window on a display device of a simple application program, but the display surface may alternately be a window of a web browser or any application program operable for displaying and manipulating data. The display surface is typically a monitor, but may alternately be a printer, flatscreen LCD display, television, and so on.
In one embodiment of the invention, a computer application includes a Query Construction Window 130 as depicted in
A user may also add columns to a report by selecting fields. Referring to
A user may also add row groupings to a report. To add row groupings, a user selects a group field from the list of fields in the Recursive Tree Structure 146 and drops the field in the Group Drop Area 142 by initiating a drag-and-drop command, or, provided the Group Drop Area 142 is active, by double-clicking the desired field, or by clicking the Arrow-Transfer-Button 156. The system captures this action by adding the field to the list of row groupings and by displaying the selected field name in the Group Drop Area 142.
A user may also add numeric summary or aggregation measures to a report. To add measures, a user selects an aggregation or measures field from the list of fields in the Recursive Tree Structure 146 and drops the field in the measures drop area by initiating a drag-and-drop command, or, provided the measures drop area is active, by double-clicking the desired field, or by clicking the Arrow-Transfer-Button 156. The system captures this action by adding the field to the list of measures and by displaying the selected field name in the Measures Drop Area 144.
Likewise, a user may add fields from related database views to a report. Typically, the Recursive Tree Structure 146 will include a list of relations defined in the relational abstraction. To add fields from a related view, a user initiates a double-click command on a relation. The system will respond by replacing the previously existing list of fields and relations in the Recursive Tree Structure 146 with a new list based upon the selected relation and the cardinality existing between the base view and the destination view of the relation. The user may then add fields from the Recursive Tree Structure 146 to the Column Group Area 143, Group Drop Area 142 or Measures Drop Area 144, as noted above.
Once a user has selected the desired fields to be grouped and displayed in a report, the user may choose to view a corresponding report. In the present invention, the list of fields and relations displayed for selection by a user is based upon a base view. Typically, the system will retrieve a list of tables and views from a database server and display them on a display surface. A user may then select one of the tables or views. Based upon the user's selection, the system will display a relational abstraction of all tables, views, fields and relations of the selected database table using the base view as a starting point.
As used herein, a “user” refers not only to a person using the present invention, but also to a program, application, operating system, function call, or any other entity that may make use of the present invention. Thus, an operating system that manipulates or otherwise employs the present invention may be classified as a user.
With reference to
Typically, the Desktop Computer 1 includes a Processing Unit 6, System Memory 7, and a System Bus 8 that couples the System Memory 7 to the Processing Unit 6. The System Memory 7 includes Read Only Memory (ROM) 9 and Random Access Memory (RAM) 10, and a Basic Input/Output System (BIOS) 11 that contains the basic routines that help to transfer information between elements within the Desktop Computer 1, such as during start-up, and the ROM 9. The Desktop Computer 1 further typically includes a Hard Disk Drive 12 connected to the System Bus 8. Hard Disk Drive 12 and its associated computer-readable media provide nonvolatile storage for the Desktop Computer 1. Although the description of computer-readable media above refers to a hard disk, it will be appreciated by those skilled in the art that other types of storage devices and media that are readable by a computer, such as a removable magnetic disk, a CD-ROM disk, a magnetic cassette, a flash memory card, a digital video disk, Bernoulli cartridge, and the like, may also be used included in, or attached to, the Desktop Computer 1.
A number of program modules may be stored in the Hard Disk Drive 12 and the RAM 10, including an Operating System 13, one or more Application Programs 14, a Web Browser Program 15, and Program Data 16. These program modules include a Data Query And Reporting User Application (“DQR Application”) 100 configured for implementing an embodiment of the present invention. A user may enter commands and information into the Desktop Computer 1 through conventional input devices such as a Keyboard 17 or a pointing device such as a Mouse 18. Other input devices (not shown) may include a pen, touch-operated device, microphone, joystick, game pad, satellite dish, scanner, or the like. A Display Device 19, such as a display screen, is also connected to the System Bus 8 via an interface. In addition to the Display Device 19, desktop computers typically include other peripheral output devices (not shown), such as speakers, scanners or printers.
The Application Server 2 and the Database Server 3 may be personal computers, minicomputers or mainframe computers, or another common application platform, and may also include many or all of the elements described relative to the Desktop Computer 1. The logical connections depicted in
When used in a typical networking environment, the Desktop Computer 1 is connected to the LAN 22 through a Network Interface Card 25. When used in a WAN networking environment, the Desktop Computer 1 typically includes a Modem 26 or other means for establishing communications over the WAN 24, such as the Internet. The Modem 26, which may be internal or external, is connected to the System Bus 8. In a networked environment, Application Programs 20, or portions thereof, may be executed on the Application Server 3 and stored in the server memory and storage devices. These application programs include a Data Query And Reporting Query Generation And Database Interface Application (“Query Engine”) 200 configured for implementing an embodiment of the present invention. Typically, the Query Engine 200 also includes an intermediate mapping or metadata layer that is used when communicating with a database server. It will be appreciated that the network connections shown are exemplary and other means of establishing a communications link between the computers may be used.
In the present example metadata, a “Customer View” Table 40 depicts a mapping between the sample Northwind database described in
According to one embodiment of the present invention, instances of the objects described in
As shown in
Table 72 of
As shown in
The join type and the join keys for the Orders Relation Object Table 76 are specified by a joinType=“LeftOuterJoin” property 80 and a <joinKey sourceColumn=“CustomerID” destColumn=“CustomerID” dataType=“Text”/> XML Tag 82, respectively. In this case, because the relationship is identified as a one-to-many relation, the join is specified as a left outer join. A left outer join of the Customer View 40 and the Order View 47 will include all records from the Customers Table 37 and the corresponding records in the Orders Table 34 where the CustomerID 38 and the CustomerID 38A are equal.
A Customer Relation Object Table 83 of
The present invention provides means to interactively and iteratively display fields for selection.
In the depicted embodiment, views may be organized into subfolders, such as a Lookup Folder 122. This facility is provided for databases having a large number of defined views. In the depicted embodiment of the present invention, a base view is set by selecting a view from a List 123 and either selecting a Finish Button 124 or double-clicking on the selected view. This base view, in conjunction with the associated metadata described in
A relational abstraction of the present invention may be exposed through a display system and according to rules of the invention enforcing proper display.
A Group By Box 139, a Details Box 140 and a Measures Box 141 are standard label boxes. The boxes below the Group By, Details and Measures areas of the Query Construction Window 130, numbered respectively 139, 140, and 141, are drop box areas where a user may drag or locate fields when building queries. Note that in the depicted embodiment, a Details Drop Box Area 143 has been selected by default, as denoted by the darker gray colored background surrounding the Details Label 140. With such selection, a user may select from a Selection Area 146 one or more fields from a Field Group 147 that pertain to an Employee View 49 (see
In the present example, a user may also follow relations between the Employee View 49 and the other views described in
Once a relational abstraction of a data store has been created, fields may be displayed for selection. In Step 184, a view is selected as the base view. In step 185, scalar fields and relations of a view are displayed. In step 186, a relation path sequence is defined from the base view. According to step 187, if the relation path is empty, the scalar fields and relations associated with the base view are displayed. If the relation path is not empty, processing continues to step 188, where it is determined if the relation path sequence contains a to-many relationship. According to step 188, if the relation path sequence does not contain a to-many relationship, the scalar fields and relations associated with a destination view are displayed. Also according to step 188, if the relation path contains a to-many relationship, processing proceeds to step 189.
In step 189, the system determines if another relationship follows a to-many relationship in the relation path sequence according to step 188. If another relationship does not follow a to-many relationship in the relation path sequence, aggregate fields and relations associated with a destination view are displayed according to step 190 and processing returns to step 186. If another relationship follows a to-many relationship in the relation path sequence according to step 188, processing proceeds to step 191 where a determination is made about what type of relation follows.
In step 191, if other than a many-to-one relation follows a to-many relation according to step 188, processing proceeds recursively to step 190 where the aggregate fields and relations associated with a destination view are displayed. If in step 191 a many-to-one relation follows a to-many relation in the relation path sequence according to step 188, processing proceeds to step 192 where aggregate fields in a distinct aggregate context and relations associated with a destination view are displayed and processing returns recursively to step 186 for further processing.
In step 202, the DQR Application 100 displays the name of the Northwind database described in
In Step 203, a user sequentially selects the Employee View 49 from the View Group List 123 and the Finish Button 124, which causes the YES branch of Step 203 to be followed. If a user does not select a view, the NO branch of Step 203 is followed and the DQR Application 100 continues to display the Window 96B of
In Step 204, the DQR Application 100 requests the fields and relations listed in the Employee View 49 from the Query Engine 200. In Step 205, the DQR Application 100 then displays the list of fields and relations of the Employee View 49 on the Display Device 19, displaying the Query Construction Window 130 described with reference to
In Step 206, the DQR Application 100 awaits user input in the form of selecting fields, such as those highlighted by the Balloon 147, or relations, such as those highlighted by Balloon 149, of
In Step 208, the DQR Application 100 monitors detail field selections. If a user does not select a detail field, the DQR Application 100 continues through the NO branch to Step 209. If a user selects a detail field, the DQR Application 100 proceeds through the YES branch to Step 212. In Step 212, the DQR Application 100 adds the name of the selected field to the Details Drop Box Area 143, and continues to Step 205 to display the fields and relations associated with the selected view.
In Step 209, the DQR Application 100 monitors group field selections. If a user selects a group field, the DQR Application 100 proceeds through the YES branch to Step 213. If a user does not select a group field, the DQR Application 100 continues through the NO branch to step 210.
In Step 210, the DQR Application 100 monitors measure field selections. If a user selects a measure field, the DQR Application 100 proceeds through the YES branch to Step 214. If a user does not select a measure field, the DQR Application 100 continues through the NO branch to Step 211.
In Step 211, the DQR Application 100 monitors the selection of relations. If a user selects a relation, the DQR Application 100 proceeds through the YES branch to Step 215. If a user does not select a relation, the DQR Application 100 continues through the NO branch to Step 205.
In Step 215, if the cardinality of the relation path ending with the selected relation is to-one, the DQR Application 100 follows the NO branch of Step 215 to Step 216. In Step 216, the DQR Application 100 retrieves the fields and relations associated with the followed relation and processing passes to Step 205. If the cardinality is to-many, the YES branch of Step 215 is followed to Step 217 where the DQR Application 100 limits retrieval of the fields associated with destination view to those fields that have a Field Type Property F07 (see
In Step 218, if at least one field has been added to the report, the YES branch is followed to Step 220, where the DQR Application 100 verifies and generates a suitable database query and displays the report on the Display Device 19. In Step 221, if the Fields Button 134 is selected, the YES branch is followed to step 205. Otherwise the process terminates.
In the present example, the Column Drop Area 285 has been selected, as depicted by the darker grey surrounding the Details Text Description 290. When selected, the fields displayed in the Available details: Box 270 are constrained to those fields available for adding columns, according to the logic described with reference to
In the present example, the relation path indicated in the Look in: Box 255 contains a to-many relation and a many-to-one relation. According to the logic described with reference to
It will be obvious to those of skill in the art that the invention described in this specification and depicted in the FIGURES may be modified to produce different embodiments of the present invention. Thus, the present invention has several advantages over the prior systems without sacrificing any of the advantages of those prior systems. Although two embodiments of the invention have been illustrated and described, various modifications and changes may be made by those skilled in the art without departing from the spirit and scope of the invention.
This application is a continuation-in-part of co-pending U.S. patent application Ser. No. 10/627,180 filed on Jul. 25, 2003 entitled “Method and System for Building a Report for Execution against a Data Store.” This prior application is incorporated herein by reference.
Number | Date | Country | |
---|---|---|---|
Parent | 10627180 | Jul 2003 | US |
Child | 10754288 | Jan 2004 | US |