Generating data visualization from object model of selected data source

By building a data object model and selecting data fields in the graphical user interface, the data visualization generation problem in complex data sources and multiple data sources is solved, and accurate and controllable data visualization is achieved, avoiding data omission and aggregation errors .

CN119066119APending Publication Date: 2024-12-03TABLEAU SOFTWARE INC
View PDF 5 Cites 0 Cited by

Patent Information

Application Number
CN202411115844.0
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Priority Date
2018-12-30
Filing Date
2019-10-16
Publication Date
2024-12-03

AI Technical Summary

Technical Problem

When existing data visualization systems deal with complex data sources and multiple data sources, it is difficult to generate data visualizations that users expect, which may result in data rows being omitted or digital aggregate fields producing exaggerated totals, and it is difficult for users to identify these problems.

Method used

By building an object model of the data, identifying objects and relationships in the data source, and using the object model to generate data visualization, allowing users to select data fields in the graphical user interface and construct data visualization through visual specifications, including identifying dimensions and measurement fields, for aggregation Calculate and display data visualization.

Benefits of technology

It effectively solves the problem of data visualization generation in complex data sources and multiple data sources, ensures data visualization accuracy and user experience, avoids data omission and aggregation errors, and improves the controllability and accuracy of data visualization.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119066119A_ABST
    Figure CN119066119A_ABST
Patent Text Reader

Abstract

The invention relates to a method for generating a data visualization according to an object model of a selected data source. A visual specification specifying a data source, a visual variable, and a data field from the data source is received. Each visual variable is associated with a data field, and each data field is a dimension or metric. From an object model of a data source, the method identifies a minimum sub-tree that includes all dimension data fields and builds a query from the minimum sub-tree. The method performs a query for a data source to retrieve a set of tuples, each tuple comprising a unique ordered combination of data values of a dimension data field. For each tuple, the method forms an extended tuple by appending aggregated data values corresponding to each metric data field. The method then constructs and displays the data visualization from the data fields in the extension tuple and from the visual variables associated with the data fields.
Need to check novelty before this filing date? Find Prior Art

Description

[0001] This application is a divisional application of an application with an application date of October 16, 2019, application number 201980085510.4, and invention name “Generating data visualization based on an object model of a selected data source”. Technical Field

[0002] The disclosed implementations relate generally to data visualization, and more particularly to interactive visual analysis of data sets using object models of the data sets. Background Art

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

[0004] Some data visualization applications provide user interfaces that enable users to build visualizations from data sources by selecting data fields and placing them in specific user interface areas to indirectly define data visualizations. See, for example, U.S. Patent Application Serial No. 10 / 453,834, filed June 2, 2003, entitled "Computer Systems and Methods for the Query and Visualization of Multidimensional Databases" (now U.S. Patent No. 7,089,266), which is incorporated herein by reference in its entirety. However, when there are complex data sources 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-built queries produce data visualizations that are not what users expect. 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, numeric aggregate fields produce inflated totals 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. Summary of the invention

[0006] 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 data visualization. In some instances, one person is a particular expert on the data, and that person creates the object model. By storing the relationships in the object model, the data visualization application can use this information to help all users who access the data, even if the user is not an expert.

[0007] 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 are conceptually in a 1:1 relationship with the object. Thus, a store object may have a single [Manager Name] or [Employee Count] associated with it. At a physical level, objects are often stored as rows in relational tables or as objects in JSON.

[0008] A class is a collection of objects that share the same properties. Comparing objects in 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.

[0009] The object model is a collection of classes and a collection of many-to-one relationships between them. Classes related by a 1-to-1 relationship are conceptually treated as a single class, even though they appear distinct to the user. Furthermore, classes related by a 1-to-1 relationship can be presented as distinct classes in a data visualization user interface. A many-to-many relationship is conceptually split into two many-to-one relationships by adding an association table that captures the relationship.

[0010] Once the class model is built, the data visualization application can assist the user in various ways. In some implementations, 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 implementations, 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 what the user selected.

[0011] According to some implementations, a method for generating data visualization. The method is executed at a computer having one or more processors and a memory. The memory stores one or more programs configured to be executed by one or more processors. The computer receives a visual specification (visual specification) specifying a data source, a plurality of visual variables, and a plurality of data fields from the data source. Each of the visual variables is associated with one or more corresponding data fields in the data field, and each of the data fields is identified as a dimension or a measure. According to the object model of the data source, the computer identifies the smallest subtree including all dimension data fields, and constructs a query for accessing the dimension data field from the smallest subtree. The computer executes a query against the data source to retrieve a set of tuples. Each tuple includes a unique ordered combination of data values ​​of the dimension data field. For each tuple, the computer forms an extended tuple by appending an aggregated data value corresponding to each metric data field in the metric data field. The computer then constructs and displays the data visualization according to the data fields in the extended tuple and according to the visual variables associated with each data field in the data field.

[0012] In some implementations, the visual specification includes one or more additional visual variables that are not associated with any data fields from the data source.

[0013] In some implementations, aggregated data values ​​for metric data fields are aggregated according to dimension data fields.

[0014] In some implementations, the computer displays the data visualization in a graphical user interface of the computer. In some implementations, displaying the data visualization includes generating a plurality of visual tags, wherein each tag corresponds to a respective expanded tuple. In some implementations, the graphical user interface includes a data visualization area, and the computer displays the data visualization in the data visualization area.

[0015] In some implementations, each of the visual variables is one of: a row attribute, a column attribute, a filter attribute, a color encoding, a size encoding, a shape encoding, and a label encoding.

[0016] In some implementations, the aggregate data value is calculated using an aggregate function that is one of the following: SUM, COUNT, COUNTD, MIN, MAX, AVG, MEDIAN, ATTR, PERCENTILE, STDEV, STDEVP, VAR, and VARP.

[0017] In some implementations, the multiple data fields are from multiple different tables in a data source.

[0018] In some implementations, the object model of the data source has a plurality of objects, and the plurality of data fields belong to two or more different objects among the plurality of objects.

[0019] In some implementations, the visual specification specifies a plurality of data sources, the visual specification specifies one or more data fields from each of the plurality of data sources, and the object model is an object model of the plurality of data sources.

[0020] According to some implementations, a method for generating data visualization is performed at a computer having one or more processors and a memory. The memory stores one or more programs configured to be executed by 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 in the visual variables is associated with a corresponding one or more data fields in the data fields, and each data field in the data fields is identified as a dimension or a measure. The computer performs a first query against the data source to form a dimension tuple, which includes different ordered combinations of data values ​​of the dimension data field D. For each metric data field in the metric data field, the computer: (i) forms a dimension set S consisting of the dimension data field D and the primary key of a table in the data source containing the corresponding metric; (ii) performs a corresponding query against the data source to retrieve an intermediate tuple including the data field in S and the corresponding metric without aggregation; and (iii) aggregates the intermediate tuple according to the dimension data field D to calculate the aggregate value of the corresponding metric. For each dimension tuple, the computer forms an extended tuple by appending the aggregated data value corresponding to each metric data field in the metric data field. The computer then builds and displays a data visualization based on the data fields in the expanded tuple and based on the visual variables associated with each of the data fields.

[0021] In some implementations, the visual specification also includes one or more additional visual variables that are not associated with any data fields from the data source.

[0022] In some implementations, the computer displays the data visualization in a graphical user interface of the computer. In some implementations, displaying the data visualization includes generating a plurality of visual tags, wherein each tag corresponds to a respective expanded tuple. In some implementations, the graphical user interface includes a data visualization area, and the computer displays the data visualization in the data visualization area.

[0023] In some implementations, each of the visual variables is one of: a row attribute, a column attribute, a filter attribute, a color encoding, a size encoding, a shape encoding, and a label encoding.

[0024] In some implementations, the aggregate data value is calculated using an aggregate function that is one of the following: SUM, COUNT, COUNTD, MIN, MAX, AVG, MEDIAN, ATTR, PERCENTILE, STDEV, STDEVP, VAR, and VARP.

[0025] In some implementations, the multiple data fields are from multiple different tables in a data source.

[0026] In some implementations, the object model of the data source has a plurality of objects, and the plurality of data fields belong to two or more different objects of the plurality of objects.

[0027] In some implementations, the visual specification specifies a plurality of data sources, the visual specification specifies one or more data fields from each of the plurality of data sources, and the object model is an object model of the plurality of data sources.

[0028] According to some implementations, a process generates data visualization. The process is executed at a computer having one or more processors and a memory, and the memory stores one or more programs configured to be executed by the one or more processors. The process receives a visual specification that specifies one or more data sources, multiple visual variables, and multiple data fields from the one or more data sources. Each visual variable is associated with one or more data fields, and each data field is identified as a dimension d or a metric m. In some implementations, the visual specification is a data structure filled based on user selections in a user interface. For example, a user can drag a field from a palette of data fields to a row tool shelf, a column tool shelf, or an encoding tool shelf (e.g., color or size encoding). Each tool shelf corresponds to a visual variable in the visual specification, and the data fields on the tool shelf are stored as part of the visual specification. In some instances, two or more data fields are associated with the same tool shelf, so the corresponding visual variable has two or more related data fields. When two or more data fields are associated with a visual variable, there is usually a specified order. In some instances, the same data field is associated with two or more different visual variables. Typically, individual data visualizations do not use all available visual variables. That is, a visual specification typically includes one or more additional visual variables that are not associated with any data fields from one or more data sources. In some implementations, each visual variable 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.

[0029] In many cases, a measure is a numeric field and a dimension is a data field with a string data type. More importantly, the labels "measure" and "dimension" indicate how the data field is to be used.

[0030] For each metric m of the data field, the process identifies a corresponding set of reachable dimensions R(m), which consists of all dimensions d of the data field that are reachable from the corresponding metric m according to a sequence of many-to-one relationships in a predefined object model with respect to one or more data sources. Note that the sequence can have a length of zero, indicating the case where dimension d and metric m are in the same class. In some implementations, when dimension d and metric m are in the same class in the predefined object model, or when metric m is an attribute of the first class C1 in the predefined object model, dimension d is an attribute of the nth class C1 in the object model. n , and n ≥ 2, and there are zero or more intermediate classes C2,…,C in the predefined object model n-1 A sequence such that for each i = 1, 2, ..., n-1, in class C i and C i+1 When there is a many-to-one relationship, dimension d is reachable from metric m.

[0031] Note that there are also trivial cases where Because there are no dimensions associated with the visual variables, or there are some metrics that cannot reach any dimension. This is the set of valid reachable dimensions.

[0032] Establishing the set of reachable dimensions leads to a partitioning of the metrics. In particular, the relation defined by m1-m2 if and only if R(m1)=R(m2) is an equivalence relation. In most cases, there is only one partition (i.e., R(m) is the same for all metrics), but in some instances, there is more than one partition.

[0033] For each distinct set of reachable dimensions R, the process forms a corresponding set of data fields S. The set S consists of each dimension in R and each metric m in the data field that satisfies R(m)=R. Typically, each data field set includes at least one metric. In some implementations, any data field set without a metric is ignored. In some implementations, the data visualization application causes an error when a data field set S without a metric is identified. In some implementations, the data visualization application creates an additional data visualization for each data field set S without a metric (in addition to the data visualization created for each data field set S that includes one or more metrics).

[0034] For each data field set S and for each metric m in the corresponding data field set S, the process rolls up the value of the metric m to the level of detail specified by the corresponding dimension in the corresponding data field set S. Then, the process builds a corresponding data visualization based on the data fields in the corresponding data field set S and based on the corresponding visual variables associated with each data field in S.

[0035] In some implementations, building a corresponding data visualization includes retrieving tuples of data from one or more data sources using one or more database queries generated according to the visual specification. For example, for a SQL data source, the process builds a SQL query and sends the query to an appropriate SQL database engine. In some instances, the tuple includes data aggregated according to corresponding dimensions in a corresponding data field set S. That is, the aggregation is performed by the data source.

[0036] Typically, the generated data visualization is displayed in a graphical user interface (e.g., a user interface for a data visualization application) on a computer. In some implementations, displaying the data visualization includes generating a plurality of visual markers, where each marker corresponds to a respective tuple retrieved from one or more data sources. In some implementations, the graphical user interface includes a data visualization area, and the process displays the data visualization in the data visualization area.

[0037] In some implementations, rolling up the value of the metric m to a level of detail specified by a corresponding dimension in the corresponding data field set S includes dividing the rows of the data table containing the metric m into groups according to the corresponding dimension in the corresponding data field set S, and calculating a single aggregate value for each group.

[0038] In some implementations, a single aggregate value is calculated using one of the following aggregate functions: SUM, COUNT, COUNTD (count of distinct elements), MIN, MAX, AVG (median average), MEDIAN, STDEV (standard deviation), VAR (variance), PERCENTILE (e.g., quartiles), ATTR, STDEVP, and VARP. In some implementations, the ATTR() aggregate operator returns the value of the expression when it has a single value for all rows, and returns an asterisk otherwise. In some implementations, the STDEVP and VARP aggregate operators return values ​​based on a biased population or the entire population. Some implementations include more aggregate operators or aggregate operators different from those listed here. Some implementations use alternative names for aggregate operators.

[0039] In some implementations, data fields are classified as "dimensions" or "measures" based on how they are used. Dimensions divide data sets, while measures aggregate the data in each division. From a SQL mindset, dimensions are elements in the GROUP BY clause, and measures are elements in the SELECT clause. Typically, discrete categorical data (e.g., fields containing state, region, or product names) are used for division, while continuous numeric data (e.g., profit or sales) are used for aggregation (e.g., calculating a sum). However, all types of data fields can be used as dimensions or measures. For example, a discrete categorical field containing product names can be used as a measure by applying the aggregation function COUNTD (distinct count). On the other hand, numeric data representing a person's height can be used as a dimension to divide people by height or height range. Some aggregation functions (e.g., SUM) can only be applied to numeric data. In some implementations, applications assign a default role (dimension or measure) to each field based on the original data type of the field, but allow users to override the role. For example, some applications assign the default role of "dimension" to categorical (string) data fields and the default role of "measure" to numeric fields. In some implementations, date fields are used as dimensions by default because they are commonly used to partition data into date ranges.

[0040] The classification of a dimension or measure also applies to the expression being calculated. For example, an expression such as YEAR([Purchase Date] is often used as a dimension to partition the underlying data into years. As another example, consider a data source that includes a product code field (as a string). If the first three characters of the product code encode the product type, then the expression LEFT([Product Code],3) can be used as a dimension to partition the data into product types.

[0041] Some implementations enable a user to specify multiple levels of detail using an interactive graphical user interface. Some examples use two levels of detail, but implementations typically allow for an unlimited number of levels of detail. In some instances, data calculated based on an aggregation at one level of detail is used in a second aggregation at a second level of detail. In some implementations, a data visualization includes a "visualization detail level" that is used by default to calculate the aggregation. This is the level of detail that is visible in the final data visualization. Implementations also provide level of detail expressions that allow a user to specify a specific level of detail in a specific context.

[0042] Some implementations provide for shelf areas that determine the characteristics of a desired data visualization. For example, some implementations include a row shelf area and a column shelf area. A user places field names into these shelf areas (e.g., by dragging fields from the schema area), and the field names define the data visualization characteristics. For example, a user may select a vertical bar chart, and a column for each distinct value of a field is placed in the column shelf area. The height of each bar is defined by another field placed in the row shelf area.

[0043] According to some implementations, a method for generating and displaying data visualization is performed at a computer. The computer has a display, one or more processors, and a memory that stores one or more programs configured to be executed by the one or more processors. The process displays a graphical user interface on the display. The graphical user interface includes a mode information area that includes multiple fields from a database. The process receives user input in the graphical user interface to specify a first aggregation. The specification of the first aggregation groups data by a first set of one or more fields in a plurality of fields, and identifies a first aggregate output field created by the first aggregation. The process also receives user input in the graphical user interface to specify a second aggregation. In some instances, the specification of the second aggregation refers to the first aggregation. The second aggregation groups data by a second set of one or more fields. The second set of fields is selected from a plurality of fields and the first aggregate output field. The second set of fields is different from the first set of fields. The process establishes a visual specification based on the specification of the first aggregation and the specification of the second aggregation.

[0044] In some implementations, the process includes retrieving tuples of data from a database using one or more database queries generated from the visual specification. In some implementations, the tuples include data calculated based on a second aggregate. In some implementations, the process includes displaying a data visualization corresponding to the visual specification, wherein the data visualization includes data calculated based on the second aggregate. In some implementations, the displayed data visualization includes a plurality of visual markers, each marker corresponding to a respective tuple retrieved from the database. In some implementations, the graphical user interface includes a data visualization area, and the process displays the data visualization in the data visualization area.

[0045] In some implementations, the graphical user interface includes a columns shelf and a rows shelf. In some implementations, the process detects a user action to associate one or more first fields of the plurality of fields with the columns shelf and to associate one or more second fields of the plurality of fields with the rows shelf. The process then generates a visual table in a data visualization area based on the user action. The visual table includes one or more panes, wherein each pane has an x-axis defined based on data of the one or more first fields associated with the columns shelf, and each pane has a y-axis defined based on data of the one or more second fields associated with the rows shelf. In some implementations, the process receives user input to associate a second aggregation with the columns shelf or the rows shelf.

[0046] In some implementations, the process retrieves tuples from the database according to fields associated with the row tool shelf and the column tool shelf, and displays the retrieved tuples as visual markers in the visual table. In some implementations, each operator of the first aggregation and the second aggregation is one of: SUM, COUNT, COUNTD, MIN, MAX, AVG, MEDIAN, ATTR, PERCENTILE, STDEV, STDEVP, VAR, or VARP.

[0047] In some examples, the first aggregate output field is used as a dimension and is included in the second set.

[0048] In some implementations, the first aggregate output field is used as a metric, and the second aggregation applies one of the aggregation operators to the first aggregate output field. For example, in some instances, the second aggregation calculates the average of the values ​​of the first aggregate output field.

[0049] In some implementations, the process displays a graphical user interface on a computer display. The graphical user interface includes a schema information area and a data visualization area. The schema information area includes a plurality of field names, wherein each field name is associated with a data field from a specified database. The data visualization area includes a plurality of shelf areas that determine characteristics of the data visualization. Each shelf area is configured to receive user placement of one or more field names from the schema information area. The process establishes a visual specification based on user selection of the one or more field names and user placement of each user-selected field name in a corresponding shelf area in the data visualization area.

[0050] In some implementations, the data visualization includes a dashboard including a plurality of different component data visualizations. The visual specification includes a plurality of component visual specifications, and each component data visualization is based on a respective one of the component visual specifications.

[0051] In some implementations, the data visualization features defined by the visual specification include a marker type and zero or more encodings of the marker. In some implementations, the marker type is one of: a bar chart, a line chart, a scatter plot, a text table, or a map. In some implementations, the encoding is selected from a marker size, a marker color, and a marker label.

[0052] According to some implementations, a system for generating data visualization includes one or more processors, a memory, and one or more programs stored in the memory. The program is configured to be executed by the one or more processors. The program includes instructions for executing any method described herein.

[0053] According to some implementations, 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.

[0054] Thus, methods, systems, and graphical user interfaces are provided for interactive visual analysis of data sets.

[0055] This application provides the following:

[0056] 1) A method for generating data visualization, comprising:

[0057] At a computer having one or more processors and a memory storing one or more programs configured to be executed by the one or more processors:

[0058] 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 a corresponding one or more of the data fields, and each of the data fields is identified as a dimension or a measure;

[0059] identifying, from the object model of the data source, a minimum subtree including all of the dimensional data fields;

[0060] constructing a query to access the dimension data field from the minimum subtree;

[0061] executing the query against the data source to retrieve a set of tuples, each tuple comprising a unique ordered combination of data values ​​of the dimension data fields;

[0062] For each tuple, forming an extended tuple by appending the aggregate data value corresponding to each of the metric data fields; and

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

[0064] 2) The method according to 1), wherein the visual specification further comprises one or more additional visual variables, and the one or more additional visual variables are not associated with any data fields from the data source.

[0065] 3) The method according to 1), wherein the aggregated data value of the metric data field is aggregated according to the dimension data field.

[0066] 4) The method according to 1) further includes displaying the data visualization in a graphical user interface of the computer.

[0067] 5) The method according to 4), wherein displaying the data visualization includes generating a plurality of visual markers, each marker corresponding to a corresponding expanded tuple.

[0068] 6) The method according to 5), wherein the graphical user interface includes a data visualization area, and the method further comprises displaying the data visualization in the data visualization area.

[0069] 7) The method according to 1), wherein each of the visual variables is selected from the group consisting of: row attributes, column attributes, filter attributes, color encoding, size encoding, shape encoding, and label encoding.

[0070] 8) The method according to 1), wherein the aggregate data value is calculated using an aggregate function, and the aggregate function is selected from the group consisting of: SUM, COUNT, COUNTD, MIN, MAX, AVG, MEDIAN, ATTR, PERCENTILE, STDEV, STDEVP.VAR and VARP.

[0071] 9) The method according to 1), wherein the multiple data fields are from multiple different tables in the data source.

[0072] 10) The method according to 1), wherein the object model of the data source has a plurality of objects, and the plurality of data fields belong to two or more different objects among the plurality of objects.

[0073] 11) The method according to 1), wherein the visual specification specifies multiple data sources, the visual specification specifies one or more data fields from each of the multiple data sources, and the object model is an object model of the multiple data sources.

[0074] 12) A computer system for generating data visualization, comprising:

[0075] one or more processors; and

[0076] Memory;

[0077] 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:

[0078] 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 a corresponding one or more of the data fields, and each of the data fields is identified as a dimension or a measure;

[0079] identifying, from the object model of the data source, a minimum subtree including all of the dimensional data fields;

[0080] constructing a query to access the dimension data field from the minimum subtree;

[0081] executing the query against the data source to retrieve a set of tuples, each tuple comprising a unique ordered combination of data values ​​of the dimension data fields;

[0082] For each tuple, forming an extended tuple by appending the aggregate data value corresponding to each of the metric data fields; and

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

[0084] 13) The computer system of 12), wherein the visual specification further comprises one or more additional visual variables, the one or more additional visual variables not being associated with any data fields from the data source.

[0085] 14) The computer system according to 12), wherein the aggregated data value of the metric data field is aggregated according to the dimension data field.

[0086] 15) The computer system according to 12), wherein the one or more programs further include instructions for performing the following operations:

[0087] generating a plurality of visual tags, each tag corresponding to a respective expanded tuple; and

[0088] The data visualization is displayed in a graphical user interface of the computer system.

[0089] 16) The computer system of 12), wherein each of the visual variables is selected from the group consisting of row attributes, column attributes, filter attributes, color encoding, size encoding, shape encoding, and label encoding.

[0090] 17) The computer system according to 12), wherein the plurality of data fields are from a plurality of different tables in the data source.

[0091] 18) The computer system according to 12), wherein the object model of the data source has a plurality of objects, and the plurality of data fields belong to two or more different objects among the plurality of objects.

[0092] 19) A computer system according to 12), wherein the visual specification specifies multiple data sources, the visual specification specifies one or more data fields from each of the multiple data sources, and the object model is an object model of the multiple data sources.

[0093] 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 a memory, the one or more programs including instructions for performing the following operations:

[0094] 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 a corresponding one or more of the data fields, and each of the data fields is identified as a dimension or a measure;

[0095] identifying, from the object model of the data source, a minimum subtree including all of the dimensional data fields;

[0096] constructing a query to access the dimension data field from the minimum subtree;

[0097] executing the query against the data source to retrieve a set of tuples, each tuple comprising a unique ordered combination of data values ​​of the dimension data fields;

[0098] For each tuple, forming an extended tuple by appending the aggregate data value corresponding to each of the metric data fields; and

[0099] A data visualization is constructed and displayed based on the data fields in the expansion tuple and based on the visual variables associated with each of the data fields. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0101] Figure 1 A process of creating a data visualization according to some implementations is conceptually illustrated.

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

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

[0104] Figure 4 Example data visualization user interfaces are provided according to some implementations.

[0105] Figure 5 A simple object model with three classes is shown according to some implementations.

[0106] Figure 6 A single class is shown having two different relationships with another class according to some implementations.

[0107] Fig. 7A and Figure 7B A collection of bow ties between relationships between four classes and a data visualization that can be presented in this context are shown in accordance with some implementations.

[0108] Figure 8 A very simple object model is shown according to some implementations, where data visualizations are created for a single class.

[0109] Figure 9A-9C Creating a data visualization that includes dimensions from two different classes that are not hierarchically nested is shown according to some implementations.

[0110] Fig.10 and Fig.11 User selection of a metric that is a property of two or more different classes in an object model is shown according to some implementations.

[0111] Figure 12A-12CUser selection of one or more metrics hierarchically over one or more selected dimensions and corresponding data visualizations are shown in accordance with some implementations.

[0112] Figures 13A-13D User selection of measures and dimensions from two or more classes in a data model that are not connected in the model and corresponding data visualizations that may be generated are shown in accordance with some implementations.

[0113] Figure 14A-Figure 14C and Fig.15 Shown is a user selection of metrics from two or more different classes in an object model (with at least one hierarchical class connecting them) and a data visualization that can be generated for this scenario, according to some implementations.

[0114] Fig.16 A pseudo-code description for determining which dimensions in an object model are reachable according to some implementations is provided.

[0115] Fig.17 is a screenshot of a user interface window for defining filters in a data visualization application according to some implementations.

[0116] Figures 18A-18C A flow chart of a process for using an object model when building a data visualization according to some implementations is provided.

[0117] Fig.19A and Fig.19B Two examples of object model diagrams are provided according to some implementations.

[0118] Fig.19C An object model diagram is shown in which not all dimensions are reachable from a root, according to some implementations.

[0119] Fig.19D An object model diagram is shown in which there are two different paths from one node to another node, according to some implementations.

[0120] Figures 20A-20F According to some implementations, a Snowflake example schema is provided that can be used to track sales.

[0121] Figure 21A-Figure 21I According to some implementations Figures 20A-20F The example is extended to trees that are not snowflakes.

[0122] Figure 22A-22I Having metrics and dimensions in different branches of a tree is shown according to some implementations.

[0123] Fig.23According to some implementations, a simple model for line items and orders is provided.

[0124] Figure 24A-Figure 24E Using an object model to accurately generate counts for data visualization is shown according to some implementations.

[0125] Figure 25A-Figure 25C Using an object model to accurately generate sums for data visualization is shown according to some implementations.

[0126] Figure 26A-26D and Figure 27A-Figure 27I Using an object model to apply filters in data visualization is shown according to some implementations.

[0127] Figure 28A-28C Using an object model to apply multiple filters in a data visualization is shown according to some implementations.

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

[0129] Reference will now be made in detail to the implementations of which examples are shown in the accompanying drawings. In the following detailed description, many 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. DETAILED DESCRIPTION

[0130] Some implementations of interactive data visualization applications use the data visualization user interface 102 to establish a visual specification 104, such as Figure 1 104 . The visual specification 104 identifies one or more data sources 106 that may be stored locally (e.g., stored on the same device displaying the user interface 102) or may be stored externally (e.g., stored 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.

[0131] 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 assigned data fields of the visual variables (e.g., the order in which the data fields are assigned to the visual variables by the user) generally affects how the data visualization is generated and displayed.

[0132] Some implementations use the object model 108 to build appropriate data visualizations. In some instances, the object model applies to one data source (e.g., a SQL database or a spreadsheet file), but the object model can contain 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 the SQL database). However, in some cases, the object model is more standardized (or less standardized) than the physical data source. The object model groups attributes (e.g., data fields) that have a one-to-one relationship with each other to form classes, and identifies many-to-one relationships among classes. In the following illustration, many-to-one relationships are shown with arrows, with the "many" side of each relationship being lower than the "one" side of the relationship in the vertical direction. The object model also identifies each data field (attribute) as a dimension or measure. In the following, the letter "D" (or "d") is used to represent a dimension, and the letter "M" (or "m") is used to represent a measure. When the object model 108 is constructed, it can facilitate the establishment of data visualizations based on data fields selected by the user. Because a single data model can be used by an unlimited number of other people, building an object model for a data source is often delegated to someone who is an expert on the data source.

[0133] 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 294. In many cases, all of the user-selected data fields are in a single data field set 294. In some instances, there are two or more data field sets 294. Each metric m is in exactly one data field set 294, but each dimension d can be in more than one data field set 294. Fig.10 , Fig.11 , Figure 13A-13C , Figure 14A-Figure 14C , Fig.15 , Fig.16 ,and Figures 18A-18C The process of building data field set 294 is described in more detail.

[0134] The data visualization application 222 (or web application 322) queries 112 the data source 106 for the first data field set 294, and then generates a first data visualization 122 corresponding to the retrieved data. The first data visualization 122 is constructed according to the visual variables 282 in the visual specification 104, the visual variables 282 having the data fields 284 assigned from the first data field set 294. When there is only one data field set 294, all of the information in the visual specification 104 is used to build the first data visualization 122. When there are two or more data field sets 294, the first data visualization 122 is based on a first visual sub-specification consisting of all of the information associated with the first data field set 294. 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 data field set 294, the filter is part of the first visual sub-specification and is therefore used to generate the first data visualization 122.

[0135] When the second (or subsequent) set of data fields 294 is present, the data visualization application 222 (or web application 322) queries 114 the data source 106 for the second (or subsequent) set of data fields 294, 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, the visual variables 282 having the data fields 284 assigned from the second (or subsequent) set of data fields 294.

[0136] Figure 21 is 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 implementations, the computing device displays a graphical user interface 102 of the data visualization application 222. The computing device 200 includes a desktop computer, a laptop computer, a tablet computer, 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 (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 circuits 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 implementations, the input device / mechanism includes a keyboard; in some implementations, the input device / mechanism includes a "soft" keyboard that is displayed on the display 208 as needed, enabling the user to "press" "keys" that appear on the display 208. In some implementations, the display 208 and the input device / mechanism 210 include a touch screen display (also known as a touch-sensitive display). In some implementations, the display is an integral part of the computing device 200. In some implementations, the display is a separate display device.

[0137] In some implementations, the memory 214 includes high-speed random access memory, such as DRAM, SRAM, DDRRAM, or other random access solid-state memory devices. In some implementations, the memory 214 includes 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 implementations, the memory 214 includes one or more storage devices located away from the CPU 202. The memory 214 or optionally a non-volatile memory device within the memory 214 includes a non-transitory computer-readable storage medium. In some implementations, the memory 214 or a computer-readable storage medium of the memory 214 stores the following programs, modules, and data structures, or a subset thereof:

[0138] An operating system 216, which includes procedures for handling various basic system services and for performing hardware-related tasks;

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

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

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

[0142] 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;

[0143] In some implementations, the user interface 102 includes a plurality of shelf areas 250 for specifying characteristics of a desired data visualization. In some implementations, the shelf area 250 includes a column shelf 230 and a row shelf 232 for specifying the arrangement of data in a desired data visualization. Typically, fields placed on the column shelf 230 are used to define columns in a data visualization (e.g., x-coordinates of visual markers). Similarly, fields placed on the row shelf 232 define rows in a data visualization (e.g., y-coordinates of visual markers). In some implementations, 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 implementations, the shelf area 250 includes a tag shelf 264 for specifying various encodings of data tags. In some implementations, the markup tool shelf 264 includes a color coding icon 270 (to specify the color of the data marker based on the data field), a size coding icon 272 (to specify the size of the data marker based on the data field), a text coding icon (to specify a label associated with the data marker), and a view level detail icon 228 (to specify or modify the level of detail of the data visualization);

[0144] · A visual specification 104 that is used to define the characteristics of a desired data visualization. In some implementations, 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 find 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 implementations, the visual specification has a visual variable corresponding to each shelf area 250. In some implementations, the visual variables also include other information, such as contextual information about the computing device 200, user preference information, or other data visualization features that are not implemented as shelf areas (e.g., analytical features);

[0145] · 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, and the many-to-one relationships between classes correspond to foreign key relationships between 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 the appropriate class objects. In some instances, the raw data source is a simple file (e.g., a spreadsheet) that is converted into multiple classes;

[0146] Data visualization generator 290, which generates and displays data visualizations according to the visual specification. According to some implementations, data visualization generator 290 uses object model 108 to determine which dimensions in visual specification 104 are reachable from the data fields in the visual specification. For each visual specification, the process forms one or more reachable dimension sets 292. This is described below in Fig.10 , Fig.11 , Figure 13A-13C , Figure 14A-Figure 14C , Fig.15 , Fig.16 ,and Figures 18A-18C Each reachable dimension set 292 corresponds to a data field set 294 , which typically includes one or more metrics in addition to the reachable dimensions in the reachable dimension set 292 .

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

[0148] Zero or more databases or data sources 106 (e.g., first data source 106-1) used by 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 relational databases, cloud databases, or statistical databases.

[0149] Each of the executable modules, applications or program sets identified above can be stored in one or more of the aforementioned memory devices, and corresponds to a set of instructions for performing the above functions. The modules or programs (i.e., 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 implementations. In some implementations, memory 214 stores the subsets of the modules and data structures identified above. In some implementations, memory 214 stores additional modules or data structures not described above.

[0150] 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 implementations 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.

[0151] Figure 3 3 is a block diagram of a data visualization server 300 according to some implementations. The data visualization server 300 may host one or more databases 328, or may 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, a memory 314, and one or more communication buses 312 for interconnecting these components. In some implementations, the server 300 includes a user interface 306, which includes a display 308 and one or more input devices 310, such as a keyboard and a mouse. In some implementations, the communication bus 312 may include circuits (sometimes referred to as a chipset) that interconnect and control communications between system components.

[0152] In some implementations, the 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 implementations, the memory 314 includes one or more storage devices located away from the CPU 302. The memory 314 or, optionally, a non-volatile memory device within the memory 314 includes a non-transitory computer-readable storage medium.

[0153] 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:

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

[0155] 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.);

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

[0157] a data visualization web application 322 that can be downloaded and executed by a web browser 220 on a user's computing device 200. In general, 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 implementations, the data visualization web application 322 includes various software modules to perform certain tasks. In some implementations, 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 implementations, the user interface module 324 specifies a shelf area 250, as described above for the computing device 200;

[0158] 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;

[0159] One or more object models 108, as described above with respect to computing device 200;

[0160] 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 describing data sources 106. The operation of the data visualization generator is described above with reference to computing device 200 and is described below in Fig.10 , Fig.11 , Figure 13A-13C , Figure 14A-Figure 14C , Fig.15 , Fig.16 ,and Figures 18A-18C described in;

[0161] In some implementations, 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 implementations, data from two or more data sources can be fused. In some implementations, 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;

[0162] 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 database 328 can store the data sources 106 that provide the data used in the generated data visualization. Each data source 106 includes one or more data fields 330. In some implementations, the database 328 stores user preferences. In some implementations, the database 328 includes a data visualization history log 334. In some implementations, the history log 334 tracks each reproduction of the data visualization by the data visualization generator 290.

[0163] The 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. The data fields 330 include original fields from the data source (e.g., columns from a database table or columns from a spreadsheet) and 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 date fields, calculating the span of time between two date fields, calculating the cumulative total of quantitative fields, calculating percentage growth, etc. In some instances, derived data fields are accessed through procedures or views stored in the database. In some implementations, the definitions of derived data fields 330 are stored separately from the data source 106. In some implementations, the database 328 stores a collection of user preferences for each user. When the data visualization web application 322 (or application 222) makes suggestions on how to view a collection of data fields 330, user preferences can be used. In some implementations, the 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 in the data visualization server (or both).

[0164] In some implementations, the data visualization history log 334 stores the visual specifications 104 selected by the user, which may 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., color and size of markers), selected data relationships, and what connectors were used. In some implementations, one or more thumbnail images of each data visualization are also stored. Some implementations 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.

[0165] Each of the executable modules, applications, or program 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 functions. The modules or programs (i.e., 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 implementations. In some implementations, memory 314 stores a subset of the modules and data structures identified above. In some implementations, memory 314 can store additional modules or data structures not described above.

[0166] although Figure 3 A data visualization server 300 is shown, but Figure 3 300. In some implementations, functions and / or data may be distributed between computing device 200 and one or more servers 300. In addition, as one skilled in the art will recognize, Figure 3 It does not necessarily represent a single physical device. In some implementations, server functionality is distributed across multiple physical devices that comprise a 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 do not necessarily have to be physically co-located (e.g., individual physical devices may be located throughout the United States or throughout the world).

[0167] Figure 4 A data visualization user interface 102 is shown according to some implementations. The user interface 102 includes a mode information area 410, also referred to as a data pane. The mode information area 410 provides named data elements (e.g., field names) that can be selected and used to build a data visualization. In some implementations, the list of field names is divided into a set of dimensions and a set of metrics (typically numeric quantities). Some implementations 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.

[0168] 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 initially has no visual graphics.

[0169] 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 uses the selected fields to define a visual graphic. The data visualization application 222 (or web application 322) displays the generated graphic 122 in the data visualization area 412. In some implementations, the information provided by the user is stored as the visual specification 104.

[0170] In some implementations, the data visualization area 412 includes a labeling tool shelf 264. The labeling tool shelf 264 allows a user to specify various encodings 426 for data labels. In some implementations, 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, which can be used to specify or modify the level of detail of the data visualization.

[0171] The object model can be depicted as a graph with classes as nodes and their many-to-one relationships as edges. As shown in this article, these graphs are arranged so that the "many" side of each relationship is always below the "one". For example, in Figure 5 , the office class 502 has a many-to-one relationship 512 with the company class 504, and the office class 502 also has a many-to-one relationship 514 with the country class 506. In this diagram, a company can have multiple offices, and a country can have multiple offices, but each office belongs to a single company and country. Figure 5 The object models in the data source 106 are connected, but not all object models are connected. Typically, the object model 108 of the data source 106 is pre-built. When the user later builds data visualization, the structure of the object model 108 helps to generate appropriate data visualization.

[0172] In general, any pair of classes is joined by at most one path through the relationship graph. When multiple paths are possible, the user may need to specify which path to use, or to rollback the dataset columns to combine two paths into one.

[0173] Some of the following figures illustrate various object models and user selections of dimensions D and measures M in the object models. Based on the location of the dimensions and measures within the object model, the data visualization generator 290 determines how many different data visualizations to generate and what to build. In this context, it is useful to define the concept of a dimension that is reachable from another data field in the object model. In particular, when there is a sequence of many-to-one relationships starting from the class containing the data field and ending with the class containing the dimension, the dimension D is reachable from the data field. Furthermore, if dimension D is in class C, then dimension D is reachable from all other data fields in class C. In this case, there is a sequence of zero many-to-one relationships starting with the data field and ending with the dimension.

[0174] With this definition of "reachable", the set of dimensions reachable from a given node in the graph can be defined. In particular, for each data field (dimension or metric) in the visual specification 104, the reachable set 292 of dimensions is all dimensions in the visual specification at the same level of detail (LOD) as the given data field, or all dimensions reachable by traversing the graph upward from the data field.

[0175] For each data field, it is also useful to identify the reachable set of visualization filters. This includes all filters on reachable dimensions. Note that metric filters can be implicitly treated as dimension filters at the appropriate level of detail.

[0176] Fig.16 A pseudo-code query is provided for determining the reachable dimensions for each data field (dimension or measure) in an object model.

[0177] For each data field, the set of reachable dimensions and reachable filters forms an implicit snowflake pattern centered around the data field. This means there is a well-defined and unique way to apply filters to data fields and aggregate measures. Displaying the results of the query for each data field itself makes it easy to interpret the results.

[0178] Besides making it easier for users to build desired visualizations, using reachable dimensions also improves the performance of data retrieval. Queries are faster because they only have to join dimensions that are reachable through many-to-one relationships. This can be understood as a generalization of the behavior of active join elimination. Queries only have to touch the tables that are absolutely necessary to create the desired visualization.

[0179] For data visualization using N data fields, this process can result in a theoretical maximum of N different queries. However, many of these queries are redundant. The results of one query can be included in the results of one or more other queries. In addition, queries that compute metrics at the same level of detail can be combined. Therefore, this process typically runs fewer queries.

[0180] From a performance perspective, generating multiple independent queries rather than a single monolithic query through a many-to-many connection has an additional advantage: the queries can be run in parallel. Because of this, some implementations can start rendering data visualizations before all queries return their results.

[0181] Given the query semantics described above, there are two main challenges that arise: multiple levels of detail and multiple domains. First, independent queries can produce results at different levels of detail. This is not particularly problematic if the levels of detail are nested (e.g., (state, city) vs. state). The process can simply copy the coarser LOD values ​​to the finer LOD. It is more challenging when the LODs partially overlap (e.g., (state, city) vs. (state, ZIP)) or are disjoint (e.g., (state, city) vs. (product, subproduct)). Second, independent queries may produce results with different domains. For example, calculating the SUM(population) per state may return entries for each of the 50 states (if the population table is complete for the United States). However, calculating the SUM(sales) per state only returns states where there are sales transactions. If the sales table does not include transactions in 10 states, then the query will only return results for 40 states.

[0182] To handle multiple levels of detail, the process begins by combining query results at the same level of detail into an aggregated results table. The process also combines nested query results (those in a strict subset / superset LOD relationship). This results in duplication of nested results, but is not detrimental because it allows comparison of totals and subtotals.

[0183] Even after combining all of these cases, there are still instances with multiple result tables when the levels of detail partially overlap or are disjoint. In these instances, implementations use various methods for visualizing the results.

[0184] In addition to handling multiple levels of detail, the implementation also handles other scenarios. There are instances where a data field has two or more different domains of values. For example, the set of all states may be different from the set of states with orders, which may be different from the set of states with employees. The object model allows a single logical concept (e.g., "state") to have multiple domains associated with it.

[0185] Another scenario is when there are multiple root ("fact") tables. The presence of multiple fact tables can introduce many-to-many relationships and duplication (repetition). In addition, multiple fact tables can change how joins are implemented. In many cases, a single fact table with a snowflake structure can be queried by presenting the joins like a tree. However, with multiple fact tables, there is an inherent ambiguity about which table is designated as the center of the snowflake, and joining among the tables in this way may not be a good visualization of the user's data model.

[0186] Another scenario arises when a table has two or more references to the same table (and its subtrees). For example, consider a scenario where each order includes an OrderDate and a ShipDate. There are various ways this data can be structured. In the first case, both OrderDate and ShipDate are data fields in the Orders table itself. In this case, the data is easily queried. In the second case, there are separate OrderDate and ShipDate tables, so there are separate joins between the Orders table and each of these different tables. In the third case, the OrderDate information and the ShipDate information are merged into a single Date table 604, as shown in Figure 605. Figure 6 . In this case, there is a first subset 606 of rows corresponding to order dates (e.g., with a date type of "order") and a second subset 608 of rows corresponding to shipping dates (e.g., with a date type of "shipping"). In this case, the order table 602 has two connections to the same date table: a first connection 616 to rows representing order dates and a second connection 618 to rows representing shipping dates.

[0187] Relationships between classes or database tables can also have a "bow-tie" arrangement. Fig. 7A The visualization in 704 uses opportunity number, case duration, product, and customer name. To display this information, two linked data visualizations 704 and 706 are used, both with a row corresponding to product 702. This uses a data visualization with a row such as Figure 7B 724 has an independent relationship with the dimension table dimAccount 712 and the dimension table dimProduct 728. These four relationships 730 look like a bow tie. The colloquial name "bow tie" is the idea that two fact tables can be associated with two dimension tables, which imposes a crossover in their relationship.

[0188] Some implementations also handle normalizing "flat" tables. For example, a data source (e.g., a data warehouse) may include data representing multiple conceptual classes. Some implementations use well-known normalization techniques to build object models that represent meaningful classes.

[0189] In each object model diagram, the sources of the dimensions and metrics of the current visual specification 104 are labeled "D" and "M". In the simplest case, there is a single class that includes all dimensions and metrics from the current visual specification 104, such as Figure 8 . In this scenario, measures and dimensions are from the same class and the calculation semantics are trivial. The process rolls up the measures to the dimension level of detail. This results in a single table containing the dimensions and the aggregated measures.

[0190] It is not necessarily obvious to create a small multiple layout for this result table, because there are no modeled relationships between attributes in a single class. However, the shelf model (rows 232 and columns 230) provides a set of heuristics to handle this. Dimensions on the same shelf create hierarchical axes (e.g., Fig. 9B ), while dimensions on different shelves create crossing axes. Multiple measures on the same axis are linked (as shown by Fig. 7A The other tool shelves are used to create tags and define their visual appearance.

[0191] In the snowflake model, metrics come from a single class, such as Fig. 9A . Dimensions come from the same class or classes that can be reached by traversing the relationship graph upward from the class of the measure. This process connects the dimensions to the classes of the measure class along the relationship edges. The measures are aggregated all the way to the dimension level of detail. All other classes are ignored. For example, in Fig. 9A , all measures are from the Orders table 904, and the data visualization has dimensions in the Product class 902 and the Region class 908. The dimensions in the Product class 902 and the Region class 908 define the level of detail used to aggregate the measures from the Order class 904. In this example, there are no dimensions or measures for the data visualization from the State class 906, so its data is not used. However, the State class 906 may be needed in a query to connect the measures from the Order class 904 to the Region class 908.

[0192] The object model indicates whether there are nested or intersecting relationships between dimensions, which is used in some implementations to determine default behavior. Fig. 9AIn the example of , product and region are independent of each other, so they should be crossed in a small multiple layout regardless of whether they are on the same axis. However, some implementations enable users to override the default behavior. For example, even when the model shows crossed relationships between independent dimensions, users may want to nest purely based on relationships defined by actual data values. Conversely, even when nesting makes sense, users may want to show crossed visualizations and introduce white space where there is no corresponding pairing between nested dimensions.

[0193] Display the cross dimension on the same axis Fig. 9B . In this case, the user places two independent dimensions, segment 920 and category 922, on the same shelf (column shelf 230). The user places the measure sales 924 on the row shelf 232, which is aggregated here using COUNT(). The segment and category dimensions are independent, so by default they are intersected. The top column header 940 lists the segments, while the lower column header 942 lists the categories. Because of the intersecting, each category is displayed for each segment, and this will appear even if there is no data for some segment / category combinations. In some implementations, the user interface 102 handles user interactions to handle the intersecting. For example, in some implementations, selecting a header in the intersecting axis (e.g., the first furniture header 926) results in selecting all corresponding data (e.g., furniture data 930, 932, and 934 for all columns with the same furniture category).

[0194] In some implementations, when there are nested dimensions on different axes, the display uses an alternative technique to show only the relevant combinations, such as Fig. 9C In this case, the data field "Subcategory" is nested under the data field "Category", and the two data fields are on different axes. The subcategories of each category are completely different (for example, Bookshelves and Chairs in the Furniture category, but Appliances, Artwork, and Binders in the Office Supplies category). Because of this, for each row in the data visualization, the display includes a separate set of column headers. This is sometimes called a grid chart. It ensures that each subcategory is only displayed in the appropriate category.

[0195] Fusion is a generalization of the snowflake case, where metrics can come from multiple classes. Fig.10 . The dimension is reachable from all metrics through a single class (sometimes called "connected LOD"). Fig.10 , state class 1004 is "Connect LOD".

[0196] If each measure is considered separately, the scenario is the same as snowflake. Each measure can be rolled up to the dimension LOD independently. Then, the aggregated measures can be joined together at the dimension LOD. For example, the measures from the Orders table 1002 are aggregated according to the dimensions in the Region class 1008, and the measures from the Suppliers class 1006 are also aggregated according to the dimensions in the Region class 1008. Because the measures from the Orders class 1002 and the Suppliers class are aggregated at the same level of detail, the result set can be directly joined to form a single table at the dimension LOD.

[0197] If the data visualization has no dimensions (e.g., it shows totals), then it is a trivial case of merging, since all (zero) dimensions can be reached from any measure. In this case, all measures are rolled up to the empty dimension LOD (i.e., one aggregate data value is calculated for each measure), and the aggregate value is displayed in a single visualization. This works even when the object model is not connected.

[0198] The restriction that all dimensions are reachable from the same class can be relaxed. As long as all dimensions are reachable from all metrics by traversing the graph upwards, the process can use the same query semantics and visual layout as standard fusion. This is done in Fig.11 , which adds product class 1110 to Fig.10 In this example, the metrics from orders 1002 and suppliers 1006 can be rolled up based on the dimensions in region class 1008 and product class 1110, despite not going through a single connection LOD.

[0199] Note that the relationship of dimensions to each other (crossing or nesting) is independent of the measure set used, so the layout of small multiple displays can use the same rules as simple fusions.

[0200] Sometimes metrics can be defined on top of object model dimensions, such as Fig. 12A As shown. In this case, the measure in the region class 1208 (perhaps population) will not be aggregated at all. Instead, the measure will be replicated (copied) as an attribute to the dimension LOD (according to the dimensions in the order class 1202 and the product class 1210). In fact, the data visualization application sees this measure as just another dimension.

[0201] To make this behavior clear to the user, some implementations suppress the aggregation function of the corresponding pills on the row tool shelf 232 or column tool shelf 230, such as Fig. 12C1250, the user interface displays Profit 1252. In some implementations, the user interface changes the representation of the expression in other ways, such as visually de-emphasizing it. In addition, some implementations visually confirm the duplication in the selection, such as repeated headers in the intersecting axes. For example, Fig. 12B , all rows 1230 in the data visualization display the same value for category sales (only one data value), and the rows are highlighted together when viewed.

[0202] A more challenging level-of-detail problem arises when not all dimensions are reachable from all non-attribute measures. For example, consider Fig.13A , which has a company class 1302 and a country class 1304, but no relationship defined between them. This is not as strange as it might seem, because the user does not have to define the relationship between two objects before the objects are used.

[0203] In some implementations, this results in two vertically linked data visualizations 1322 and 1324, such as Fig. 13B In some implementations, two different data visualizations are linked horizontally, such as Fig. 13C and Fig.13D As shown. Fig. 13C , the tops of the two visualizations 1332 and 1334 are aligned horizontally. Fig.13D , the bottoms of the two data visualizations 1342 and 1344 are aligned horizontally. Note that Fig. 13C and Fig.13D The plots in do not share an axes.

[0204] In some implementations, when there are multiple data visualizations, one visualization is displayed at a time, allowing the user to browse through them. To help the user understand which fields were used to generate the visualization, some implementations gray out unused fields or filters when the corresponding visualization is not displayed or has no focus.

[0205] In some instances, dimensions and measures are linked through a many-to-many relationship, such as Fig.14A In this example, the dimensions and measures from the customer class 1402 are connected to the state class 1406 through a many-to-one relationship. Similarly, the dimensions and measures from the company class 1404 are connected to the state class 1406 through a many-to-one relationship.

[0206] This situation is very similar to Fusion, except that dimensions are included from two bottom classes 1402 and 1404. The result is that dimensions from the Company class 1404 are not reachable from measures in the Customer class 1402, and vice versa. This situation is similar to Fig.13A, except that an additional state class 1406 is added.

[0207] In some scenarios, some implementations combine fused and disconnected behaviors, nested within states, to display separate data visualizations about customers and companies, e.g. Fig. 14B As shown. Fig. 14B As shown, the state dimension is reachable from both the customer class 1402 and the company class 1404, so the overall visualization is aligned by state 1420. For example, Fig. 14B The partial data visualization in shows data for Alabama 1422 and Arizona 1424. Within each horizontal region of a state, there is a list of companies 1426, along with visual markers 1428 representing the sales of each company. In addition, each horizontal state region also has a list of customer names 1430, along with visual markers 1432 (e.g., bars) representing the profit of each of these individual customers. This shows partial alignment based on the shared dimension from the state category 1406.

[0208] Fig. 14C Some implementations are shown using Fig.14A The same object model is shown here to create data visualizations with different selections of data fields. Fig. 14C , the user has selected the metric Sales from the Customer class 1402, and has also selected the metric Sales from the Company class 1404. However, the user has not yet selected any dimensions from either of these two classes. Because the dimension State is reachable from all measures, there is a single data visualization with each measure rolled up to State. However, with these two fact tables and limited sales, the question arises as to which states to display. In some implementations, the default behavior is to perform a full outer join, displaying each state, along with the Company sales and Customer sales for each state (blanks indicate zeros). For example, in Fig. 14C , Alabama has no company or customer sales 1450. California, on the other hand, has company sales 1452 and customer sales 1454. Some states have only one or the other, such as Florida has only company sales 1456, while Illinois has only customer sales 1458. In some implementations, the user can select what data to display (e.g., omit states that have no data from either customer class 1402 or company class 1404). In some implementations, states with no company or customer activity are omitted by default.

[0209] Fig.15 Shown with Fig.14A Same scenario, except that the dimension from the State class 1506 is omitted. Due to the object model, it is known that the Customer class 1502 and the Company class 1504 have a many-to-many relationship through the State class 1506. However, the user did not request that the State be included in the visualization.

[0210] In this scenario, some implementations simply ignore the links that go through the state and produce the same visualization as in the disconnected scenario ( Figures 13A-13D ). This approach has the following advantages: The transition from not having states in the data visualization to having states included is somewhat natural. The user starts with two separate lists, then adding states will create two separate lists for each state.

[0211] Some implementations handle this scenario in another way. Some implementations produce separate visualizations, but automatically highlight the linked behavior on the states. In some cases, this makes sense. For example, if customers can be served by companies in the same state, then clicking on a customer in the customer list will highlight the companies in their state that can serve them. Conversely, clicking on a company will highlight the customers they can serve. On the other hand, when there is no such interesting semantics, highlighting companies in the same state as some customers can be a distracting or counterproductive default behavior. This type of cross-highlighting can also be computationally expensive, so making it the default behavior is impractical for implementations on devices with limited computing resources.

[0212] Based on these examples, some implementations perform the following steps based on the selected dimensions and metrics and the corresponding object models. First, the process divides the metrics in the visual specification 104 by the set of dimensions that are reachable from each metric (creating one or more reachable dimension sets 292). Second, for each set of metrics that can reach the same set of dimensions, the process rolls up the metrics to the dimensional detail level. Each reachable dimension set 292 forms a data field set 294 with its corresponding metrics. Third, the process creates a separate data visualization for each data field set 294 using the visual variable mappings associated with the dimensions and metrics in the data field set. The process ignores all other mappings.

[0213] Some implementations also handle Fig. 14B In this case, there are separate customer and company lists nested within states. In this case, it is useful to nest the separate visualizations within a common dimension. Similar scenarios arise where it is useful to display a set of measures as subtotals or totals interleaved with or in the margins of a display for another set of measures.

[0214] Some implementations prevent users from entering scenarios with multiple visualizations by limiting them to one of the easier scenarios above. For example, some implementations limit users to a fused scenario by requiring the user to select a "connect LOD" object for each graph and then disabling dimensions that are not reachable by walking up the graph from the connect LOD and metrics that are not reachable by walking down the tree.

[0215] In fusion, it is not always clear which path a many-to-one relationship takes. When a many-to-one relationship takes the expected path, fusion gives correct and useful results. When it does not go as expected, a "*" is displayed. Some implementations take a similar approach to the object model problem here. For example, instead of making separate lists, some implementations display the cross product of the lists and copy the metrics. This is described in more detail below.

[0216] How filters are applied is closely related to the domain problem. Filters should be applied explicitly down the relationship graph (from one to many). Figure 5 In the object model, a filter on Company should also apply to Office. Filtering up the graph (e.g., if the filter removes all Offices in the UK, should the UK also be filtered?) is more problematic.

[0217] In some implementations, each filter has a specified LOD, and it is applied at that level of detail.

[0218] In some implementations, when the above process results in two or more different data visualizations, the process performs a natural join of all aggregated result sets to produce a single cross-result table. This aggregate is a single table, so it can be presented in the usual way. This is an extended form of data fusion, as described in more detail below. This method duplicates data, but the duplication occurs after aggregation, so it is less likely to be analytically incorrect.

[0219] To help users understand how a particular field is calculated, some implementations gray out and filter out all fields that are not reachable from a pellet in the tool shelf area when the user clicks on the pellet. For example, the hover text of the pellet indicates "SUM(Sales) is calculated for each state, filtered by Ship Date (1 dimension and 2 filters not used)".

[0220] This approach may duplicate a large amount of data. This may result in a large amount of data markup, which may cause rendering performance issues. However, the duplication is done after all calculations are complete, so query times are not affected. The duplication of data may cause some confusion to the user. Some implementations address this issue by interactively highlighting the duplicated data in the visualization for the user. Optionally, some implementations avoid automatically stacking data when it is known that data is duplicated. When viewing the data, some implementations display separate result sets to help the user understand the level of detail for each data point.

[0221] Some implementations combine data fusion with the object model for building data visualizations. Data fusion is a special data integration feature that allows users to easily connect to multiple different data sources and build visualizations that use data from all of them. This enables users to answer common business analysis questions when related data may be stored in different locations (e.g., Tableau Server, corporate data warehouse, spreadsheet files, and CSV files).

[0222] Data fusion eliminates the distinction between primary and secondary data sources. In data fusion, there is no user-visible distinction between primary and secondary data sources. An important implication of this symmetry is that users will be able to fuse "chains" of data sources together (e.g., A is fused with B, and B is fused with C), allowing the creation of non-star schemas.

[0223] Data Fusion provides full outer join semantics, rather than being limited to left join semantics between a primary data source and all secondary data sources. Therefore, the domain of analysis is not limited to the set of entries in the primary data source. In Data Fusion, the default is to always display all data from all data sources. Users can control this behavior through filters and / or settings in the data source relationships. In addition, related fields are treated interchangeably, always displaying the entire merged field regardless of which data source it comes from. For example, if two different tables are joined on a state data field, the state data field from either table can be used in the same way. If users want to limit the domain to one of the input data sources, they can place the related field on the filter shelf and get a special filter dialog option that allows multi-selection of data sources contributing to the domain.

[0224] Data Fusion eliminates the need to manage link status in the Schema Viewer. Links in the Schema Viewer allow users to control the level of detail when fused data sources are connected. In Data Fusion, the need for per-diagram link icons is eliminated due to outer join semantics. Users still need to specify data source relationships, but the UI for this makes this easier.

[0225] Data fusion supports all calculations and data modeling concepts everywhere. All calculations work on all data sources, because there is no source specified as the primary data source in data fusion. In particular, non-additive aggregations (such as COUNTD and MEDIAN) work on all data sources; dimensions from all data sources use row-level data to divide the view (ATTR aggregation is not used by default); cross-data source calculations work on row-level data and can be used as dimensions; data from all data sources is geocoded and the results can be used as dimensions in visualizations; and sets, groups, bins, combined fields, and LOD expressions work consistently regardless of which data source they come from.

[0226] Data fusion provides rich data source relationships. In data fusion, users can fuse on geocoding results (and in some implementations users can fuse with table calculations). In addition, users can specify a richer set of relational operators (such as spatial inclusion) along with more standard operators (such as < and ≠).

[0227] It is useful to compare the specific process of data fusion to the join performed in data integration. There are at least three parts of data integration that are related to data fusion. First, data integration semantics generally require that the join occur at the beginning of the data processing pipeline. This has many undesirable consequences that are best addressed with data fusion.

[0228] The user experience for data integration starts in the data modeling tool. Users have to make complex decisions, such as which database tables to include in their data source and which connection types to use before they can see their data. In contrast, data fusion allows users to incrementally build the set of tables they use in their analysis and define relationships only when necessary. Some implementations even infer some default relationships for them. This aspect of data fusion is the default experience in some implementations. Users only have to define specific connections in rare scenarios where, for example, the replication behavior of a connection is actually desirable.

[0229] Joining before aggregation copies data, which often makes the aggregation inaccurate. This can sometimes be avoided using LOD expressions to undo the copying. On the other hand, in data fusion, the behavior of joining after aggregation addresses a much wider range of analytical scenarios and is a better default. In addition, performing the join after aggregation is usually much more efficient.

[0230] Joins change the user's data. Inner, left, and right joins filter the input data, changing the domain of the user's data. Left, right, and outer joins introduce NULLs into fields, which can also be very confusing, especially if there are already NULLs in the data. By delaying the join until later in the pipeline and by not exposing the details of the join to the user, data fusion has the flexibility to provide better behavior by default.

[0231] For the reasons above, some implementations provide user interfaces where fusion semantics are the default. Specifying concrete connections in the data source definition is allowed, but this would be an advanced scenario to cover a small subset of data integration use cases.

[0232] The second way to compare data integration and data fusion is the connection diagram UI in the data preparation window. Several implementations of data fusion utilize the same basic connection diagram.

[0233] The third way to compare data integration and data fusion is with respect to data federation. Some implementations of data fusion use data federation. This means that the fusion calculations can be moved to where the federation is performed (e.g., Tableau Data Engine).

[0234] In data fusion, all data sources behave essentially like a "primary" data source. An important implication of this design is that when there is a many-to-many relationship between dimensions from multiple data sources, the data visualization generator 290 can visually replicate measures across multiple marks. This is expected. In fact, this is exactly how LOD expressions work. If two LOD expressions calculate a coarser aggregation than the visualization LOD, each of them is replicated across all marks.

[0235] An important point to note is that with merge semantics, joins may introduce duplicate markers, but the aggregate value is still meaningful. In contrast, joins in data integration first copy the data, often resulting in meaningless aggregate values ​​and duplicate markers. Therefore, merge semantics is the default behavior.

[0236] Geocoding can be applied to both tables first to allow convergence on the spatial type resulting from the geocoding. This is treated as convergence on any other calculation.

[0237] Densification is applied after data fusion. Using full outer join semantics avoids the need to apply densification first, since all data is pulled from both data sources.

[0238] When using data fusion, all related fields have a "Use selected data sources" option 1702 instead of the "Use All" option, such as Fig.17 Here the user can specifically select which data source fields should be merged to produce the fields of the related field.

[0239] A common filter on a related field is applied at row level to all related tables on the domain of the related field (which is the union of the domains of the input fields).

[0240] Conditions and top N filters on related fields are treated just like non-related field filters on the table containing the field used in the sort calculation.

[0241] Filters on non-related fields are always applied to the source table at the row level.

[0242] Filters on non-related fields are calculated on the source table, and then the table is rolled up to the related field level to obtain the domain of related fields that pass the filter. The filter is then applied to remove values ​​that do not pass the filter. Importantly, it is not used to remove values ​​that exist in the related table but not in the source table.

[0243] Some implementations generate query trees for database queries. At a high level, some implementations use the following pattern to generate queries for visualizations:

[0244] Apply data source filters to each data source.

[0245] Evaluate data source specific calculations, including geocoding.

[0246] Create a join tree across filtered data sources using defined merge relationships. Use outer joins by default anywhere the user doesn't explicitly specify not to go. Insert evaluations of cross-data source calculations where necessary in the tree (if a cross-data source calculation is used as a relationship field, it needs to be evaluated in the middle of the join tree). Equally related fields are merged into a single field.

[0247] · Apply dimension filters. Importantly, filters that do not explicitly exclude "unmatched" values ​​are assumed to retain them.

[0248] For each data source, select a different combination of data relational fields and visualization dimensions from that data source. The result is a table that maps from the relational fields of the data source (the "join LOD") to the LOD of the visualization.

[0249] Join the table back to the corresponding data source and apply a “group by” to roll up the results to the visualization LOD. All aggregations are rolled up directly from the row level to the visualization LOD.

[0250] • Join all resulting tables together on the visualization LOD column to produce the visualization data table.

[0251] • Metric filters are applied, followed by the rest of the standard query pipeline.

[0252] For LOD expressions, filters with aggregate conditions, or top-N filters, a subquery is generated that replicates a fragment of the above tree.

[0253] While the above schema specifies the general process of building a query tree for any fusion scenario, additional optimizations that convert this into a much more efficient form can be applied.

[0254] To achieve optimization, some implementations include metadata that tracks the functional dependencies between data fields in each table. In some implementations, this information is available from primary key information in the data source or from calculated field relationships. For extracted data sets, some implementations previously analyzed the table during extraction and make this metadata available to the query pipeline.

[0255] Some implementations also use containment dependencies, such as primary key / foreign key information. For tables from the same SQL connection, some implementations obtain this information from the database metadata. In other cases, the user provides this information.

[0256] Some implementations learn these metadata attributes from past queries, such as from the data visualization history log 334 .

[0257] In some implementations, data fusion uses joins instead of doing the fusion as a special case stage in the data interpreter.Data fusion is implemented as an AQL (Analytical Query Language) logic tree, which is compiled into a join tree using the query pipeline (with some appropriate optimization extensions).

[0258] In some implementations, the union tree is ultimately executed primarily in the Tableau data engine. To enable spatial analysis scenarios, some implementations also move geocoding to the Tableau data engine.

[0259] Some implementations include various performance optimizations. In some implementations, the method includes:

[0260] Partition MIN / MAX / SUM / COUNT across detail levels so that these aggregates can be requested in the first query without requiring a second pass.

[0261] If the containment dependencies are known, full outer joins that do not increase the size of the domain can be simplified or removed.

[0262] If some functional dependencies are known, this process can avoid rollups that do nothing.

[0263] Some existing optimizations can be generalized. In particular, filter pushdown can improve performance.

[0264] One assumption of fusion is that the process can create a table that maps the row-level data of a data source to the dimensions (e.g., a set of tags) in the view. This is sometimes referred to as a "join table" in some implementations of data fusion. To enable master table behavior for all data sources in data fusion, the process sends a corresponding join table to each data source that has an aggregation in progress.

[0265] Conceptually, a join table maps from the relevant columns of the data source to the contributing dimensions in the data visualization. This can be simplified by instead creating a table that maps from the relevant columns to a labeled index (e.g., a unique integer for each combination of dimension values). This avoids the need to send dimensions to the data source. Dimension values ​​can be long, leading to complex queries. And if dimension values ​​are strings (which is common), the process can run into marshaling problems when moving data between data sources. Labeled indexes avoid these problems.

[0266] Given a join table that maps from a related column to a tagged index, the procedure can join it to a remote database in a variety of ways. If the related column functionally determines the tagged index (which is the most common scenario), the procedure can convert the join into a simple case expression as long as there are not too many cases. If the related column does not functionally determine the tagged index, the procedure can convert the join table into a table literal as long as the database supports table literals (for example, SQL Server or Postgres) and there are not too many rows in the table. The procedure can create a temporary table on the remote database and join to it there. This only works if the user has permission to create temporary tables. Finally, the procedure can pull the remote data source into the Tableau Data Engine and make the join there.

[0267] Figures 18A-18C A flow chart of a process 1800 for generating (1802) a data visualization according to some implementations is provided. The method is executed (1804) at a computing device 200 having one or more processors and memory. The memory stores (1806) one or more programs configured to be executed by the one or more processors.

[0268] The process receives (1808) 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 of the plurality of visual variables 282 is associated (1810) with a corresponding one or more data fields 284, and each of these assigned data fields 284 is a dimension d or a metric m. Typically, the visual specification 104 includes (1812) 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 implementations, each visual variable 282 is (1814) one of the following: a row attribute, a column attribute, a filter attribute, a color encoding, a size encoding, a shape encoding, or a label encoding.

[0269] For each metric m of the data field, the process identifies (1816) a corresponding set of reachable dimensions R(m) 292, which consists of all dimensions d of the data field that are reachable from the corresponding metric m according to a sequence of many-to-one relationships in a predefined object model of one or more data sources. The sequence may have a length of 0, indicating a case where the dimension and metric are in the same class. In some implementations, when the dimension d and the metric m are in the same class in the predefined object model, or the metric m is an attribute of the first class C1 in the predefined object model, the dimension d is an attribute of the nth class C1 in the object model. n , and n ≥ 2, and there are zero or more intermediate classes C2,…,C in the predefined object model n-1 A sequence such that for each i = 1, 2, ..., n-1, in class C i and C i+1 When there is a many-to-one relationship, dimension d is reachable from metric m (1820).

[0270] For each distinct set of reachable dimensions R 292, the process forms (1822) a corresponding set of data fields S294 of data fields, where S consists of each dimension in R and each metric m of the data field such that R(m)=R.

[0271] For each (1824) of the data field sets S294, the process generates a corresponding data visualization. First, for each metric m in the corresponding data field set S, the process rolls up (1826) the value of the metric m to the level of detail specified by the corresponding dimension in the corresponding data field set S. In some implementations, rolling up the value of the metric m to the level of detail specified by the corresponding dimension in the corresponding data field set S includes (1828) dividing the rows of the data table containing the metric m into groups according to the corresponding dimension in the corresponding data field set S and calculating a single aggregate value for each group.

[0272] Typically, the operator that computes a single aggregate is (1830) one of the following: SUM, COUNT, MIN, MAX, or AVERAGE. In some implementations, the keywords CNT and AVG are used instead of COUNT and AVERAGE. Some implementations provide additional aggregation operators. For example, some implementations provide an ATTR() aggregation operator. For each group, the ATTR() operator determines whether all values ​​in the group are the same. If so, the ATTR() operator returns a unique value for the group; otherwise, the ATTR() operator returns "*", indicating that there are multiple values ​​for the group. In some implementations, the single aggregation operator is (1830) one of the following: SUM, COUNT, COUNTD, MIN, MAX, AVG, MEDIAN, ATTR, PERCENTILE, STDEV, STDEVP, VAR, and VARP.

[0273] For each (1824) data field set S294, the process also builds (1832) a corresponding data visualization based on the data fields in the corresponding data field set S294 and based on the corresponding visual variable 282 associated with each data field in S. In some implementations, building the corresponding data visualization includes (1834) retrieving tuples of data from one or more data sources 106 using one or more database queries generated from the visual specification 104. In some implementations, the tuples include (1836) data aggregated according to the corresponding dimensions in the data field set S294.

[0274] In some implementations, the process displays (1838) the corresponding data visualization in the graphical user interface 102 of the computing device 200. In some implementations, displaying the data visualization includes (1840) generating a plurality of visual tags, each tag corresponding to a corresponding tuple retrieved from one or more data sources. In some implementations, the graphical user interface 102 includes the data visualization area 412, and the data visualization is displayed in the data visualization area.

[0275] Some data visualization applications take the fields required for data visualization ("Viz"), which can be specified in the data visualization user interface 102, and extract a flat, denormalized table from the data source containing these elements. By flattening out the data, the data model can duplicate data from some separate tables in the underlying data source. When aggregate functions are run on this data model, the aggregation of these metrics may be affected by these copies, resulting in surprising (i.e., unexpected and / or undesirable) results.

[0276] Another problem with the flat model is that it introduces possible inefficiencies. In many cases, users can use LOD calculations to get the expected calculation results, which cover the entire flat result table for correct aggregation. Because the table is flat, filtering occurs on the entire result table even if the relevant fields are in a single small subtable.

[0277] The disclosed implementation addresses these and other issues. Objects in the object model are collections of data that can be referenced by primary keys. They may be SQL tables, spreadsheet ranges, or SQL stored procedures. Composite objects can also be created to merge object models or resolve ambiguities in data relationships. Each object can be characterized in the object model by a list of its columns: which columns constitute the primary key, and which columns serve as foreign keys. In some cases, only a subset of the fields can be used as dimensions in Viz.

[0278] The object model of a data system is a graph that relates the various objects in many-to-one relationships from foreign keys to primary keys. Edges point from the many (foreign key) side to the one (primary key) side. It is important that the object model graph is singly connected and contains no cycles so that the path through the graph is unambiguous. The overall shape of this graph is a polytree.

[0279] The object model engine creates AQL models from the object model perspective and creates abstract queries from Viz.

[0280] Queries that process Viz use perspectives in the object model. A perspective is a subtree of Viz, and it defines a set of fields (those on the subtree) that can be used as dimensions in Viz.

[0281] When the object model engine examines the fields in a Viz, it separates the fields into aggregate values ​​("measures"), and other values ​​("dimensions") that determine the level of detail in the Viz. Fig.19A and Fig.19B Two examples of supported object model graphics are provided. An "M" in a box indicates that the object corresponding to the box has one or more measures. A "D" in a box indicates that the object corresponding to the box has one or more dimensions. Looking at the supported model, objects that contain dimensions have root nodes. For example, Fig.19A The root node in is node 1902. Fig.19B , the root node is node 1912. Note that before the root node, Fig.19B There are other nodes in the , but they do not have dimensions. With this structure, all nodes with dimensions can be reached from the root node through a series of foreign key to primary key relationships (such as Fig.19Aand Fig.19B ).

[0282] Fig.19C Similar to Fig.19B , but with an additional node 1924 that is unreachable from the root node 1922. In some implementations, in the presence of a node that is not reachable from the root (e.g., node 1924), all fields at the unreachable node are considered metrics or attributes.

[0283] Fig.19D Another problematic situation is shown where there are two different paths between two nodes 1936 and 1938. Some implementations do not support this situation because it requires an arbitrary choice of a path, leading to unpredictable results.

[0284] Given a set of fields and the angles that are valid for that set, the object model query processor modifies the data query in the AQL model. The query pipeline then further processes the model and further optimizes the data query for efficiency. The object model engine follows these steps.

[0285] Step 1. Identify the dimension fields from the collection. A "select distinct" query using these dimensions defines the level of detail of the object model query results. Find the minimum subtree in the object model that contains these dimension fields and identify the root of that subtree. (If there is no subtree, this is not a valid perspective for the field.) The dimension query tree is the subtree that contains all dimensions. Next, a subquery is formed that SELECTs these dimensions through the dimension query tree. Because this uses an INNER JOIN, the domain will only be those rows that have a non-NULL value for each dimension. Some implementations use UNION to bring in rows with empty columns. Finally, apply dimension filters (if any).

[0286] Step 2. For each aggregated measure, add the measure object's primary key to the dimensions identified in step 1. Find the minimum subtree of these fields, as in step 1. For calculations involving only scalar fields, treat these fields in the same way. For calculations using only aggregated fields, treat these fields as aggregated measures (except for the final aggregation applied to the aggregated measure). Execute different subqueries for these fields and the (non-aggregated) measures themselves. This gives a result set in which each measure record is replicated / copied / included at the level of detail (LOD) of the overall query. Evaluate the calculation, treating the result as another aggregated measure for the rest of the query. Next, remove the measure object's primary key from the query and create subqueries, aggregating by the measure's aggregation function, grouping by the remaining dimensions. This gives a result set with a key consisting of the fields of the dimensions and another measure with an LOD that is explicitly aggregated to these dimensions. Evaluate the calculations using only the aggregated fields. These evaluations will result in new aggregated measure fields with respect to the rest of the query.

[0287] Step 3. JOIN the measure subqueries together on the dimensions. When the domain query includes empty facts, these JOINs can be partial OUTER JOINs to preserve the domain from step 1. Finally, apply the measure filter to the results of this query.

[0288] This approach defines a query where the result set has the following properties:

[0289] The collection of dimensions forms the multi-column key of the result set. The collection defines the domain of the result and establishes which rows will be in the result set.

[0290] Each aggregate measure is aggregated individually (accurate to the domain's level of detail). Measures moving to the left are copied to the LOD. Measures moving to the right are aggregated.

[0291] Filters and calculations become artifacts in the resulting AQL model. Typically, they will be placed in the tree along with objects at the correct level of detail that define the effect. The object model query process does not change the order in which calculations or filters are executed. This is handled by the AQL execution pipeline.

[0292] Snowflakes and trees are common examples of object models. In an object model, objects are the basic building blocks. To be an object, a subquery on a data source is as follows:

[0293] Normalized with respect to the measure in the query. Each row represents a different measure for all fields used as measures in Viz. Note that normalization of dimension fields is not mandatory.

[0294] If the object is at the root of the object model tree, no foreign key can refer to it. In these cases, the object may not need to have a primary key for the purposes of this query process.

[0295] The basic task of the author object model is to connect objects in the data source (along dimensions) into a tree (possibly among multiple trees) where the edges are many-to-one relationships between objects. The tree join graph ensures that queries will be unambiguous. The many-to-one relationships ensure that there will be no duplication of measures among the node objects of the join tree.

[0296] Snowflakes are a special case of trees where all measures are in the root object. These are particularly easy to convert in the object model because no special care needs to be taken with aggregate functions. The object model does not change the query semantics on these datasets, but it is generally more efficient.

[0297] Figures 20A-20F Provides a Snowflake example that can be used to track sales. Fig. 20A The related database tables LineItems 2002, Products 2004, Orders 2006, and Customers 2008 are shown. If a data analyst creates a Viz to calculate the total price by product category and order zip code, the process will connect the information from the LineItems 2002, Products 2004, and Orders 2006 tables. Fig. 20B As shown, the user has placed the data fields Category 2020 and Postal Code 2022 on the Rows Shelf, and SUM(Price) 2024 is specified as a Text field. This creates a Text table 2026, and uses Fig. 20C The query shown.

[0298] although Fig. 20C The query in is accurate and efficient, but applying the object model transformation is beneficial. Step 1 of the object model engine isolates the keys that will be used to index the query results, such as Fig.20D This will create a different row for each unique combination of product category and order postal code. Fig.20E As shown, step 2 of the process aggregates the measure (Price) for the key. This calculates a different value, SUM(LineItems.Price), for each different combination of category and zip code.

[0299] Finally, in step 3, the object model engine joins the two results together, as Fig.20F. This new query does not change the semantics of the query. In some cases, this new query allows for more efficient filtering and handling of "null fields appear" cases. The query pipeline can optimize this query to be similar to a class query or better, especially because it has object model information.

[0300] Figure 21A-Figure 21I Let's extend the previous example to trees that are not snowflakes. Fig.21A As shown, the schema now contains Sales Tax 2110 in the Orders table 2006. Note that this is the logical location for Sales Tax 2110 because Sales Tax is calculated once for each order.

[0301] like Fig. 21B As shown, the user has placed two measure values ​​SUM(price) 2126 and SUM(sales tax) 2128 into the measure values, and the measure value 2124 has been assigned a text code. In addition, the user has placed the data field Order 2122 on the Rows shelf, and the measure name field 2120 on the Columns shelf. Fig.21D As shown in the data visualization created using the typical query, it is useful to view the data for rows 2132 and 2134. Although the total price is correct, the sales tax amount is overstated. Fig. 21C Because it totals the price field and the sales tax field at the order level, the sales tax is repeated on each order whenever there is more than one line item.

[0302] It is useful to understand how the steps of the object model engine solve this problem. Step 1 of the object model engine uses Fig.21E The simple query shown gives a separate row for each Order.ID. Because there are two measures, there is a query in step 2. To retrieve the price information use Fig.21F In the query, the sales tax information is used Figure 21G In both cases they correctly compute the aggregate value at the appropriate LOD (i.e. by Order.ID).

[0303] Step 3 joins these three subqueries together, appropriately obtaining different levels of detail for the two measures, such as Fig.21H Notice that this uses a partial outer join to save all orders in Viz, even those that have no line items.

[0304] Figure 22A-22I It is shown to have measures and dimensions in different branches of the tree and demonstrates the need for a more sophisticated implementation provided by the object model engine. Fig.22AA small portion of the database schema for movies and actors and the relationships between them is shown (including the actor table 2206, the movie table 2204, and the appearance table 2202). Note that the relationship between actors and movies is many-to-many. In addition, a single actor can appear in multiple roles in a single movie. The appearance table 2202 is therefore used to illustrate the complex relationship between movies 2204 and actors 2206. A simple join of these tables produces the following: Fig. 22B The result set is shown. (The actual dollar amounts have been replaced by smaller integers to simplify presentation.)

[0305] From this simple database of movies and actors, one can use Fig. 22C The Viz shown asks how much box office (grossed) actors have appeared in. Here, the user has placed the data field FullName 2222 on the Rows shelf and the data field SUM(Box Office) 2224 on the Text Encoding icon. Note that there are some duplicate counts for each actor, as shown in chart 2226: Peter Sellers actually only appeared in two movies with a total box office of $2,000, while John Rhys-Davies appeared in three movies with a total box office of $3,000. These incorrect results are generated by typical queries such as Fig.22D shown.

[0306] On the other hand, with the object model, the process proceeds in the usual way. Step 1 of the object model process generates a list of different actors, such as Fig.22E In step 2, the process first determines the box office at a level of detail defined by the actor's full name and Movie.ID (a finer level of detail than Viz). Fig.22F Next, Figure 22G As shown, the intermediate table is aggregated at the LOD of Viz.

[0307] Finally, step 3 combines the two queries as expected, this time using an INNER JOIN to get only the actors who are already in the movie. The query that combines the results is in Fig.22H The resulting visualization is shown in Fig.22I As seen here, visualization 2236 has the correct data.

[0308] Calculated fields in a viz can be treated similarly to other fields, even though the source data used for the calculation is not all drawn from a single object. When calculations are based on a single object, they are collected at the appropriate level of detail, calculated at that level, and then aggregated in the usual way.

[0309] When a calculation uses fields from different objects, there are two cases:

[0310] Non-aggregate fields treated as scalars are replicated / copied / included into a single LOD. If possible, the object model engine builds LODs for all these scalars at the root of the subtree that contains them.

[0311] Aggregate functions in calculations are aggregated at the LOD of the Viz, just like aggregation functions on measures.

[0312] Consider a simple line item / order model like Fig.23 The calculation of Extension = [Price] * [Quantity] is calculated at the LOD of the Line Item table. It acts like another available field on that table.

[0313] Now, if each customer has a discount rate that can be applied to their purchases, the calculation would be: Discounted Extension = ([Price] * [Quantity]) * (1 - [Discount Rate]) It would also make sense at the LOD of the Line Item table. In general, unaggregated fields are collected to the root of the subtree that contains them for calculations. This way, even a complex calculation such as Line Item Profit = ([Price] * (1 - [Discount Rate]) - [StockingPrice]) * [Quantity] is calculated at the LOD of the Line Item, which is "correct". Note that some simple calculations that cannot be connected via subtrees in the object model do not make sense in the object model engine. For example, consider Net Sales = [Refunds.Amount] - [DiscountedExtension]. There is no LOD where this calculation is useful.

[0314] Calculations are often aggregated within Viz definitions. In these cases, the object model treats them as metrics at the LOD of their common subtree root and aggregates them according to the Viz LOD.

[0315] Aggregate calculations (all metrics in a calculation are aggregated) are also calculated at the LOD of the Viz. Consider two calculations: Extension = [Price] * [Quantity] and SumOfExtension = Sum([Extension]). The two metric granules, SUM(Extension) and AGGR (Aggregate of Extensions), give the same result in any particular Viz.

[0316] For calculations combining aggregates and scalars, the object model engine processes them in steps. First, scalars are collected to the appropriate LOD, and then aggregates are aggregated to the same LOD. Calculations can be performed at that LOD. Any other aggregation in the Viz is done at this point. For example, in the expression DiscountExtended = Sum([Price]*[Quantity])*(1-[DiscountRate]), [DiscountExtended] is calculated at the LOD of [DiscountRate], which is in the Order object.

[0317] Many calculations involve If-Then statements, such as this one involving parameters:

[0318] Sale Extension=IF([Sale Discount]>[Discount Rate])THEN

[0319] ([Price]*[Quantity])*(1-[Sale Discount])

[0320] ELSE

[0321] ([Price]*[Quantity])*(1-[Discount Rate])

[0322] END

[0323] Parameters do not belong to any object and are constant for any LOD. The calculation will have the LOD at the LineItem object. Both branches of the IF-THEN-ELSE statement must have the same LOD so that the calculation can be analyzed independently of the specific data.

[0324] Because the object model usually consists of INNER JOINs, filters can be applied at the object level (directly in a subquery on that object). This results in efficient queries with no semantic changes compared to the previous model.

[0325] Some implementations give the user a "Show All Records" option on a per-object level. This has an impact on the domain of the dimension established in the first part of the query. Initially, the dimensional query is specified as if using an INNER JOIN, which does not allow rows with missing links. One solution is to use a combination of LEFT and RIGHT JOIN. Consider Fig. 20A The mode shown.

[0326] Assume that a user requests to display all records of a customer object. Some implementations use UNION to add additional customers to the initial dimension domain query.

[0327] These implementations then RIGHT JOIN it into the order, and then RIGHT JOIN into the line item to begin creating a domain that includes all customers. In some embodiments, the query is divided into subqueries to avoid ambiguity. If the customer object has a many-to-one relationship with any other object, each part of the angle is captured in the subquery, and another subquery can be created to LEFT JOIN the customer object to these objects. Then apply the nesting of RIGHT JOIN subqueries until the LOD of the dimension domain. Each additional subtree is collected into its own subquery (RIGHT JOIN from the original spine to the new subquery). This provides a clear overall query, which promotes all dimensions from the customer object to the domain of the query. An alternative combination method uses JOIN via FULL OUTER JOIN to subqueries that have all objects displayed in each subquery.

[0328] The object model engine improves the ability to obtain correct aggregation calculations without false duplication of measure data. This is achieved by using the shape of the data (including many-to-one relationships between tables) to control replication and deduplication in a semantically correct manner. Queries created by the object model engine are broken down into component parts that first replicate the necessary data into a single table and then deduplicate it (i.e., remove false duplication) before measure aggregation.

[0329] Fig.24A 2406. These data segments are all connected by appearances 2402, which are instances of a particular actor playing a particular role in a particular movie. There are many opportunities for duplication in this data source, because actors play in many movies, and movies have many actors. In addition, the same actor can play more than one role in a single movie.

[0330] Joining these four tables together will produce a flat set of rows 2412, such as Fig. 24B As shown. Suppose a user wants to create a visualization that shows how many actors each director has worked with. Note that a single actor may have multiple roles and / or movies with each director. Looking at director Peter Jackson, we should see that he has directed four actors in four films (within this limited sample). However, Fig.24C Ten rows 2420 are shown in which Peter Jackson is paired with an actor. Fig.24D The data visualization in shows bars 2422 corresponding to 10 actors, which is exaggerated. In this visualization, there is duplication both between movies (e.g., Liv Tyler in Peter Jackson movies) and within movies (e.g., John Rhys-Davies as Gimli and Treebeard). Queries based on the flattened table count the same data multiple times.

[0331] Fig.24E 24. The use of the object model engine to compute the correct results is shown for the same data source. For the object model query, the primary key 2428 of the Actor object 2408 is added to the SELECTION of Director and Actors.FullName to create the initial Director / Actor results 2430. This is used in the deduplication step. NOTE: Most objects (all except those at the root of the object model tree) have a primary key. This is a field (or collection of fields grouped together) that is guaranteed to be unique in the object. The primary key acts as an index for each row in the object.

[0332] Next, grouping by the dimension (Director) and the primary key of the measure (Actors.ID) (2440) removes duplication to produce a deduplicated director / actor result set 2432. Each (director, actor) pair appears only once in this intermediate table 2432.

[0333] The primary key of a measure object is guaranteed to be unique, so using it in a grouping will produce correct results (e.g. even if there are multiple actors with the same name). In this example, there are no duplicate full names for any actor, but a real dataset might have duplicates.

[0334] Finally, the data is aggregated (2442) by director to obtain actor counts for each director in an aggregated result set 2434. The aggregated result set is used to build (2444) a data visualization 2436 that includes a bar 2438 for Peter Jackson with the appropriate counts.

[0335] Figure 25A-Figure 25C The use of an object model engine to generate correct level of detail (LOD) calculations is shown. Suppose the user wants to see the total "Star Power" of a movie. In this example, the "Star Power" of each actor is approximated by the box office revenue of each actor's movie. The Star Power of a movie is then the sum of the Star Powers of all the actors in the movie. This example uses Fig.24A The data source shown.

[0336] The user can calculate the star power of each actor using LOD calculation as: Star Power = {FIXED [Actor.Fullname]: Sum ([Gross])}. However, to avoid duplication, this LOD calculation should be calculated via object model semantics.

[0337] like Fig.25A As shown, the first step is to join the movie table 2406, the appearance table 2402, and the actor table 2408 to create a first result set 2502. The first result set 2502 has multiple rows 2520 for actor John Rhys-Davies (including multiple rows for the same movie). Next, the object model engine deduplicates the rows from the first result set 2502 (2510) to create a second result set 2504. In the second result set 2504, there are only three rows 2522 for actor John Rhys-Davies. In the second result set 2504, the actor is paired with the movie only once. Finally, the object model engine aggregates (2512) the data from the second result set by actor to calculate the SUM (box office) ("star appeal") for each actor in the third result set 2506. This LOD calculation effectively creates a new object with a primary key (e.g., full name) and an aggregated measure (e.g., SUM (box office)) of the target LOD.

[0338] Fig.25B The star appeal result set 2506 is shown joined (2536) with the table 2532 associating movies and actors. This creates a star appeal result set 2534 for movies, but with some duplicate rows. Fig.25C , the object model engine deduplicates (2550) the intermediate result set 2534 to form a deduplicated result set 2540. Finally, the object model engine aggregates (2552) the deduplicated result set to calculate the total star power for each movie in the final result set 2542.

[0339] Figure 26A-26D Shows how the object model engine exactly handles adding filters to Viz and using Fig.24AThe data object model shown in Figure 1. Suppose a user wants to see the total box office revenue of each actor's movies, but only those movies where the actor played a non-human role. This may cause duplication problems. For example, Fig.26A A data visualization 2604 created based on the flattened data set 2602 is shown. Using this flattened data set, some totals are exaggerated. For example, John Rhys-Davies and Liv Tyler are both in the same Lord of the Rings movie, but John Rhys-Davies has a larger total because he played multiple non-humans. This is shown by bars 2606 and 2608 in the data visualization 2604.

[0340] exist Fig.26B , the box office data 2610 for each (actor, movie) combination is joined (2620) to filter data 2612, creating a filtered but duplicated result set 2614. The object model engine builds a result set of box office data for the measure "Box Office", which includes dimensions (Actors.FullName), measures (Movies.Gross), and the primary key of the measure object (Movies.ID). The filter can be thought of as its own query. It uses the same columns that the measure query would use (i.e., the Viz dimension plus the primary key of the measure, which are Actors.FullName and Movies.ID). However, the object model engine adds the filter calculation (and all the fields that need to be calculated) instead of the measure columns. Here, the filter calculation is Species <> "Human", which returns TRUE for elves, tree people, dwarves, and gods. The appropriate fields are added to the duplicate query and filter, which can be joined row by row. The object model engine can also JOIN to Filter = TRUE to remove rows for human characters. However, note that there is still some duplication of John Rhys-Davies.

[0341] exist Fig.26B The duplication noted in the final result set 2614 in can be removed by applying GROUP BY and JOIN, as in Fig.26C As shown. Fig.26C, the JOIN+GROUP BY operation 2622 produces a final result set 2624 without duplicates. The GROUP BY has removed duplicates. All three Lord of the Rings movies are counted only once for Liv Tyler and John Rhys-Davies. (Sean Bean's appearance in The Fellowship of the Ring was removed by the filter before the GROUP BY). Aggregates are now calculated correctly.

[0342] exist Fig.26D In, from Fig.26C The final result set 2624 is aggregated (2640) to form result set 2632, which is used (2642) in data visualization 2634. Note that bars 2636 and 2638 for John Rhys-Davies and Liv Tyler are the same size, as they should be.

[0343] Figure 27A-Figure 27I Additional examples of using the object model engine to apply filters are provided. Consider a data source filter that may be on an object or field that is not in the metadata exposed by the data server. For example, suppose that actor is a "hidden" object and species <> "human" is a hidden data source filter. A client's Viz requests box office by actor name. The client is not aware of the filter for species <> "human". If no filter is applied, the results will be like Fig.27A But this obviously includes appearances by actors who appear as "humans". Note that the two objects explicitly mentioned in Viz are actors and movies. We also need to consider the object that connects them, which is appearances.

[0344] exist Fig.27B In , the object model engine performs a JOIN 2720 between the repeated non-human role table 2710 and the role filter table 2712 to form an intermediate result set 2714 that is filtered but has repeated data. Fig.27C , the same operations are performed, but JOIN and GROUP BY operations 2722 are used to form a filtered and deduplicated intermediate result set 2716.

[0345] Fig.27D The filter is shown being applied to the movie result set 2732 by JOIN 2740 the role filter table 2712 to the movie result set 2732. This produces an intermediate result set 2736 of movies that is filtered but has duplicate data. As with the actor query, duplicates can be removed by applying a JOIN with a GROUP BY 2750, such as Fig.27EThis produces an intermediate movie result set 2752 that is filtered and de-duplicated.

[0346] Fig.27F JOIN 2760 is shown applied to the actor intermediate result set 2716 and the movie intermediate result set 2752, which produces a filtered final result set 2762. However, this technique produces an extra row 2764. Separating the query for actor and movie results in the incorrect inclusion of Sean Bean's appearance in The Fellowship of the Ring. He played a non-human (Zeus), and there are non-humans in The Fellowship of the Ring, but he appeared in The Fellowship of the Ring as the human Boromir.

[0347] Figure 27G-Figure 27I An alternative process that the object model engine can use to solve this problem is shown in Figure 27G , the object model engine performs a JOIN and GROUP BY 2770 between the role filter table 2712 and the original table 2710, which has the roles played by each actor. This produces an intermediate result set 2772, which is filtered and has the roles played by each actor. Fig.27H , the object model engine performs a JOIN and GROUP BY 2780 between the role filter 2712 and the table 2732 that associates box office with movie roles. This produces an intermediate result set 2782 that is filtered and associates box office with movie roles.

[0348] Finally, in Fig.27I , the object model engine applies a JOIN 2790 between the filtered actors and the filtered movies (based on the roles played) to produce the final correct result set 2792. Note that this result set does not include Fig.27F The extraneous row 2764 seen in the table 2762 of . Here, the JOIN between the two tables includes not only the information in the cast, but also the information about their own role columns. This removes Boromir from the final result.

[0349] Mandatory filters are always applied as inner joins to Viz, while optional filters are applied when they apply to Viz (e.g., when the filter is "in" the same subtree of the object model as the fields of Viz). In some implementations, the object model engine takes the dimensions and measures of Viz, finds their least common ancestor in the object model, and finds the least common ancestor of all fields in the filter. If one of these objects is a direct ancestor of the other, the filter applies to Viz. For example, consider the filter species <> "human", which is optional. When calculating the count of actors by director, the filter on species <> "human" is applicable. On the other hand, when calculating the sum of box office by director, the filter on species <> "human" is not applicable.

[0350] When there are multiple metrics, there is the question of whether they should be coordinated. Some implementations choose a single solution, while others provide user-configurable options. For example,

[0351] The species filter will remove rows where the actor plays a human from the director / actor combination.

[0352] For director / box office combinations, should identical rows be removed?

[0353] If it is, then the box office portion of Viz will look different than it did in standalone Viz.

[0354] If not, then the box office section and the actor section will consider different movies and will likely have different domains.

[0355] These issues are Figure 28A-28C In Fig.28A , the first table 2802 has aggregated data and no filter is applied. If the filter is applied (2810), the result set 2804 has only a single row, and the data from that single row does not match the corresponding row in the first result set. Similarly, in Fig.28B , the unfiltered first result set 2812 has six rows. If the data is filtered (2820), then result set 2814 has only a single row, and the count is different from the first result set.

[0356] Fig.28C A comparison is shown of two result sets 2832 and 2834 that have filters applied differently. In the first result set 2832, the filter is applied by metric, while in the second result set 2834, the filter is applied on all fields.

[0357] The terms used in the description of the present invention are only for the purpose of describing a specific implementation, 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 include the plural forms as well, unless the context clearly indicates otherwise. It is also to be understood that the term "and / or" as used herein refers to any and all possible combinations of one or more of the associated listed items and includes these combinations. It is also to be 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 combinations thereof.

[0358] For the purpose of explanation, the foregoing description is described with reference to specific implementations. However, the above illustrative discussion is not intended to be exhaustive or to limit the invention to the precise form disclosed. In view of the above teachings, many modifications and variations are possible. The implementations are selected and described in order to best explain the principles of the invention and its practical application, so as to enable those skilled in the art to best utilize the invention and various implementations with various modifications suitable for the specific purpose contemplated.

Claims

1. A method for generating data visualizations, include: 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: A visual specification is established based on one or more data sources, a plurality of visual variables, and a plurality of data fields from the one or more data sources, wherein: Each of the visual variables is associated with a corresponding one or more data fields from the one or more data sources; and The data fields associated with the visual variables form a set of dimension data fields D and a set of metric data fields; Execute a first query on a plurality of tables of the one or more data sources to form dimension tuples, the dimension tuples comprising a unique ordered combination of data values ​​of the set of dimension data fields D, each dimension tuple in the dimension tuples forming a different data row in a data table to generate data visualization; For each metric data field in the set of metric data fields: forming a corresponding dimension set S consisting of the set of dimension data fields D and the dimensions of the primary keys of the tables in the one or more data sources containing the corresponding metric data fields; executing a corresponding query against the one or more data sources to retrieve intermediate tuples including the data fields in S and the corresponding metric data fields without aggregation; and Aggregating the intermediate tuples according to the set of dimension data fields D to calculate the aggregated value of the corresponding metric data field; For each metric data field in the set of metric data fields, extending the dimension tuple by inserting an additional column into the data table, the additional column containing an aggregated data value corresponding to the respective metric data field; and A data visualization is constructed and displayed based on the data fields in the expanded dimension tuple and based on the visual variables associated with each of the data fields.

2. The method according to claim 1, in, The visual specification also includes one or more additional visual variables that are not associated with any data fields from the one or more data sources. 3 . The method of claim 1 , further comprising displaying the data visualization in a graphical user interface of the computer.

4. The method according to claim 3, in, Displaying the data visualization includes generating a plurality of visual markers, each of the visual markers corresponding to a respective expanded tuple.

5. The method according to claim 3, in, The graphical user interface includes a data visualization area, and the method further includes displaying the data visualization in the data visualization area.

6. The method according to claim 1, in, Each of the visual variables is selected from the group consisting of: row attributes, column attributes, filter attributes, color encoding, size encoding, shape encoding, and label encoding.

7. The method according to claim 1, in, The aggregate data value is calculated using an aggregate function selected from the group consisting of: SUM, COUNT, COUNTD, MIN, MAX, AVG, MEDIAN, ATTR, PERCENTILE, STDEV, STDEVP, VAR, and VARP.

8. The method according to claim 1, in, The plurality of data fields are from a plurality of different tables in the one or more data sources.

9. The method according to claim 1, in, The one or more data sources include an object model having a plurality of objects, and the plurality of data fields belong to two or more different objects of the plurality of objects.

10. The method according to claim 9, in, The visual specification specifies a plurality of data sources, the visual specification specifies one or more data fields from each of the plurality of data sources, and the object model is an object model of the plurality of data sources.

11. A computer system for generating data visualization, include: monitor; one or more processors; and A memory storing one or more programs configured to be executed by the one or more processors, the one or more programs including instructions for performing the following operations: A visual specification is constructed based on one or more data sources, a plurality of visual variables, and a plurality of data fields from the one or more data sources, wherein: Each of the visual variables is associated with a corresponding one or more data fields from the one or more data sources; and The data fields associated with the visual variables form a set of dimension data fields D and a set of metric data fields; Execute a first query on a plurality of tables of the one or more data sources to form dimension tuples, the dimension tuples comprising a unique ordered combination of data values ​​of the set of dimension data fields D, each dimension tuple in the dimension tuples forming a different data row in a data table to generate data visualization; For each metric data field in the set of metric data fields: forming a corresponding dimension set S consisting of the set of dimension data fields D and the dimensions of the primary keys of the tables in the one or more data sources containing the corresponding metric data fields; executing a corresponding query against the one or more data sources to retrieve intermediate tuples including the data fields in S and the corresponding metric data fields without aggregation; and Aggregating the intermediate tuples according to the set of dimension data fields D to calculate the aggregated value of the corresponding metric data field; For each metric data field in the set of metric data fields, extending the dimension tuple by inserting an additional column into the data table, the additional column containing an aggregated data value corresponding to the respective metric data field; and A data visualization is constructed and displayed based on the data fields in the expanded dimension tuple and based on the visual variables associated with each of the data fields.

12. The computer system of claim 11, wherein the one or more programs include instructions for executing the method of any one of claims 2-10.

13. A 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 comprising instructions for performing the following operations: establishing a visual specification based on one or more data sources, a plurality of visual variables, and a plurality of data fields from the one or more data sources, in: Each of the visual variables is associated with a corresponding one or more data fields from the one or more data sources; and The data fields associated with the visual variables form a set of dimension data fields D and a set of metric data fields; Execute a first query on a plurality of tables of the one or more data sources to form dimension tuples, the dimension tuples comprising a unique ordered combination of data values ​​of the set of dimension data fields D, each dimension tuple in the dimension tuples forming a different data row in a data table to generate data visualization; For each metric data field in the set of metric data fields: forming a corresponding dimension set S consisting of the set of dimension data fields D and the dimensions of the primary keys of the tables in the one or more data sources containing the corresponding metric data fields; executing a corresponding query against the one or more data sources to retrieve intermediate tuples including the data fields in S and the corresponding metric data fields without aggregation; and Aggregating the intermediate tuples according to the set of dimension data fields D to calculate the aggregated value of the corresponding metric data field; For each metric data field in the set of metric data fields, extending the dimension tuple by inserting an additional column into the data table, the additional column containing an aggregated data value corresponding to the respective metric data field; as well as A data visualization is constructed and displayed based on the data fields in the expanded dimension tuple and based on the visual variables associated with each of the data fields.

14. The computer-readable storage medium according to claim 13, in, The one or more programs include instructions for executing the method of any one of claims 2-10.

Citation Information

Patent Citations

  • Navigation and visualization of relational database

    CN102004754A

  • Improved data visualization configuration system and method

    CN103562905A

  • Method and system for setting up multi-dimension data set in relation database using metadats

    CN1508728A

  • Specifying multidimensional calculations for a relational OLAP engine

    CN1726495A

  • Systems and methods for filtering data used in data visualizations that use relationships

    US9779150B1