JP2023040041A - Correlated incremental loading of multiple data sets for interactive data prep application - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide systems, methods and user interfaces to prepare and curate data for use by a data visualization application.

SOLUTION: The invention provides a user interface that includes a data flow pane and a profile pane. The data flow pane displays a flow diagram that identifies a data source. For each of multiple queries against the data source, the process issues the query against the data source asynchronously with an initial block size. Upon retrieval of the initial set of rows, the process repeats the query asynchronously with an updated block size until all of the rows have been retrieved. The process periodically determines a high water mark for rows from the data source that have been retrieved for all of the queries. When the water mark changes, the process updates the profile pane to display data value histograms for multiple data fields in the data source. Each bar in each data value histogram counts the rows below the water mark that have a single specific data value or range of data values.

SELECTED DRAWING: Figure 1

COPYRIGHT: (C)2023,JPO&INPIT

Description

The disclosed implementations relate generally to data visualization, and more specifically to systems, methods, and user interfaces for preparing and curating data for use by data visualization applications. .

Data visualization applications enable users to visually understand data sets containing distributions, trends, outliers, and other factors important to making business decisions. Some datasets are very large or complex and contain many data fields. Understand and analyze data using a variety of tools, including dashboards with multiple data visualizations. However, often the data needs to be manipulated or processed to be in a format that can be easily used in data visualization applications. Various ETL (extract/transform/load) tools may be used to build a usable data source.

There are currently two main models in the field of ETL and data preparation. Dataflow style systems focus the user on the manipulation and flow of data through the system. This makes the overall structure of the job clear and allows the user to easily control these steps. However, these systems are generally poor at presenting the actual data to the user and not really understanding what the user needs or needs to do with the data. can become difficult. These systems may be subject to an explosion of nodes. If each small operation gets its own node in the diagram, even moderately complex flows can become confusing rat's nests of nodes and edges.

Potter's Wheel-style systems, on the other hand, provide the user with a very specific spreadsheet-style interface to the actual data, allowing the user to sculpt the data through direct action. . While the user is actually creating the data flow in these systems, that flow is generally blocked, making it difficult for the user to understand and control the overall structure of the job.

For large datasets, some data preparation tools take a very long time to load data. For example, if you have multiple queries running synchronously, the user has to wait for all the data to load. Some systems try to reduce the impression of slowness by executing queries and loading data asynchronously. However, asynchronous loading prevents the user from manipulating the data, and the interface may display inconsistent data because the interface displays the data separately for each separate asynchronous query.

The disclosed implementation addresses the problems of existing data preparation tools in several ways. By running multiple asynchronous queries, data load time is reduced and data from multiple queries is coordinated so the user interface always displays consistent data. In addition, the user can immediately manipulate the data and make necessary changes while the data is loading. Changes are applied to data already displayed, and when new data from a query arrives, the same changes are applied to new rows of data.

According to some implementations, a computer system for preparing data for subsequent analysis has one or more processors and memory. The memory stores one or more programs configured for execution by one or more processors. One or more programs contain executable instructions. The system displays a user interface that includes a dataflow pane, a profile pane, and a data pane. The Data Flow pane displays a node/link flow diagram that identifies data sources. For each of multiple queries against the data source, the system asynchronously issues the query against the data source using an initial block size that specifies the number of rows. Once the initial set of rows from the data source that satisfy each query is retrieved, the system asynchronously repeats the query with updated block sizes until all rows satisfying the query are retrieved. The system stores the retrieved rows satisfying each query in a local cache. Periodically (e.g., based on a timer or triggered by receipt of query results from one of the queries), the system retrieves and stores data source rows in the local cache for all queries. determine a unique identifier that identifies the This unique identifier is sometimes referred to as a high water mark. When the unique identifier changes, the system updates the profile pane to display data value histograms for multiple data fields within the data source. Each bar in each data value histogram indicates the number of rows in the data source that are (i) designated by a unique identifier and (ii) have a single specific data value or range of data values for the respective data field. . In this way, the system provides a consistent view of the data in the profile pane while multiple independent queries are executed asynchronously.

In some implementations, each iteration of each query against the data source specifies a block size that is larger than the previous block size of the respective query. In some implementations, each iteration of each query against the data source specifies a block size that is twice the previous block size of the respective query.

In some implementations, the periodic determination of the unique identifier is throttled so that it occurs no more than once per second.

In some implementations, if the unique identifier changes, the system updates the row of data from the data source displayed in the data pane according to the unique identifier.

In some cases, the first node of the flow diagram is selected first and the histogram of data values displayed in the profile pane corresponds to the calculated data set of the first node. In some cases, the user selects a second node of the flow diagram during execution of the asynchronous query. In response to user selection, the system updates the profile pane to display new data value histograms for multiple data fields from the second node's result set. Each bar in each data value histogram indicates the number of rows in the result set that have a single specific data value or range of data values for the respective data field.

In some implementations, the unique identifier is the primary key value of a primary key field in the data source, and a row in the data source has a unique identifier if the key value corresponding to the row is less than the primary key value. Specified by In some implementations, the unique identifier is a high-level row number, and a row in the data source is specified by a unique identifier if the row number corresponding to that row is less than or equal to the high-level row number. be done. In some implementations, the sort order for each of the queries is the same.

In some cases, a user changes the data displayed in the profile pane while executing one or more asynchronous queries. In response to user input, the system translates the user input into operations to be applied to rows retrieved from the data source and saves the definition of the operations. Updating the profile pane includes applying the defined operation to the rows retrieved by the query if the unique identifier has changed.

The user can make various changes to the data in the profile pane. In some cases, the user input is the selection of a single bar in the data value histogram corresponding to the first data value bin of the first data field, thereby changing the data displayed in the profile pane to the Filter to rows in the data source whose data value corresponds to the first data value bin. The saved operation applies the data displayed in the profile pane to a filter that filters to rows in the data source where the data value of the first field corresponds to the first data value bin.

In some cases, user input removes the data value histogram corresponding to the first data field from the profile pane. Updating the profile pane when the unique identifier has changed includes omitting the first data field from the data pane.

In some cases, user input adds a computed column to the profile pane with a corresponding data value histogram computed as a function of one or more other columns retrieved by the query. Updating the profile pane when the unique identifier changes includes updating the data value histogram of the computed column according to the additional rows retrieved from the function and data source.

In some cases, user input renames the first data column in the profile pane to a new name. Updating the profile pane if the unique identifier has changed involves retaining the new name of the first data column.

In some cases, user input converts the data type of the first data column in the profile pane to a new data type according to a conversion function. Updating the profile pane when the unique identifier changes includes applying a transform function to the first data column of the additional rows retrieved from the data source.

In some cases, user input removes the histogram bar corresponding to the bin of the first data column in the profile pane. Updating the profile pane if the unique identifier has changed includes deleting the retrieved additional row if the row has a data value in the first data column that matches the bin. In some implementations, each bin corresponds to an individual data value or a continuous range of data values.

According to some implementations, the process refactors the flow diagram. The process is executed in a computer system 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. The process includes displaying a user interface including multiple panes including a dataflow pane and a palette pane. The Data Flow pane contains a flow diagram with multiple existing nodes, each of which specifies a respective operation for retrieving data from a respective data source or a respective operation for transforming data. or specify each operation to create each output dataset. The palette pane also contains multiple flow element templates. The process receives a first user input to select an existing node from a flow diagram or select a flow element template from a palette pane; and in response to the first user input, (i) Displaying a movable icon representing a new node for placement, the new node specifying a data flow operation corresponding to the selected existing node or the selected flow element template in the flow diagram. , displaying, and (ii) displaying one or more drop targets in the flow diagram according to dependencies between data flow operations of the new node and operations of the plurality of existing nodes. The process further includes receiving a second user input to place the movable icon on the first of the drop targets and stopping detecting the second user input. In response to stopping detecting the second user input, the process inserts a new node into the flow diagram of the first drop target. The new node executes the specified dataflow operation.

According to some implementations, each of the existing nodes has a respective intermediate data set computed according to the respective operation specified, and inserting a new node into the flow diagram at the first drop target may be , including computing intermediate datasets for new nodes according to the specified dataflow operations.

According to some implementations, the new node is placed in the flow diagram after the first existing node with the first intermediate data set, and the calculation of the intermediate data set of the new node causes the data flow operation to be performed in the first including applying to intermediate datasets of

According to some implementations, the new node has no predecessor node in the flow diagram, and the computation of the intermediate dataset of the new node may take data from the data source to form the intermediate dataset. included.

According to some implementations, the process further includes displaying a sampling of data from the intermediate data set in a data pane of the user interface in response to stopping detecting the second user input. A data pane is one of multiple panes.

According to some implementations, the dataflow operation filters the rows of data based on the value of the first data field and displaying one or more drop targets is such that the intermediate data set is the first data field. , including displaying one or more drop targets immediately after an existing node that contains a data field of

According to some implementations, a first user input selects an existing node from the flow diagram, and inserting a new node into the flow diagram at the first drop target creates a copy of the existing node.

According to some implementations, inserting a new node into the flow diagram at the first drop target further includes removing an existing node from the flow diagram.

According to some implementations, a dataflow operation includes multiple operations that are performed in a specified order.

In 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, memory, and displays. The one or more programs contain instructions for implementing a system for refactoring flow diagrams as described herein.

According to some implementations, a computer system prepares data for analysis. A computer system includes one or more processors, memory, and one or more programs stored in the memory. The program is configured to be executed by one or more processors. The program displays the user interface of the data preparation application. The user interface includes dataflow panes, tool panes, profile panes, and data panes. The Dataflow pane displays a node/link flow diagram that identifies data sources, operations, and output datasets. The Tools pane contains a Data Source Selector that allows the user to add data sources to the flow diagram, an Operations Palette that allows the user to insert nodes into the flow diagram to perform specific transformation operations, and It contains a palette of other flow diagrams that the user can incorporate into the flow diagram. The Profile pane displays the schema corresponding to the selected node in the flow diagram, including information about the data fields and statistics about the data values in the data fields, and allows the user to manipulate individual data elements to modify the flow diagram. . The data pane displays rows of data corresponding to selected nodes in the flow diagram and allows the user to manipulate individual data values to modify the flow diagram.

In some implementations, the information about the data fields displayed in the profile pane includes the data range of the first data field.

In some implementations, in response to the first user action on the first data range of the first data field in the profile pane, a new node is added to the flow diagram that filters the data to the first data range.

In some implementations, the profile pane allows the user to map the data range of the first data field to specified values. This adds a new node to the flow diagram that performs the user-specified mapping.

In some implementations, a node is added to the flow diagram that filters the data to the first data value in response to the first user interaction with the first data value in the data pane.

In some implementations, in response to the user changing the first data value of the first data field in the data pane, perform a change to each row of data where the data value of the first data field is equal to the first data value. A new node is added to the flow diagram.

In some implementations, in response to a first user action on the first data field of the data pane, a node is added to the flow diagram that splits the first data field into two or more separate data fields.

In some implementations, a new operation is added to the operations palette in response to the first user action of dragging the first node in the dataflow pane to the tool pane. The new operation corresponds to the first node.

In some implementations, the profile pane and data pane are configured to update asynchronously when selections are made in the dataflow pane.

In some implementations, the information about data fields displayed in the profile pane includes one or more histograms that display the distribution of data values for the data fields.

According to some implementations, the method is performed on an electronic device with a display. For example, the electronic device can be a smart phone, tablet, notebook computer, or desktop computer. The method implements any of the computer systems described herein.

In 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, memory, and displays. The one or more programs contain instructions for implementing a system that prepares data for analysis as described herein.

Thus, methods, systems, and graphical user interfaces are disclosed that allow users to analyze, prepare, and curate data, as well as refactor existing data flows.

To better understand the aforementioned systems, methods and graphical user interfaces, as well as additional systems, methods and graphical user interfaces that provide data visualization analysis and data preparation, the following, in conjunction with the following figures: It is necessary to refer to the implementation description. In these figures, like reference numerals refer to corresponding parts throughout the figures.

4 illustrates a graphical user interface used in some implementations; 1 is a block diagram of a computing device, according to some implementations; FIG. 4 illustrates a user interface of a data preparation application, according to some implementations. Some features of the user interface shown in FIGS. 3A and 3B are described. 4 illustrates a sample flow diagram, according to some implementations. 2 illustrates a pair of flows that work together but run at different frequencies, according to some implementations. Describe building joins using a data preparation application, according to some implementations. 4 shows a portion of a log file, according to some implementations. 4 illustrates a portion of a lookup table, according to some implementations. Shows some operations, inputs, and outputs of a flow according to some implementations. 1 illustrates some components of a data preparation system, according to some implementations. Indicates evaluating a flow for either analysis or execution, according to some implementations. 1 schematically represents an asynchronous subsystem used in some data preparation implementations; 4 illustrates a sequence of flow operations, according to some implementations; 3 illustrates three aspects of a type system, according to some implementations. Shows the properties of the type environment, according to some implementations. Shows simple type checking based on all known data type flows, according to some implementations. Shows a simple type failure with a completely known type, according to some implementations. 6 illustrates a simple type environment computation for partial flows, according to some implementations. 4 shows packaged container node types according to some implementations. Shows a more complex type environment scenario according to some implementations. Shows how some implementations reuse more complex type environment scenarios. Shows properties of many of the most commonly used operators, according to some implementations. 4 illustrates a flow and corresponding execution process according to some implementations; We show that, according to some implementations, execution of the entire flow starts with an implicit physical model at the input and output nodes. FIG. 10 shows that, according to some implementations, executing a partial flow instantiates a physical model along with the result. FIG. 10 illustrates executing parts of a flow based on past results, according to some implementations; FIG. FIG. 10 illustrates evaluating flows with fixed nodes 860, according to some implementations. FIG. 4 shows a portion of a flow diagram, according to some implementations. 4 illustrates the process of establishing a high water mark for result sets obtained from multiple asynchronous queries, according to some implementations. Figure 6 shows how the data preparation user interface is updated while data is being read from the data source, according to some implementations. 4 illustrates user interaction with partially loaded data in a data preparation user interface and subsequent updates to the user interface when additional data arrives asynchronously, according to some implementations. 4 is an example profile pane of a data preparation user interface, according to some implementations.

Reference will now be made to implementations, examples of which are illustrated in the accompanying drawings. In the following description, numerous specific details are set forth in order to provide a thorough understanding of the invention. However, it will be apparent to those skilled in the art that the present invention may be practiced without these specific details.

FIG. 1 shows a graphical user interface 100 for interactive data analysis. User interface 100 includes a data tab 114 and an analysis tab 116, according to some implementations. When data tab 114 is selected, user interface 100 displays schema information area 110, also referred to as data pane. Schema information area 110 provides data fields that can be selected and used to build data visualizations. In some implementations, the list of field names is divided into groups of dimensions (such as categorical data) and groups of measures (such as numeric). Some implementations also include a list of parameters. When the analysis tab 116 is selected, the user interface displays a list of analysis functions instead of data elements (not shown).

Graphical user interface 100 also includes data visualization area 112 . Data visualization area 112 includes multiple shelf areas, such as column shelf area 120 and row shelf area 122 . These are also referred to as column shelves 120 and row shelves 122 . As shown here, the data visualization area 112 also has a large space for displaying visual graphics. Initially, the space has no visual graphics because no data element has been selected yet. In some implementations, the data visualization area 112 has multiple layers referred to as sheets.

FIG. 2 is a block diagram illustrating a computing device 200 that may display graphical user interface 100 according to some implementations. The computing device may also be used by a data preparation (“data prep”) application 250 . Various examples of computing device 200 include desktop computers, laptop computers, tablet computers, and other computing devices having displays and processors capable of executing data visualization application 222 . Computing device 200 typically includes one or more processing units/cores (CPUs) 202 for executing modules, programs, and/or instructions stored in memory 214, 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. Communication bus 212 may include circuitry that interconnects and controls communications between system components.

Computing device 200 includes a user interface 206 with a display device 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 optionally displayed on the display device 208 to allow the user to “press keys” displayed on the display 208 . In some implementations, display 208 and input device/mechanism 210 include a touch screen display (also referred to as a touch sensitive display).

In some implementations, memory 214 includes high speed random access memory such as DRAM, SRAM, DDR RAM, or other random access solid state memory devices. In some implementations, 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, memory 214 includes one or more storage devices remotely located from CPU(s) 202 . Memory 214, or alternatively, non-volatile memory device(s) within memory 214, includes non-transitory computer-readable storage media. In some implementations, memory 214, or computer-readable storage media in memory 214, stores the following programs, modules, and data structures, or a subset thereof.
• An operating system 216 that handles various basic system services and contains procedures for performing hardware-dependent tasks.
- One or more communication network interfaces 204 (wired or wireless) and one or more communication networks, such as the Internet, other wide area networks, local area networks, metropolitan area networks, etc., to connect the computing device 200 to other computers and devices. Communication module 218 used to connect to.
- A browser (or other application capable of displaying web pages) 220 that allows users to communicate with remote computers or devices over a network.
• A data visualization application 222 that provides a graphical user interface 100 for users to construct visual graphics. For example, the user selects one or more data sources 240 (which may be stored on computing device 200 or may be stored remotely), selects data fields from the data source(s), selects Use fields to define visual graphics. In some implementations, the information provided by the user is stored as visual specification 228 . The data visualization application 222 includes a data visualization generation module 226, which receives user input (eg, visual specifications 228) and generates corresponding visual graphics (also “data visualization” or “datavis”). ). Data visualization application 222 then displays the generated visual graphics on user interface 100 . In some implementations, the data visualization application 222 runs as a standalone application (eg, desktop application). In some implementations, the data visualization application 222 performs the following within another application using web pages served by the web browser 220 or web server.
- Zero or more databases or data sources 240 (eg, a first data source 240-1 and a second data source 240-2) used by the data visualization application 222; In some implementations, data sources are stored as spreadsheet files, CSV files, XML files, flat files, or stored in relational databases.

In some cases, computing device 200 stores data prep application 250, which can be used to analyze data and process it for subsequent analysis (eg, by data visualization application 222). . FIG. 3B shows an example user interface 251 used by data prep application 250 . As described in more detail below, data prep application 250 allows users to build flows 323 .

Each of the above-identified sets of executable modules, applications, or procedures may be stored in one or more memory devices and correspond to sets of instructions for performing the functions described above. The modules or programs (i.e., sets of instructions) identified above need not be implemented as separate software programs, procedures, or modules, and thus various subsets of these modules may be used in various implementations. may be combined or otherwise rearranged. In some implementations, memory 214 stores a subset of the modules and data structures identified above. Additionally, memory 214 may store additional modules or data structures not described above.

Although FIG. 2 illustrates computing device 200, FIG. 2 is intended more as a functional illustration of the various features that may be present, rather than as a structural overview of the implementations described herein. ing. In practice, items shown separately could be combined and some items could be separated, as recognized by those skilled in the art.

3A and 3B illustrate user interfaces for preparing data according to some implementations. These implementations have at least five regions with different functions. FIG. 3A shows this conceptually as menu bar area 301 , left pane 302 , flow pane 303 , profile pane 304 and data pane 305 . In some implementations, profile pane 304 is also referred to as a schema pane. In some implementations, the functions of the “left pane” 302 are located in alternate locations, such as below the menu pane 301 or below the data pane 305 .

This interface provides the user with multiple streamlined and coordinated views so that the user can see and understand what needs to be done. This novel user interface provides users with multiple views of flows and data, helping users not only perform actions, but also discover actions that need to be performed. The flow diagram in flow pane 303 combines and summarizes the actions to make the flow more readable, and is coordinated with the view of the actual data in profile pane 304 and data pane 305 . A data pane 305 provides a representative sample of the data at every point in the logic flow and a profile pane provides a histogram of the domain of the data.

In some implementations, menu bar 301 has a file menu with options for creating new dataflow specifications, saving dataflow specifications, and loading previously created dataflow specifications. Prepare. A flow specification is sometimes referred to as a flow. A flow specification describes how input data from one or more data sources is manipulated to create a target data set. Target datasets are typically used in subsequent data analysis using data visualization applications.

In some implementations, the left pane 302 includes a list of recent data source connections and a button to connect to new data sources.

In some implementations, the flow pane 303 contains a visual representation of the flow specification (flow diagram or flow). In some implementations, a flow is a node/link diagram that shows the data sources, operations performed, and target outputs of the flow.

Some implementations allow flexible execution of the flow by treating parts of the flow as declarative queries. That is, the user specifies the purpose (inputs and outputs, etc.) rather than having the user specify all the computational details. The process that runs the flow chooses an execution strategy that optimizes the plan and improves performance. Depending on the implementation, the user may also selectively prohibit this action to control its execution.

In some implementations, profile pane 304 displays the schema and associated statistics and/or visualizations of the node selected in flow pane 303 . Some implementations support selection of multiple nodes at the same time, while other implementations support selection of only one node at a time.

In some implementations, data pane 305 displays row-level data for the node selected in flow pane 303 .

In some implementations, the user creates a new flow using the File->New Flow option in the menu bar. Users can also add data sources to flows. In some cases, the data source is a relational database. In some cases, one or more of the data sources are file-based, such as CSV files or spreadsheet files. In some implementations, users add file-based sources to the flow using the file connection affordances in the left pane 302 . This opens a file dialog asking the user to select a file. In some implementations, the left pane 302 also includes database connection affordances, which allow the user to connect to databases (eg, SQL databases).

When a user selects a node (eg, table) in flow pane 303 , the node's schema is displayed in profile pane 304 . In some implementations, profile pane 304 includes statistics or visualizations such as the distribution of data values for a field (eg, as a histogram or pie chart). In implementations that allow selection of multiple nodes in flow pane 303 , the schema of each of the selected nodes is displayed in profile pane 304 .

Also, when a node is selected in the flow pane 303 , the data of that node is displayed in the data pane 305 . Data pane 305 typically displays data as rows and columns.

Depending on the implementation, a flow can be easily edited using the flow pane 303, profile pane 304, or data pane 305. For example, some implementations enable node/table right-click operations in any of these three panes to add new columns based on scalar calculations on existing columns in that table. For example, a scalar operation can be a mathematical operation that computes the sum of three numeric columns, a string operation that concatenates string data from two columns that are strings, or (a date is encoded as a string at the data source). can be a conversion operation that converts a string column to a date string. In some implementations, the right-click menu (accessed from a table/node in flow pane 303, profile pane 304, or data pane 305) provides an option "Create Calculated Field...". Selecting this option brings up a dialog for creating a calculation. In some implementations, calculations are restricted to scalar calculations (with the exception of, for example, aggregations, custom level of detail calculations, and table calculations). When a new column is created, the user interface adds a computed node to the flow pane 303, connects the new node to its predecessor node, and selects this new node. In some implementations, scroll boxes are added to the flow pane 303 as the number of nodes in the flow diagram grows. In some implementations, flow diagram nodes can be grouped and labeled. It is displayed hierarchically (eg, showing high-level flow first, then drilling down to show details of the selected node).

A user can also delete a column by manipulating flow pane 303, profile pane 304, or data pane 305 (eg, by right-clicking on the column and selecting the Delete Column option). Deleting a column adds a node to the flow pane 303, connects the new node appropriately, and selects the new node.

In flow pane 303, the user can select a node and select "output as" to create a new output dataset. In some implementations this is done with a right click. This brings up a file dialog that allows the user to select a target filename and directory (or database and table name). Doing this adds a new node to the flow pane 303 but does not actually create the target dataset. In some implementations, the target dataset includes the first file containing the data (Tableau Data Extract or TDE) and a corresponding index or pointer entry pointing to the data file (Tableau Data Source). There are two components including Source) or TDS).

The actual output data file is created when the flow runs. In some implementations, the user selects “File->Run Flow” from menu bar 301 to run the flow. Note that one flow can generate multiple output data files. In some implementations, flow diagrams provide visual feedback at runtime.

In some implementations, the menu bar 301 includes a "Save" or "Save As" option in the "File" menu, which allows the user to save the flow. In some implementations, flows are saved as ".loom" files. This file contains everything needed to recreate the flow on load. Once a flow is saved, it can be reloaded later using the Load menu option in the File menu. This will bring up a file picker dialog allowing the user to load past flows.

FIG. 3B shows a user interface for data preparation, showing user interface elements for each of the panes. Menu bar 311 contains one or more menus, such as a file menu and an edit menu. Although an edit menu is available, further changes to the flow are performed by having interaction with the flow pane 313, profile pane 314, or data pane 315. FIG.

In some implementations, the left pane 312 includes a data source palette/selector, which includes affordances for discovering and connecting to data. A set of connectors includes an extract-only connector containing a cube. Implementations can publish custom SQL expressions to any data source that supports it.

The left pane 312 also contains an operations palette that displays operations that can be placed in a flow. This includes arbitrary joins (joins of any type and joins with various predicates), unions, pivots, column renaming and restrictions, projection of scalar calculations, filters, aggregations, data type conversions, data parsing, Includes coalescing, merging, splitting, aggregation, value replacement, and sampling. In some implementations, set creation (e.g., dividing data values of a data field into sets), binning (e.g., grouping numeric data values of a data field into sets of ranges), and table calculations (e.g. , which calculates the data value of each row (e.g. percentage of the total) that depends not only on the row's data value, but also on other data values in the table.

Left pane 312 also contains a palette of other flows that may be incorporated in whole or in part into the current flow. This allows users to reuse flow components to create new flows. For example, if a part of a flow is created that uses a combination of 10 steps to scrub for a particular type of input, you can save that 10-step flow part and use it in the same flow or in a completely different Can be reused in flows.

Flow pane 313 displays a visual representation (eg, node/link flow diagram) 323 of the current flow. A flow pane 313 provides an overview of the flow to help document the process. Many existing products have very complicated flows that make it hard to understand. The disclosed implementation facilitates comprehension by coalescing nodes, keeping the overall flow simpler and more concise. As noted above, implementations typically add scroll boxes as the number of nodes increases. The need for scrollbars is reduced by coalescing multiple related nodes into supernodes, also called container nodes. This allows the user to see the overall flow more conceptually and drill down into details only when necessary. In some implementations, when a "supernode" is expanded, the flow pane 313 shows only the nodes within the supernode, and the flow pane 313 has a heading that indicates which part of the flow Identified as being displayed. Implementations typically enable multiple levels of hierarchy. Complex flows can include multiple levels of node nesting.

As noted above, profile pane 314 contains schema information about the data for the single node (or nodes) currently selected in flow pane 313 . As shown here, the schema information provides statistical information about the data, such as a histogram 324 of the data distribution for each of the fields. A user can interact directly with the profile pane to modify flow 323 (eg, by selecting a data field to filter rows of data based on the value of that data field). Profile pane 314 also provides the user with relevant data about the currently selected single node (or nodes) and visualizations that guide the user's work. For example, histogram 324 shows the distribution of domains in each column. In some implementations, brushing is used to indicate how these domains have interactions with each other.

The example here shows how the process differs from typical implementations by allowing the user to directly manipulate the data in the flow. Consider two alternative ways to filter specific rows of data. In this case, the user wants to exclude the state of California from consideration. Using common tools, the user selects the 'filter' node, places the filter at a specific location in the flow, then brings up a dialog box to allow calculations such as 'state_name<>'CA'' Enter an expression. In the implementations disclosed herein, a user can view a profile pane 314 (e.g., showing field value "CA" and the number of rows with that field value) and a data pane 315 (e.g., having "CA" as the value of state_name). You can see the data values in the individual rows). In some implementations, the user can right-click CA in the list of state names in profile pane 314 (or data pane 315) and select Exclude from the dropdown. The user interacts with the data itself rather than the flow elements interacting with the data. Implementations provide similar functionality for calculations, joins, unions, aggregations, etc. Another advantage of this approach is the immediate results. If "CA" is filtered out, the filter is applied immediately. If the operation takes a long time to complete, the operation is performed asynchronously and the user can continue working while the job is running in the background.

Data pane 315 displays rows of data corresponding to the node(s) selected in flow pane 313 . Each column 315 corresponds to one of the data fields. A user can directly interact with the data in the data pane to change the flow 323 in the flow pane 313 . The user can also manipulate the data pane directly to change individual field values. In some implementations, when the user makes a change to one field value, the user interface applies the same change to all other values in the same column, and this value (or pattern) is Matches just the value. For example, if a user changes "WA" to "Washington" for one field value in the State data column, in some implementations all other "WA" values in the same column will be changed to "Washington". Updated. In some implementations, the column is further updated to replace state abbreviations in the column with full state names (eg, replace "OR" with "Oregon"). In some implementations, the user is prompted for confirmation before applying global changes to the entire column. In some implementations, a change to one value in one column can be applied (automatically or pseudo-automatically) to other columns as well. For example, a data source may include both state of residence and state of billing. Then the state format change can be applied to both.

The sampling of data in data pane 315 is selected to provide valuable information to the user. For example, in some implementations, rows are selected that display the full range of values for the data field (including outliers). As another example, if the user selects a node with more than one data table, in some implementations rows are selected that support joining the two tables. The rows displayed in data pane 315 are selected to display both matching and non-matching rows between the two tables. This helps determine which fields to use for the join and/or which type of join to use (eg, inner join, left outer join, right outer join, or full outer join).

FIG. 3C shows some of the features displayed in the user interface and what the features display. As shown in FIG. 3B above, flow diagram 323 is always displayed in flow pane 313 . Profile pane 314 and data pane 315 are also always shown, but the content of these panes changes based on the node(s) selected in flow pane 313 . In some cases, when a node is selected in flow pane 313, one or more node-specific panes are displayed (not shown in Figures 3A or 3B). When displayed, node-specific panes are added to other panes. In some implementations, node-specific panes are displayed as floating popups that can be moved. In some implementations, node-specific panes are displayed at fixed locations within the user interface. As noted above, left pane 312 includes a data source palette/chooser for selecting or opening a data source, as well as an operations palette for selecting operations that may be applied to flow diagram 323 . Some implementations also include an “Other Flows Palette” that allows users to import all or part of another flow into the current flow 323 .

Different nodes in the flow diagram 323 perform different tasks and therefore the internal information of the nodes is different. Additionally, in some implementations, different information is displayed depending on whether a node is selected. For example, unselected nodes contain a brief description or label, while selected nodes display more detailed information. In some implementations, the status of the operation is also displayed. For example, some implementations display nodes in flow diagram 323 differently depending on whether an operation on the node has been performed. Additionally, in some implementations within the operations palette, operations are displayed differently depending on whether they are available on the currently selected node.

Flow diagram 323 provides an easy, visual way to understand how data is being processed, and organizes the process in a logical way for the user. Although the user can edit the flow diagram 323 directly in the flow pane 313, changes to operations are typically made in a more rapid manner and the data or schema in the profile pane 314 or data pane 315 are Directly manipulated (for example, right-clicking a data field's statistics in the profile pane to add or remove columns from the flow).

Instead of displaying a node for each minor operation, the user can group operations into a few more important nodes. For example, a join followed by deletion of two columns can be implemented in one node instead of three separate nodes.

Within flow pane 313, the user can perform various tasks, including:
● Change node selection. This determines the data displayed in the rest of the user interface.
●Pin flow operation. This allows the user to specify that part of the flow should be executed first and cannot be reordered.
● Split and combine operations. The user can easily reorganize the operations to match the ongoing logical model. For example, a user may want to create one node called "Hospital Code Normalization". This node contains many operations and special cases. A user can first create an individual operation and then coalesce the nodes representing the individual operations with the supernode "Hospital Code Normalization". Conversely, after creating a node that contains many individual operations, the user may choose to split one or more operations (e.g., to create a more commonly reusable node). can.

Profile pane 314 provides a quick way for the user to understand if the results of the conversion are as expected. Outliers and erroneous values are typically visually "popped out" based on comparisons with both other values within a node, or based on comparisons of values of other nodes. The profile pane helps users identify data problems, whether the problem is caused by incorrect conversions or dirty data. The profile pane not only helps users find bad data, but also allows them to directly interact and fix any problems found. In some implementations, profile pane 314 is updated asynchronously. When a node is selected in the flow pane, the user interface initiates entry of partial values (such as data value distribution histograms) that improve over time. In some implementations, the profile pane includes an indicator to alert the user when completed. For very large datasets, some implementations build profiles based on sample data only.

Within profile pane 314, the user can perform various tasks, including:
● Investigate data coverage and correlations. A user can use the profile pane 314 to focus on specific data or column relationships using direct navigation.
● Filter data or data ranges. A user can add filtering operations to flow 323 through direct interaction. This creates a new node in the flow pane 313 .
● Data conversion. A user can interact directly with the profile pane 314 to map values from one range to another. This creates a new node in the flow pane 313 .

Data pane 315 provides a way for the user to view and modify the rows resulting from the flow. Typically, the data pane selects a sampling of rows corresponding to the selected node (eg, samples of 10, 50, or 100 rows instead of 1 million rows). In some implementations, rows are sampled to display various functions. In some implementations, rows are statistically sampled, such as every n rows.

Data pane 315 is typically where the user cleans up data (eg, if the source data is not clean). Like the profile pane, the data pane updates asynchronously. When a node is first selected, the rows of data pane 315 begin to appear, and the sampling improves over time. For most datasets there is only a subset of the data that can be used here (unless the dataset is small).

Within data pane 315, the user can perform various tasks, including:
● Sorting for navigation. Users can sort the data in the data pane based on columns, but it doesn't affect the flow. Its purpose is to help navigate the data in the data pane.
● Filtering for navigation. The user can filter the data in the view, but no filters are added to the flow.
● Add filters to the flow. Users can also create filters to apply to flows. For example, a user can select an individual data value for a particular data field and perform an action that filters the data according to that value (eg, exclude that value or include only that value). In this case, user interaction creates a new node in data flow 323 . Some implementations allow the user to select multiple data values in a column and create a filter based on the set of selected values (e.g. exclude a set or restrict to only that set). do).
● Change row data. The user can change the row directly. For example, change the data value of a particular field in a particular row from 3 to 4.
● Mapping one value to another. A user can change the data value in a particular column and have the change propagated to change all rows with that value in the particular column. For example, replace "NY" with "NY" throughout the state column.
● Column splitting. For example, if the user sees the date formatted as "November 14, 2015", the user can split this field into three separate fields for day, month, and year.
● Column merging. A user can merge two or more columns to create a single combined column.

Node-specific panes display information specific to the selected node in the flow. Since node-specific panes are often not needed, the user interface typically does not specify an area with the user interface dedicated to this purpose. Instead, node-specific panes are displayed as needed, typically using pop-ups that float to other areas of the user interface. For example, some implementations use node-specific panes to provide specific user An interface is provided.

The Data Source Palette/Chooser allows the user to bring in data from various data sources. In some implementations, the data source palette/chooser is in the left pane 312 . A user can use the data source palette/chooser to perform various tasks such as:
● Establishing a data source connection. This allows users to pull data from data sources such as SQL databases, data files such as CSV or spreadsheets, non-relational databases, web services, and other data sources.
● Setting connection properties. A user can specify credentials and other properties required to connect to a data source. For some data sources, properties include selection of specific data (eg, a specific table in a database or a specific sheet from a workbook file).

In many cases, as described above, users invoke operations on nodes in the flow based on user interaction with profile pane 314 and data pane 315 . In addition, left pane 312 provides an operations palette that allows the user to invoke specific operations. For example, in some implementations, the Actions Palette includes a "Call Python Script" option. Additionally, as users create nodes for reuse, they can be saved as available operations in the operations palette. The Operations Palette provides a list of known operations (including user-defined operations), allowing users to incorporate operations into flows using user interface gestures (such as drag and drop).

Other flow palettes/choosers are provided in some implementations. This allows users to easily reuse flows that they have created or flows that have been created by others. The Other Flows palette provides a list of other flows that the user can initiate or incorporate. Some implementations support selection of parts of other flows as well as selection of entire flows. Users can incorporate other flows using user interface gestures such as drag and drop.

Inside the node, the exact operation being performed on the node is specified. There is enough information for the user to "refactor" the flow or understand it in more detail. The user can see exactly what is inside the node (eg, the operation being performed) and can move the operation from one node to another.

Some implementations include a project model that allows users to group multiple flows into one "project" or "workbook." For complex flows, the user can divide the entire flow into more understandable components.

In some implementations, operational status is displayed in left pane 312 . Since many operations run asynchronously in the background, the operation status area shows the user the ongoing operation and progress (1% complete, 50% complete, 100% complete, etc.). Operation status indicates operations that are running in the background and allows the user to cancel operations, update data, and run partial results to completion. .

A flow, such as flow 323 in FIG. 3B, represents a pipeline of rows passing from an original data source, through transformations, to a target data set. For example, FIG. 3D shows a simple example of flow 338 . This flow is based on traffic accidents involving vehicles. The relevant data is stored in the accident and vehicle tables of the SQL database. In this flow, a first node 340 reads data from the incident table and a second node 344 reads data from the vehicle table. In this example, the incident table is normalized (342) and one or more key fields are identified (342). Similarly, one or more key fields are identified for vehicle data (346). The two tables are joined using a shared key (348) and the result is written to the target dataset (350). If both the accident table and the vehicle table are in the same SQL database, another way is to create a single node that reads data from the two tables in one query. A query can specify which data fields to select and whether to restrict the data with one or more filters (such as a WHERE clause). In some cases, it may be necessary to change the data used to join the tables, so the data is retrieved and joined locally, as shown in flow 338 . For example, the primary key of the vehicle table may be an integer data type, while the accident table may use zero-padded character fields to specify the vehicle involved.

The flow abstraction as shown in Figure 3D is common to most ETL and data preparation products. This flow model allows the user to logically control the transformation. Such flows are generally interpreted as imperative programs and are executed with little or no platform modification. That is, the user has provided specific details to define the physical controls over execution. For example, a typical ETL system working in this flow pulls down two tables from a database as specified, shapes the data as specified, joins the tables in the ETL engine, and the result is the target written to the dataset. While having full control over the physical plan can be useful, it eliminates the system's ability to modify or optimize the plan (eg, run the aforementioned flow in SQL Server) to improve performance. Since in many cases the customer does not need to control the details of the execution, this implementation allows operations to be expressed declaratively.

Some implementations here range from fully declarative queries to imperative programs. Some implementations utilize an internal analytical query language (AQL) and federated evaluators. By default, flows are interpreted as a single declarative query specification whenever possible. This declarative query is converted to AQL and passed to the query evaluator. The query evaluator eventually splits the operators and executes them in a distributed manner. In the example of Figure 3D above, the entire flow can be cast as one query. If both tables are from the same server, this entire operation will likely be pushed to a remote database, greatly improving performance. This flexibility not only allows the execution of optimization and distribution flows, but also allows the execution of queries against live data sources (eg, from transactional databases as well as data warehouses).

If the user wants to control the actual order of execution of the flow (for performance reasons etc.), the user can fix the operation. By pinning, the flow execution module is instructed not to move the operation past that point in the plan. In some cases, a user may temporarily want extreme control over the order (eg, while authoring or debugging a flow). In this case, all operators can be fixed and the flow will execute exactly in the order specified by the user.

Note that not all flows can be decomposed into a single AQL query, as shown in Figure 3E. In this flow, there is an hourly drop 352 that runs every hour (362) and the data is normalized (354) before adding (356) to the staging database. Next, each day (364), data from the staging database is aggregated (358) and written out (360) as the target dataset. In this case, the hourly schedule and the daily schedule should be left as separate parts.

4A-4V illustrate some aspects of adding coupling to a flow, according to some implementations. As shown in FIG. 4A, the user interface includes left pane 312 , flow area 313 , profile area 314 and data grid 315 . In the example of FIGS. 4A-4V, the user first connects to the SQL database using the connection palette in left pane 312 . In this case, the database contains Mortality Analysis and Reporting System (FARS) data provided by the US Department of Transportation Highway Traffic Safety Administration. As shown in FIG. 4B, the user selects the “Accident” table 404 from the list of available tables 402 . 4C, the user drags the incident table icon 406 to the flow area 313. In FIG. When table icon 406 is dropped into flow area 313, node 408 is created to represent the table, as shown in FIG. 4D. At this point, the accident table data is read and the profile information for the accident table is displayed in profile pane 314 .

Profile pane 314 provides distribution data for each of the columns, including state column 410, as shown in FIG. 4E. In some implementations, each column of data in the profile pane displays a histogram showing the distribution of the data. For example, California, Florida, and Georgia have high accident counts, while Delaware has low. In the profile pane, you can easily identify columns that are keys or partial keys using the key icon 412 at the top of each column. As shown in FIG. 4F, in some implementations, three different icons are used to designate whether a column is a database key, a system key 414, or an “almost” system key 416. In some implementations, a column is approximately a system key if the column in combination with one or more other columns is the system key. In some implementations, a column is nearly a system key if the column would be a system key if rows with null values were excluded. In this example, both "ST case" and "case number" are mostly system keys.

In FIG. 4G, the user has selected the “People” table 418 in the left pane 312 . In FIG. 4H, the user drags the people table 418 into the flow area 313, which appears as a moveable icon 419 while being dragged. After dropping the people table icon 419 into the flow area 313, a people node 422 is created in the flow area, as shown in FIG. 4I. At this stage there is no connection between the incident node 408 and the person node 422 . In this example, both nodes are selected, so profile pane 314 is split into two parts. A first part 420 shows the profile information for the incident node 408 and a second part 421 shows the profile information for the person node 422 .

FIG. 4J provides an enlarged view of flow pane 313 and profile pane 314 . The profile pane 314 includes an option 424 for displaying candidate join columns (ie, potential joins of data from two nodes). After selecting this option, the data fields that are candidates for merging are displayed in profile pane 314, as shown in FIG. 4K. Now that the join candidates are displayed, the profile pane 314 displays an option 426 to hide the join column candidates. In this example, profile pane 314 indicates that column STCase from the Persons table may be joined with the STCases field from the Incidents table (430). The profile pane also shows that there are three additional candidate joins for the Incidents table (428) and two additional candidate joins for the People table (432). In FIG. 4L, the user clicks (433) the hint icon and in response, two candidate columns are placed side by side in the profile pane, as shown in FIG. 4M. The header 434 of the ST case column of the accident table indicates that it can be joined with the ST case column of the person table.

FIG. 4N shows another method of combining data from multiple nodes. In this example, the user has loaded accident table data 408 and population table data 441 into flow area 313 . Simply dragging the population node 441 over the accident node 408 automatically creates a bond and displays a bond experience pane 442 where the user can review and/or modify the bond. In some implementations, the combined experience is placed in profile pane 314 . In other implementations, the combined experience temporarily replaces profile pane 314 . Once the join is created, a new node 440 is added to the flow, which graphically represents the creation of the connection between the two nodes 408 and 441.

Combined experience 442 includes a toolbar area 448 with various icons, as shown in FIG. 4O. When join candidate icon 450 is selected, the interface identifies which fields of each table are join candidates. Some implementations include a favorite icon 452, which (e.g., has been selected by the user in the past, identified as important by the user in the past, or commonly selected by the user in the past). ) shows the highlighted "favorites" data field. In some implementations, favorites icon 452 is used to designate particular data fields as favorites. Due to limited space for displaying columns in profile pane 314 and data pane 315, some implementations use information about favorite data fields to select columns to be displayed by default.

In some implementations, selection of the “Show Keys” icon 454 causes the interface to identify which data columns are keys or are part of a key consisting of multiple data fields. Some implementations include a data/metadata toggle icon 456 that switches the display from displaying information about data to displaying about metadata. In some implementations, data is always displayed, and metadata icon 456 toggles whether metadata is displayed in addition to data. Some implementations include a data grid icon 458, which toggles the display of data grid 315. In FIG. 4O, the data grid is currently displayed, so selecting the data grid icon 458 will not display the data grid. Implementations also typically include a search icon 460 that displays a search window. By default, searches are applied to both data and metadata (eg, both data field names and field data values). Some implementations include advanced search options to more precisely specify what to search for.

On the left side of the join experience 442 are a series of join controls that contain the specification of a join type 464 . As known in the art, joins are typically left outer joins, inner joins, right outer joins, or full outer joins. These are represented graphically by the combined icon 464 . The current bond type is highlighted, but the user can change the bond type by selecting another icon.

In some implementations, a join clause summary 466 is provided. It displays both the names of the fields on both sides of the join and a histogram of the data values for the data fields on both sides of the join. If there are multiple data fields in the join, in some implementations all relevant data fields are displayed. Other implementations include user interface controls (not shown) for scrolling through data fields within the binding. Some implementations also include a summary control 468 that indicates the number of rows in each of the tables that are joined based on the type of join condition. Selection of portions within this control determines what is displayed in profile pane 314 and data grid 315 .

4P, 4Q, and 4R show alternative user interfaces for the binding control area 462. FIG. In both cases, the binding type is displayed at the top. In either case there is a visual representation of the data fields involved in the join. Here, there are two data fields in the join: STCase and Year. Each of these options also has a section that graphically shows the percentage of rows in each table that have been joined. The upper portion of FIG. 4Q is displayed in FIG. 4U below.

FIG. 4R includes a lower portion showing how the two tables are related. Split bar 472 represents the rows of the accident table and split bar 474 represents the population table. The large middle bar 477 represents rows that are connected by an inner join between two tables. Since the currently selected join type is a left outer join, the join result set 476 also includes a portion 478 representing rows of the accident table that are not linked to any rows of the population table. Below is another rectangle 480 . This represents a row in the population table that is not linked to any row in the accident table. Part 480 is not included in result set 476 because the current join type is a left outer join (the rows of bottom rectangle 480 are included in a right outer join or full outer join). The user can select any part of this diagram and the selected part will be displayed in the profile and data panes. For example, the user can select the "left outer portion" rectangle 478 and then look at the rows in the data pane to see if those rows are relevant to the user's analysis.

FIG. 4S illustrates a binding experience using the binding control interface elements shown in FIG. 4R, including binding control selector 464. FIG. Here, left outer join icon 482 is highlighted, as shown more clearly in the enlarged view of FIG. 4T. In this example, the first table is the accident table and the second table is the factor table. As shown in FIG. 4U, the interface displays both bound 486 and unbound 488 rows. There are many unjoined rows in this example. The user can select unbound bar 488 to display the display of FIG. 4V. By brushing the profile and filtering the data grid, the user sees that nulls are the result of unmatched values with left outer joins, since there are no entries in the factor table prior to 2010.

The disclosed implementation supports many features that support various scenarios. Many of the functions have been described above, but the following scenarios illustrate the functions.

Scenario 1: Collecting Event Logs Alex works in the IT department and one of his jobs is to collect and prepare logs from machines in the infrastructure to be used for various debugging and analysis in the IT company. to create a shared dataset that

The machine is running Windows and Alex needs to collect application logs. There is already an agent that runs nightly and dumps a CSV export of logs to a shared directory. Each day's data is output to a separate directory, formatted to indicate the machine name. An excerpt of the application log is shown in Figure 5A.

Application log excerpts have some interesting features.
● The first line contains header information. This may or may not be the case generally.
• Each row of data has 6 columns, but the header has 5 columns.
● The delimiter here is clearly a ``,''.
● The last column may contain a multi-line string enclosed in quotation marks. Note that lines 3-9 here are all part of one line. Also note that this field uses double quotes to indicate quotes and should be taken literally.

Alex creates a flow that reads all CSV files in a particular directory and performs a jagged union on them (e.g. if at least one of the CSV files has a data field , create a data field, but if the same data field exists in more than one CSV file, create only one instance of that data field). In the CSV input routine, reading column 5 works fine, but the quotes in column 6 get stuck and are read as multiple columns.

Alex then
● Select columns in the data pane and merge them back together.
● Add the machine name obtained from the file name. He does this by selecting the machine name in the example data and selecting "extract as new column". The system infers patterns from this action.
● Right-click and select Add Identifier to generate a unique identifier for each row.
● Edit column names and types directly in the data pane.

This is all accomplished by direct action on the data in data pane 315 , which results in logic being inserted into the flow in flow pane 313 .

Alex then drags the target data repository onto the flow pane and wires the output to add these records to the cache containing the complete log records.

Finally, Alex's flow queries this target dataset to find the set of machines we reported on the previous day. This is compared to the current machines and a warning is printed to Alex with a list of machines that were expected not to report.

Note that Alex could have achieved the same result in different ways. for example,
• Alex can create two separate flows. One to run the capture and one to compare each day's machine to the previous day's machine and alert Alex of the results.
• Alex can create a flow that performs ingestion in one stage. Once that is done, Alex can run a second flow, querying the database and comparing each day to the previous day and alerting Alex.
• Alex can create flows that have targets as both inputs and outputs. This flow performs an ingest, writes it to the database, and aggregates to find the machine of the day. It also queries the target to get the previous day's results, performs a comparison, and raises a warning.

Alex knows that the machine needs to report overnight, so Alex runs the flow first thing every morning. Then use the rest of the morning to check the machines that didn't report.

Scenario 2: FARS Collection and Integration Bonnie works for an insurance company and wants to pull Mortality Analysis and Reporting System (FARS) data as a component of her analysis. The FARS data is available via FTP and Bonnie needs to figure out how to get it and put it together. She decided to do this using the data prep application 250 .

Bonnie researched a series of formats published by FARS and decided to use DBF files. These DBF files are scattered across FTP sites and are only available in compressed ZIP archives. Bonnie explores the treeview and selects the file she wants to download. Once the data is downloaded, Bonnie initiates the next step in the flow. She selects the collection of files and selects "Extract". This adds the step of unzipping the files into separate directories labeled by year.

As the data begins to gather, Bonnie sees a problem.
• In the first year there are 3 files corresponding to the 3 tables: Incidents, Persons and Vehicles. In subsequent years there will be many more tables besides these.
● File names are not standardized. For example, the name of the accident file is "accident.dbf" for 1975-1982 and 1994-2014, but is named "accYYYY.dbf" (where YYYY is the 4-digit year) for the years in between. .
● Even if the table name is the same, the structure changes slightly over time. The latest table contains additional columns not present in the previous data.

Bonnie starts with an accident table that exists in every year. She selects the files, right-clicks, and selects "union." This performs a jagged union, preserving columns. She repeats this with the other three tables that exist in all years, then with the remaining tables. Once she has done this, the final stage of her flow creates 19 separate tables.

Having obtained this, she attempts to assemble the data. It looks like the common join key could be the column ST_CASE, but just looking at the profile pane of the accident table shows that this is nowhere key column. The ST_CASE is not important, but if you click on the year you can easily see that there is only one ST_CASE per year. Similarly, year and ST_CASE look like good join keys.

She puts her hands on the people table. Before joining this table, each of the tables requires a year, which does not exist. However, since there is a year in the file path, you can select this data in the data pane and choose "Extract as new column". The system guesses the correct pattern for this and extracts the year for each row. Then select both tables in the flow, select one column and the ST_CASE column and drag them to the other table to create a join.

Now that we have the key, we continue to create joins to flatten the FARS data. Once complete, publish the data as a TDE (Tableau Data Extraction) to Tableau Server so that the team can use the data.

Scenario 3: FARS Cleanup Colin is another employee in the same department as Bonnie. Some people are trying to use the data that Bonnie's flow produces, but it contains a lot of cryptographic value. They turn to Colin when they find out that Bonnie has moved on to another company.

Looking at the flow, Colin can easily see its overall logic, as well as the encrypted coded data. The process seemed daunting when he found a 200-page PDF manual containing a cryptographic lookup table (LUT). An example of a PDF lookup table is shown in FIG. 5B. Some are simple, some are very complex.

Colin gets his hands on some of the more important tables. He found that he could select a table in a PDF file and paste it into the flow pane 313 . In some cases, the data in the table is not completely correct, but it works and Colin can manually patch the results in the data pane 315, saving him considerable time. His work is visibly fruitful. Even if the table is not aligned, it will be immediately apparent.

Ultimately, Colin pulls in 12 LUTs that he believes are particularly relevant to the analysis the team is performing, publishes the results, and makes the data available to the team. When detailed information about a particular column is desired, Colin can further extend the flow to include additional LUTs.

Scenario 4: Finding Data Errors Daniel, a developer at a large software company, is looking at data representing build times. Daniel created the data in an easy-to-use CSV format, where he has fine control over the format of the data, but now wants to read the data and add it to the database he created.

When loading the data she scans the profile view 314 . What immediately struck her as odd was the presence of some negative time builds. Something clearly exists and she wants to debug the problem, but she also wants to pull the data together for analysis.

She selects negative hours in the profile view and clicks "Keep Only" to keep only the rows with errors. She adds targets to pass these to the file. She uses those raw lines to guide debugging.

Back in the flow, she adds another branch just before the filter. She selects negative values again (eg, in profile pane 314 or data pane 315) and then simply presses "delete." This replaces the value with null. This is a good indicator that the actual value is simply unknown. She continues with the rest of the simple flow, adding build data to the database, and then checking for negative values.

Scenario 5: Vehicle Parts Tracking Earl works for an automobile manufacturer and is responsible for maintaining a data set that shows the current status of each vehicle and plant major part. Data is reported to multiple operational stores, but these operational stores are very large. There are hundreds of thousands of parts, and as an automated facility, thousands of records are mechanically created for each vehicle or part as it passes through the factory. These operational stores also contain many records such as other operational information (eg, "pressure at valve 134 is 500 kPa") that is not related to part status. There is a business need for quick and concise records for each part.

Earl drags a table from each of the three relational operational stores to the flow pane 313 . Two of them store data as a single table containing log records. Third, there is a small star schema, which Earl flattens quickly by creating joins by dragging and dropping.

Then, with additional drag and drop, Earl can quickly perform a union of jagged tables. As a result, he can drag and drop columns together and the interface coalesces the results.

The part identification number has some problems, including hyphens in one system value. Earl gets one of the values in the data pane 315, selects the hyphen, and presses delete. The interface infers a rule to remove hyphens from this column and inserts into the flow a rule to remove hyphens from all data in that column.

Earl doesn't need most status codes because they aren't relevant to the current project. He simply wants the status code associated with the part. He pulls a table containing information about status codes and drops it into the last node of the flow. As a result, a new binding is created for the status code. Here he selects only those rows where "Target Type" equals "Part" and selects "Keep Only" to filter out other values. This filtering is done in profile pane 314 or data pane 315 .

Finally, Earl only needs the last value of each part. With direct gestures, he sorts the data in the data pane by date, groups by part number, and adds a "top-n" table calculation to get only the last update for each part.

Earl runs the flow and discovers that it takes 4 hours to run. But he knows how to speed it up. He can record the last time he ran a flow, and can only incorporate new records in each subsequent run. However, doing this requires updating existing rows in the cumulative set and adding rows only if they represent new parts. He needs a "merge" operation.

Earl uses the part number to identify matches and provides actions if a match does or does not occur. Using the update logic, Earl's flow takes just 15 minutes to run. By saving time, the company can more closely track where parts are in the warehouse and what their status is.

Earl then pushes this job to a server so that it can be scheduled and run centrally. You can also create a scheduled task on your desktop machine, which runs the flow using a command line interface.

Scenario 6: Investment Broker Gaston works for an investment broker whose team captures and digests data generated by the IT department, making the data available to various teams working with clients. The IT department creates various data sets representing portions of the client's portfolio (bond positions, stock positions, etc.), but each alone is not what Gaston's client needs.

One team, led by Harmine, needs to pull all customer location data together so that the team can answer questions when customers call. Data preparation is less complicated.

Gaston processes the nightly database drops generated by the IT department, unions them, and performs a few simple checks to make sure the data is okay. It then filters to what Harmine's team needs and creates a TDE for use by the team.

In past tours, Gaston forgot to run the flow every morning. However, with the new Data Prep Application 250, this flow can be handled declaratively. He sends Harmine the TDS that Hermine's team uses. This ensures that all data visualizations Harmine's team creates run directly against the database. This means that Gaston doesn't have to worry about updating data, and it runs quickly.

Another team, led by Ian, uses similar data to review the performance of customer accounts. To generate this data, Gaston reuses the work he did for Hermine, but filters the data into Ian's team's accounts and runs additional flows so that Ian's team can perform the analysis. Combine the data with various indexes and performance indicators so that it can be executed. This work is expensive and doesn't seem to work well live. When he runs the flow, it takes hours to complete, but Ian's team only needs it once a month. Gaston does this once a month by setting up a recurring calendar item on the server.

Scenario 7: Customer data scrubbing Karl is a strategic account manager for a large software company. He uses Tableau to identify participants at industry conferences, who they work for, who they represent, whether they are active customers or prospects, whether the company is a small company. You're trying to visualize information about whether it's big or big.

Karl has a list of conference attendees and has had similar experiences in the past. In his last experience, it took him 8 hours to clean up his list and 15 minutes to finish building the visualization. This time he uses the data preparation application 250 to speed up and automate the process.

Karl wants to clean up the company name first. As expected, the data shows that the same company is often listed in multiple different formats, some of which are misspelled. He calls a fuzzy deduplication routine provided in the operations palette to identify potential duplicates. He reviews the results and fixes multiple cases where the algorithm was overestimated. He also finds multiple cases missed by the algorithm and groups them together. This creates a customer list that includes legitimate company names.

It then attempts to join the data with the list of companies held in the Tableau Server data source. He found that each company had multiple listings. Several different companies may have the same name, and one company may have multiple accounts based on region.

To sort this out, Carl uses the REST connector he discovered for LinkedIn™ and passes it to each email address in the data to get each person's country and state. This procedure takes the information he has (eg, the person's name, company, job title) and uses LinkedIn's search functionality to find the best results for each entry. The company and location data are then combined with the data in the server to find the correct account.

Carl has found that his union doesn't always work. The legal company name he chooses doesn't always match the one in his account database. He converts his joins to fuzzy joins, checks for fuzzy matches, and manually corrects the results.

Now that you have cleaned up your data, open it up in Tableau and create a visualization of the data.

Commonly used flow features are:
● Multi-level unions, joins, and aggregations that require the user to precisely control the logical order of operations.
● Layouts arranged and annotated by the user for better understanding.
● The structure of the data needs to be clarified as it progresses through the flow.
• Reuse a portion of the flow to generate two different outputs.
● Authors who prepare data for two or more other users, possibly in separate teams.
● Schedule a flow to run automatically.

Data preparation applications are sometimes categorized as ETL (Extraction, Transformation, and Loading) systems. Each of the three phases performs a different type of task.

In the extraction phase, users pull data from one or more available data sources. A user typically performs the following tasks:
● Simply move files. For example, a user can retrieve files from an FTP source before other processing.
Data that differ widely in structure (e.g., relational, semi-structured, or unstructured), format (e.g., structured storage, CSV files, or JSON files), and sources (e.g., file systems or formal databases) take in.
● Selectively read the entire source or part of the source. Partial reads are often done when pulling data that is newer or has changed since the last time it was ingested, or when sampling or pulling chunks for performance reasons.

During the transformation phase, the user transforms the data in various ways. Transformation typically includes the following tasks:
• Clean the data, including correcting errors, handling missing or duplicate values, adjusting variant values that should be identical, and conforming to standards.
● Extend or enrich data through scalar and table calculations, aggregations, row and column filtering, pivoting (de-)or incorporating external data (such as geocoding).
● Combining multiple sources via unions or joins (including fuzzy joins).
• De-interleave multiple types of data that are grouped together (either by rows or columns) for separate processing.
● Extract profiles or metrics about your data to better understand your data.

In the loading phase, the user saves the results so that they can be analyzed. This includes:
• Writing data to Tableau Data Extraction (TDE), formatted files (such as CSV or Excel), or external databases.
● Create snapshots on a schedule.
● Add or update data with new or changed results.

After a user builds a flow to prepare data, the user often needs to:
● Schedule flows to run at specified times or in coordination with other flows.
● Share your flow results with others.
● Share the flow itself with other users so they can examine, modify, duplicate, and manage the flow. This includes sharing flows or data with IT so they can improve and manage it.

The disclosed system 250 gives the user control. Data prep applications often make intelligent choices for the user, but the user can always exercise control. In many cases, there are two different aspects of control. In controlling the logical order of operations (used to ensure that results are correct and consistent with the user's desired semantics) and physical controls (used primarily to ensure performance) be.

The disclosed data prep application 250 also provides flexibility. A user can assemble and reassemble the data generation components in any way to achieve the desired shape of the data.

The disclosed data prep application 250 provides incremental interaction and immediate feedback. As the user performs actions, the system provides feedback through immediate results as well as visual feedback on samples of the user's data.

Typically, ETL tools use imperative semantics. That is, the user specifies the details of all operations and the order in which they are to be performed. This gives the user complete control. In contrast, a SQL database engine can evaluate declarative queries and choose the optimal execution plan based on the data requested by the query.

The disclosed implementation supports both imperative and declarative operations, allowing the user to select between these two execution options with varying levels of granularity. For example, a user may initially want to have full control of the flow while learning about a new data set. Once the user is satisfied with the results, the user can then relinquish all or part of the control to the data prep application in order to optimize execution speed. In some implementations, the user can specify default behavior (imperative or declarative) for each flow, overriding the default behavior of individual nodes.

The disclosed implementation can write data to many different target databases, including TDE, SQL Server, Oracle, Redshift, flat files, and others. In some cases, flows create new datasets in the target system. In other examples, a flow modifies an existing dataset by adding new rows, updating existing rows, inserting rows, or deleting rows.

Errors can occur during flow execution. Errors may include transient system problems, potential known error conditions in the data for which users may encode corrective actions, and implied constraints not considered by the author. be. The disclosed implementations generally handle these error conditions automatically when possible. For example, if the same error condition occurred in the past, some implementations reapply the known solution.

Flows are essentially data transformations, but implementations allow users to annotate outputs with declarative modeling information to describe how the outputs should be used, displayed, validated, or combined. Examples include:
● Annotations that affect how values are displayed in the Tableau, such as default colors or formatting.
● Annotations on the field indicating units or systems.
● Create aliases and groups.
● Functional constraints such as primary and foreign keys between tables.
● Domain constraints, such as requiring fields to be positive.

The disclosed implementation generally includes the following components.
● A front-end area that users interact with to view, build, edit, and run flows.
- Abstract Flow Language (AFL). It is an internal language that expresses all the logic in the flow, such as connecting to sources, computations and other transformations, modeling operations, and processing rows that are the result of the flow.
● Execution engine. This engine interprets and executes AFL programs. In some implementations, this engine runs locally. Queries may be pushed to remote servers, but results and further processing are done using local resources. In a server environment, the server provides a shared distributed execution environment for flows. This server can schedule and execute flows from many users and can automatically analyze and scale out AFL flows.
● A catalog server that allows publishing flows to others.

Some data visualization applications can run data prep flows and can use TDE or other authored datasets to build data visualizations.

The disclosed implementation can also import some dataflows created by other applications (eg, created with ETL tools).

Implementations allow users to:
• Connect to and read from the data source, as shown in Figure 6B.
• Construct a flow that combines the supported operations (see Figure 6A) in any order and combination.
● See a reasonable sample of how data is transformed at each step of the flow (eg, in the profile and data panes).
● Craft visualization of data at every step of the flow.
• Run the completed flow locally to generate output such as TDE or CSV output (see Figure 6C).
● Publish pipeline or TDE results to a catalog server.
● Import a TDS (Tableau Data Source) created in DataPrep as an explicit flow.

Accessing a configured server allows users to:
● Share TDE with others.
● Share data prep pipelines (flows) with other users with proper security.
● Run data prep pipelines in a server environment to create TDEs manually or on a schedule.

A node's output can be sent to multiple successor nodes. There are two basic cases here. In the first case, the flow diverges and never returns. If the flow does not converge, there will be several outputs from the flow. In this case, each branch is effectively a separate query made up of all preceding queries in the tree. Where possible, implementations optimize this to avoid executing shared parts of the flow multiple times.

In the second case the flow converges. Semantically, this means that the line goes through both passes. Again, execution of a flow generally does not duplicate its predecessor flow. Note that both these cases can occur in one flow.

The user interface allows the following:
● Users will be able to create forks in flows. When a new node is added, the user can specify whether the new node should fork the selected node or be inserted as an intermediate node in an existing sequence of operations. For example, if there currently exists a path from node A to node B and the user chooses to insert a new node at A, the user can either create a second path to the new node or You can choose to insert a new node between
● Users will be able to run individual outputs of a flow instead of the entire flow.

Users can add filters to arbitrarily complex flows. For example, a user can click at a point in the flow to add a filter and then enter a computation that acts as a predicate. In some implementations, computational expressions are restricted to scalar functions. However, some implementations enable more complex expressions such as aggregations, table calculations, and level of detail expressions.

The user can edit any filter, even if it was inferred by the system. In particular, all filters are represented as expressions.

Profile pane 314 and data pane 315 provide an easy way to create filters. For example, in some implementations, a user can select one or more data values in a data pane column, right-click, and select "Keep Only" or "Exclude." This inserts the filter into the flow of the currently selected node. The system infers an expression to implement the filter and saves the expression. If the user later needs to change the filters, whether immediately or in a year, they can easily do so.

In profile pane 314, the user can select buckets that specify ranges of values for data fields. For example, for categorical fields, this range is typically specified as a list of values. For numeric fields, this range is typically specified as a continuous range with an upper or lower bound. A user can select a bucket to easily create a filter that selects (or excludes) all rows with a range of field values.

When a user creates a filter based on multiple values in one column or multiple buckets in one column, the filter expression uses OR. That is, a row matches an expression if it matches any one of the values or ranges.

Users can also create filters based on multiple data values in a single row in the data pane. In this case the filter expression uses AND. That is, only rows that match all specified values match the expression. This can also be applied to buckets in the profile pane. In this case, rows must match in each of the selected bucket ranges.

Some implementations also allow filters to be created based on multiple data values, including more than one row and including more than one column. In this case, the formulas created are in disjunctive normal form, with each disjunction corresponding to one of the rows having the selected data values. In some implementations, the same approach applies to profile window range selection.

In each of these cases, the user visually selects a data value or bucket and uses simple gestures (e.g., right-click and menu selection) to restrict rows to only selected values or to select Note that we create a filter that excludes values. The user does not need to understand how to write expressions in correct Boolean logic.

As shown above with respect to Figures 4A-4V, the user can create joins. As shown in Figure 9 below, bindings may be pushed to a remote server for execution, depending on whether declarative execution is enabled.

In some implementations, a simplified or condensed version of the flow is provided as nodes and annotations. In some implementations, the user can toggle between full view or summary view, or toggle individual nodes to hide or show details within nodes. For example, one node may contain 12 operations to perform cleanup for a particular source file. After repeating the cleanup procedure a few times, they are working fine and users generally don't want to confirm details. Even though the details remain, the user can hide the clutter by displaying only a summarized version of the node.

In some implementations, operational nodes that do not fan out are collapsed together into annotations on the node. Operations such as joins and splits break the flow at additional nodes. In some implementations, the layout of compressed views is automatic. In some implementations, the user can rearrange nodes in the summary view.

Both the profile pane and data pane provide useful information about the set of rows associated with the currently selected node in the flow pane. For example, the profile pane displays the cardinality of various data values in the data (eg, a histogram showing how many rows have each data value). Shows the distribution of values for multiple data fields. Due to the amount of data displayed in the profile pane, data retrieval is typically performed asynchronously.

In some implementations, the user can click data values in the profile pane to see proportional brushing of other items. When the user selects a particular data value, the user interface will:
● Indicate selection.
• Use proportional brushing to show correlations with other columns in that table.
● Filter or highlight the relevant data pane to show only rows whose values match your selection. (This filters the data displayed in the Data pane; it does not create a Filter node in the Flow pane.)
● If multiple values are selected in the profile pane, all selected values are displayed and the data pane is filtered accordingly (that is, filtered to rows matching any one value). ).

In some implementations, rows are not displayed in the data pane unless specifically requested by the user. In some implementations, data panes are always automatically populated and the process proceeds asynchronously. In some implementations, different standards apply based on the row cardinality of the selected node. For example, some implementations display a row if the cardinality is below a threshold and do not display the row or continue asynchronously if the cardinality is above the threshold. In some implementations, two thresholds are specified to designate a set of rows as small, large, or extra large. In some implementations, the interface displays small cardinality rows, displays large cardinality rows asynchronously, and does not display very large cardinality results. Of course, the data pane can only display a small number of rows. This is typically selected by sampling (eg, every n rows). In some implementations, the data pane implements infinite scrolling to accommodate unknown amounts of data.

The disclosed data prep application provides a document model that the user interface natively reads, modifies, and manipulates. This model describes the flow to the user while providing the format for the UI. This model can be transformed into a running tableau model using AQL and federated evaluators. This model also enables reliable caching and reuse of intermediate results.

As shown in FIG. 7A, the data model contains three sub-models, each describing the flow through the appropriate stages of evaluation. The first sub-model is “Room Doc” 702 . (In some implementations, data prep applications are referred to as "Looms.")

The Loom doc 702 is a model that explains the flow, and the flow is the flow that the user directly confirms and operates. Loom doc 702 contains all the information needed to perform all ETL operations and type checking. Typically, the Loom doc 702 does not contain information purely necessary for rendering or editing the flow. Loom doc 702 is structured as a flow. Each operation has:
● A set of properties that describe how the operation should be performed.
● Zero or more inputs describing the data on which to perform the operation.
● Zero or more outputs describing the data resulting from this operation.

There are four main types of operations: input operations, transform operations, output operations, and container operations.

Input operations perform the "extraction" portion of the ETL. They are configured to bind a flow to a data source, pull data from that source and expose that data to the flow. Input operations include reading CSV files or connecting to SQL databases. An input operation node typically has zero inputs and at least one output.

The transform operation performs the "transform" portion of the ETL. They provide "functional" operations on streams of data and transform them. Examples of transformation operations include "make calculation as '[HospitalName]-[Year]'", "filter rows with hospitalId='HarborView'", and so on. A transform node has at least one input and at least one output.

Output operations provide the "read" portion of the ETL. They have a secondary role of actually updating the downstream data source with the incoming data stream. These nodes have one input and no output (no "output" to subsequent nodes in the flow).

Container operations group other operations into logical groups. These are used to facilitate flow documentation. Container operations are exposed to the user as "nodes" in the flow pane. Each container node contains other flow elements (eg, a series of regular nodes) and fields for documents. A container node can have any number of inputs and any number of outputs.

A data stream represents the actual rows of data that move across the flow from one node to another. Logically these can be displayed as rows, but operationally the data streams can be implemented in a variety of ways. For example, some flows are simply compiled to AQL (Analytical Query Language).

An extensible operation is one that the data prep application does not directly know how to evaluate and that calls into third-party processes or code. These are operations that are not performed as part of the Federation Evaluator.

A logical model 704 is a model that includes all entities, fields, relationships, and constraints. It is built by executing a flow and defines a model that is built in any part of the flow. The fields of the logical model are the resulting columns. Some entities are composed of other entities, but entities in the logical model represent tables of results. For example, unions have entities that are the result of other entities. Logical model constraints represent additional constraints such as filters. Relationships in the logical model represent relationships between entities and provide sufficient information to combine them.

A third sub-model is the physics model 706 . The physical model contains metadata for caching, such as information identifying whether the flow should be rerun, as well as how to query the flow directly in the results database. Metadata includes:
● A hash of the logical model at this point.
● Timestamp for each root data source, and when it was last queried.
- A path or URI that describes the location of the result data.

This data is used to optimize the flow as well as enable faster result navigation.

A physical model contains references (such as pointers to files or data stores) to the logical models used to create this physical model. The physical model 706 also includes a Tableau Data Source (TDS) that identifies the data sources used to evaluate the model. Typically this is generated from the logical model 704 .

The physical model also contains AQL (Analytical Query Language) queries that are used to extract data from the specified data sources.

As shown in FIG. 7A, loom doc 702 is compiled (722) to form logical model 704, and logical model 704 is evaluated (724) to form a physical model.

FIG. 7B shows a file format 710 used by some implementations. File format 710 is used for both local and remote execution. Note that the file format contains both data and flow. In some cases, a flow may perform a copy/paste to create data. In such cases, the data becomes part of the flow. The file format keeps the UI state separate from the underlying flow. Some displays are saved with the application. Other parts of the layout are user-specific and stored outside the application. The file format can be versioned.

File formats include a multi-document format. In some implementations, the file format has three main parts, as shown in FIG. 7B. In some implementations, file format 710 includes editing information 712 . This section is responsible for keeping the editing experience continuous across devices and across editing sessions. This section contains data that is not needed for flow evaluation, but is needed to reconstruct the user's UI. Editing information 712 includes an undo history, which includes a persistent undo buffer that allows users to undo operations after an editing session is closed and reopened. be Editing information also includes UI states that are not reflected in how the flow is executed, such as the pane being displayed and the x/y coordinates of the flow nodes. When the user reopens the UI, the user can see what was in the past, making it easier to resume work.

File format 710 includes Loom Doc 702, as described above with respect to FIG. 7A. This is the only section of the file format that is required. This section contains flows.

File format 710 also includes local data 714, which includes any tables or local data needed to execute the flow. This data can be created through user interaction, such as pasting an HTML table into a data prep application, or if the flow uses a local CSV file that must be uploaded to the server for evaluation. can be done.

The evaluation subsystem is shown in Figure 7C. An evaluation subsystem provides a reliable method for evaluating flows. The evaluation subsystem also provides an easy way to manipulate the results of past executions or layer operations on top of flow operations. Additionally, the evaluation subsystem provides a natural way to reuse results from parts of the flow when executing later parts of the flow. The evaluation subsystem also provides a fast way to act on cached results.

As shown in FIG. 7C, there are two basic contexts for evaluating flows. Upon executing 740 the flow, the process evaluates the flow and feeds the results into the output node. When running in debug mode, the process writes results to a temporary database, which can be used to speed navigation, analysis, and execution of partial flows.

In navigation and analysis (730), the user is exploring the dataset. This includes checking data distribution, searching for dirty data, etc. In these scenarios, the evaluator generally avoids running the entire flow and instead performs faster queries directly against the temporary database created from past flow runs.

These processes take advantage of good metadata about caching to ensure that smart caching decisions can be made.

Some implementations include an asynchronous subsystem, as shown in FIG. 7D. Asynchronous subsystems provide users with non-blocking operation. Users are not blocked from fetching rows if they are doing a lot of operations that do not require row fetching. Asynchronous subsystems provide incremental results. In many cases, users do not need the complete set of data to begin validating or understanding results. In such cases, an asynchronous subsystem provides the best results on arrival. The asynchronous subsystem also provides a reliable "cancel" operation for queries in progress.

In some implementations, the asynchronous model includes four main components.
● Browser layer. This layer gets the UUID and update version from the asynchronous task it starts. Then use the UUID to get the update.
●REST APIs. This layer starts tasks on a thread pool. Tasks in the thread pool update the status service as they get updates. When the browser layer wants to know if there are any updates, it calls a REST API procedure to get the latest status.
● Aql API. This layer is called as if it were a synchronous call with callbacks. A call ends only when the underlying request ends. However, the callback can update the status service while the row has already been processed. This makes it possible to provide the user with a step-by-step progress.
● Federation Evaluator. Since AqlApi runs as a new process, it calls a federation evaluator that provides another layer of asynchrony.

The implementation of the cancel operation depends on where the cancellation occurs. At the browser layer, simply send a cancel request to stop polling for results. The REST API makes it easy to send a cancel event to a running thread.

In some implementations, flows can be safely and easily "refactored" after they have already been created. Current ETL tools allow you to create flows that look simple at first, but become unchangeable when scaled up. This is because it is difficult for people to understand how changes affect the flow, and it is difficult to break chunks of behavior into pieces that are relevant to business requirements. Much of this is due to the user interface, but the underlying language needs to provide the necessary information for the UI.

The disclosed implementation allows users to create flows that can be easily refactored. This means that the user can easily execute an operation or node.
● Move operations and rearrange them logically. Implementations provide direct feedback as to whether these operations create errors. For example, suppose a user has a flow of ADD_COLUMN->FILTER. As long as the FILTER does not use added columns, the user can drag the FILTER node before the ADD_COLUMN node. If the FILTER uses the new column, the interface immediately raises an error and notifies the user of the problem.
● Combine multiple operations and nodes into one new node (reusable). This new node has a "type" to accept and a "type" to return. For example, suppose a user has a snippet of a flow containing JOIN_TABLES->ALTER_COLUMN->ALTER_COLUMN->ALTER_COLUMN. Depending on the implementation, the user may combine these four steps into one node and assign the node a meaningful name such as "FIXUP_CODES". The new node takes two tables as input and returns a table. The input table types include the columns they were joined to and any columns that were to be used in ALTER_COLUMNS. The type of the output table is the type resulting from the operation.
● Separate operations from nodes. Here, the user can reorganize the operations that were organically added to the node during immediate operations. For example, a user has a huge node with 20 operations and wants to split the 10 operations related to hospital code modification into their own nodes. The user can select those nodes and pull them. If there are other operations on the node that depend on the deleted operation, the system will display an error and suggest a fix that creates a new node after the FixupHospitalCodes node.
● Inline operations into existing nodes. After the user has cleaned, there may be work that belongs to another part of the flow. For example, when the user cleans up the insurance code, it finds some problems in the hospital code and cleans it up. Next, the user wants to move the hospital code cleanup to the FixupHospitalCodes node. This is done using a simple drag and drop operation. When the user attempts to drop an operation to a location in the flow before the dependent operation, the interface provides immediate visual feedback that the suggested drop location will not work.
● Change the type and immediately see if any part of the flow is broken. The user can use the flow and then decide to change the type of one of the columns. Depending on the implementation, the user is immediately notified of any problems, even before running the flow.

In some implementations, when the user is refactoring a flow, the system assists by identifying drop targets. For example, if the user selects a node and begins dragging it within the flowpane, in some implementations the location where the node can be moved is displayed (eg, highlighted).

The disclosed data prep application uses a language that has three aspects.
●Formula language. This is how the user defines the calculation.
● Data flow language. This is how the user defines the inputs, transformations, relationships, and outputs of the flow. These operations modify the data model directly. The types in this language are entities (tables) and relationships rather than individual columns. Users do not see this language directly, but use it indirectly by creating nodes and operations in the UI. Examples include joining tables and deleting columns.
● Control flow language. These are operations that can occur around dataflows, but are not really dataflows. For example, copying a zip from a file share and unzipping it, getting an exported TDE and copying it to a share, running a dataflow against an arbitrary list of data sources, and so on.

Although these languages are different, they overlap each other. Expression languages are used by flow languages, and flow languages can be used by control flow languages.

This language describes the flow of operations logically from left to right as shown in FIG. 8A. However, due to how the flow is evaluated, in a practical implementation the operations can be rearranged to improve performance. For example, moving filters to a remote database when extracting data can greatly improve overall execution speed.

Dataflow languages are the languages most people associate with data prep applications because they describe the flows and relationships that directly affect ETL. There are two main components in this part of the language: models and nodes/operations. This differs from standard ETL tools. Instead of a flow that manipulates the data directly (e.g. the flow of the actual rows from the 'filter' operation to the 'add field' operation), the flow disclosed is a logical model that specifies what to create and a logical model Define a physical model that defines how to implement This abstraction provides more leeway for optimization.

A model is a basic noun. A model describes the schema and relationships of the data being manipulated. As noted above, there is a logical model and a separate physical model. A logical model provides a basic "type" of flow at a particular point. Describe the fields, entities, and relationships that describe the data to be transformed. This model includes sets, groups, and so on. A logical model specifies what is needed, but not the implementation. The core parts of this model are:
• Fields: These are the actual fields that will be converted to (or assisted in calculations for) data fields in the output. Each field is associated with an entity and an expression. Not all fields are necessarily displayed. There are three types of fields: physical fields, calculated fields, and transient fields. Physical fields are embodied in the resulting dataset. These are either fields or calculations as appropriate. Computed fields are written to the resulting TDS as computed fields and are therefore never instantiated. Temporary fields are created for better computation of physical fields. They are never written out. If a temporary field is referenced by a calculated field, the language issues a warning and treats this field as a calculated field.
● Entity: An object that describes the namespace of the logical model. Entities can consist of collections of entities that are created by arriving schemas of tables or that are related together by relationships.
• Relationships: Objects that describe how various entities are related to each other. They can be used to combine multiple entities into new composite entities.
● Constraints: Describes the constraints added to the entity. Constraints include filters that actually limit the entity results. Some constraints apply. A mandatory constraint is guaranteed from an upstream source, such as a unique or not-null constraint. Some constraints are expressed. These are constraints that are believed to be true. Whenever data is found to violate this constraint, the user is notified in some way.

A flow can contain one or more forks in the logical model. Forks of a flow use the same logical model for each fork. However, there are new entities under the covers on both sides of the fork. These entities are essentially passed on to the original entity unless columns are projected or removed.

One reason for creating new entities is to track relationships between entities. These relationships remain valid even if no fields are changed. However, when the field changes, it becomes a new field in the new entity, so we know the relationship no longer works.

In some implementations, nodes or operations can be fixed. A flow describes the logical order of a set of operations, but the system is free to optimize the processing by having different physical orders. However, the user may want to ensure that the logical order and physical order are exactly the same. In such cases, the user can "pin" the node. When a node is pinned, the system ensures that pre-pin operations physically occur before post-pin operations. In some cases, this leads to some form of realization. However, the system will stream through this whenever possible.

A physical model describes the realization of a logical model at a particular point. Each physical model has a reference to the logical model that was used to generate it. Physical models are important for caching, incremental flow execution, and read operations. The physical model contains references to files containing the results of the flow. This is a unique hash describing the logical model up to this point. The physical model also specifies the TDS (Tableau Data Source) and AQL (Analytical Query Language) generated for execution.

Nodes and operations are basic verbs. Nodes in the model contain operations that define how data is shaped, calculated, and filtered. For consistency with the UI language, the term "operation" refers to one of the "nodes" in the flow that does something. Nodes refer to containers that contain operations and are used to map to what is displayed to the user in the UI's flow pane. Each special node/operation has associated properties that describe how it operates.

There are four basic types of nodes: input operations, transform operations, output operations, and container nodes. Input operations create logical models from external sources. An example is the operation of importing CSV. An input operation represents E (extraction) in ETL. A transform operation transforms a logical model into a new logical model. A transform operation takes a logical model and returns a new logical model. A transform node represents a T (transform) in ETL. An example would be a project operation that adds columns to an existing logical model. Output operations take a logical model and materialize it in another data store. For example, an operation that takes a logical model and implements the result in a TDE. These operations represent the L (reads) of ETL. A container node is the basic abstraction for how composition is done throughout the flow, and also provides an abstraction for what is displayed when the node is displayed in the UI.

As shown in Figure 8B, the type system consists of three main concepts.
● Operations are atomic actions, each with inputs and outputs and a required set of fields.
● Mandatory fields are fields that are required for an operation. Required fields can be determined by evaluating the operation in an empty type environment and collecting any of the "expected" fields.
● The Type Environment is a construct that determines how to find the type of a particular point in the flow. Each "edge" of the flow graph represents a type environment.

Type checking is performed in two stages. During the type environment creation phase, the system executes the flow in the direction of the flow. The system keeps track of the types required by each node and the type environment that the node outputs. If the flow is abstract (eg, does not actually connect to any input nodes), an empty type environment is used. Mold refinement is the second step. In this phase, the system takes the type environments from the first phase and flows them "backwards" to see if type narrowing that occurred in creating the type environments caused type conflicts. . During this phase, the system also creates a set of fields required for the entire subflow.

Each operation has an associated type environment. This environment contains all accessible fields and their types. As shown in Figure 8C, the type environment has five properties.

An environment can be either "Open" or "Closed." If the environment is open, it is assumed that there may be unknown fields. In this case, all unknown fields are assumed to be of type. These fields are added to the AssumedTypes field. If the environment is closed, the environment assumes all fields are known, so any unknown fields fail.

All known types are in the Types member. This is a mapping from field names to their types. A type can be another type environment or it can be a field. A field is the most basic type.

Each field consists of two parts. basicTypes is a set of types describing the set of possible types for the field. If there is only one element in this set, its type is known. A type error occurs if the set is empty. If the set has multiple elements, there are multiple possible types. The system can resolve and further narrow the type if necessary. derivedFrom is a reference to the field that was used to derive it.

Each field in scope has a set of potential types. Each type can be any combination of boolean, string, integer, decimal, date, datetime, double, geometry, and duration. There is also an "Any" type. This is an abbreviation for any type.

In an open environment, it may be a field that is known not to exist. For example, after a "removeField" operation, the system may not see all the fields in the type environment (because they are open), but the system knows that the field just removed does not exist. there is The type environment property "NotPresent" is used to identify such fields.

The AssumedTypes property is a list of types added because they are referenced, not types added because they are defined. For example, given an expression [A]+[B] to be evaluated in an open environment, the system assumes there are two fields, A and B. The AssumedTypes property allows the system to track content added in this way. These fields can be rolled up to further filter the types as well as determine the fields required for the container.

A "Previous" type environment property is a reference to the type environment from which it is derived. This is used in the type refinement stage when traversing backwards through the flow looking for type mismatches.

A type environment can also be configured. This happens for operations that receive multiple inputs. When the type environments are merged, each type environment is mapped to the values of that type's collection. Further type solutions are then delegated to individual type environments. The operator is then responsible for transforming this type environment into an output type environment. Often the type environment is "flattened" in some way to create a new type environment with only fields as types.

It is used by the Join and Union operators to provide a way to accurately use all fields of various environments in their own expressions and map environments to output type environments.

The type environment created by an input node is the schema returned by the data source it is reading from. For SQL databases, this would be the schema of the table, query, stored procedure, or view to extract. For CSV files, this will be the schema pulled from the file regardless of the types the user has associated with the columns. Each column and its type is converted to a field/type mapping. Additionally, the type environment is marked as closed.

The type environment of a transformation node is the environment of its inputs. If there are multiple inputs, merge them to create a type environment for the operation. The output is a monotyped environment based on operators. A number of operations are shown in the tables of FIGS. 8J-1 through 8J-3.

Since a container node can have multiple inputs, its type environment becomes a composite type environment that routes appropriate child type environments to appropriate output nodes. Pulling and reusing containers resolves in an empty type environment for each input to determine dependencies.

In some implementations, a container node is the only type of node that can have more than one output. In this case, there may be multiple output type environments. This should not be confused with branching of the output, which can occur at any node. However, when branching the output, each of the output edges will be in the same type environment.

There are some cases where type errors are flagged when the system detects conflicting requirements for a field. Unresolved fields are not treated as errors at this stage, as this stage can occur in flows where the input is not constrained. However, when the user tries to run the flow, the unresolved variables are reported as a problem.

Many inputs have specific definitions of types. For example, specific definitions include using CHAR(10) instead of VARCHAR(2000), the collation the field uses, or the scale and precision of decimal types. Some implementations do not track these as part of the type system, but track them as part of the runtime information.

UI and middle tier can be obtained in runtime type. This information not only passes through normal callbacks, but is embedded in tempdb types (eg, if the system is populating data from cached executions). The UI presents the more specific known types to the user, but does not type check based on them. This allows you to create OutputNodes that use more specific types, while allowing other parts of the system to use more simplified types.

FIG. 8D shows a simple type check based on the flow of all known data types. FIG. 8E shows a simple type failure with a perfectly known type. FIG. 8F shows a simple type environment computation for a partial flow. FIG. 8G shows types of packaged container nodes. FIG. 8H shows a more complex type environment scenario. FIG. 8I shows reuse of a more complex type environment scenario.

Some implementations infer data types and use the inferred data types to optimize or validate data flows. This is especially useful for text-based data sources such as XLS or CSV files. Based on how the data element is used later in the flow, it may be possible to infer the data type, and the inferred data type can be used earlier in the flow. In some implementations, data elements received as text strings can be cast as the appropriate data type immediately after being retrieved from the data source. In some cases, data type inference is recursive. That is, inferring the data type of one data element allows the system to infer the data type of one or more additional data elements. In some cases, data type inference is used to determine the exact data type (for example, a type that determines that a data element is numeric but cannot determine whether it is an integer or a floating-point number). One or more data types can be excluded without discrimination.

Most type errors are caught during the type checking stage. This is done immediately after computing the initial type environment, adjusting the scope based on what is known about each type.

This phase starts with the type environment of all terminals. For each type environment, the system reverts to the past environment. The process loops back until it reaches a closed environment or an environment with no previous environment. The process then checks the type of each environment to determine if there are fields of different types. In that case, the process raises a type error if their intersection is null. If any fields have different types and the intersection is not null, the process sets the type to the intersection and the type environment of the affected nodes is recomputed. In addition, any "assumed" types are added to the previous type environment and the type environment is recomputed.

A few details are tracked. First, the field name itself is not necessarily unique, since the user can overwrite the field with another arbitrary type. As a result, the process uses a pointer from the type to the type that was used to generate it, thereby avoiding getting confused by unrelated things that resolve to the same name in different parts of the graph. For example, suppose there is a node running a project where field A is of type [int, decimal], but A is a string. It is an error to go back to a previous version of A and say that the type doesn't work. Alternatively, backtracking at this point does not backtrack A past the addField operation.

Type checking narrows one variable at a time. In the above steps, type checking is only applied to one variable before recomputing the known variables. This is safe when there are overloaded functions with multiple signatures, such as Function1(string,int) and Function1(int,string). Suppose this is called as Function1([A], [B]). The process determines that the types are A: [String, int] and B: [String, int]. However, it is invalid for types to resolve to A:[String] and B:[String], because if A is a String, then B must be an int. Some implementations handle this type dependency by re-computing the type environment each time the type is narrowed.

Some implementations optimize the work they do by working only on nodes that actually have a required field that contains a narrowed variable. There are a few subtleties here. If A is narrowed, B may also be narrowed. See the Function1 example above. In such a case the system needs to know when B has changed and also check its narrowing.

If you want to see how the operator works, here "Is Open", "Multi-Input", "Input Type", "Resulting Type" It is best to think of them in terms of four main properties identified as .

An operation is designated as open as it passes through the queue. For example, "filter" is an open operation. This is because all columns in the input are also included in the output. Group by is not open because columns that are not aggregated or grouped are not included in the result type.

The "multi-input" property specifies whether this operation receives multiple input entities. For example, a join is multi-input because it takes two entities and makes them one. Union is another operation on multiple inputs.

The "input type" property specifies the type required for the node. For multi-input operations, this is a composite type where each input has its own type.

The "result type" property specifies the resulting output type of this operation.

The tables in Figures 8J-1, 8J-2, and 8J-3 show the properties of many of the most commonly used operators.

Flows are often created over time as needs change. When flow grows by organic evolution, it can be large and complex. A user may need to modify the flow to meet changing needs or to reorganize the flow to make it easier to understand. Such flow refactoring is difficult or impossible with many ETL tools.

This implementation not only allows refactoring, but also helps the user to do so. At one technical level, the system can take the RequireFields of any node (or sequence of nodes) and light a drop target at any point that has a type environment to support it.

In another scenario, existing nodes in the flow are reused. For example, suppose a user wishes to perform a series of operations to create a custom node. The custom node acts to do "insurance code normalization". A user can create a container node that contains multiple operations. The system can then calculate the fields it requires. A user can save a node for future use by using the save command or by dragging a container node to left pane 312 . Now when you select a node from the palette in the left pane, the system lights up the drop targets in the flow and the user can drop the node onto one of the drop targets (e.g. as in the refactoring example above). can be done.

Since ETL can be cumbersome, this implementation allows for a variety of system extensions. Extensions include:
● User-defined flow operations. Users can extend the dataflow with input, output and transformation operations. These operations can use custom logic or analytics to change the contents of the row.
● Control flow scripts. Users can build scripts that perform operations other than dataflows, such as downloading files from shares, unzipping files, and running flows for all files in a directory.
● Command line scripts. Users can run flows from the command line.

The implementation takes a language-agnostic approach to how people use the extensibility provided.

The first extension allows users to build custom nodes that fit the flow. There are two parts to creating an extension node:
● Define the output type. For example, "everything that arrives, not just the new string 'foo'".
● Provide a script or executable to actually perform the conversion.

Some implementations define two node types that allow for user-defined extensions. "ScriptNode" is a node where the user can write scripts to manipulate rows and return them. The system provides API functions. The user can then create a transform (or input or output) node as a script (such as in Python or Javascript). A "ShellNode" is a node where a user can define an executable program to run and pipe lines to the executable. The executable then writes results to stdout, errors to stderr, and exits when complete.

Internal processing becomes more complex when users create flow extensions. Instead of compiling everything into one AQL statement, the process splits the evaluation into two parts around the custom node and sends the result from the first part to the node. This is illustrated in Figures 8K and 8L, where a user-defined node 850 divides the flow into two parts. During flow evaluation, user-defined script node 852 receives data from the first part of the flow and provides output to the second part of the flow.

In addition to customizations that change the flow data in some way, users can create scripts that control how the flow runs. For example, suppose a user needs to pull data from a share that publishes a spreadsheet daily. In the defined flow, we already know how to handle CSV or Excel files. A user can create a control script that iterates through a remote share, pulls down the relevant files, and then executes those files.

There are many common operations that users can add to dataflow nodes, such as pattern union. However, as technology continues to evolve, there will always be ways to retrieve or store data that do not correspond to system-defined dataflow nodes. These are the cases where control flow scripts are applicable. These scripts are executed as part of the flow.

As mentioned above, flows can also be invoked from the command line. This allows you to embed scripts in other processes or nightly jobs.

Implementations have a flow evaluation process that provides many useful features. These features include:
● Execute the flow to the end.
● Split the flow to ensure order or "fixed" operations.
● Break up the flow so that you can run third-party code.
● Run a flow, but run it from the output of a previously run flow, instead of going back to the upstream data source.
● Pre-run parts of the flow to populate the local cache.

The evaluation process works based on the interaction between the logical model and the physical model. The embodied physical model becomes the starting point for the flow. However, the language runtime provides an abstraction for defining subsections of the flow to execute. In general, the runtime does not decide when to run subflows and fullflows. It is determined by other components.

FIG. 8M shows that execution of the entire flow begins with implicit physical models at the input and output nodes. FIG. 8N shows that the physics model is instantiated with the result when executing the partial flow. FIG. 8O shows the execution portion of the flow based on past results.

The physical model can be reordered to optimize processing, but the logical model is generally irrelevant and hides these details from the user. A flow evaluator makes it appear that the nodes are evaluated in the order they appear in the flow. If the node is fixed, then in fact the flow is implemented there and it is guaranteed that the left part will be evaluated before the right part. In fork flow, general preflow is performed only once. This process is idempotent. This means that if a failure causes the input operator to be called again, it will not fail. Note that the returned data does not have to be exactly the same as the original data (ie, if the data in the upstream data source changed between the first and second attempts).

Execution of the conversion operator has no side effects. Extraction operators, on the other hand, typically have side effects. Operations that change the datasource before the datasource in the flow do not appear until the next run of the flow. Read operators generally have no side effects, but there are exceptions. In fact, some read operators require side effects. For example, pulling down and unzipping a file from a share is considered a side effect.

Some implementations are case sensitive with respect to column names, while others are not. In some implementations, a user-configurable parameter is provided to specify whether column names are case sensitive.

In general, cached views of objects always "forward" in time.

FIGS. 8P and 8Q illustrate flow evaluation with fixed node 860. FIG. During flow evaluation, the node before the pin is executed first to produce a user node result 862, which is used later in the flow. Note that fixation does not prevent relocation of execution within each part. A fixed node is effectively a logical checkpoint.

In addition to nodes that are pinned by the user, some nodes are inherently pinned based on the operations they perform. For example, if a node calls custom code (such as a Java process), logical operations cannot be moved between nodes. Custom code is a "black box", so its inputs and outputs must be clearly defined.

In some cases, moving operations can improve performance, but has the side effect of reducing consistency. In some cases, users can use pinning as a way to ensure consistency, but at the cost of performance.

As noted above, a user can edit data values directly in the data grid 315 . In some cases, the system infers general rules based on the user's edits. For example, a user may add the string "19" to the data value "75" to create "1975". The system, based on the data and the user's edits, rules that the user wants to fill in a string to form 4 character years for 2 character years with a missing century. can be inferred. In some cases, the inference is based solely on the change itself (e.g., prepending "19"), while in other cases, the system assumes that the column data (e.g., if the column value is "74" - '99'). In some implementations, the user is asked to confirm the rule before applying the rule to other data values in the column. In some implementations, the user can also choose to apply the same rule to other columns.

Editing a data value by the user includes appending to the current data value, deleting part of a string, replacing one substring with another substring, or or any combination of these. For example, phone numbers can be specified in various formats, such as (XXX)YYY-ZZZZ. The user edits one particular data value to remove brackets and dashes and add dots to XXX. YYY. ZZZZ may be created. The system can infer rules based on a single instance of editing a data value and apply rules across columns.

As another example, rules can be inferred for numeric fields as well. For example, if the user replaces negative values with zeros, the system may infer that all negative values should be zeroed.

In some implementations, rules are inferred when two or more data values are edited in a single column of data grid 315 according to a sharing rule.

FIG. 9 illustrates how logic flow 323 is executed in different ways depending on whether the operation is specified as imperative or declarative. There are two input datasets in this flow, dataset A 902 and dataset B 904 . In this flow, these datasets are retrieved directly from the data source. According to flow, two datasets 902 and 904 are combined using a join operation 906 to produce an intermediate dataset. After the join operation, flow 323 applies filter 908 . This creates another intermediate data set with fewer rows than the first intermediate data set created by join operation 906 .

If all nodes in this flow are marked as required, then when you run the flow, the nodes will be executed exactly as you specified them. Data sets 902 and 904 are obtained from data sources, these data sets are locally combined, and then the number of rows is reduced by a filter.

If nodes in this flow are specified to have declarative execution (generally the default), the execution optimizer may reorganize the physical flow. A first scenario assumes that data sets 902 and 904 are from separate data sources and that filter 908 is applied only to fields of data set A 902 . In this case, filters can be pushed back to the query that retrieved dataset A 902, thus reducing the amount of data retrieved and processed. This is particularly useful when data set A 902 is obtained from a remote server and/or the filter removes a significant number of rows.

In a second scenario, declarative execution is again assumed, but assuming that both dataset A 902 and dataset B 902 are obtained from the same data source (e.g., each of these datasets is stored in the same database corresponding to a table in the same database on the server). In this case, the flow optimizer can push the entire execution back to the remote server and build a single SQL query with a WHERE clause that joins the two tables and applies the filter operation specified by the filter node 908. . This flexibility in execution can significantly reduce overall execution time.

Because users build and modify dataflows over time, incremental flow execution is provided in some implementations. Intermediate results for each node are saved and recomputed only when needed.

To determine if a node needs to be recomputed, some implementations use flow hashes and vector clocks. Each node in flow 323 has its own flow hash and vector clock.

A flow hash for a particular node is a hash value that identifies all operations in the flow up to the particular node. If any aspect of the flow definition is changed (eg, adding nodes, deleting nodes, or changing operations on any node), the hash will be different. Note that the flow hash only keeps track of the flow definition and does not look at the underlying data.

Vector clocks track versioning of data used by nodes. This is a vector because a particular node may use data from multiple sources. A data source includes any data source accessed by any node up to a particular node. The vector contains a monotonically increasing version value for each of the data sources. In some cases, the monotonically increasing values are timestamps from the data source. Note that the values correspond to the data source, not when the data is processed by the nodes in the flow. In some cases, a data source can provide a monotonically increasing version value (eg, the data source has edit timestamps). If the data source cannot provide such a version number, data prep application 250 computes a proxy value (e.g., when the query was sent to or retrieved from the data source). In general, it is preferable to use a version value that indicates when the data was last modified rather than a value that indicates when the data prep application last queried the data.

By using flow hashes and vector clocks, data prep application 250 limits the number of nodes that need to be recalculated.

FIG. 10 illustrates a process of establishing a high water mark for result sets obtained from multiple asynchronous queries, according to some implementations. Each group of four bars represents a time point, with increasing times in the order T ₁ , T ₂ , T ₃ and T ₄ . The four bars in each group represent the partial results of four different queries running asynchronously. Each group of dotted lines represents the rows of data retrieved from the data source for all queries. The dotted line is sometimes referred to as the high water mark. A high water mark is typically designated with a unique identifier. In some implementations, the unique identifier is the primary key value from the data source. For example, if four queries each retrieve data from the same data source in primary key order, the primary key value can be used as the high water mark. In some implementations, the unique identifier is a line number.

At the first time T ₁ , the fourth result set 1008-1 has the fewest rows among the four result sets 1002-1, 1004-1, 1006-1 and 1008-1. Thus, the high water mark 1010-1 at T ₁ is determined by the fourth result set 1008-1. At a second time _T2 , more results were received for the second result set 1004-2 and the third result set 1006-2, while the first result set 1002-2 and the fourth result set 1008-2 remains the same. Therefore, the high water mark 1010-2 remains the same. At a third time _T3 , the first result set 1002-3 received additional rows of data, but the second result set 1004-3, the third result set 1006-3, and the fourth result set 1008-3 remains the same. Therefore, the high water mark 1010-3 remains the same. At a fourth time T4, the first result set 1002-4, the second result set 1004-4, and the third result set 1006-4 remain the same, but the fourth result set 1008-4 Additional rows are retrieved. At this point, the fourth result set 1008-4 has more rows than the second result set 1004-4, so the high water mark 1010-4 is determined by the second result set 1004-4. be.

In some implementations, a recalculation of the high water mark is triggered when a new row is received in any of the queries. In some implementations, recalculation of the high water mark is triggered based on a timer (eg, once per second). In some implementations that use timers, the first test determines if the result set has changed since the last update (or test). In some implementations, the timing intervals are non-linear. For example, a first test/update after 1/2 second, a second test/update after 1 second, and a third update after 2 seconds.

FIG. 11 illustrates how the data preparation user interface is updated while data is being read from the data source, according to some implementations. Computer system 200 includes a data prep application 250 and a cache 1112 that stores partial query results. The data prep application 250 displays the user interface 100 that allows the user to interact with and modify the data received from the data sources stored in the database 240 . Database 240 may be stored on computer system 200 or may be stored remotely (eg, on a database server). Data is retrieved using multiple asynchronous queries 1120 and received as partial query results 1122 (eg, in blocks specified by data prep application 250). Typically, the initial block of each query is small so that the data can be quickly loaded into the user interface. This allows the user to immediately begin manipulating the data. The block size typically increases, eg, doubling each time a block of rows is received.

The data update module 1110 updates the user interface 100 as new rows of data arrive. There are multiple aspects of the data refresh module 1110 . First, implementations can configure the execution time of the data update module. In some implementations, the data update module is executed each time a new row of data is received for any of the queries 1120. In some implementations, the data update module is triggered by a timer (eg, once per second). In some timer-triggered implementations, an initial test is performed to determine if any of the result sets have changed since the data update module was last run. The data update module then calculates the high water mark and compares it to the past high water marks. If they are the same, no action is taken at this point.

If the high water mark has changed, the data update module 1110 updates the user interface 100 according to the new high water mark. Data is updated everywhere the data is displayed (eg, the data value histogram in the profile pane, such as histogram 1310 in FIG. 13). In some cases, as shown in FIGS. 12 and 13, the user edits the data and/or performs actions (such as scroll position or object selection) to change parameters of how the data is displayed. In these cases, data update module 1110 updates data according to data changes and view parameters to preserve what the user sees (eg, no wild jumps in user interface 100).

FIG. 12 illustrates user interaction with partially loaded data in a data preparation user interface and subsequent updates to the user interface when additional data arrives asynchronously, according to some implementations. there is As shown in FIG. 11, partial results 1122 are retrieved from database 240 and stored in cache 1112 . The user interface 100 is updated at 1200-1 for the first time with data from the cache. Once some data is displayed, the user can filter the data, exclude certain data, brush the data, remove columns, add new columns, rename columns, change column data types, Or changes 1212 can be made to the data, such as applying a transformation function to the columns. These changes are applied to the data a second time 1200-2. These changes to the data (or representation of data) are based on the cache and the current high water mark. This change is also stored as a series of stored operations 1214 (eg, as part of one or more nodes of the corresponding flow diagram). As additional data is received and the high water mark changes, the data update module 1110 uses the updated set of rows (up to the new high water mark) from the cache to retrieve the stored operations 1214. The data is applied to update 1216 the user interface 100 . Thus, a third time, 1200-3, the change is still displayed to the user, and the change is applied to the new row of data. In other words, updated data does not undo, undo, or ignore the user's action 1212 .

FIG. 13 is an example profile pane of a data preparation user interface, according to some implementations. The profile pane includes a data value histogram for each data field displayed, such as histogram 1310 for field "day of the week" (which identifies the day of the week on which each accident occurred in the accident data set). Each bar in the data value histogram is a "bin" corresponding to an individual data value or range of data values. For dimensional data fields, each bin typically has a single data value, whereas numeric fields are typically binned by a range of values.

The State data field has bins for each state, including bin 1302 for California. The user can select the California bin 1302 to filter the display state to only those accident table rows where accidents occurred in California (or exclude California rows). Once the selection is made, the data value histograms for the other data fields use brushing to show what percentage of each bar corresponds to the State=“California” row.

The user can delete columns or rename columns. For example, the user can select the "Road Fnc" column 1304 to remove it from the display. Alternatively, the user can select a different column name, such as "Road Condition". In some cases it makes sense to change the data type of the selected data field. The user can also add new columns, such as adding a new column to place 1306 . When adding a new column, the data in that column is usually expressed as a function of other columns. For example, add a new column that computes the two-letter state abbreviation for each row's State data value.

The user can also change the data values of existing columns. For example, the day of the week data values 1312 are encoded as numbers 1-7 in this data set. Many users find it convenient to convert these to the names of the days of the week (eg, replace 1 with "Monday", replace 2 with "Tuesday", etc.). A user can make these edits directly to the data in the Profile pane of the DataPrep user interface 100 .

All of these changes are stored as part of stored operation 1214 and applied to new rows of data as they are received and updated. For example, if the data value 1 in the "Day of the Week" data field is replaced with "Monday", this same transformation is applied to all new data rows received that have "1" as the day of the week.

The disclosed implementation has the following advantages.
● Display the user's incremental results when available.
• Users can explore incoming data through scrolling, selection, brushing, and so on.
● It allows users to perform flow-based actions when data arrives, even if the action affects the data as it arrives.

As data arrives, the profile and data panes are updated periodically to reflect new data.

While the data is loading, the user can manipulate the data to view and change the data. For example, users can:
● Pan the profile pane both vertically and horizontally. Doing this loads the in-view profile and displays the current state of the cache.
● Pan the data pane both vertically and horizontally. This will give you the current view of the cache.
● Perform selections in the profile pane as if the data were fully loaded. These selections represent filters. Filters should already be robust to additional data/domains. These filters are applied and used to facilitate user interaction. Note: These selections are typically based on the value selected, not position. For example, suppose the user selects bins (also called buckets) in the field "foo" that range from 1 to 5. This means that the filter for foo is in the range [1,5]. This causes brushing in the profile pane and filtering in the data pane. This filter persists as more data arrives. If the selected bin is merged into a larger bin, this bin will be partially brushed even if it contains the selected range.
● All other viewstate options (such as sorting) continue to work and do not block incremental loading.
● The view of all nodes is kept live. For example, the join summary area of a join node allows the user to select join parts during data loading.

Once the data arrives, the user can edit the data.
● You can make edits independent of the specific data in the column. For example, columns can be removed or added, columns renamed, or column types changed.
● Can be edited according to the specific data in the column. For example, you can right click/delete a bin in the profile pane. These operations have an implicit selection range, and the range is inferred on the first selection even if the selected item is merged into a larger range.
● You can configure operations such as joins and aggregations.

Chaining multiple actions in addition to reading data preserves the behavior outlined here. for example,
1. User reads table T from SQL server.
2. After the metadata is loaded, but before the loading is complete, the user deletes column c.
3. The system continues reading T and also begins calculating T-{c}. The user can see the metadata for this node displayed and perform an action (eg delete column d).
4. The system continues reading T, but decides to stop computing T-{c} and instead compute T-{c,d} from T directly. Alternatively, the system decides to continue reading T to compute T-{c} and then compute T-{c,d}.
5. The user can continue to change its state when T-{c,d} becomes available.

In addition to user actions that change data with only partial results loaded, the user can perform other actions in the user interface that are retained when more data arrives. For example, user actions to select, scroll, or change viewstate are preserved. Vertical scrolling and horizontal scrolling apply to both the profile pane and the data pane. If the user selects a particular object in either pane, that selection is retained as new data arrives. View state is maintained, such as brushing and filtering.

The terminology used in the description of the invention herein is for the purpose of describing particular implementations only and is not intended to be limiting of the invention. As used in the description of the present invention and in 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 will also be understood that the term "and/or" as used herein refers to and includes any and all possible combinations of one or more of the associated listed items. As used herein, the terms “comprise” and/or “comprising” specify the presence of the stated features, steps, acts, elements and/or components, but one or more It will further be understood that does not exclude the presence or addition of other features, steps, acts, elements, components, and/or groups thereof.

The foregoing description, for purposes of explanation, has been described with reference to specific implementations. However, the illustrative discussion above is not intended to be exhaustive or to limit the invention to the precise forms disclosed. Many modifications and variations are possible in light of the above teaching. The implementations were chosen and described in order to best explain the principles of the invention and its practical application, thereby allowing those skilled in the art to make various modifications suitable for the invention and the particular uses contemplated. implementation can be maximized.

Claims (20)

A computer system for preparing data for subsequent analysis, comprising:
one or more processors;
memory;
one or more programs stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising:
displaying a user interface including a dataflow pane, a profile pane, and a datapane, the dataflow pane displaying a node/link flow diagram identifying data sources;
For each of a plurality of queries against said data source,
issuing each of the queries asynchronously against the data source with an initial block size that specifies the number of rows;
Upon retrieving an initial set of respective rows from the data source satisfying the respective query, asynchronously repeating the respective query with an updated block size until all the rows satisfying the query are retrieved. , and storing retrieved rows satisfying each of said queries in a local cache;
regularly,
determining a unique identifier that identifies rows of the data source retrieved and stored in the local cache for all of the queries; and updating the profile pane if the unique identifier has changed to: displaying data value histograms of a plurality of data fields in a data source, wherein each bar of each data value histogram is (i) designated by the unique identifier; and (ii) for a respective data field. indicating the number of rows of the data source that have a single specific data value or range of data values;
thereby providing a consistent view of data in the profile pane while multiple independent queries are being executed asynchronously; and 2. The computer system of claim 1, wherein each iteration of each query against the data source specifies a block size that is larger than the previous block size of the respective query. 3. The computer system of claim 2, wherein each iteration of each query against the data source specifies a block size that is twice the size of the previous block size of the respective query. 2. The computer system of claim 1, wherein said periodic determination of said unique identifier is coordinated so that it occurs no more than once per second. 2. The computer system of claim 1, further comprising updating rows of data from the data source displayed in the data pane according to the unique identifier if the unique identifier has changed. 2. The computer system of claim 1, wherein a first node of said flow diagram is first selected and said data value histogram displayed in said profile pane corresponds to a calculated data set of said first node. . receiving a user selection of a second node of the flow diagram during execution of the asynchronous query;
Updating the profile pane to display new data value histograms for a plurality of data fields from the second node result set in response to the user selection, wherein each bar of each data value histogram is 7. The computer system of claim 6, further comprising: displaying the number of rows of the result set that have a single specific data value or range of data values for each data field. The unique identifier is a primary key value of a primary key field of the data source, and a row of the data source is identified by the unique identifier if the key value corresponding to the row is less than the primary key value. 2. The computer system of claim 1, specified. the unique identifier is a high-level row number, and a row of the data source is designated by the unique identifier if the row number corresponding to the row is less than or equal to the high-level row number; 2. The computer system of claim 1. 10. The computer system of claim 9, wherein each of said queries has the same sort order. while one or more of said asynchronous queries are being executed;
receiving user input to modify data displayed in the profile pane;
responsive to the user input, converting the user input into an operation to be applied to the retrieved rows from the data source and saving a definition of the operation;
2. The computer system of claim 1, wherein updating the profile pane comprises applying the defined operation to rows retrieved by the query if the unique identifier has changed. The user input is the selection of a single bar of a data value histogram corresponding to the first data value bin of the first data field, thereby converting the data displayed in the profile pane to the first data value bin of the first field. filtering to rows of the data source whose data values correspond to the first data value bin;
the saved operation causes the data displayed in the profile pane to be applied to a filter that filters to rows of the data source where the data value of the first field corresponds to the first data value bin; 12. The computer system of claim 11. the user input causes the data value histogram corresponding to the first data field to be removed from the profile pane;
12. The computer system of claim 11, wherein updating the profile pane when the unique identifier has changed comprises omitting the first data field from the data pane. the user input adds a computed column to the profile pane with a corresponding data value histogram computed as a function of one or more other columns retrieved by the query;
wherein updating the profile pane when the unique identifier changes comprises updating the data value histogram for the computed column according to the additional rows obtained from the function and the data source. Item 12. The computer system according to Item 11. the user input renames the first data column of the profile pane to a new name;
12. The computer system of claim 11, wherein updating the profile pane when the unique identifier has changed comprises retaining the new name of the original data column. the user input causes the data type of the first data column of the profile pane to be converted to a new data type according to a conversion function;
12. The method of claim 11, wherein updating the profile pane when the unique identifier changes comprises applying the transform function to the first data column of the additional rows obtained from the data source. The described computer system. the user input removes the histogram bar corresponding to the first data column bin in the profile pane;
Updating the profile pane if the unique identifier has changed removes any of the additional rows obtained if the row has a data value in the first data column that matches the bin. 12. The computer system of claim 11, comprising: 18. The computer system of claim 17, wherein the bins correspond to individual data values or continuous ranges of data values. A non-transitory computer-readable storage medium storing one or more programs configured to be executed by a computer system having one or more processors, memory, and a display, the one or more programs comprising:
displaying a user interface including a dataflow pane, a profile pane, and a datapane, the dataflow pane displaying a node/link flow diagram identifying data sources;
For each of a plurality of queries against said data source,
issuing each of the queries asynchronously against the data source with an initial block size that specifies the number of rows;
Upon retrieving an initial set of respective rows from the data source satisfying the respective query, asynchronously repeating the respective query with an updated block size until all the rows satisfying the query are retrieved. , and storing retrieved rows satisfying each of said queries in a local cache;
regularly,
determining a unique identifier that identifies rows of the data source retrieved and stored in the local cache for all of the queries; and updating the profile pane if the unique identifier has changed to: displaying data value histograms of a plurality of data fields in a data source, wherein each bar of each data value histogram is (i) designated by the unique identifier; and (ii) for a respective data field. indicating the number of rows of the data source that have a single specific data value or range of data values;
providing a consistent view of data in said profile pane while multiple independent queries are being executed asynchronously; and A method of preparing data for subsequent analysis, comprising:
In a computer system 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,
displaying a user interface including a dataflow pane, a profile pane, and a datapane, the dataflow pane displaying a node/link flow diagram identifying data sources;
For each of a plurality of queries against said data source,
issuing each of the queries asynchronously against the data source with an initial block size that specifies the number of rows;
Upon retrieving an initial set of respective rows from the data source satisfying the respective query, asynchronously repeating the respective query with an updated block size until all the rows satisfying the query are retrieved. , and storing retrieved rows satisfying each of said queries in a local cache;
regularly,
determining a unique identifier that identifies rows of the data source retrieved and stored in the local cache for all of the queries; and updating the profile pane if the unique identifier has changed to: displaying data value histograms of a plurality of data fields in a data source, wherein each bar of each data value histogram is (i) designated by the unique identifier; and (ii) for a respective data field. indicating the number of rows of the data source that have a single specific data value or range of data values;
thereby providing a consistent view of data in the profile pane while multiple independent queries are being executed asynchronously.

Images