Patent Application
20140280139
Traditional Structured Query Language (SQL) databases use unique tables to represent and describe data structures in relational database management systems. While SQL tables are very organized and uniform, next-generation databases are being developed that are non-relational and schema-free, such as Not Only SQL (NoSQL) databases, that have heterogeneous data structures in a single table.
NoSQL databases have emerged as cost-effective solutions for very large data sets. The heterogeneous, loosely structured nature of table row data in NoSQL databases complicates common database development tasks, such as data analysis and semantic error detection. One problem with existing tools for viewing and editing NoSQL databases is the lack of a way to distinguish between different entity types. Due to the volume (e.g., number of rows) and width of data (e.g., number of columns) described, it can be difficult to differentiate between semantically distinct data rows within a single table. For example, a NoSQL table may encapsulate two or more distinct data structures where each data structure has its own columns. Existing development tools cannot differentiate semantically distinct row data based on their column values. As a result, development tasks, such as analysis and error detection for NoSQL and other schema-less data, can be a considerable challenge in non-relational and schema-free databases.
Another problem with existing NoSQL databases is that the data is spread out so that it is difficult to see all of the data for a given entity on one screen. Many of the columns for a given row will be blank because those columns or properties are associated with other data structures in the same table. As a result, the user has to scroll horizontally through the database to see all of the properties for a selected row.
An additional problem caused by these blank cells is the creation of a lot of “white space” (i.e., unused or unneeded space) in the table. It can be difficult to visualize the state and meaning of a row representing a selected data structure if columns for other data structures are interspersed with the columns for the selected data structure. Large gaps between the properties for a given row make it difficult for the user to scan the table.
A further problem with the display of existing NoSQL databases is the use of uniform column widths regardless of the amount of space required by the property associated with each column. A property requiring few pixels (e.g., 1-10 characters) is assigned the same space as a property requiring very many pixels (e.g., 100+ characters). As a result, additional white space is added to the shorter property, and the longer property is likely only partially displayed, which may render it unreadable.
This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.
Embodiments provide a viewer/editor for schema-less data, such as a NoSQL database. The data structures are displayed so that each entity type in the data uses a different color and variable column widths. This allows the user to identify relationships between entities. For a selected entity, only the properties applicable to that entity are displayed by the viewer/editor. In addition to distinguishing between entity types so that the user can focus on selected data, the invention optimizes the column width of the display so that the user can see as much information as possible.
When a NoSQL database is selected, a detector analyzes the data row-by-row and identifies one or more entity types represented by the data. Each row is assigned to a particular entity type. The detector further determines an optimum column width for the data in each entity type. The database table is opened in a viewer/editor that assigns each row a color based upon its entity type. The viewer/editor removes unused properties from each row and displays the data using an optimized column width for each property. When a user selects a row, a header row is displayed using the appropriate properties for the selected row.
To further clarify the above and other advantages and features of embodiments of the present invention, a more particular description of embodiments of the present invention will be rendered by reference to the appended drawings. It is appreciated that these drawings depict only typical embodiments of the invention and are therefore not to be considered limiting of its scope. The invention will be described and explained with additional specificity and detail through the use of the accompanying drawings in which:
The presence or absence of one or more cell values may indicate a given row's data structure or schema and its intended semantics. For example, in table 100 rows representing Customers will include values for the FirstName, LastName, and Email columns, but will not include values for the OrderDate, OrderTotal, and Address columns. On the other hand, rows representing Orders will include values for the OrderDate, OrderTotal, and Address columns, will have no value for the FirstName, LastName, and Email columns.
In this simple example table 100, there are two entity types—customer and order—and only a few columns or properties for each entity type. However, it is likely that working tables will have additional data structures, such as employee, product, and billing data for this example. Also, each entity is likely to have more properties, which would require additional columns, such as additional columns for customer address, phone, gender, order history, and the like for this example. For each data structure, many of the columns will be blank because they are associated with other data structures. This makes it extremely difficult to visualize the state or meaning of a row representing a customer if the columns for other entities are interspersed with those pertinent to the customer.
Entity Type Detection
In step 205, the detector determines if the entity properties matches any of the known entity types. The matching process may operate under varying degrees of strictness. For example, an exact match may be required (e.g., identical properties arranged in the same order), or the detector may require some less strict overlap (e.g., a threshold percentage or minimum number of matching properties, or the properties appearing in any order).
If the entity properties do not match any known entity type in step 205, then the process moves to step 206 where the detector creates a new entity type. In step 207, the detector selects visualization properties for the new entity type. The visualization characteristics may be a color, for example, that is used to indicate the entity type for each entity in the table. In step 208, the detector adds the new entity type to the list of known entity types used above in step 204. In step 209, the detector then designates the selected entity as the new entity type and, in step 210, assigns the visualization characteristics to the entity. The process then returns to step 202 to select a next entity in the table.
Table 300 of
For example, the detector selects row 101 as the first entity and then identifies the entity's properties (step 203) and compares them to properties for known entity types (step 204). If the entity's properties do not match known types (step 205), which would be the case for the first entity in the table, then the detector creates a new entity type (step 206). The detector also selects visualization characteristics for the new entity type (step 207). The information for the new entity type is added to the list of known entities (step 208). The detector then designates the row as entity Type 1 (step 209) and applies the visualization characteristics to the entity (step 210). For example, row 301 in
A new entity type property may be added to each row in the table. Each unique entity type is assigned a different color so that rows of the same type are easily identified in the table. The detector determines entity types for each row based on the properties of an entity. So, after processing row 101/301, the detector continues to the next entity—row 102—and continues identifying entity types. If two entities have the same properties, then those entities are considered to be the same type by the detector. Any entity that has a different set of properties than a previously indexed entity is assigned a different entity type by the detector. Accordingly, the detector will match the properties for row 102 to the properties for entity Type 1 (step 205). The detector will then designate row 102 as entity Type 1 (step 211) and assign row 102 with the characteristics of entity Type 1 (step 212). Table 300 shows row 302 after processing by the detector with entity Type 1 in column 312 and is highlighted with the visualization characteristics of entity Type 1.
The detector continues through the table and identifies the entity type for rows 103-111 and assigns them the appropriate Type (312) and visual characteristics shown in rows 303-311.
In one embodiment, the determination of the entity types may be based upon the name of each property. The property name may be found in a header row, for example. The detector may use varying degrees of strictness when matching property names. For example, the detector may or may not consider the capitalization, spelling, punctuation, and font of the property name when comparing two properties.
In addition to using property names, the detector may use the data type for each property (e.g., Boolean, string, integer, or any other data type assigned to the property) to identify and distinguish entity types. For example, if two properties have the same name, then the entity types for those properties could be distinguished from each other by looking at the data type of each property. For example, if a table includes one column labeled “gender” with a string data type (e.g., populated with the values “male” or “female”) and another column also labeled “gender” but with an integer data type (e.g., populated with “0” for males and “1” for females), then the data type for each property can be used to further distinguish beyond the name of the property. In a database that does not have named properties (i.e., no header row or column names), the data types of the properties or columns may be used to detect separate entity types.
In other embodiments, in addition to using property names, the detector may consider the order of properties to determine entity type. For example, two separate entity types may have the similarly named properties; however, these properties may have been entered at different times or may have originated from different sources. The order of the properties as they appear in the table may be used to distinguish between the entity types. Alternatively or additionally, the order in which the properties were added to the table or were collected may be used to determine the entity type.
The presence of null values versus non-null values may also be used to determine entity types. Null values may be treated as if the property exists in the entity in one embodiment, but in other embodiments null values are treated as if the entity does not have the property. Depending upon whether or not the null value is considered to be part of the entity, the detector determines how to assign an entity type to that entity.
Entity Type Vizualization
Referring to table 100, row 101 has the properties: Key, FirstName, LastName, and Email. This entity is marked as entity type one by the detector and is assigned a unique color. Row 101 will be displayed by a viewer/editor with the unique color as shown in
The detector analyzes row 104 next and identifies the properties: Key, OrderDate, OrderTotal, and Address. This entity is marked as a new entity type—entity type two—and it is assigned a new unique color as shown in
Proceeding through the table, the detector assigns each row to an existing or new entity type and assigns the appropriate color for that entity type. As a result, in table 300, rows 101-103, 106, 107, and 111 are designated as entity type one and are assigned the shading appropriate for entity type one. Rows 104, 105, 108, and 109 are designated as entity type two and are assigned the shading appropriate for entity type two.
Row 110 includes the properties: Key, OrderDate, and OrderTotal. While this group of properties is similar to entity type two, it is missing the Address property and, therefore, is designated as entity type three with its own unique color as shown in
Once all of the rows have been designated as the appropriate entity type by the detector, at runtime a viewer/editor displays the table and the assigned colors so that the user can easily identify the different data structures that are supported by table 100. This also allows the user to more easily review and analyze the data for the different entities. The entity Type property 312 may also be displayed to the user, which allows the user to filter and sort the data by entity type.
The entity Type property 312 may be any designation, such as a word or text as shown in
A user may determine that this new entity type 3 was created solely as a result of missing data in the Address field—i.e. otherwise row 110 would have been designated as and colored like entity type two. In one embodiment, the user may manually re-designate row 110 as entity type two so that it appears like the other Order data. In other embodiments, the detection algorithm may allow for variations in the properties required by a given entity type. For example, the user may identify a particular property that can be ignored when distinguishing between entity types. As a result, it would not matter whether or not that property is present in an entity when determining the entity type. In other embodiments, the entity type detection may not require an exact property match, but may consider two entities to be of the same type if they have at least a threshold number or percentage of overlapping properties.
In other embodiments, the designation of new entity type three may alert the user to other issues, such as, for example, a data-collection problem. The Address field in row 110 may be missing due to an incomplete order form, broken order web page link, or an untrained employee. Therefore, the designation of a separate entity type three may provide useful information for the user to investigate or otherwise act upon.
For purposes of illustration, tables 100 and 300 are shown with dummy data in the properties for each entity type. This dummy data is displayed in
The viewer/editor may have the option of collapsing the displayed columns so that only the properties relevant to a selected entity are shown. This would improve the visual display for the user when viewing tables by removing blank columns or properties, for example. This would also eliminate excessive blank space 403 that is created by areas of empty cells in table 400.
Width Optimization
To address the problems illustrated by the tables shown in
Although the rows for a selected entity type may be compressed to improve the display, rows corresponding to other entity types (i.e., non-selected rows) may or may be compressed. The properties for the non-selected entity types are not shown in the header row 602. The rows for non-selected entity types are further distinguished by their assigned color or highlighting or with another unique identifier.
The rows for other entity types, such as rows 654 for entity type one and row 605 for entity type three, are also compressed, in one embodiment, but the properties for these rows are not shown in the header row 602. The rows 654, 605 for non-selected entity types are still shown in their assigned color or highlighting.
In step 701, the number of columns to be displayed n is determined. Initially, the value of n is the total number of properties for the entity type. In step 702, the total width available on the display is determined. The total width t may correspond to the entire screen width or may be a window, box, or other designated section of the display. The value of t depends on the width of the overall application window for the viewer/editor and may be defined as a number of pixels, inches, millimeters, etc.
In step 703, the width required per column wr is calculated for each property. The width required per column wr is the width required to display the characters for a given property. For example, in a date field with the format mm/dd/yyyy, the width required for that property is the number of pixels (or inches or millimeters) required to display ten characters, or in a gender field with the options “male” or “female,” the width required is the number of pixels required to display six characters. The required width may be determined based upon the length of the longest data value actually present for a property in an existing entity. For example, if the entries in a gender property are all “male,” then the width required is the number of pixels need to display just four characters. In other embodiments, the required width may be the longest possible value for a property whether or not the longest value is actually present. Using the previous example, where all entries in a gender property are “male,” then the width required may be the number of pixels need to display six characters even if “female” never appears in the column.
In step 704, a default width wa of the columns to be displayed is calculated. The default width may be calculated as:
w
a
=t/n (Eq. 1)
In step 705, a decision is made to determine if the width required wr for any of the columns is less than the default width wa. If no column requires less space than the default width, then in step 706, the remaining columns are all assigned to the default width or to a percentage of the remaining available width. For example, the remaining columns may be distributed evenly or based on their relative size. As much data may be shown in each cell as fits within the default width assigned in step 706.
If one or more columns require less space than the default width, then in step 707, a group of columns are designated as group d where each column x in group d satisfies the requirement:
w
rx
<w
a (Eq. 2)
The columns in group d require less space than the default column width. Accordingly, if the column was assigned the default width, space would be wasted. Instead, in step 708, the width of each column x in group d is set to the width required for that column:
W
dx
=w
rx (Eq. 3)
In this way, each column in group d is allocated only the space required, which allows the excess space from group d to be redistributed to the other columns that required as much or more than the initial default column width.
In step 709, the total remaining width t on the display is calculated. This is equal to the initial width less the widths assigned to each of the columns in group d or:
t=t−(wd1+wd2+ . . . +wdm) (Eq. 4)
where there were m columns in the initial group d.
In step 710, the remaining number of columns n to be displayed is determined. This is equal to the initial number of columns less the number m of columns in group d or:
n=n−m (Eq. 5)
The process then returns to step 704 and, using the updated values oft and n, steps 704-710 are repeated as necessary to redistribute the remaining display space to the columns.
The following example may be used to illustrate the width optimization process described above. A table includes four columns with properties that require (i.e., wrx) 200, 250, 300 and 400 pixels, respectively, and the available display area (i.e., t) is 1000 pixels.
In this case, the number of columns to be displayed is four, so n=4. The default width is determined as: wa=t/n=1000/4=250 pixels.
Analyzing the required widths (wrx) for each property, it is observed that the first column (wr1=200) meets the test wrx<wa. Accordingly, column 1 is designated as group d and the width for column 1 is set to the required width wr1=200 pixels.
The total remaining width available on the display is now t=1000−200=800 pixels and there are three remaining columns.
The default width for the remaining columns is determined as: wa=t/n=800/3=266 pixels. Analyzing the required widths (wrx) for each remaining property, it is observed that the second column (wr2=250) meets the test wrx<wa. Accordingly, column 2 is designated as group d and the width for column 2 is set to the required width: wr2=250 pixels.
The total remaining width available on the display is now t=1000−200−250=550 pixels and there are two remaining columns.
The default width for the remaining columns is determined as: wa=t/n=550/2=275 pixels. Analyzing the required widths (wrx) for each remaining property, it is observed that no column meets the test wrx<wa. Accordingly, the widths for both remaining columns 3 and 4 are set to the default width: wr3, wr4, =275 pixels.
In this way, the unneeded width that would have been used by column 1 has been redistributed to columns 3 and 4.
In step 804, one or more visualization characteristics are applied to each entity in the table. For example, an entity type property (312) may be assigned to each row in the table and/or each row may be highlighted or shown in a color assigned to the associated entity type.
In step 805, a user display is generated for a table comprising heterogeneous data. The table may be, for example, a NoSQL or schema-less table. In step 806, a user selection of an entity is detected. The entity type for the selected entity is identified in step 807. In step 808, the header row is modified by removing the properties that are not associated with the selected entity type and assigning the unique identifier for the selected entity type to the header row.
It will be understood that steps 701-710 of the process illustrated in
The invention may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, and so forth, which perform particular tasks or implement particular abstract data types. The invention may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in local and/or remote computer storage media including memory storage devices.
With reference to
The computer 900 typically includes a variety of computer-readable media 904. Computer-readable media 904 may be any available media that can be accessed by the computer 900 and includes both volatile and nonvolatile media, and removable and non-removable media, but excludes propagated signals. By way of example, and not limitation, computer-readable media 904 may comprise computer storage media and communication media. Computer storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules or other data. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can accessed by the computer 900. Communication media typically embodies computer-readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media. The term “modulated data signal” means a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communication media includes wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared and other wireless media. Combinations of the any of the above may also be included within the scope of computer-readable media. Computer-readable media may be embodied as a computer program product, such as software stored on computer storage media.
The data storage or system memory 902 includes computer storage media in the form of volatile and/or nonvolatile memory such as read only memory (ROM) and random access memory (RAM). A basic input/output system (BIOS), containing the basic routines that help to transfer information between elements within computer 900, such as during start-up, is typically stored in ROM. RAM typically contains data and/or program modules that are immediately accessible to and/or presently being operated on by processing unit 901. By way of example, and not limitation, data storage 902 holds an operating system, application programs, and other program modules and program data.
Data storage 902 may also include other removable/non-removable, volatile/nonvolatile computer storage media. By way of example only, data storage 902 may be a hard disk drive that reads from or writes to non-removable, nonvolatile magnetic media, a magnetic disk drive that reads from or writes to a removable, nonvolatile magnetic disk, and an optical disk drive that reads from or writes to a removable, nonvolatile optical disk such as a CD ROM or other optical media. Other removable/non-removable, volatile/nonvolatile computer storage media that can be used in the exemplary operating environment include, but are not limited to, magnetic tape cassettes, flash memory cards, digital versatile disks, digital video tape, solid state RAM, solid state ROM, and the like. The drives and their associated computer storage media, described above and illustrated in
A user may enter commands and information through a user interface 905 or other input devices such as a tablet, electronic digitizer, a microphone, keyboard, and/or pointing device, commonly referred to as mouse, trackball or touch pad. Other input devices may include a joystick, game pad, satellite dish, scanner, or the like. Additionally, voice inputs, gesture inputs using hands or fingers, or other natural user interface (NUI) may also be used with the appropriate input devices, such as a microphone, camera, tablet, touch pad, glove, or other sensor. These and other input devices are often connected to the processing unit 901 through a user input interface 905 that is coupled to the system bus 903, but may be connected by other interface and bus structures, such as a parallel port, game port or a universal serial bus (USB). A monitor 906 or other type of display device is also connected to the system bus 903 via an interface, such as a video interface. The monitor 906 may also be integrated with a touch-screen panel or the like. Note that the monitor and/or touch screen panel can be physically coupled to a housing in which the computing device 900 is incorporated, such as in a tablet-type personal computer. In addition, computers such as the computing device 900 may also include other peripheral output devices such as speakers and printer, which may be connected through an output peripheral interface or the like.
The computer 900 may operate in a networked or cloud-computing environment using logical connections 907 to one or more remote devices, such as a remote computer. The remote computer may be a personal computer, a server, a router, a network PC, a peer device or other common network node, and typically includes many or all of the elements described above relative to the computer 900. The logical connections depicted in
When used in a networked or cloud-computing environment, the computer 900 may be connected to a public or private network through a network interface or adapter 907. In some embodiments, a modem or other means for establishing communications over the network. The modem, which may be internal or external, may be connected to the system bus 903 via the network interface 907 or other appropriate mechanism. A wireless networking component such as comprising an interface and antenna may be coupled through a suitable device such as an access point or peer computer to a network. In a networked environment, program modules depicted relative to the computer 900, or portions thereof, may be stored in the remote memory storage device. It may be appreciated that the network connections shown are exemplary and other means of establishing a communications link between the computers may be used. Additional data storage, such as cloud-based storage, may be accessed via network interface 907.
Although the subject matter has been described in language specific to structural features and/or methodological acts, it is to be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or acts described above. Rather, the specific features and acts described above are disclosed as example forms of implementing the claims.
