The model configuration of a dataset allows data from different sources to be integrated, enabling auxiliary data to be presented visually and helping users gain a comprehensive understanding of the data from multiple perspectives. After completing Data Connections Overview, you can configure the model to create a dataset.
Prerequisites:
Extracted datasets support multi-table Join and multi-table Union operations as described below; direct connection datasets have the following version-based limitations:

• Before version V2.50.0, only single-table direct connection queries are supported
• From version V2.50.0 and later, except that Finder data connections only support single tables, other direct connection data sources support multi-table Union, Join, and custom SQL. Multi-table operations cannot be performed across data sources or data connections, for example, Table A from data connection A and Table B from data connection B cannot be used for union/join, nor can operations involving both Table A and Table B appear in custom SQL.

For the differences between extraction and direct connection, see: Data source integration-->Connection methods

The product supports integrating data from different sources. You can build a data model simply by dragging and dropping.
Step 1: The list on the left displays only tables you have permission to access. Drag the tables needed for the dataset into the central "model area". The system supports cross-source Union and cross-source Join.

Step 2: Click the capsule of each table to select the fields (columns) to use. Fields not selected will not be imported.

Step 3: Configure the matching relationships/association methods and association fields for merging.

• Merging (Union): Click the blue area labeled 'Merge relationship established' and configure the matching relationships for merging below.

• Association (Join): Click the connection icon in the center and set the association method and association fields in the pop-up window.

Step 4: During model construction, you can click the "Preview" button in the upper right corner to preview the data.

Caution: The preview result is randomly sampled data and is for reference only.

3.1 Usage restrictions #

• Multi-table join in extract mode: Supports data from the same data connection/data source, and also supports data from different data connections/data sources.
• Direct connection mode has the following restrictions based on the version:
  • Before version V2.50.0, only single-table direct queries are supported; multi-table join is not supported
  • From version V2.50.0 and later, except that Finder data connections only support single tables, other direct connection data sources support multi-table join from the same data connection, but do not support cross-data source or cross-data connection usage

3.2 Usage #

(1) Click the “+” at the top of the list on the left to add more types of data connections; multiple data connections can be selected in one canvas.
(2) Then drag tables from the same data connection/different data connections into the central "model area". After that, select the required fields and configure the join relationships.

3.3 Common join types #

3.3.1 Join #

JOIN is used to connect different data tables. It is commonly used for analytical data that is stored in different tables for different purposes. When analyzing data, you need to connect the desired data based on the same fields in different tables. For example, an order connects the customer, dishes, kitchen, supplier, and more through the order number.

Caution: The field types used for joining must be consistent.

3.3.2 Inner join #

The logic is to take the "intersection" of the join fields in Table1 and Table2 where the conditions match, extract the corresponding data rows from both tables within this intersection, and concatenate the corresponding columns, as shown below:

3.3.3 Left join #

This means using the rows of the left table as the starting point, searching for rows in the right table that meet the join field conditions according to the join criteria, and then combining the specified fields from that row with the left table to form a complete data row, as shown below:

3.3.4 Right join #

In essence, the logic is the same as left join, except that the right table is used as the main data row for the Cartesian product calculation of data columns; that is, A right join B = B left join A.

3.3.5 Full join #

A full outer join returns all rows from both the left and right tables. If a row does not have a matching row in the other table, the columns from the other table in the select list will contain null values. If there are matching rows between the tables, the entire result set row contains the data values from the base tables.

4.1 Usage restrictions #

• Union of multiple tables in extract mode: supports data from the same data connection/data source, and also supports data from different data connections/data sources.
• Direct connection mode has the following restrictions depending on the version:
  • Before version V2.50.0**, only single-table direct queries are supported; merging multiple tables (Union) is not supported**
  • From version V2.50.0 and later, except that Finder data connections only support single tables, other direct connection data sources support merging multiple tables from the same data connection, but do not support cross-data-source or cross-data-connection usage

4.2 Usage method #

(1) Click the “+” at the top of the list on the left to add more types of data connections; you can select multiple data connections in one canvas.
(2) Then drag tables from the same data connection/different data connections into the central "Model Area".
(3) Click the blue area labeled 'Merge relationship established' and configure the matching relationship below.

5.1 Replace table #

Tables that have already been dragged onto the canvas in the dataset support replacement, for example, replacing an existing MySQL data table with an Excel data table.

Extract mode supports cross-data-source table/data connection replacement, but direct connection mode only supports tables from the same data connection.

After clicking the above "Replace Table" button, select the target replacement table on the left, then drag the target table to the right to complete the table replacement.

When replacing a table, note that there may be differences in fields before and after replacement. You need to confirm which fields are deleted and which are added; new fields can be selected and applied, while deleted fields are only available for preview.

Because fields from the original table may be used later, using fields that have been deleted can result in errors. For example, creating new expressions or charts/dashboards based on those fields will cause errors. Therefore, it is recommended that when the following warning pop-up appears, you confirm carefully before proceeding to the next step.

5.2 Append files #

Only offline files (Excel, Csv, Access) support file appending. Users can manually add new data to the dataset by appending files.
The file append function does not affect the synchronization of data within the files prior to appending. For example, when the user operates as follows:

April 12: The user creates a dataset using file A (containing 100 rows of data) and completes the first synchronization. April 13: The user uploads file B (containing 10 rows of data) using the file append function, saves it, and synchronizes the data again.

The data in each partition in the system is as follows:

P_date=0412: 100 rows of data (from file A) P_date=0413: 110 rows of data (from file A + file B)

The user can update the appended data in the system by resynchronizing historical data.

After resynchronizing the data for April 12, P_date=0412 should contain 110 rows of data (from file A + file B).

Operation steps

• Right-click and select "Append files"

• Click or drag to upload the appended file

• After previewing the appended file data, set the matching relationship between the appended file and the original table fields, then click Finish.

• After saving the dataset, data links created using the extraction method require manual synchronization to synchronize the new data to the base table, while direct connection tables do not require manual synchronization.

Users can also append files in the data connection by following the steps above. See Append files.

5.3 Replace files #

Only offline files (Excel, Csv, Access) support file replacement.
After replacing a file, the data in the replaced file will stop synchronizing. For example, when the user operates as follows:

April 12: The user created a dataset using file A (containing 100 rows of data) and completed the initial synchronization. April 13: The user used the file replacement feature to upload file B (containing 10 rows of data), saved it, and then resynchronized the data.

The data in each partition in the system is as follows:

P_date=0412: 100 rows of data (from file A) P_date=0413: 10 rows of data (from file B)

The user can update the replaced data in the system by resynchronizing historical data.

After resynchronizing the data for 0412, P_date=0412 should contain 10 rows of data (from file B).

Operation steps

• Right-click and select [Replace File]
• Click or drag to upload the replacement file

• After previewing the data in the replacement file, set the mapping relationship between the replacement file and the original table fields, then click Finish.

The user can also perform file append operations in the data connection by following the above steps. Refer to Replace file.

6.1 Usage method #

For the extraction table, click the table in the model to display its extraction settings below. In the extraction settings, select the extraction mode as full or incremental, select to filter part of the data, and add extraction parameters if needed.

Caution: If there are multiple tables in the model, you can configure each individual table in the model.

6.2 Extraction settings #

6.2.1 Full extraction #

If the table has a small amount of data or the original data is frequently updated, you can use the full extraction method to synchronize the table data.
Each time data is synchronized, all data in the table will be synchronized to the product's storage and computing engine.

6.2.2 Incremental extraction #

• Incremental determination: Currently, only timestamp-based incremental determination is supported.
• Incremental field: Select the field for incremental determination. Currently, only time fields are supported for this purpose.
• Time format: If the incremental field is in date format, it will be displayed as yyyy-MM-dd HH:mm:ss or yyyy-MM-dd. For other formats, select the appropriate number of timestamp digits.

6.3 Other features #

6.3.1 Data filtering #

By filtering the data in this table, unnecessary data can be excluded, and these filter fields do not need to be extracted.

6.3.2 Extraction parameters #

Click "Add condition" under extraction parameters to add more parameters. For detailed settings, see Extraction Parameters.

After saving the dataset, switching between data extraction and direct connection is not supported.

During data modeling, some complex logic can be implemented by creating datasets using custom SQL models. DataWind supports custom SQL modeling and analysis, expanding the depth of scenarios that agile BI can support to meet the requirements of complex data analysis scenarios. For details, see Custom SQL.