Data flow design with static and dynamic elements

A data flow design system is presented that includes support for both static elements and dynamic elements. Thus, the data flow design system provides a design environment graphical tool to users to design data flows that leverage both the repeatability of static elements and the adaptability of dynamic elements. Static elements process data that typically do not change over time, while dynamic elements process data that do change. For instance, new data fields are added to an input data source of a data flow. The dynamic elements automatically link the new data fields to mapping fragments of the data flow. Mapping fragments process data based on configuration parameters including expression and filter rules. Users use a user interface of the design environment to view and add mapping fragments, static links, and dynamic links to the data flow.

FIELD OF DISCLOSURE

This disclosure relates generally to implementations of database and/or database management system, including data modeling, schema, conceptual layout and physical layout, and more particularly to graphical user interfaces for generating or modifying the data flow design in database, schema, or database structure with static and dynamic elements.

BACKGROUND

Enterprises collect large volumes of digital data and often want to integrate data from various sources and analyze the data. Digital data may be stored in databases, spreadsheets, text documents, online websites, or other forms of electronic records. Integrating data from these sources typically requires processing the data into a standardized format. Each enterprise may have a preferred standardized format that requires customization for their particular use. Data flow design systems provide enterprises tools to design custom data flows for integrating and analyzing their data. For example, a business uses data flows to process raw sales data and generate sales reports to drive business operation decisions.

Existing data flow design systems may not be flexible to changes in data flows over time. Input data sources often change due to various factors, e.g., an enterprise uses new data collection or database management software or the enterprise wants to analyze new types of data. Users of existing design systems need to manually update previously designed data flows to accommodate these changes, which can be time consuming, tedious, and prone to errors.

SUMMARY

Data integration in a database management system provides users valuable insight by unifying data from a variety of sources. For example, businesses use data integration to combine information across databases and determine analytics that drive business decisions. A data flow design system in the database management system includes graphical user interface tools for both creating and modifying data flows with multiple mapping fragments, and enables the data transformations underlying the mapping fragments. The data flows retrieve data from multiple online databases and unify the retrieved data by performing customized procedures created by the user.

A user designs data flows using the graphical user interface tools. A data flow processes data, for example, determining the sum of a row of numbers in a spreadsheet. The graphical user interface tool includes a display area that shows data flows and icons that the user selects to add mapping fragments to a data flow. The parts include different types of mapping fragments, or portions of the mapping, and links that connect the fragments together. The user may drag and drop a section of one fragment to another fragment to create a link between the two fragments. The user may also customize the fragments using different rules. The user executes the data flow, which processes input data to create output data, for example, to process sales order records into a master sales order database.

The data flow design system provides a design environment to users to design data flows that have mapping fragments (i.e., the portions of a mapping), static elements, and dynamic elements (i.e., static and dynamic “ports”). Mapping fragments are reusable objects including transformations that are applied to input data of the data flow. Static elements process data that typically do not change over time, while dynamic elements process data that do change. For instance, new data fields are added to a source online database of a data flow. The dynamic elements automatically link the new data fields to mapping fragments of the data flow. Additionally, mapping fragments include static ports and dynamic ports. A static port corresponds to one data field, while a dynamic port corresponds to zero or more data fields. A static link maps a static port of an upstream mapping fragment to an input port of a downstream mapping fragment. A dynamic link maps a data field group or a dynamic port of an upstream mapping fragment to an input port of a downstream mapping fragment such that all data fields of the upstream mapping fragment's data field group or dynamic port flow to the input port of the downstream mapping fragment, optionally subject to inclusion and exclusion rules.

According to one embodiment, a method begins with receiving, in a data integration development environment, a definition of a data flow modeling a data mapping. The definition includes mapping fragments and dynamic and static links between the mapping fragments, as well as configuration parameters, as follows. First, a plurality of mapping fragments are received for inclusion in the data flow, wherein each mapping fragment comprises a plurality of ports, the plurality of ports including at least one dynamic port or one static port, a dynamic port corresponding to zero or more ports of the plurality of ports, a static port corresponding to one port of the plurality of ports, each port corresponding to at least one data field. Next input is received creating at least one dynamic link between a dynamic port of an upstream mapping fragment of the plurality of mapping fragments and a dynamic port of a downstream mapping fragment of the plurality of mapping fragments, the dynamic link providing all data fields of the dynamic port to the dynamic port. Input is also received creating at least one static link between a static port of an upstream mapping fragment of the plurality of mapping fragments and a static port of a downstream mapping fragment of the plurality of mapping fragments, the static link providing a data field of the static port to the static port. One or more configuration parameters is received for applying to at least one mapping fragment of the plurality of mapping fragments, each configuration parameter including at least one configuration parameter value. Next, the configuration parameters are applied to the at least one mapping fragment by replacing each configuration parameter value of the one or more configuration parameters with a corresponding runtime value, and an executable runtime definition is compiled based at least in part on the definition of the data flow and the runtime values.

According to one embodiment, a non-transitory computer-readable memory storing a computer program executable by a processor produces a user interface with a data flow display area for displaying a data flow and a data flow icon selection area, adjacent to the data flow display area, comprising a plurality of icons for adding mapping fragments to the data flow. The user interface is configured by the computer program to display a new mapping fragment in the data flow display area, the mapping fragments including at least one data field group in response to receiving input selecting an icon of the plurality of icons in the data flow icon selection, where a dynamic port corresponds to a group of zero or more data fields, and a static port corresponds to one data field. The user interface is also configured to, in response to receiving input dragging a data field group or dynamic port of an upstream mapping fragment in the data flow display area to a downstream mapping fragment in the data flow display area, both display a visual connection between the upstream mapping fragment and the downstream mapping fragment indicative of a dynamic link and add the data fields of the data field group or the dynamic port of the upstream mapping fragment to a newly generated dynamic port in the downstream mapping fragment.

DETAILED DESCRIPTION

Particular embodiments as described herein relate to a data flow design environment using both static elements and dynamic elements.FIG. 1andFIG. 2show an overview of a data flow design system that uses mapping fragments to process input data and generate output data from online databases.FIGS. 3A-Billustrate example mapping fragments, static links, and dynamic links.FIG. 4A-Bshows mapping fragments with configuration parameters to process the input data.FIG. 5illustrates a process for designing data flows using the design environment.FIGS. 6A-GandFIG. 7show example user interfaces of the design environment.FIGS. 8, 9, and 10A-B show snippets of XML code representing mapping fragments and compiled data flows.

System Overview

FIG. 1is a diagram of a system architecture of a data flow design system100according to one embodiment. The system architecture includes a data flow design system100, a client device110, one or more databases105, and a database management system150, connected to each other over a network140. In other embodiments, different and/or additional entities can be included in the system architecture.

The data flow design system100is a system for managing data flow information and is further described below with reference toFIG. 2. Generally, the data flow design system100provides a graphical user interface for a user to design data flows, enables the underlying data flows, and receives input from the user via the graphical user interface. The graphical user interface is also referred to as the design environment115, which is displayed on the client device110of the user. Based on input received from the user, the data flow design system100stores information representing data flows designed by the user. The data flow design system100includes various components, discussed in greater detail below in conjunction withFIG. 2.

The database management system150, also referred to as DBMS, is an application that interfaces the client device110to databases105over the network140. Databases105are organized collections of data, for example, data tables, spreadsheets, reports, and other types of documents. The client device110(or the data flow design system100) may retrieve information from—or write information to—a database, e.g., databases105or an online server, using the DBMS150. In some embodiments, the data flow design system100and/or the client device110include local databases.

The client device110is a computing device that can receive input from a user of the client device110as well as transmit and receive data via the network140. For instance, the client device110may be a desktop computer, laptop computer, smart phone, tablet, or any other device including computing functionality and data communications capabilities. Client device110is configured to communicate via the network140, which may comprise any combination of local area or wide area networks, using both wired and wireless communication systems.

The network140enables communications between the client device110and the data flow design system100. In one embodiment, the network140uses standard communications technologies and/or protocols. The data exchanged over the network140can be represented using technologies and/or formats including the hypertext markup language (HTML), the extensible markup language (XML), etc. In addition, all or some of links can be encrypted using conventional encryption technologies such as secure sockets layer (SSL), transport layer security (TLS), virtual private networks (VPNs), Internet Protocol security (IPsec), etc. In another embodiment, the entities can use custom and/or dedicated data communications technologies instead of, or in addition to, the ones described above.

The design environment115is a graphical user interface for designing data flows using the data flow system100. A user interacts with the design environment115using the client device110, for example, to add, edit, or remove elements of a data flow. The design environment115displays a graphical representation of a data flow125, input data120, and output data135. The data flow125receives the input data120from a source database, e.g., one of databases105, using the DBMS150. The data flow125processes the input data120using a set of mapping fragments130, which are reusable objects that contain a set of one or more transformations. Mapping fragments apply the transformations to data that flows through the mapping fragments. Mapping fragments are further described with reference toFIGS. 3A-BandFIG. 4A. For reference, example snippets of XML code representations of mapping fragments are shown inFIGS. 8 and 9. Based on the application of the transformations, the data flow125generates the output data135. The data flow125may write the output data135to a target database, e.g., one of databases105, using the DBMS150. In an example use case, the input data120includes information about customers of a business (e.g., first names and last names of customers who have purchased a product from the business), and may have one of the databases105as a source of the data. The data flow125formats customer information for display in a report (e.g., showing product purchases in the latest month) using the mapping fragments130. The output data135is a representation of the report (e.g., a spreadsheet or text document), and may ultimately be stored in a target database, such as one of databases105.

Data Flow Design System

FIG. 2is a diagram of a system architecture200of the data flow design system100within the system environment ofFIG. 1, according to one embodiment. The data flow design system100comprises a user interface manager210, data flow module220, rules engine230, compilation module240, execution module250, data flow store270, and rules set store280. In other embodiments, the data flow design system100may include additional, fewer, and/or different modules and stores.

The user interface manager210generates the design environment115, e.g., graphical user interface600as shown inFIG. 6A-F, including content and transformation information from the data flow design system100. The content included in the design environment115includes graphical representations of elements of a data flow such as mapping fragments, which are described in more detail with reference toFIGS. 3A-Band4. Additionally, an example user interface600is further described with reference toFIG. 6A-F. The user interface manager210may generate, and toggle between, different views of the data flow. For example, a condensed view of the data flow represents mapping fragments using icons (further described with reference toFIG. 7) smaller in size relative to corresponding graphical elements in a regular (i.e., non-condensed) view.

The design environment115generated by the user interface module210may also allow users of the data flow design system100to communicate information to the data flow design system100. The user interface may include interactive elements (e.g., a graphical menu of different design controls) that allow a user to input data flow information, or input a request to the data flow design system100to perform an action. For example, a user uses the design controls to add, edit, or remove elements, e.g., mapping fragments, of a data flow. As an additional example, a user selects a “run” control to execute the data flow. Once the user interface module210has generated the design environment115, the user interface module210presents the design environment115to users, for example, in a display area of the client device110.

The data flow module220processes data flow information input by a user of the data flow design system100. In particular, the data flow module220receives the data flow information from the user interface module210and stores the data flow information in the data flow store270. For example, the data flow information describes a mapping fragment of a data flow that the user wants to design. The data flow module220generates a corresponding mapping fragment, associates the mapping fragment with the data flow, and stores the mapping fragment in the data store270.

The compilation module240compiles an executable runtime definition of a data flow. An example executable runtime definition of a data flow is further described with reference toFIG. 6G. For reference, example snippets of XML code representations of a compiled executable runtime definition of a data flow are shown inFIGS. 10A-B. The compilation module240receives the information about the data flow from the data flow store270. The compilation module240compiles the executable runtime definition based on elements of the data flow such as mapping fragments, static ports, dynamic ports, and runtime links. In some embodiments, the compilation module240runs a proprietary engine to compile the executable runtime definition. If an input data source or output data source and/or system of the data flow changes, the compilation module240does not necessarily need to recompile the executable runtime definition.

Recompiling is avoided because data sources and/or systems typically evolve over time. Thus, eliminating the need to recompile a data flow due to non-data flow related changes saves time. Additionally, the data flow is more compact, easier to read, and more maintainable, e.g., the data flow dynamically adapts to changes to data sources and/or systems. In one embodiment, the compilation module240compiles the executable runtime definition in response to receiving input from the client device110, via the user interface manager200, requesting to compile the executable runtime definition (e.g., the request corresponding to the “run” design control further described with reference toFIG. 6A). The compilation module240provides the executable runtime definition to the execution module250for execution.

The execution module250executes an executable runtime definition of a data flow. The execution module250receives the executable runtime definition from the compilation module240. In one embodiment, the execution module250executes the executable runtime definition in response to receiving input from the client device110, via the user interface manager200, requesting to execute the data flow. In an example use case, the execution module250reads input data and writes output data—e.g., input data120and output data135inFIG. 1, respectively—by executing the executable runtime definition.

The data flows described herein include static elements and/or dynamic elements. Static elements include static ports and static links, and dynamic elements include dynamic ports and dynamic links. Compared to static elements, dynamic elements differ in both function and visual appearance in the design environment115. Users of the data flow design system100interact with the design environment115to add, remove, and/or edit mapping fragments, static elements and dynamic elements to design data flows.

Mapping Fragments

FIG. 3Ashows an example of mapping fragments with static links310according to one embodiment. A mapping fragment has a name and includes data fields associated with a data field name and type. The data fields store data that flows through the mapping fragment in a data flow. For example, the mapping fragment300is named “Read_Customer_Data” and includes data fields305. In particular, the data fields305include data fields corresponding to a customer's dealership identifier (i.e., data field name “dealership_ID”), region (i.e., data field name “region”), customer identifier (i.e., data field name “customer”), first name (i.e., data field name “firstname”), last name (i.e., data field name “lastname”), and gender (i.e., data field name “gender”). The data field “dealership_ID” is a character (char) type data field. The data field “customer” is an integer type data field. The data fields “region,” “firstname,” “lastname,” and “gender” are string type data fields. In some embodiments, the data fields305include other data field groups, data field names, and/or data field types (e.g., decimal, date/time, float, Boolean, character, null, etc.). A data field group includes one or more data fields organized under a certain data field group name. A mapping fragment includes at least one data field group, i.e., a data field group including all data fields of the mapping fragment. Users may use the design environment115to create additional data field groups.

Mapping fragments include ports corresponding to one or more data fields. A static port is a traditional port that represents one data field. A dynamic port allows multiple data fields to flow into a mapping fragment over a link, which is further described below. For example, the mapping fragment300includes a static port representing each of the data fields “dealership_ID,” “region,” “customer,” “firstname,” “lastname,” and “gender.” Further, the mapping fragment300may also include a dynamic port that allows all—e.g., six—data fields of the mapping fragment300to flow through a link of the mapping fragment300.

Data flows may include different types of mapping fragments each corresponding to a different type of transformation. For example, types of mapping fragments include “read data,” “target,” “expression,” “aggregator,” “joiner,” and “filter,” among others. A “read data” type mapping fragment retrieves information from a source database, e.g., one of databases105, via the DBMS150. The retrieved information may be further processed by other mapping fragments of a data flow. A “target” type mapping fragment writes information to a target database, e.g., one of databases105, via the DBMS150. The information is based on data processed by mapping fragments of a data flow. An “expression” type mapping fragment performs an expression on data from a data field, e.g., performing the expression row by row. For example, the expression is a mathematical operation performed on numerical type data (e.g., multiplying an integer by a scaling factor or determining the absolute value of a decimal). In another example, the expression is a string operation performed on string type data (e.g., concatenating a string, trimming space characters from a string, or determining a substring). An “aggregator” type mapping fragment performs an expression on groups of data from the data fields. For example, the expression determines the sum of integer type data from multiple data fields or concatenates string type data from multiple data fields. A “joiner” type mapping fragment combines data from multiple heterogeneous (or homogeneous) input data from multiple source databases, e.g., databases105. Heterogeneous input data includes input data that have different formats or types of data fields. For example, one input data is a table of integers, while a second input data is a text document of characters and/or strings. A “filter” type mapping fragment selects data to flow through the mapping fragment based on data field values or another condition. For example, a mapping fragment only selects records where “country=USA” (i.e., the string value of “country” equals the string “USA”) to flow through the mapping fragment. Different types of mapping fragments include different types of icons to visually differentiate the mapping fragments from each other, which are further described with reference toFIG. 4A.

FIG. 8shows a snippet of XML code of a mapping fragment according to one embodiment. In particular, the mapping fragment includes instances of transformations, e.g., “<Instance transformation=“U:7PeivxI0 . . . ” Instances of transformations are analogous to function calls of a program because the instances do not necessary include instructions for applying a transformation. Rather, e.g., the instances may include references to other portions of code. The instances include multiple ports, e.g., “<TransformationFieldPort imx:id=“ID_6” . . . ” and fields, e.g., “transformationField=“U:7PeiwR . . . ,” which are used as input parameters to a transformation. In some embodiments, the input parameters are not known during design time, but are determined by runtime of a data flow.

FIG. 9also shows a snippet of XML code of a mapping fragment according to one embodiment. In particular, the mapping fragment is an expression type mapping fragment that performs a string operation on data flowing through the mapping fragment.

Static Links

In a data flow, a static link maps a static port of an upstream mapping fragment to an input port of a downstream mapping fragment. An upstream mapping fragment is a mapping fragment that precedes a downstream mapping fragment in the data flow. For example, the static links310shown inFIG. 3Ainclude six static links each corresponding to one of the data fields305of the upstream mapping fragment300. Each static link is mapped to an input port corresponding to one of the data fields325of downstream mapping fragment320named “mapping fragment1.” Thus, data from the data fields305of the upstream mapping fragment300flow through the static links310to the data fields325of the downstream mapping fragment320.

Dynamic Links

FIG. 3Bshows an example of mapping fragments with a dynamic link330according to one embodiment. Mapping fragment340includes a data field group named “Fields (6)” that allows data from all—e.g., six—of the data fields345to flow through the dynamic port and to the dynamic link330. The dynamic link330maps the “Fields (6)” data field group to an input port named “From_Read_Customer_Data” (e.g., based on the name of the upstream mapping fragment340) of downstream mapping fragment350. Due to the dynamic link330, the mapping fragment350generates generated ports organized under the “From_Read_Customer_Data” input port, i.e., a dynamic port. A generated port is used in a mapping fragment in a similar way to a static port. However, generated ports may be removed or added to a mapping fragment at runtime as result of changes to an upstream mapping fragment of a data flow. Specifically, mapping fragment350includes six generated ports corresponding to each of the data fields355, i.e., the character type data field named “dealership_ID,” the integer type data field named “customer,” as well as the string type data fields named “region,” “firstname,” “lastname,” and “gender.” Data from the data fields345flow through the dynamic link330to the data fields355. A user of the data flow design system100may toggle between viewing and hiding generated ports by selecting an expander control360of the dynamic port “From_Read_Customer_Data.” In other embodiments, dynamic links map a dynamic port of an upstream mapping fragment to a port of a downstream mapping fragment.

Static elements and dynamic elements each have different features. In particular, static elements provide more repeatability than dynamic elements, which is useful for designing data flows with mainly fixed data structures. For example, a fixed data structure is a mapping fragment that maintains the same number and types of data fields over time, or experiences a minimal amount of modifications to data fields. On the other hand, dynamic elements provide more data flow design flexibility than static elements. That is, dynamic elements such as dynamic ports and dynamic links are more adaptable to changes in a data flow.

Data Flows

FIG. 4Ais a diagram of a data flow400including mapping fragments, static elements, and dynamic elements according to one embodiment. The data flow400includes a mapping fragment402named “Read_Customer_Data,” mapping fragment412named “Expression,” mapping fragment422named “Filter,” and mapping fragment432named “Target.” Mapping fragment402is a “read data” type mapping fragment, and thus has a “read data” type icon404. Mapping fragment422is a “filter” type mapping fragment, and thus has a “filter” type icon424. Mapping fragment412is an “expression” type mapping fragment, and thus has an “expression” type icon414. Mapping fragment432is a “target” type mapping fragment, and thus has a “target” type icon434.

The dynamic link408maps a data field group of upstream mapping fragment402to the input dynamic port “From_Read_Customer_Data” of downstream mapping fragment412. The input dynamic port has a “strings-only” inclusion rule, i.e., rule438. Thus, only the data from the four string type data fields, e.g., “region,” “firstname,” “lastname,” and “gender” from the upstream mapping fragment402flow through the dynamic link408to the input dynamic port “From_Read_Customer_Data.” Thus, mapping fragment412generates a generated port416for each data field of the data field group.

Mapping fragment412also has a “fullname” output static port whose value depends on an expression that performs a string operation. The expression performs a string operation on the “firstname” and “lastname” data fields of mapping fragment412. In particular, the expression is applied by concatenating the string data of “lastname” to the end of the string data of “firstname” to generate the data value stored in the “fullname” dynamic port. For instance, “Curie” concatenated to the end of “Marie” results in the “fullname” data value, “Marie Curie.” In this example, the expression also inserts a space character between “firstname” and “lastname.”

The static link410maps a static port representing the “customer” data field of upstream mapping fragment402to the input static port representing the “customer” data field of the downstream mapping fragment422. Thus, data from the “customer” data field of data fields406flows through the static link410to the “customer” data field of data fields426. The static link460maps a static port of the “customer” data field of data fields426to an input static port of the downstream mapping fragment432. Since the mapping fragment422is a “filter” type mapping fragment, it may apply configuration parameter rules to exclude or include certain data fields based on their data field values. For example, the mapping fragment422may have a configuration parameter rule that only includes “Customer >50000,” i.e., values of “customer” that are greater than 50000.

Runtime links handle situations where an upstream mapping fragment has dynamic elements, but a corresponding downstream mapping fragment does not support dynamic elements. Specifically, runtime links include information for generating links at runtime between generated ports in the upstream mapping fragment and static ports in the downstream mapping fragment. An example of generated links due to the runtime link430is further described below with reference toFIG. 4B. The runtime link430maps the “Fields” data field group435of the upstream mapping fragment412to the “Fields” data field group462of the downstream mapping fragment432. In the design environment115user interface, a runtime link is graphically distinct from static links and dynamic links. For example, the runtime link430is represented by a thicker line than the dynamic link408, e.g., the runtime link430is seven pixels wide, while the dynamic link408is one pixel wide. In some embodiments, runtime links are represented in a different color or transparency level than static links and/or dynamic links. Runtime links provide data flow design flexibility because certain inputs or configurations of a data flow may not be known until runtime (execution of a data flow). Thus, runtime links supplement static links that, in contrast, operate using inputs and configurations that are known before runtime.

Since the data flow400includes static links410and460, a dynamic link408, and a runtime link430, the data flow400combines both static elements and dynamic elements. Thus, a user designing the data flow400using the design environment115may customize the data flow400based on different types of data sources and/or data processing procedures. The combination of static elements and dynamic elements helps unify input data from a variety of source databases, e.g., because a certain input data may only support static elements, but not dynamic elements. Further, another input data may need to be re-formatted by a mapping fragment to match the format of a different input data. The user also customizes data flows by creating configuration parameters (e.g., rules describing filters, mathematical operations, or string operations) to apply to mapping fragments. The user uses static elements for sections of the data flow400that requires more repeatability and is less likely to change. The user uses the static links410and460because the “customer” data field remains the same over time, e.g., because a customer will keep the same customer identifier represented by the data value of the “customer” data field. Additionally, the user uses the dynamic link408because the string type data fields are likely to change over time. For example, the user adds an additional string type data field for a customer's “middlename,” “city,” or “state” to the mapping fragment402. The dynamic link408adapts to the change by automatically generating a new generated port in a mapped downstream mapping fragment corresponding to the additional string type data field.

FIG. 4Bshows a compiled executable runtime definition470of the data flow400shown inFIG. 4Aaccording to one embodiment. In particular, the compilation module240generates links of the data flow400based on any dynamic links or runtime links. For example, the compilation module240replaces the dynamic link408with a set of four static links472each mapping a static port of upstream mapping fragment402to a static port of downstream mapping fragment412. Additionally, the compilation module240replaces the runtime link430with a set of five static links474each mapping a static port of upstream mapping fragment412to a static port of downstream mapping fragment432. The compilation module240removes dynamic ports and data field groups from mapping fragments of the data flow400. Note that the static links410and460are unchanged. In some embodiments, the compilation module240converts text type data fields into string type data fields, or performs other types of data field type conversions when compiling executable runtime definitions of data flows.

FIG. 10Ashows a snippet of XML code of a compiled executable runtime definition of a data flow according to one embodiment. The compiled executable runtime definition code includes, e.g., tags “<Characteristic>,” “<inputBinding>,” “<Capability>,” and “<Relational Field>,” representing characteristics, input bindings, capabilities, and relational fields, respectively. A characteristic, e.g., “<Characteristic imx:id=“ID_2” xsi:type=“optimizer:OptimizerCharacteristic”>“,” may further describe functions of a mapping fragment, for example, whether the mapping fragment is designed to run in a specific environment (e.g., a cloud database or a local engine). An input binding, e.g., <InputBinding imx:id=“ID_5” . . . ,” may indicate how a runtime value is provided to a mapping fragment.

FIG. 10Balso shows a snippet of XML code of the compiled executable runtime definition of the data flow according to one embodiment. A capability, e.g., “<Capability imx:id=“ID_61” xsi:type=“datasourceoperationl:ReadCapability” . . . ,” may indicate a signature of a data source, e.g., for a “read data” or “target” type mapping fragment. Example signatures include read, write, and look up. A relational field, e.g., “<RelationalField imx:id=“ID_64” . . . ,” may describe an element within a signature of a capability. Since relational fields are associated with a capability, mapping fragments can be compatible with a variety of data source types.

Process Flow

FIG. 5is a flowchart of a process500of designing a data flow according to one embodiment. In some embodiments, the process500is used by the data flow design system100—e.g., modules of the data flow design system100described with reference toFIG. 2—within the system environment inFIG. 1. The process500may include different or additional steps than those described in conjunction withFIG. 5in some embodiments or perform steps in different orders than the order described in conjunction withFIG. 5. The process500is described in the context of an example use case where a user of the data flow design system100is the owner of a car dealership business. The user wants to generate sales reports at the end of each month to help make projections for sales in upcoming months, study customer behaviors, and evaluate the overall performance of the business, among other uses. Thus, the user uses a data integration development environment, e.g., user interface600illustrated inFIGS. 6A-F, to design a data flow that generates sales reports.

The data flow design system100receives510, in the data integration development environment, a definition of a data flow from a client device110via the user interface manager210described inFIG. 2. The definition of the data flow includes mapping fragments, e.g., mapping fragments620,628,634, and640shown inFIG. 6D, with data fields corresponding to dynamic ports and static ports. The mapping fragment620retrieves data about the business' customers via the DBMS150from a source such as a spreadsheet, database, or any other digital record of customer data logged by the user. The data flow design system100receives520, via the user interface manager210, input creating a dynamic link between a data field group of an upstream mapping fragment and a data field group of a downstream mapping fragment. For example, inFIG. 6E, the dynamic link648maps the dynamic port “From_Read_Customers” of upstream mapping fragment628to the dynamic port “From_Expression” of downstream mapping fragment640. The data flow design system100receives530, via the user interface manager210, input creating a static link between a static port of an upstream mapping fragment and a static port of a downstream mapping fragment. For example, inFIG. 6F, one of the static links658maps a static port corresponding to the “price” data field of upstream mapping fragment634to a static port of a corresponding “price” data field of the downstream mapping fragment640. The user interface manager210provides the data flow to the client device110for display to the user in the design environment. In some embodiments, the data flow is represented by a condensed icon view, e.g., including icons shown inFIG. 7.

The data flow design system100receives540, from the client device110via the user interface manager210, configuration parameters associated with the mapping fragments. For example, referring back toFIG. 4A, configuration parameter rule438is associated with the “From_Read_Customer_Data” dynamic port of mapping fragment412. The configuration parameters include configuration parameter values, e.g., the configuration parameter rule438has a configuration parameter value corresponding to a string data type filter. The rules engine230applies550the configuration parameters to the mapping fragments by replacing the configuration parameter values with corresponding runtime values. The compilation module240compiles560an executable runtime definition of the data flow (e.g., the executable runtime definitions shown inFIG. 4BorFIG. 6G).

According to one embodiment, the execution module250executes570the executable runtime definition to generate output sales report data, e.g., stored to a target database, e.g., one of databases105, via the DBMS150. In other embodiments the execution may be initiated from an entity outside the data flow design system100. For example, the sales report organizes the customers based on demographic information, e.g., age, gender, or ethnicity. To achieve this organization, the user may use configuration parameter rules that filter data based on data values of data fields representing demographic information, e.g., the “gender” data field shown inFIG. 4A. Based on the sales report, the user may determine that most customers are in the 18-25 years old age range. Further, the user uses configuration parameters corresponding to mathematical expressions to determine total revenue earned from customers in the age range. Thus, the user may focus on advertising cars popular to 18-25 year olds in the next month. If the user stores new customer data on a new online server accessible by the data flow system100via the DBMS150, then the data flow does not need to be recompiled before executing. The execution module250resolves any runtime links, e.g., runtime link430shown inFIG. 4A, when executing the executable runtime definition.

Design Environment User Interface

An example data flow user interface600is shown inFIGS. 6A-F. A user of the data flow design system100designs data flows with mapping fragments, static elements, and dynamic elements using the user interface600.

FIG. 6Ashows the data flow design environment user interface600according to one embodiment. The user interface600is an embodiment of the design environment115shown inFIG. 1, and includes a data flow602named “My_Flow,” a display area608for displaying the data flow602, and a menu606user interface. The display area608includes a “read data” type mapping fragment620named “Read_Customers” of the data flow602.

The menu606includes different types of design control icons that are selectable by a user.FIG. 6Ashows seven design control icons in the menu606, though in other embodiments, menus may include additional, different, and/or fewer design control icons. The user interface manager210receives input, via a client device110from a user designing the data flow602, indicating selections of the design control icons. Based on the selections, the data flow design system100performs a corresponding function. For example, if the selection corresponds to the “run” design control icon604, the compilation module240compiles an executable runtime definition of the data flow602, and the execution module250executes the executable runtime definition. The user selects the “read data” design control icon404or the “target” design control icon434, to add a “read data” type mapping fragment or a “target” type mapping fragment to the data flow602, respectively. Similarly, the user selects the “expression” design control icon414, “aggregator” design control icon612, “joiner” design control icon614, or “filter” design control icon424to add an “expression” type mapping fragment, “aggregator” type mapping fragment, “joiner” type mapping fragment, or “filter” type mapping fragment to the data flow602, respectively.

FIG. 6Bshows the data flow user interface600illustrating a drag and drop feature according to one embodiment. Compared to the data flow602shown inFIG. 6A, the data flow602shown inFIG. 6Bincludes an additional “expression” type mapping fragment628named “Expression,” e.g., as result of the user selecting the “expression” design control icon414inFIG. 6A. The user interface manager210receives input indicating a “drag and drop” action from the user via the client device110. For instance, the user selects the area624corresponding to the “fields” data field group of mapping fragment620that represents a group of all data fields622of mapping fragment620, i.e., the “firstname” and “lastname” data fields. The user “drags” the selection of area624to the mapping fragment628and “drops” the selection to the area626inside the mapping fragment622. Accordingly, the user interface manager210logs the “drag and drop” action and provides input information to the data flow module220. Based on the input information, the data flow module220generates a dynamic link between upstream mapping fragment620and downstream mapping fragment628, which is further described below with reference toFIG. 6C.

FIG. 6Cshows the data flow user interface600illustrating a dynamic link630according to one embodiment. Compared to the data flow602shown inFIG. 6B, the data flow602shown inFIG. 6Bincludes the additional dynamic link630, e.g., as result of the “drag and drop” action from the user shown inFIG. 6B. The dynamic link630maps the “Fields” dynamic port of upstream mapping fragment620to the input “From_Read_Customers” dynamic port of downstream mapping fragment628. The “From_Read_Customers” dynamic port includes two generated ports632each corresponding to a data field of the “Fields” dynamic port.

The “Fields” data field group of mapping fragment628now includes a “Trimmed_Strings” dynamic port added by the user. The “Trimmed_Strings” dynamic port has a macro configuration parameter, also referred to as a macro. Generally, a dynamic port with a macro applies a procedure to one or more other dynamic ports to generate new generated ports. For example, the macro of the “Trimmed_Strings” dynamic port applies a left trim string operation (i.e., removing leading space characters on the left side of a string) on each string type data field of the “From_Read_Customers” dynamic port. Further, a generated port under “Trimmed_Strings” is generated for each string type data field of the “From_Read_Customers” dynamic port. Thus, the “Trimmed_Strings” dynamic port includes two generated ports672, “firstnameT” and “lastnameT,” which store the left trim values of the “firstname” and “lastname” data fields of “From_Read_Customers,” respectively.

FIG. 6Dalso shows the data flow user interface600according to one embodiment. Compared to the data flow602shown inFIG. 6C, the data flow shown inFIG. 6Dincludes an additional “read data” type mapping fragment634named “Read_Sales” and an additional “joiner” type mapping fragment640named “Joiner,” e.g., as result of the user selecting the “read data” design control icon404and the “joiner” design control icon614, respectively. The user interface module210receives input indicating another “drag and drop” action from the user via the client device110. For instance, the user selects the area642corresponding to the “Trimmed_Strings” dynamic port of mapping fragment628that represents the generated ports672of mapping fragment628. The user “drags” the selection of area642to the mapping fragment640and “drops” the selection to the area644inside the mapping fragment640corresponding to the “Master” data field group646. Accordingly, the data flow module220generates a dynamic link between upstream mapping fragment628and downstream mapping fragment640, which is further described below with reference toFIG. 6E.

FIG. 6Ealso shows the data flow user interface600illustrating the drag and drop feature according to one embodiment. Compared to the data flow602shown inFIG. 6D, the data flow shown inFIG. 6Eincludes an additional dynamic link648, e.g., as result of the “drag and drop” action from the user shown inFIG. 6D. The dynamic link648maps the “Trimmed_Strings” dynamic port of upstream mapping fragment628to the input dynamic port “From_Expression” under the “Master” data field group646. The “From_Expression” dynamic port includes two generated ports650each corresponding to a generated port of the “Trimmed_Strings” dynamic port.

The user interface module210receives input indicating yet another “drag and drop” action from the user via the client device110. For instance, the user selects multiple data fields652corresponding to the static ports of mapping fragment634representing the “price,” “quantity,” and “Buyer_lname” data fields636. The user “drags” the selection of the multiple data fields652to the mapping fragment640and “drops” the selection to the area654inside the mapping fragment640corresponding to the “Detail” data field group656. Accordingly, the data flow module220generates static links between upstream mapping fragment634and downstream mapping fragment640, which is further described below with reference toFIG. 6F.

FIG. 6Fshows the data flow user interface600illustrating static links according to one embodiment. Compared to the data flow602shown inFIG. 6D, the data flow shown inFIG. 6Fincludes additional static links658, e.g., as result of the “drag and drop” action from the user shown inFIG. 6E. Each of the static links658map a static port of the data fields636of the upstream mapping fragment634to a static port of the data fields660under the “Detail” data field group656of the downstream mapping fragment640. The data flow module220generates the static links658instead of a dynamic link because, e.g., the user selected multiple static ports, rather than a dynamic port or data field group, to drop in the downstream mapping fragment.

In some embodiments, the downstream mapping fragment of a “drag and drop” action does not allow the creation of new ports or does not support dynamic elements. In this case, when the user “drops” a selection of an area, the user interface600displays a dialog user interface that allows the user to design a runtime link for mapping an upstream mapping fragment to the downstream mapping fragment. The runtime link may be a combination of, for example, a configuration parameter representing a set of data fields whose values are supplied at runtime, a procedure based on data field names, or a database table lookup via the DBMS150.

In some embodiments, the mapping fragment640joins a column of a table associated with the “lastname” data field and another column of a table associated with the “buyer_lname” (i.e., buyer lastname) data field because the mapping fragment640is a “joiner” type mapping fragment. These two data fields represent similar information, for example, a buyer's last name, and thus may be used for a match. Generally, a “joiner” type mapping fragment merges multiple tables (e.g., from source databases) into one table including a union of all columns of each merged table. In particular, the “joiner” type mapping fragment matches at least one column of each merged table. The matched columns are shown in an output data field group of the “joiner” type mapping fragment, for example, the “output” data field group670(e.g., shown inFIG. 6Fin a collapsed view for purposes of clarity).

FIG. 6Gshows a compiled executable runtime definition of the data flow602shown inFIG. 6Faccording to one embodiment. The compilation module240replaces the dynamic link630with a set of two static links662each mapping a static port of upstream mapping fragment620to a static port of downstream mapping fragment628. Additionally, the compilation module240replaces the dynamic link648with a set of two static links664each mapping a static port of upstream mapping fragment628to a static port of downstream mapping fragment640. The compilation module240removes dynamic ports and corresponding data field groups, e.g., dynamic ports “From_Read_Customers” and “Trimmed_Strings” are removed from mapping fragment628. Further, the dynamic port “From_Expression” and data field groups “Output,” “Master,” and “Detail” are removed from the mapping fragment640. The “Fields” data field groups from the other mapping fragments are removed as well. Note that the static links658are unchanged.

FIG. 7shows linked icons in the user interface600according to one embodiment. The user interface600includes an icon view of the data flow602shown inFIG. 6C. The icon view displays the icons for each mapping fragment of the data flow602, but not the data fields of the mapping fragments. In particular, the mapping fragment620and the mapping fragment628are represented by the “read data” icon700and the “expression” icon710, which are linked by the dynamic link630. The icon view presents a condensed view of the data flow602to the user. For a data flow including a large number of mapping fragments and data fields, a condensed view provides a user friendly user interface600which is not cluttered by details of the data flow.

In one embodiment, the data flow design system100displays data flows in the icon view in the user interface600by default. Thus, mapping fragments added to the data flows are represented by the corresponding icons. The user interface600includes user preferences that allow a user to toggle between the icon view, i.e., as shown inFIG. 7, and the regular view, i.e., as shown inFIG. 6C.

SUMMARY