Generate data visualizations of multi-fact datasets using appropriate metric aggregations

Abstract

The computer receives a visual specification that specifies a data source, visual variables, and data fields from the data source. Each visual variable is associated with a data field (e.g., a dimension and / or a measure) or a filter. The computer obtains a data model that encodes the data source as a tree of related logical tables. Each logical table includes logical fields, each of which corresponds to a data field or to a calculation that spans the logical tables. The computer generates dimension subqueries for the dimensions and filters. The computer also generates an aggregate measure subquery grouped by dimension for each measure. The computer forms a final query by joining the dimension subqueries to each of the aggregate measure subqueries. The computer then executes the final query and displays a data visualization based on the results of the final query.

Description

Technical Field

[0001] The disclosed embodiments relate generally to data visualization, and more particularly to interactive visual analysis of data sets using an object model of the data sets.

[0002] background

[0003] Data visualization applications enable users to visually understand datasets, including distributions, trends, outliers, and other factors important for making business decisions. Some data elements are calculated based on data from a selected dataset. For example, data visualizations often use sums to aggregate data. Some data visualization applications enable users to specify the "level of detail" (LOD) that can be used for aggregate calculations. However, specifying a single LOD for a data visualization is not sufficient to create some calculations.

[0004] Some data visualization applications provide user interfaces that enable users to create visualizations from data sources by selecting data fields and placing them within specific user interface areas to indirectly define the data visualization. See, for example, U.S. patent application Ser. No. 10 / 453,834, filed Jun. 2, 2003, entitled “Computer Systems and Methods for the Query and Visualization of Multidimensional Databases” (now U.S. Pat. No. 7,089,266), which is incorporated herein by reference in its entirety. However, when there are complex and / or multiple data sources, it may not be clear what type of data visualization, if any, to generate based on the user's selections.

[0005] Additionally, some system-constructed queries produce data visualizations that are not what the user expected. In some cases, some data rows are omitted (for example, when there is no corresponding data in one of the fact tables). In some cases, totals produced by numeric aggregate fields are inflated because the same data value is counted multiple times. These issues can be particularly problematic because end users may not be aware of the problem and / or do not know what caused it.

[0006] Overview

[0007] Generating data visualizations that combine data from multiple tables can be challenging, especially when there are multiple fact tables. In some cases, it can be beneficial to build an object model of the data before generating the visualization. In some instances, a single person is a specific expert on the data, and this person creates the object model. By storing relationships in the object model, the data visualization application can leverage this information to assist all users who access the data, even those who are not experts.

[0008] An object is a collection of named properties. Objects often correspond to real-world objects, events, or concepts, such as stores. Properties are descriptions of an object that conceptually have a 1:1 relationship with it. Thus, a store object might have a single [Manager Name] or [Employee Count] associated with it. At the physical level, objects are often stored as rows in relational tables or as objects in JSON.

[0009] A class is a collection of objects that share the same properties. Comparing objects within a class and aggregating them must be analytically meaningful. At the physical level, classes are often stored as relational tables or as arrays of objects in JSON.

[0010] An object model is a collection of classes and a collection of many-to-one relationships between them. Classes related by a one-to-one relationship are conceptually treated as a single class, even if they appear distinct to the user. Furthermore, classes related by a one-to-one relationship can be presented as distinct classes in data visualization user interfaces. By adding an association table to capture the relationship, a many-to-many relationship is conceptually split into two many-to-one relationships.

[0011] Once the object model is built, the data visualization application can assist the user in various ways. In some embodiments, based on the data fields that have been selected and placed on a shelf in the user interface, the data visualization application can recommend additional fields or restrict what actions can be taken to prevent unusable combinations. In some embodiments, the data visualization application allows the user considerable freedom in selecting fields and uses the object model to build one or more data visualizations based on the user's selections.

[0012] According to some embodiments, a method generates a data visualization. The method is performed at a computer having one or more processors and memory. The memory stores one or more programs configured to be executed by the one or more processors. The computer receives a visual specification specifying a data source, a plurality of visual variables, and a plurality of data fields from the data source. Each visual variable is associated with (i) one or more corresponding data fields or (ii) one or more filters, and each of the data fields is identified as a dimension or a measure. The computer obtains a data model (or object model) that encodes the data source as a tree of logical tables. Each logical table has its own physical representation and includes one or more logical fields. Each logical field corresponds to a data field or a calculation that spans one or more logical tables. Each edge of the tree connects two related logical tables. The computer generates a dimension subquery based on the logical tables that provide the data fields for the dimensions and filters. The computer also generates an aggregated measure subquery for each measure, grouped by dimension, based on the logical tables that provide the data fields for each measure and filter. The computer forms a final query by joining the dimension subquery to each of the aggregated measure subqueries using the dimensions. The computer then performs a final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields. The computer then creates and displays a data visualization based on the data fields in the tuples and based on the visual variables associated with each of the data fields.

[0013] In some embodiments, a computer generates each aggregated metric subquery by performing a series of operations. The computer computes a metric subtree of a tree of logical tables. The metric subtree is the minimum subtree required to provide the data fields for the corresponding metric. The computer also computes a dimension-filter subtree of a tree of logical tables. The dimension-filter subtree is the minimum subtree required to provide all physical inputs for dimensions and filters. When a dimension-filter subtree does not share any logical tables with a metric subtree, the computer adds the adjacent logical table from the metric subtree to the dimension-filter subtree. The computer compiles the metric subtree to obtain a metric join tree, and compiles the dimension-filter subtree to obtain a dimension-filter join tree. The computer layers calculations and filters on the metric join tree and the dimension-filter join tree to obtain an updated metric subtree and an updated dimension-filter subtree, respectively. The computer de-duplicates the updated dimension-filter subtree by applying a group-by operation using the dimensions and link fields, the link fields including (i) keys from the relationships between the logical tables and (ii) calculated data fields shared with the measure subtree, to obtain a de-duplicated dimension-filter subtree. The computer combines the de-duplicated dimension-filter subtree with the updated measure subtree to obtain an aggregate measure subquery.

[0014] In some implementations, the computer compiles the metric subtrees by inner joining the logical tables in the metric subtrees to obtain a metric join tree.

[0015] In some implementations, a computer calculates a dimension-filter subtree by performing a series of operations: the computer inner joins logical tables shared with the metric subtree in the dimension-filter subtree, and left joins (also referred to as a left outer join) logical tables not shared with the metric subtree in the dimension-filter subtree, to obtain a dimension-filter join tree.

[0016] In some embodiments, a computer combines the deduplicated dimension-filter subtree with the updated metric subtree by performing a series of operations. The computer determines whether the deduplicated dimension-filter subtree contains a filter. When the deduplicated dimension-filter subtree contains a filter, the computer performs an inner join on the updated metric subtree with the deduplicated dimension-filter subtree. When the deduplicated dimension-filter subtree does not contain a filter, the computer performs a left outer join on the updated metric subtree with the deduplicated dimension-filter subtree.

[0017] In some embodiments, a computer determines whether the key indicates a many-to-one relationship or a one-to-one relationship between the first logical table and the second logical table. When the key indicates a many-to-one relationship between the first logical table and the second logical table, the computer includes the first table and the second table in the metric subtree, thereby avoiding grouping in a deduplication operation on the first logical table and the second logical table.

[0018] In some embodiments, when the dimension-filter subtree joins the metric subtree only along many-to-one links and one-to-one links, the computer replaces the table shared by the metric subtree and the dimension-filter subtree with the deduplicated dimension-filter subtree.

[0019] In some implementations, the computer generates the dimension subquery by inner joining the first one or more logical tables in the tree of logical tables. Each of the first one or more logical tables provides data fields for the dimension and / or filter.

[0020] In some implementations, the computer forms the final query by joining the dimension subquery and the aggregate measure subquery across dimensions using outer joins and applying COALESCE after each outer join.

[0021] In some implementations, when the visualization has no dimensions, the computer performs a full join between the aggregated metric subqueries to form the final query.

[0022] According to some embodiments, a system for generating data visualizations includes one or more processors, a memory, and one or more programs stored in the memory. The programs are configured to be executed by the one or more processors. The programs include instructions for performing any of the methods described herein.

[0023] According to some embodiments, a non-transitory computer-readable storage medium stores one or more programs configured to be executed by a computer system having one or more processors and a memory. The one or more programs include instructions for executing any of the methods described herein.

[0024] Thus, methods, systems, and graphical user interfaces are provided for interactive visual analysis of data sets. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] For a better understanding of the aforementioned embodiments as well as additional embodiments of the present invention, reference should be made to the following description of the embodiments taken in conjunction with the accompanying drawings, wherein like reference numerals refer to corresponding parts throughout.

[0027] Figure 1 The process of creating a data visualization according to some embodiments is conceptually illustrated.

[0028] Figure 2 is a block diagram of a computing device according to some implementations.

[0029] Figure 3 is a block diagram of a data visualization server according to some embodiments.

[0030] Figure 4 An example data visualization user interface is provided according to some implementations.

[0031] Figure 5A An example data model (or object model) is shown in accordance with some implementations.

[0032] Figure 5B Data visualization according to some embodiments is shown.

[0033] Figure 5C Data visualization according to some embodiments is shown.

[0034] Figure 6A An example data model or object model is shown in accordance with some implementations.

[0035] Figure 6B Data visualization according to some embodiments is shown.

[0036] Figure 7 An example query is shown in accordance with some implementations.

[0037] Figure 8A Data visualization according to some embodiments is shown.

[0038] Figure 8B An example query is shown in accordance with some implementations.

[0039] Figure 9A Data visualization according to some embodiments is shown.

[0040] Figure 9B An example query is shown in accordance with some implementations.

[0041] Figure 10A and Figure 10B An example query is shown in accordance with some implementations.

[0042] Figure 11A Data visualization according to some embodiments is shown.

[0043] Figure 11B An example query is shown in accordance with some implementations.

[0044] Figure 11C-11F Data visualization according to some embodiments is shown.

[0045] Figure 12A Data visualization according to some embodiments is shown.

[0046] Figures 12B-12D An example query is shown in accordance with some implementations.

[0047] Figures 13A-13D A flow chart of a method for generating a data visualization using an object model according to some implementations is provided.

[0048] Like reference numerals designate corresponding parts throughout the drawings.

[0049] Reference will now be made in detail to embodiments, examples of which are illustrated in the accompanying drawings. In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the present invention. However, it will be apparent to one of ordinary skill in the art that the present invention may be practiced without these specific details.

[0050] Description of Embodiments

[0051] Some embodiments of the interactive data visualization application use the data visualization user interface 102 to establish a visual specification 104, such as Figure 1As shown. The visual specification identifies one or more data sources 106 that may be stored locally (e.g., on the same device displaying the user interface 102) or externally (e.g., on a database server or in the cloud). The visual specification 104 also includes visual variables. The visual variables indirectly specify the characteristics of the desired data visualization based on selected data fields from the data sources 106. In particular, the user assigns zero or more data fields to each visual variable, and the values ​​of the data fields determine the data visualization that will be displayed.

[0052] In most instances, not all visual variables are used. In some instances, some visual variables have two or more assigned data fields. In this scenario, the order of the visual variable's assigned data fields (e.g., the order in which the data fields are assigned to the visual variable by the user) often affects how the data visualization is generated and displayed.

[0053] Some embodiments use an object model 108 (sometimes referred to as a data model) to create appropriate data visualizations. In some instances, the object model applies to a single data source (e.g., a SQL database or a spreadsheet file), but an object model can encompass two or more data sources. Typically, unrelated data sources have different object models. In some instances, the object model closely mimics the data model of the physical data source (e.g., a class in the object model corresponds to a table in a SQL database). However, in some cases, the object model is more (or less) normalized than the physical data source. The object model groups attributes (e.g., data fields) that have a one-to-one relationship with each other into classes and identifies many-to-one relationships between classes. In the diagrams below, many-to-one relationships are illustrated with arrows, with the "many" side of each relationship vertically lower than the "one" side of the relationship. The object model also identifies each data field (attribute) as either a dimension or a measure. Below, the letter "D" (or "d") is used to indicate a dimension, while the later "M" (or "m") is used to indicate a measure. When object model 108 is built, it can facilitate building data visualizations based on data fields selected by the user.Because a single object model can be used by an unlimited number of other people, building an object model for a data source is typically delegated to someone who is an expert on the data source.

[0054] When a user adds data fields to a visual specification (e.g., indirectly by placing data fields on a shelf using a graphical user interface), the data visualization application 222 (or web application 322) groups 110 the user-selected data fields together according to the object model 108. Such groups are referred to as data field sets. In many cases, all user-selected data fields are in a single data field set. In some instances, there are two or more data field sets. Each metric m is in exactly one data field set, but each dimension d can be in more than one data field set.

[0055] The data visualization application 222 (or web application 322) queries (112) the data source 106 for a first set of data fields and then generates a first data visualization 122 corresponding to the retrieved data. The first data visualization 122 is constructed based on the visual variables 282 in the visual specification 104, which have data fields 284 assigned to them from the first set of data fields. When there is only one set of data fields, all of the information in the visual specification 104 is used to build the first data visualization 122. When there are two or more sets of data fields, the first data visualization 122 is based on a first visual sub-specification consisting of all of the information associated with the first set of data fields. For example, assume that the original visual specification 104 includes a filter that uses data field f. If field f is included in the first set of data fields, then the filter is part of the first visual sub-specification and is therefore used to generate the first data visualization 122.

[0056] When the second (or subsequent) set of data fields exists, the data visualization application 222 (or web application 322) queries 114 the data source 106 for the second (or subsequent) set of data fields and then generates a second (or subsequent) data visualization 124 corresponding to the retrieved data. The data visualization 124 is constructed according to the visual variables 282 in the visual specification 104, which have the data fields 284 assigned to them from the second (or subsequent) set of data fields.

[0057] Figure 2is a block diagram illustrating a computing device 200 that can execute a data visualization application 222 or a data visualization web application 322 to display a data visualization 122. In some embodiments, the computing device displays the graphical user interface 102 of the data visualization application 222. The computing device 200 includes desktop computers, laptop computers, tablet computers, and other computing devices with a display and a processor capable of running the data visualization application 222. The computing device 200 generally includes one or more processing units / cores (CPUs) 202 for executing modules, programs, and / or instructions stored in a memory 214 and thereby performing processing operations; one or more network or other communication interfaces 204; memory 214; and one or more communication buses 212 for interconnecting these components. The communication bus 212 may include circuitry for interconnecting and controlling communications between system components. The computing device 200 includes a user interface 206, which includes a display 208 and one or more input devices or mechanisms 210. In some embodiments, the input device / mechanism includes a keyboard; in some embodiments, the input device / mechanism includes a "soft" keyboard that is displayed on the display 208 as needed, allowing the user to "press" "keys" that appear on the display 208. In some embodiments, the display 208 and the input device / mechanism 210 include a touch screen display (also known as a touch-sensitive display). In some embodiments, the display is an integral part of the computing device 200. In some embodiments, the display is a separate display device.

[0058] In some embodiments, the memory 214 includes a high-speed random access memory, such as DRAM, SRAM, DDRRAM, or other random access solid-state memory devices. In some embodiments, the memory 214 includes a non-volatile memory, such as one or more magnetic disk storage devices, optical disk storage devices, flash memory devices, or other non-volatile solid-state storage devices. In some embodiments, the memory 214 includes one or more storage devices located away from the CPU 202. The memory 214 or alternatively the non-volatile memory device within the memory 214 includes a non-transitory computer-readable storage medium. In some embodiments, the memory 214 or the computer-readable storage medium of the memory 214 stores the following programs, modules, and data structures, or a subset thereof:

[0059] Operating system 216, which includes processes for handling various basic system services and for performing hardware-related tasks;

[0060] A communications module 218 for connecting the computing device 200 to other computers and devices via one or more communications network interfaces 204 (wired or wireless) and one or more communications networks (e.g., the Internet, other wide area networks, local area networks, metropolitan area networks, etc.);

[0061] A web browser 220 (or other client application), which enables a user to communicate with a remote computer or device over a network;

[0062] A data visualization application 222 that provides a graphical user interface 102 for enabling a user to construct visual graphics (e.g., a single data visualization or a dashboard with multiple related data visualizations). In some embodiments, the data visualization application 222 executes as a standalone application (e.g., a desktop application). In some embodiments, the data visualization application 222 executes within the web browser 220 (e.g., as a web application 322);

[0063] A graphical user interface 102 that enables a user to create data visualizations by visually specifying elements, as shown below. Figure 4 As shown in;

[0064] In some embodiments, the user interface 102 includes a plurality of shelf areas 250 for specifying the characteristics of a desired data visualization. In some embodiments, the shelf area 250 includes a column shelf 230 and a row shelf 232 for specifying the arrangement of data in the desired data visualization. Typically, fields placed on the column shelf 230 are used to define the columns in the data visualization (e.g., the x-coordinates of the visual markers). Similarly, fields placed on the row shelf 232 define the rows in the data visualization (e.g., the y-coordinates of the visual markers). In some embodiments, the shelf area 250 includes a filter shelf 262 that enables a user to limit the data being viewed based on selected data fields (e.g., limiting the data to rows where a particular field has a particular value or has values ​​within a particular range). In some embodiments, the shelf area 250 includes a tag shelf 264 for specifying various encodings for data tags. In some embodiments, the markup tool shelf 264 includes a color coding icon 270 (to specify the color of a data marker based on the data field), a size coding icon 272 (to specify the size of a data marker based on the data field), a text coding icon (to specify a label associated with a data marker), and a view level detail icon 228 (to specify or modify the level of detail of the data visualization);

[0065] a visual specification 104 that defines the characteristics of a desired data visualization. In some embodiments, the visual specification 104 is established using the user interface 102. The visual specification includes an identified data source 280 (i.e., specifies what the data source is), which provides sufficient information to locate the data source 106 (e.g., a data source name or a network full path name). The visual specification 104 also includes visual variables 282 and assigned data fields 284 for each visual variable. In some embodiments, the visual specification has a visual variable corresponding to each shelf area 250. In some embodiments, the visual variables also include other information, such as contextual information about the computing device 200, user preference information, or other data visualization features not implemented as shelf areas (e.g., analytical features);

[0066] One or more object models 108 that identify the structure of the data source 106. In the object model, data fields (attributes) are organized into classes, where the attributes in each class have a one-to-one correspondence with each other. The object model also includes many-to-one relationships between classes. In some instances, the object model maps each table in the database to a class, with the many-to-one relationships between classes corresponding to foreign key relationships between the tables. In some instances, the data model of the underlying source does not map cleanly to the object model in this simple manner, so the object model includes information that specifies how to convert the raw data into objects of the appropriate class. In some instances, the raw data source is a simple file (e.g., a spreadsheet) that is converted into multiple classes;

[0067] Data visualization generator 290 generates and displays data visualizations according to visual specifications. According to some embodiments, data visualization generator 290 uses object model 108 to generate queries 294 (e.g., dimension subqueries, aggregate measure subqueries, and / or final queries) and / or optimizes queries using query optimizer 292. According to some embodiments, referring to Figures 5A-11F To describe the details of query generation and optimization techniques.

[0068] Visualization parameters 236 , which contain information used by the data visualization application 222 rather than information provided by the visual specification 104 and the data source 106 ; and

[0069] Zero or more databases or data sources 106 (e.g., first data source 106-1) that are used by the data visualization application 222. In some implementations, the data sources can be stored as spreadsheet files, CSV files, XML files, flat files, JSON files, tables in a relational database, a cloud database, or a statistical database.

[0070] Each in the executable module, application or process concentration identified above can be stored in one or more of the aforementioned memory devices, and corresponds to the set of instructions for performing the above functions. The modules or programs (that is, instruction sets) identified above do not need to be implemented as independent software programs, processes or modules, and therefore the various subsets of these modules can be combined or otherwise rearranged in various embodiments. In some embodiments, memory 214 stores the modules and data structures identified above. In some embodiments, memory 214 can store additional modules or data structures not described above.

[0071] although Figure 2 Computing device 200 is shown, but Figure 2 It is intended more as a functional description of various features that may be present rather than as a schematic diagram of the architecture of the embodiments described herein. In practice and as recognized by one of ordinary skill in the art, items shown separately may be combined and some items may be separated.

[0072] Figure 3 3 is a block diagram of a data visualization server 300 according to some embodiments. The data visualization server 300 can host one or more databases 328 or can provide various executable applications or modules. The server 300 typically includes one or more processing units / cores (CPUs) 302, one or more network interfaces 304, memory 314, and one or more communication buses 312 for interconnecting these components. In some embodiments, the server 300 includes a user interface 306, which includes a display 308 and one or more input devices 310 (e.g., a keyboard and mouse). In some embodiments, the communication bus 312 may include circuitry (sometimes referred to as a chipset) that interconnects and controls communication between system components.

[0073] In some embodiments, memory 314 includes high-speed random access memory, such as DRAM, SRAM, DDRRAM, or other random access solid-state memory devices, and may include non-volatile memory, such as one or more magnetic disk storage devices, optical disk storage devices, flash memory devices, or other non-volatile solid-state storage devices. In some embodiments, memory 314 includes one or more storage devices located remotely from CPU 302. Memory 314, or alternatively, a non-volatile memory device within memory 314, includes non-transitory computer-readable storage media.

[0074] In some implementations, the memory 314 or the computer-readable storage medium of the memory 314 stores the following programs, modules, and data structures, or a subset thereof:

[0075] Operating system 316, which includes processes for handling various basic system services and for performing hardware-related tasks;

[0076] A network communication module 318 for connecting the server 300 to other computers via one or more communication network interfaces 304 (wired or wireless) and one or more communication networks (e.g., the Internet, other wide area networks, local area networks, metropolitan area networks, etc.);

[0077] A web server 320 (e.g., an HTTP server), which receives web requests from users and responds by providing responsive web pages or other resources;

[0078] a data visualization web application 322 that can be downloaded and executed by a web browser 220 on a user's computing device 200. Generally speaking, the data visualization web application 322 has the same functionality as the desktop data visualization application 222, but provides the flexibility of being accessed from any device at any location with a network connection, and does not require installation and maintenance. In some embodiments, the data visualization web application 322 includes various software modules to perform certain tasks. In some embodiments, the web application 322 includes a user interface module 324 that provides a user interface for all aspects of the web application 322. In some embodiments, the user interface module 324 specifies a shelf area 250, as described above for the computing device 200;

[0079] When the user selects features of a desired data visualization, the data visualization web application also stores the visual specifications 104. The visual specifications 104 and the data they store are described above with respect to the computing device 200;

[0080] One or more object models 108 , as described above for computing device 200 ;

[0081] a data visualization generator 290 that generates and displays data visualizations based on user-selected data sources and data fields and one or more object models 108 describing the data sources 106. The operation of the data visualization generator is described above with respect to the computing device 200;

[0082] In some embodiments, the web application 322 includes a data retrieval module 326 that builds and executes queries to retrieve data from one or more data sources 106. The data sources 106 can be stored locally on the server 300 or in an external database 328. In some embodiments, data from two or more data sources can be fused. In some embodiments, the data retrieval module 326 uses the visual specification 104 to build queries, as described above for Figure 2 As described in the computing device 200;

[0083] In some implementations, the memory stores visualization parameters 236, as described above with respect to computing device 200; and

[0084] One or more databases 328 that store data used or created by the data visualization web application 322 or the data visualization application 222. The databases 328 can store the data sources 106 that provide data used in the generated data visualizations. Each data source 106 includes one or more data fields 330. In some embodiments, the databases 328 store user preferences. In some embodiments, the databases 328 include a data visualization history log 334. In some embodiments, the history log 334 tracks each time a data visualization is reproduced.

[0085] Database 328 can store data in many different formats and typically includes many different tables, each with multiple data fields 330. Some data sources contain a single table. Data fields 330 include original fields from the data source (e.g., columns from a database table or columns from a spreadsheet) as well as derived data fields that can be calculated or constructed from one or more other fields. For example, derived data fields include calculating months or quarters from a date field, calculating the span of time between two date fields, calculating cumulative totals for quantitative fields, calculating percentage increases, and so on. In some instances, derived data fields are accessed through procedures or views stored in the database. In some embodiments, the definitions of derived data fields 330 are stored separately from data source 106. In some embodiments, database 328 stores a set of user preferences for each user. User preferences can be used when the data visualization web application 322 (or application 222) makes recommendations on how to view a set of data fields 330. In some embodiments, database 328 stores a data visualization history log 334, which stores information about each data visualization generated. In some implementations, database 328 stores other information, including other information used by data visualization application 222 or data visualization web application 322. Database 328 can be separate from data visualization server 300 or can be included with the data visualization server (or both).

[0086] In some embodiments, the data visualization history log 334 stores visual specifications 104 selected by the user, which can include a user identifier, a timestamp when the data visualization was created, a list of data fields used in the data visualization, the type of data visualization (sometimes referred to as a "view type" or "chart type"), data encoding (e.g., the color and size of the markers), the data relationships selected, and what connectors were used. In some embodiments, one or more thumbnail images of each data visualization are also stored. Some embodiments store additional information about the created data visualization, such as the name and location of the data source, the number of rows from the data source included in the data visualization, the version of the data visualization software, etc.

[0087] Each of the executable modules, applications or process concentrations identified above can be stored in one or more of the aforementioned memory devices, and corresponds to an instruction set for performing the above-mentioned functions. The modules or programs (that is, instruction sets) identified above do not need to be implemented as independent software programs, processes or modules, and therefore the various subsets of these modules can be combined or otherwise rearranged in various embodiments. In some embodiments, memory 314 stores the modules and data structures identified above. In some embodiments, memory 314 stores additional modules or data structures not described above.

[0088] although Figure 3 A data visualization server 300 is shown, but Figure 3 It is intended more as a functional description of various features that may be present rather than as a schematic diagram of the architecture of the embodiments described herein. In practice and as one of ordinary skill in the art will recognize, items shown separately may be combined and some items may be separated. In addition, some programs, functions, processes, or data shown above with respect to server 300 may be stored or executed on computing device 200. In some embodiments, functions and / or data may be distributed between computing device 200 and one or more servers 300. In addition, one of ordinary skill in the art will recognize that Figure 3 It does not necessarily refer to a single physical device. In some embodiments, server functionality is distributed across multiple physical devices that comprise the server system. As used herein, references to a "server" or "data visualization server" include various groups, collections, or arrays of servers that provide the described functionality, and the physical servers need not be physically collocated (e.g., individual physical devices may be located throughout the United States or throughout the world).

[0089] Figure 4A data visualization user interface 102 is shown according to some embodiments. The user interface 102 includes a schema information area 410, also referred to as a data pane. The schema information area 410 provides named data elements (e.g., field names) that can be selected and used to build a data visualization. In some embodiments, the list of field names is divided into a set of dimensions and a set of metrics (typically numeric quantities). Some embodiments also include a list of parameters. The graphical user interface 102 also includes a data visualization area 412. The data visualization area 412 includes multiple tool shelf areas 250, such as a column tool shelf area 230 and a row tool shelf area 232. These are also referred to as column tool shelves 230 and row tool shelves 232. In addition, the user interface 102 includes a filter tool shelf 262, which can include one or more filters 424.

[0090] As shown here, the data visualization area 412 also has a large space for displaying visual graphics. Because no data element has been selected in this illustration, the space is initially empty of visual graphics.

[0091] The user selects one or more data sources 106 (which may be stored on the computing device 200 or stored remotely), selects data fields from the data sources, and defines a visual graphic using the selected fields. The data visualization application 222 (or web application 322) displays the generated graphic 122 in the data visualization area 412. In some embodiments, the information provided by the user is stored as a visual specification 104.

[0092] In some embodiments, the data visualization area 412 includes a labeling tool shelf 264. The labeling tool shelf 264 allows a user to specify various codes 426 for data labels. In some embodiments, the labeling tool shelf includes a color coding icon 270, a size coding icon 272, a text coding icon 274, and / or a view level detail icon 228 that can be used to specify or modify the level of detail of the data visualization.

[0093] In some embodiments, a data visualization platform enables users to build visualizations using a single logical table through drag-and-drop actions, even when data comes from multiple physical tables. Logical tables can be constructed through physical modeling, which can include row and column transformations (pivots), joins, and unions. Tables combined through physical modeling represent the logical tables themselves. In some data visualization platforms (such as Tableau), a query generation model automatically maps user actions to underlying queries on data in physical tables.

[0094] In some embodiments, an analyst creates an object model. Figure 5AAn example of an object model is shown in , where there are six logical tables. Figure 5A In the example in , each table has its own metric granularity and is better modeled as a logical table, regardless of the actual physical storage of the data. The example object model includes a line item table 502, which has a join 514 with an order table 504 and another join 516 with a product table 506. The example also shows an order table 504, which has a join 518 with an address table 508 and another join 520 with a customer table 510. The address table 508 has a join 522 with the state table 512.

[0095] Assume that the user creates Figure 5B Visualization 530 is shown. In this case, Sales 524 is a measure from the Line Items table 502 and Population 526 is a measure from the States table 512. While the correct result for Sales may be obtained, the data visualization exaggerates the measure of Population. For this example, the population of some states is represented as billions of people 528. This exaggeration occurs because the data visualization framework queries all of the tables joined together. The granularity of this join tree is that of Line Items 502. In other words, each row represents a Line Item and will contain a column containing the population of the state in which the Line Item occurs. Therefore, SUM(Population) will produce the population of the state multiplied by the number of Line Items for that state. This problem occurs because six conceptually logical tables are treated as a single logical table.

[0096] One solution to fix metric multiplication is to use level of detail calculations. For example, the calculation {Fixed[State(State)]:MIN([Population])} can be rewritten to aggregate population to its native granularity. Figure 5C A data visualization 540 is shown after using level of detail calculation 532 according to some embodiments. As shown, the population is correctly displayed in millions 536 (rather than Figure 5B (The units are incorrectly displayed in billions in the graph.) However, a major drawback of this approach is that it requires the analyst to be aware of the multiples and understand the semantics of these calculations. In addition, the default axis label 534 is a complex expression instead of "Population." The disclosed embodiments provide an alternative solution that is automatically performed by the data visualization application.

[0097] To overcome at least some of these problems, some embodiments include a method for mapping drag-and-drop actions to a finer-grained logical model. Rather than a single logical table, some embodiments operate on a tree of logical tables, where each node is a logical table (with its own physical representation) and each edge is a link between two tables.

[0098] Some embodiments handle situations where the primary key of one or more logical tables is unknown or cannot be determined (without more complex analysis). In other words, the primary key of a logical table is lost. The primary key is a powerful tool for recovering the granularity of a table.

[0099] Some embodiments handle multiple relationship cardinalities between logical tables. Relationships can be many-to-one, one-to-one, or many-to-many. Some embodiments treat unknown relationship types as many-to-many. Some embodiments use relationship information to recover primary keys. For example, the field on the "one" side of the relationship contains the primary key.

[0100] In the following description, a logical field refers to a data field that is generated from the underlying representation within a logical table (eg, a field from a physical database table that supports the logical table), or refers to a calculation that has input across logical tables.

[0101] Figure 6A According to some embodiments, the object model is shown as a logical tree. As shown in the numbers, these tables correspond to Figure 5A Tables in the example shown. Each logical table has a many-to-one relationship with its neighbor to the right. Sometimes these relationships have unknown cardinality.

[0102] Some embodiments map user actions to visualizations using appropriate metric aggregation. Some embodiments generate visualizations using a logical tree structure ( Figure 6B An example of a visualization is shown in ), where metrics are aggregated at their native granularity. Figure 5A The example described allows analysts to obtain appropriate population values ​​without having to add new calculations.

[0103] Some embodiments calculate global values. For illustration, assume that the state table 512 contains the complete set of states. Figure 6B In the visualization, not all states may have sales. Therefore, if all tables are joined together using an inner join, the visualization framework may drop states with no sales (e.g., Alaska and Hawaii). One solution is to use a partial or full outer join to retain all states. Some implementations generate visualizations that encompass the entire domain by first querying the logical tables necessary to calculate dimension values.

[0104] Some implementations ensure that measure values ​​are represented or preserved even when new dimensions are added. For example, Sales may not have a State due to a missing or malformed foreign key. If the tables are inner joined together, the Sales value will be dropped. Some implementations avoid this problem by querying the tables required to obtain the complete measure value and using a left join to ensure that missing dimensions do not cause the measure to be dropped. For the example above, Sales without a State is coded by a "Null" State.

[0105] Some implementations query fewer tables than would be required for a solution that does not use a tree of logical tables. For the example above, an alternative framework would have queried Figure 6A Join tree for all six tables shown. Using the techniques disclosed herein, the data visualization framework can identify that only the line item table 502, order table 504, address table 508, and state table 512 must be queried because these are the logical tables containing the dimensions and measures, or are logical tables along the join paths to these tables.

[0106] Some embodiments utilize or combine primary key and cardinality information when such information is available, but these techniques will produce correct results even in the worst case (e.g., when all links or relationships between logical tables are many-to-many, or when the logical tables have no known primary keys). Some embodiments combine such information to generate simpler queries.

[0107] Some embodiments map the visual description to a high-level query representation that includes dimensions, metrics, and filters. Traditional embodiments assume a single logical table and convert this representation to a lower-level query representation. On the other hand, the techniques described herein generate queries that encode the semantics of a tree of logical tables. Some embodiments generate subqueries that include dimensions and subqueries for each aggregate metric (grouped by dimension). Some embodiments join these subqueries together across dimensions, as further described below.

[0108] Generate subqueries

[0109] To generate a dimension subquery, some embodiments join all logical tables containing dimension fields, or join these tables together and group by the set dimension. When generating a measure subquery, some embodiments generate a flat table at the granularity of the measure, which contains the input fields of the measure (in the case of logical measures) and the dimensions. Some embodiments apply aggregation and grouping on the dimensions. In the discussion below, non-logical fields (i.e., non-calculated fields) are sometimes referred to as physical fields, and logical tables are sometimes referred to as tables.

[0110] For each metric, some embodiments use an algorithm to generate a flat table at the metric's granularity. The algorithm includes collecting the physical input fields for the dimensions, metrics, and filters. The algorithm also includes computing the minimum subtree required for all physical input fields (referred to as the physical subtree). The algorithm also includes computing another minimum subtree required to provide all physical inputs for the metric (referred to as the metric subtree).

[0111] The algorithm also includes partitioning the subtree into subtree components. The tree emanating from the metric subtree is called a dimension filter subtree. In this case, the metric subtree and the dimension filter subtree are not joined. Logical fields or filters may span into or across the metric subtree. In this case, the algorithm includes creating a dimension-metric subtree that merges one or more dimension filter subtrees with a minimal set of tables (e.g., adjacent tables) from the metric subtree.

[0112] The algorithm also includes assigning logical fields and filters to the subtree containing all of its inputs. The algorithm also includes layering the logical fields and filters on top of a join tree consisting of all tables in the joined subtrees. Some implementations inner join the tables in the metric subtree and left outer join other tables along paths emanating from the metric table.

[0113] Some embodiments deduplicate each dimension subtree (and dimension / measure subtrees, if applicable) on a dimension using link fields. The structure of the deduplication step is a "Group By" on a set of fields, and a "MAX" on the remaining fields. Some embodiments use a set of link fields that include: (i) the calculated logical dimension fields from the measure table and the physical fields required for the filter (sometimes called physical input fields or data fields), and (ii) the relationship fields that link this subtree with the measure subtree. Some embodiments left outer join all subtrees together starting with the measure subtree. If the dimension subtree has a filter, some embodiments add a constant calculation to the dimension subtree and add a filter on top of the join to ensure that this calculation is not empty.

[0114] The following examples illustrate the application of the above algorithm according to some embodiments. Figures 5A-5C and Figure 6A-Figure 6B For the visualization example discussed, the pre-aggregated subquery for state and population is simply the state table 512. According to some embodiments, the query for state and sales is Figure 7. The physical subtree consists of the line item table 502 (for metrics), the state table 512 (for dimensions), and the order table 504 and address table 508, as these tables are required to provide the physical input fields. For this example, the metric subtree is the line item, and there is a single dimension subtree containing the other three tables. Some embodiments group the dimension subtree by dimension (state) and key (shown as primary key 'PK') in the spanning relationship between the line item table 502 and the dimension subtree (704). In this case, the dimension subtree is joined via a link between the line item table 502 and the order table 504 (702). Some embodiments join the metric subtree and the dimension subtree via this link. Joins 702, 706, and 708 are left outer joins from the metric subtree to ensure that rows with missing states in the line item table are not lost.

[0115] Next reference Figure 8A and Figure 8B , suppose a user creates a calculation across logical tables, such as [full city name] = [city name] + “,” + [state name], where [city name] comes from the address table 508 and [state name] comes from the state table 512. Figure 8A A data visualization 800 in this scenario is shown according to some embodiments. According to some embodiments, Figure 8B The query for the visualization is shown in . The physical join tree includes the state table 512 (for measures and dimensions) and the address table 508 (for dimensions).

[0116] For this example, the metric subtree is state, and the dimension subtree includes a logical dimension field 804 that spans the address table 508 and the state table 512. Thus, this example illustrates an instance of a dimension-metric subtree. Some embodiments join (802) this subtree to the metric subtree using the relationships between the metric table and the remaining tables in the dimension-metric subtree. Some embodiments deduplicate (806) the dimension-metric subtree using the dimension (full city name), the join relationship (state foreign key (FK)), and the physical input (state name) of the dimension that falls in the metric portion of the dimension-metric subtree. Some embodiments then perform a join using the key from the relationship and the physical input fields (808).

[0117] Next reference Figure 9A and Figure 9B , assume that a user wants to create a visualization 900 of the sum of sales by shipping mode filtered to the "Technology" category. Some embodiments generate Figure 9BQuery shown. The physical join tree is the line item table 502 (for metrics), the order table 504 (for dimensions), and the product table 506 (for filters). The metric subtree is the line item table 502. In this case, there are two non-joined dimension subqueries: orders (for dimensions) and products (for filter 906). Some embodiments left outer join (902) the metric subquery (sometimes referred to as the metric subtree) and the deduplicated shipping mode dimension subquery 904 on the keys from the relationship. Next, some embodiments left join (912) this result with the deduplicated dimension subquery 908. Some embodiments add a sentinel calculation (e.g., sentinel 910) on top of the filters (e.g., filters 906 and 914). Some embodiments add filters to only retain rows where the sentinel value is non-null, thereby ensuring that the filter is respected given the left outer join. Some embodiments swap the join order of the dimension and dimension-metric subgraphs while achieving the same result.

[0118] Figure 10A and Figure 10B An example of an optimized query according to some embodiments is shown. Some embodiments generate optimized queries when information about relationship cardinality and / or primary keys is available.

[0119] In some embodiments, when computing metric subtree components, the system pulls in tables that can be reached via chains of many-to-one links or one-to-one links. Figure 7 For the state and row item subqueries described, some embodiments exploit the fact that all links are many-to-one to expand the metric subtree to include all tables. Figure 10A The query shown in is simpler than before. In particular, with Figure 7 The query in is different, Figure 10A The query shown in does not have any "group by" operations (e.g., operation 704). Some embodiments perform this optimization without knowing all the cardinalities of the edges. For example, the optimization only needs to know that the three relationships between tables 502, 504, 508, and 512 are many-to-one, but it does not need to know anything about the tree ( Figure 5A ). Even with partial information, some embodiments can generate optimized queries. For example, if it is known that the line item-order link is many-to-one, some embodiments can perform a partial optimization by including the line item table and the order table in a metric subtree and joining the address table to the state table in the metric subtree.

[0120] In some embodiments, if the dimension / metric subtree is joined with the metric subtree only along many-to-one links and one-to-one links, then when computing the metric subtree, the set of tables shared by the metric and dimension / metric subtree is replaced by the deduplicated dimension / metric subtree. Figure 8B In the example query described, if the relationship between address and state is known to be many-to-one, some implementations will simplify the query, such as Figure 10B This optimization is based on the fact that the dimension-metric subtree is linked to the metric subtree only via many-to-one links (address-state in this case).

[0121] Combined subqueries

[0122] Given the dimension subtrees and the subtrees for each aggregate measure, some embodiments combine these queries using outer joins to form the final query.Some embodiments join on the dimensions in the visualization and, after each join, apply COALESCE on the left and right instances of each dimension. Figure 11A A data visualization 1100 is shown that displays the sum of population and sales (described above) grouped by region and category. Some embodiments combine subqueries to form a final query 1102 (e.g., Figure 11B shown), for Figure 11A In some implementations, each sub-query (eg, sub-queries 1104 and 1106) has a different domain. Figure 11C Dimension subqueries (for Figure 11B Data visualization 1120 of the example in ). Figure 11D Shown is a data visualization 1130 for a subquery (with dimensions) on the sum of sales, in accordance with some embodiments. Figure 11E Shown is a data visualization 1140 for a subquery (with dimensions) on a population sum, in accordance with some embodiments.

[0123] In some implementations, outer joins ensure that all combinations of dimensions that appear in at least one subquery are represented. A coalesce ensures that after the join, all non-null values ​​for each dimension are represented. For example, when joining the rest of the query in the population subquery, (region, category) = (central, null) only appears on the right side of the outer join. If the left version of the dimension is selected, this will result in an incorrect result of (null, null). Similarly, if the right version of the dimension is selected, (null, furniture) will be produced from the sales sum subquery.

[0124] Some embodiments provide for visualizations that do not have dimensions (e.g., Figure 11FThe visualization 1150 shown, corresponding to the sum of sales and population), performs a full join between the metric subqueries.

[0125] Some embodiments generate visualizations based on the object model of complex queries. To illustrate, assume a user creates a calculation across logical tables, such as [Tax Adjusted Sales] = [Sales] * [Sales Tax Rate]. Here, [Sales] comes from the Line Items table 502 and [Sales Tax Rate] comes from the States table 512. Assume the query also includes a filter predicate calculation where [Segment] = 'Home Office' AND [Region] = 'East', where [Segment] comes from the Customers table 510 and [Region] comes from the Addresses table 508. Now, further assume the user wants to create a visualization SUM([Tax Adjusted Sales]) grouped by category where the filter predicate is true. Figure 12A Shown is a data visualization 1200 generated using the techniques described herein, according to some implementations.

[0126] Assuming the cardinality of the relationship is unknown, some implementations generate Figure 12B , to compute the metric at its native granularity. For this example, the physical join tree includes the Line Items table 502, Orders table 504, Addresses table 508, and State table 512 (for the metric), the Products table 506 (for the dimension), and the Customers table 510 (which we need for the filter). A metric is a logical field that spans the Line Items, Orders, Addresses, and State tables. The metric subtree includes these tables inner-joined together. Some embodiments also layer on the definition of [Tax Adjusted Sales].

[0127] Some embodiments start with two dimension subtrees: product and customer. Since the filter on the predicate spans from customer to address, some embodiments generate a dimension-measure subtree of orders, addresses, and customers. For the subtree corresponding to category, some embodiments group products by category and link key (product PK) and left join it to the measure subtree. For the dimension-measure subtree, some embodiments inner join the tables from the measure subtree together and left join customer. Some embodiments add the filter predicate logical fields and apply the filter. The filter predicate is a calculation using physical inputs in the measure subtree [region]. Therefore, some embodiments deduplicate this subtree on the relationship fields from the customer-order link (because it is the only link from a dimension object to a measure object) and [region]. Some embodiments also join the dimension-measure subtree with the rest of the query using these fields.

[0128] Some embodiments simplify the above reference Figure 12BIf the order-customer link is known to be many-to-one, some embodiments simplify the query by eliminating the need for a dimension subquery (since it is known that the customer can be safely joined to the metric subtree without affecting granularity, as described above with reference to Figure 10A described above). Figure 12C 12 shows an example of an optimized query 1204 according to some embodiments. Similarly, if it is known that the line item-product link is many-to-one, some embodiments apply similar logic to reduce or simplify the query even further, such as Figure 12D The optimized query 1206 is shown in FIG.

[0129] Figures 13A-13D A flowchart of a method 1300 for generating (1302) a data visualization using an object model according to the above techniques is provided, according to some embodiments. The method 1300 is executed (1304) at a computing device 200 having one or more processors and memory. The memory stores (1306) one or more programs configured to be executed by the one or more processors.

[0130] The computer receives (1308) a visual specification 104 that specifies one or more data sources 106, a plurality of visual variables 282, and a plurality of data fields 284 from the one or more data sources 106. Each visual variable 282 is associated with (i) a corresponding one or more data fields 284 or (ii) one or more filters, and each of the data fields 284 is identified as a dimension or a measure. In some embodiments, the visual specification 104 includes one or more additional visual variables that are not associated with any data field 330 from the one or more data sources 106. In some embodiments, each visual variable 282 is one of: a row attribute, a column attribute, a filter attribute, a color encoding, a size encoding, a shape encoding, or a label encoding.

[0131] The computer obtains (1310) a data model that encodes a data source as a tree of logical tables. Each logical table has its own physical representation and includes one or more logical fields. Each logical field corresponds to a data field or to a calculation that spans one or more logical tables. Each edge of the tree connects two related logical tables. The computer generates (1312) a dimension subquery based on the logical tables that provide data fields for dimensions and filters. In some embodiments, the computer generates the dimension subquery by inner joining (1314) the first one or more logical tables in the tree of logical tables, where each of the first one or more logical tables provides a data field for a dimension or filter.

[0132] The computer also generates (1316) an aggregated measure subquery grouped by dimension for each measure based on a logical table that provides data fields for each measure and filter.

[0133] Next reference Figure 13B , the computer forms (1318) the final query by joining the dimension subquery to each of the aggregate measure subqueries using the dimensions. In some embodiments, the computer forms the final query by joining (1320) the dimension subquery and the aggregate measure subquery on the dimensions using outer joins and applying COALESCE after each outer join. In some embodiments, when the visualization has no dimensions, the computer performs (1322) a full join between the aggregate measure subqueries to form the final query. Some embodiments use a special table (called Table Dee in some Tableau products) with an empty schema and a single row to represent visualizations without dimensions, and determine whether a given visualization has no dimensions by checking whether the base table in the dimension subquery is the special table. Some embodiments also use special tables for constant values. For example, SUM(1) is evaluated as the value 1 projected on top of the special table. Since the special table has only one row, SUM(1) is evaluated as the value 1. In some embodiments, if none of the inputs to a logical field belong to any table, the field is evaluated using the special table.

[0134] The computer then executes (1324) a final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields. The computer then constructs and displays (1326) a data visualization (e.g., in a graphical user interface 102 for the computing device 200) based on the data fields in the tuples and based on the visual variables associated with each data field.

[0135] Next reference Figure 13CIn some embodiments, the computer generates each aggregate metric subquery by performing a series of operations. The computer computes (1328) a metric subtree of the tree of logical tables. The metric subtree is the minimum subtree required to provide the data fields for the corresponding metric. In some embodiments, the computer compiles (1330) the metric subtree by inner joining the logical tables in the metric subtree to obtain a metric join tree. Using inner joins helps ensure that the order of joins does not matter, thereby providing consistent semantics when calculating granularity. Inner joins provide a native level of granularity for metrics. There are multiple tables in the metric subtree only when the metric subtree is a calculation. However, in most cases, the metric subtree includes a single table. When the calculated field spans multiple tables, the native granularity of the field is the input to which it is joined. The computer also computes (1332) a dimension filter subtree from the tree of logical tables. The dimension filter subtree is the minimum subtree required to provide all physical inputs for dimensions and filters. (For non-calculated dimension fields, the physical input is the dimension field itself. For calculated dimensions, the physical input is all the data fields required to calculate the dimension.) In some embodiments, the computer calculates the dimension-filter subtree by inner joining (1334) the logical tables in the dimension-filter subtree that are shared with the metric subtree and left joining the logical tables in the dimension-filter subtree that are not shared with the metric subtree, thereby obtaining the dimension-filter join tree. Assume that there are calculated dimensions that span multiple tables in a many-to-one relationship. For each different combination of rows that can be combined, a separate instance of the calculation is required. The inner join produces this set of rows.

[0136] When the dimension-filter subtree does not share any logical tables with the metric subtree, the computer adds (1336) the adjacent logical tables from the metric subtree to the dimension-filter subtree. The computer compiles (1338) the metric subtree to obtain a metric join tree and compiles the dimension-filter subtree to obtain a dimension-filter join tree. Figure 13D, the computer layers the calculations and filters on the metric join tree and the dimension-filter join tree (1340) to obtain an updated metric subtree and an updated dimension-filter subtree, respectively. The computer deduplicates the updated dimension-filter subtree (1342) by applying a grouping operation (GB) to obtain a deduplicated dimension-filter subtree, the grouping operation using the dimensions and link fields, the link fields including (i) keys from the relationship between the logical tables (e.g., primary key equals foreign key) and (ii) data fields of the calculations shared with the metric subtree. Assume that there are dimensions that are calculations. Further assume that the calculation has physical input fields (sometimes referred to as data fields) that are located in the metric portion of the dimension-filter subtree. These fields are also link fields. The grouping operation finds a unique set of dimensions. Some embodiments use link fields to join back. In some cases, when there are calculations that share fields with the metric subtree, some embodiments recover the unique rows from which the metrics come. In such instances, the join acts like quasi-packing the primary key classes of the metric subtrees together.

[0137] The computer then combines (1344) the deduplicated dimension-filter subtree with the updated metric subtree to obtain an aggregated metric subquery.

[0138] In the case of a primary key, some embodiments do not use the dimension-filter subtree. In this case, some embodiments combine the primary keys of all tables of the metric subtree.

[0139] In some embodiments, the computer combines the deduplicated dimension-filter subtree with the updated metric subtree by performing a series of operations. The computer determines whether the deduplicated dimension-filter subtree contains a filter. When the deduplicated dimension-filter subtree contains a filter, the computer performs an inner join (1346) on the updated metric subtree with the deduplicated dimension-filter subtree. When the deduplicated dimension-filter subtree does not contain a filter, the computer performs a left outer join (1348) on the updated metric subtree with the deduplicated dimension-filter subtree.

[0140] In some embodiments, the computer determines whether the key indicates a many-to-one relationship or a one-to-one relationship between the first logical table and the second logical table. When the key indicates a many-to-one relationship between the first logical table and the second logical table, the computer includes (1350) the first table and the second table in the metric subtree, thereby avoiding grouping in a deduplication operation on the first logical table and the second logical table.

[0141] In some embodiments, when the dimension-filter subtree joins the metric subtree only along many-to-one links and one-to-one links, the computer replaces (1352) a table shared by the metric subtree and the dimension-filter subtree with the deduplicated dimension-filter subtree.

[0142] The terms used in the description of the present invention are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the description of the present invention and the appended claims, the singular forms "a", "an", and "the" are intended to also include plural forms, unless the context clearly indicates otherwise. It is also to be understood that the terms "and / or" as used herein refer to any and all possible combinations of one or more of the associated listed items and include these combinations. It should be further understood that the terms "comprises" and / or "comprising" when used in this specification specify the presence of stated features, steps, operations, elements and / or parts, but do not exclude the presence or addition of one or more other features, steps, operations, elements, parts and / or their groups.

[0143] For purposes of explanation, the foregoing description has been described with reference to specific embodiments. However, the illustrative discussions above are not intended to be exhaustive or to limit the invention to the precise forms disclosed. Many modifications and variations are possible in light of the above teachings. The embodiments are chosen and described in order to best explain the principles of the invention and its practical application, thereby enabling others skilled in the art to best utilize the invention and the various embodiments with various modifications as are suitable for the particular use contemplated.

[0144] Aspects of the present disclosure may be implemented in one or more of the following embodiments:

[0145] Item 1): A computer system for generating data visualization, comprising:

[0146] one or more processors; and

[0147] Memory;

[0148] The memory stores one or more programs configured to be executed by the one or more processors, and the one or more programs include instructions for performing the following operations:

[0149] receiving a visual specification that specifies a data source, a plurality of visual variables, and a plurality of data fields from the data source, wherein each of the visual variables is associated with (i) a corresponding one or more of the data fields or (ii) one or more filters, and each of the data fields is identified as a dimension or a measure;

[0150] Obtaining a data model encoding the data source as a tree of logical tables, each logical table having its own physical representation and including one or more corresponding logical fields, each logical field corresponding to a data field or to a calculation spanning one or more logical tables, wherein each edge of the tree connects two related logical tables;

[0151] generating a dimension subquery based on a logical table providing the data fields for the dimension and the filter;

[0152] generating, for each metric, an aggregated metric subquery grouped by the dimension based on a logical table providing the data fields for the corresponding metric and the filter;

[0153] forming a final query by joining the dimension subquery to each of the aggregate measure subqueries using the dimension;

[0154] executing the final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields; and

[0155] A data visualization is created and displayed based on the data fields in the tuple and based on the visual variables associated with each of the data fields.

[0156] Item 2): The computer system of item 1), wherein generating each aggregate metric subquery comprises:

[0157] Calculating a metric subtree of the tree of the logical table, wherein the metric subtree is a minimum subtree required to provide the data field for the corresponding metric;

[0158] Calculating a dimension-filter subtree of the tree of the logical table, wherein the dimension-filter subtree is the minimum subtree required to provide all physical inputs for the dimension and the filter;

[0159] Based on a determination that the dimension-filter subtree does not share any logical tables with the metric subtree, adding an adjacent logical table from the metric subtree to the dimension-filter subtree;

[0160] compiling the metric subtrees to obtain a metric join tree, and compiling the dimension-filter subtrees to obtain a dimension-filter join tree;

[0161] layering calculations and filters on the metric join tree and the dimension-filter join tree to obtain an updated metric subtree and an updated dimension-filter subtree, respectively;

[0162] deduplicating the updated dimension-filter subtree by applying a group by operation using the dimensions and link fields, the link fields comprising (i) keys from the relationships between the logical tables and (ii) physical input fields of a calculation shared with the metric subtree to obtain a deduplicated dimension-filter subtree; and

[0163] The deduplicated dimension-filter subtree is combined with the updated metric subtree to obtain the aggregated metric subquery.

[0164] Item 3): The computer system according to Item 2), wherein compiling the metric subtree comprises inner joining logical tables in the metric subtree to obtain the metric join tree.

[0165] Item 4): A computer system according to Item 2), wherein compiling the dimension-filter subtree includes inner joining a logical table shared with the metric subtree in the dimension-filter subtree, and left joining a logical table not shared with the metric subtree in the dimension-filter subtree to obtain the dimension-filter join tree.

[0166] Item 5): The computer system according to item 2), wherein combining the deduplicated dimension-filter subtree with the updated metric subtree comprises:

[0167] Determining whether the deduplication dimension-filter subtree contains a filter;

[0168] Inner-joining the updated metric subtree with the deduplicated dimension-filter subtree based on the determination that the deduplicated dimension-filter subtree contains a filter; and

[0169] Based on the determination that the deduplicated dimension-filter subtree does not contain a filter, the updated metric subtree is left outer joined with the deduplicated dimension-filter subtree.

[0170] Item 6): The computer system according to item 2), wherein the one or more programs further include instructions for performing the following operations:

[0171] determining whether the key indicates a many-to-one relationship or a one-to-one relationship between the first logical table and the second logical table; and

[0172] According to the determination of the many-to-one relationship between the first logical table and the second logical table indicated by the key, the first table and the second table are included in the metric subtree, thereby avoiding grouping in the deduplication operation of the first logical table and the second logical table.

[0173] Item 7): The computer system according to Item 2), wherein the one or more programs further include instructions for performing the following operations:

[0174] Based on a determination that the dimension-filter subtree joins the metric subtree only along many-to-one links and one-to-one links, a table shared by the metric subtree and the dimension-filter subtree is replaced with the deduplicated dimension-filter subtree.

[0175] Item 8): A computer system according to Item 1), wherein generating the dimension subquery includes inner joining the first one or more logical tables in the tree of the logical tables, wherein each of the first one or more logical tables provides a data field for a dimension or filter.

[0176] Item 9): A computer system according to Item 1), wherein forming the final query includes: joining the dimension subquery and the aggregate measure subquery on the dimension using outer joins, and applying COALESCE after each outer join.

[0177] Item 10): A non-transitory computer-readable storage medium storing one or more programs configured to be executed by a computer system having a display, one or more processors, and a memory, the one or more programs including instructions for performing the following operations:

[0178] receiving a visual specification that specifies a data source, a plurality of visual variables, and a plurality of data fields from the data source, wherein each of the visual variables is associated with (i) a corresponding one or more of the data fields or (ii) one or more filters, and each of the data fields is identified as a dimension or a measure;

[0179] Obtaining a data model encoding the data source as a tree of logical tables, each logical table having its own physical representation and including one or more corresponding logical fields, each logical field corresponding to a data field or to a calculation spanning one or more logical tables, wherein each edge of the tree connects two related logical tables;

[0180] generating a dimension subquery based on a logical table providing the data fields for the dimension and the filter;

[0181] generating, for each metric, an aggregated metric subquery grouped by the dimension based on a logical table providing the data fields for the corresponding metric and the filter;

[0182] forming a final query by joining the dimension subquery to each of the aggregate measure subqueries using the dimension;

[0183] executing the final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields; and

[0184] A data visualization is created and displayed based on the data fields in the tuple and based on the visual variables associated with each of the data fields.

Claims

1. A method for generating data visualization, comprising: The following operations are performed at a computer having a display, one or more processors, and a memory storing one or more programs configured to be executed by the one or more processors: receiving a visual specification that specifies a data source, a plurality of visual variables, and a plurality of data fields from the data source, wherein each of the visual variables is associated with (i) a corresponding one or more of the data fields or (ii) one or more filters, and each of the data fields is identified as a dimension or a measure; Obtaining a data model encoding the data source as a tree of logical tables, each logical table having its own physical representation and including one or more corresponding logical fields, each logical field corresponding to a data field or to a calculation spanning one or more logical tables, wherein each edge of the tree connects two related logical tables; generating a dimension subquery based on a logical table providing the data fields for the dimension and the filter; generating, for each metric, an aggregated metric subquery grouped by the dimension based on a logical table providing the data fields for the corresponding metric and the filter; forming a final query by joining the dimension subquery to each of the aggregate measure subqueries using the dimension; executing the final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields; and A data visualization is created and displayed based on the data fields in the tuple and based on the visual variables associated with each of the data fields.

2. The method according to claim 1, wherein The subquery that generates each aggregate measure includes: Calculating a metric subtree of the tree of the logical table, wherein the metric subtree is a minimum subtree required to provide the data field for the corresponding metric; Calculating a dimension-filter subtree of the tree of the logical table, wherein the dimension-filter subtree is the minimum subtree required to provide all physical inputs for the dimension and the filter; Based on a determination that the dimension-filter subtree does not share any logical tables with the metric subtree, adding an adjacent logical table from the metric subtree to the dimension-filter subtree; compiling the metric subtrees to obtain a metric join tree, and compiling the dimension-filter subtrees to obtain a dimension-filter join tree; layering calculations and filters on the metric join tree and the dimension-filter join tree to obtain an updated metric subtree and an updated dimension-filter subtree, respectively; deduplicating the updated dimension-filter subtree by applying a group by operation using the dimensions and link fields, the link fields comprising (i) keys from the relationships between the logical tables and (ii) calculated data fields shared with the metric subtree to obtain a deduplicated dimension-filter subtree; and The deduplicated dimension-filter subtree is combined with the updated metric subtree to obtain the aggregated metric subquery.

3. The method according to claim 2, wherein: Compiling the metric subtree includes inner joining logical tables in the metric subtree to obtain the metric join tree.

4. The method according to claim 2, wherein: Calculating the dimension-filter subtree includes inner joining a logical table shared with the metric subtree in the dimension-filter subtree, and left joining a logical table not shared with the metric subtree in the dimension-filter subtree to obtain the dimension-filter join tree.

5. The method according to claim 2, wherein: Combining the deduplicated dimension-filter subtree with the updated metric subtree includes: Determining whether the deduplication dimension-filter subtree contains a filter; Inner-joining the updated metric subtree with the deduplicated dimension-filter subtree based on the determination that the deduplicated dimension-filter subtree contains a filter; and Based on the determination that the deduplicated dimension-filter subtree does not contain a filter, the updated metric subtree is left outer joined with the deduplicated dimension-filter subtree.

6. The method according to claim 2, further comprising: According to the determination of the many-to-one relationship between the key indicating the first logical table and the second logical table, the first logical table and the second logical table are included in the metric subtree, thereby avoiding grouping in the deduplication operation of the first logical table and the second logical table.

7. The method according to claim 2, further comprising: Based on a determination that the dimension-filter subtree joins the metric subtree only along many-to-one links and one-to-one links, a table shared by the metric subtree and the dimension-filter subtree is replaced with the deduplicated dimension-filter subtree.

8. The method according to claim 1, wherein Generating the dimension subquery includes inner joining first one or more logical tables in the tree of logical tables, wherein each of the first one or more logical tables provides a data field for a dimension or filter.

9. The method according to claim 1, wherein: Forming the final query includes joining the dimension subquery and the aggregate measure subquery on the dimension using outer joins, and applying COALESCE after each outer join.

10. The method according to claim 1, wherein Forming the final query includes performing a full outer join between the aggregated measure subqueries based on a determination that the visualization has no dimensions.

11. A computer system for generating data visualization, comprising: one or more processors; as well as Memory; The memory stores one or more programs configured to be executed by the one or more processors, and the one or more programs include instructions for performing the following operations: receiving a visual specification that specifies a data source, a plurality of visual variables, and a plurality of data fields from the data source, wherein each of the visual variables is associated with (i) The data fields are associated with corresponding one or more data fields or (ii) one or more filters, and each of the data fields is identified as a dimension or a metric; Obtaining a data model encoding the data source as a tree of logical tables, each logical table having its own physical representation and including one or more corresponding logical fields, each logical field corresponding to a data field or to a calculation spanning one or more logical tables, wherein each edge of the tree connects two related logical tables; generating a dimension subquery based on a logical table providing the data fields for the dimension and the filter; generating, for each metric, an aggregated metric subquery grouped by the dimension based on a logical table providing the data fields for the corresponding metric and the filter; forming a final query by joining the dimension subquery to each of the aggregate measure subqueries using the dimension; executing the final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields; and A data visualization is created and displayed based on the data fields in the tuple and based on the visual variables associated with each of the data fields.

12. The computer system according to claim 11, wherein: The subquery that generates each aggregate measure includes: Calculating a metric subtree of the tree of the logical table, wherein the metric subtree is a minimum subtree required to provide the data field for the corresponding metric; Calculating a dimension-filter subtree of the tree of the logical table, wherein the dimension-filter subtree is the minimum subtree required to provide all physical inputs for the dimension and the filter; Based on a determination that the dimension-filter subtree does not share any logical tables with the metric subtree, adding an adjacent logical table from the metric subtree to the dimension-filter subtree; compiling the metric subtrees to obtain a metric join tree, and compiling the dimension-filter subtrees to obtain a dimension-filter join tree; layering calculations and filters on the metric join tree and the dimension-filter join tree to obtain an updated metric subtree and an updated dimension-filter subtree, respectively; deduplicating the updated dimension-filter subtree by applying a group by operation using the dimensions and link fields, the link fields comprising (i) keys from the relationships between the logical tables and (ii) physical input fields of a calculation shared with the metric subtree to obtain a deduplicated dimension-filter subtree; and The deduplicated dimension-filter subtree is combined with the updated metric subtree to obtain the aggregated metric subquery.

13. The computer system according to claim 12, wherein: Compiling the metric subtree includes inner joining logical tables in the metric subtree to obtain the metric join tree.

14. The computer system according to claim 12, wherein: Compiling the dimension-filter subtree includes inner joining logical tables in the dimension-filter subtree that are shared with the metric subtree, and left joining logical tables in the dimension-filter subtree that are not shared with the metric subtree to obtain the dimension-filter join tree.

15. The computer system according to claim 12, wherein: Combining the deduplicated dimension-filter subtree with the updated metric subtree includes: Determining whether the deduplication dimension-filter subtree contains a filter; Inner-joining the updated metric subtree with the deduplicated dimension-filter subtree based on the determination that the deduplicated dimension-filter subtree contains a filter; and Based on the determination that the deduplicated dimension-filter subtree does not contain a filter, the updated metric subtree is left outer joined with the deduplicated dimension-filter subtree.

16. The computer system according to claim 12, wherein: The one or more programs also include instructions for performing the following operations: determining whether the key indicates a many-to-one relationship or a one-to-one relationship between the first logical table and the second logical table; and According to the determination of the many-to-one relationship between the first logical table and the second logical table indicated by the key, the first logical table and the second logical table are included in the metric subtree, thereby avoiding grouping in the deduplication operation of the first logical table and the second logical table.

17. The computer system according to claim 12, wherein: The one or more programs also include instructions for performing the following operations: Based on a determination that the dimension-filter subtree joins the metric subtree only along many-to-one links and one-to-one links, a table shared by the metric subtree and the dimension-filter subtree is replaced with the deduplicated dimension-filter subtree.

18. The computer system according to claim 11, wherein: Generating the dimension subquery includes inner joining first one or more logical tables in the tree of logical tables, wherein each of the first one or more logical tables provides a data field for a dimension or filter.

19. The computer system according to claim 11, wherein: Forming the final query includes joining the dimension subquery and the aggregate measure subquery on the dimension using outer joins, and applying COALESCE after each outer join.

20. A non-transitory computer-readable storage medium storing one or more programs configured to be executed by a computer system having a display, one or more processors, and memory, the one or more programs comprising instructions for performing the following operations: receiving a visual specification that specifies a data source, a plurality of visual variables, and a plurality of data fields from the data source, wherein Each of the visual variables is associated with (i) a corresponding one or more of the data fields or (ii) one or more filters, and each of the data fields is identified as a dimension or a measure; Obtaining a data model encoding the data source as a tree of logical tables, each logical table having its own physical representation and including one or more corresponding logical fields, each logical field corresponding to a data field or to a calculation spanning one or more logical tables, wherein each edge of the tree connects two related logical tables; generating a dimension subquery based on a logical table providing the data fields for the dimension and the filter; generating, for each metric, an aggregated metric subquery grouped by the dimension based on a logical table providing the data fields for the corresponding metric and the filter; forming a final query by joining the dimension subquery to each of the aggregate measure subqueries using the dimension; executing the final query on the data source to retrieve tuples containing different ordered combinations of data values ​​for the data fields; and A data visualization is created and displayed based on the data fields in the tuple and based on the visual variables associated with each of the data fields.

Images