Dataset combination

Background

The main difference between a dataset combination and a dataset association lies in the order of operations:

• Dataset association: Data is joined first and then aggregated.

  If your data tables are closely related and frequently joined for analysis, use dataset association. This creates a reusable dataset for various business scenarios.

  Example: left outer join

  • Left table

    

  • Right table

    

  • Join result

    

    Note

    Example explanation: In this case, the left and right tables are joined on user ID and customer ID. A left outer join includes all rows from the left table. It returns data from the right table where a match is found and returns a hyphen (-) where no match is found. For example, the user with ID VIP004 has no corresponding customer ID in the right table, so Store ID, Order Number, and Purchase Quantity are displayed as "-".

• When your data tables have flexible relationships and require different joins for various analyses, create separate datasets. You can then use the dataset combination feature in a dashboard to configure the number of datasets and their relationships.

  Example:

  • Left table

    

  • Right table

    

  • Result of dataset combination

    

    Note

    Example explanation:

    • In this case, the left and right tables are first aggregated and then joined on user ID and customer ID. For the user with ID VIP001, the purchase amounts are 3,000 and 1,000, totaling 4,000, and the purchase quantities are 5 and 35, totaling 40.

    • If a row in the left table corresponds to multiple dimension values in the right table, the result is an asterisk (*). For example, the user with ID VIP001 corresponds to multiple Store IDs and Order Numbers in the right table, so the result displays as "*".

    • If a row in the left table has no matching data in the right table, the result is a hyphen (-). For example, the user with ID VIP004 has no corresponding data in the right table, so Store ID and Order Number display as "-".

Prerequisites

1. To use a dataset combination in a dashboard, you must first create a dashboard and a chart. For more information, see Create a dashboard and Visual chart overview.

2. To use a dataset combination on a data screen, you must first create a data screen. For more information, see Create a data screen.

3. To use a dataset combination in a spreadsheet, you must first create a spreadsheet. For more information, see Create a spreadsheet.

Entry points

• From a dashboard or data screen

  On the edit page of a dashboard or data screen, select a chart. In the Data panel, click the dataset ①, select "Switch dataset and clear field configurations" ②, click Multi-select ③, select multiple datasets ④, then click OK ⑤.

  

  Note

  You can select a maximum of 10 datasets.

  If no dataset is selected for the chart, click Select a dataset for step ①.

• From a spreadsheet

  On the spreadsheet edit page, insert a dataset table or a free-form cell. In the Data panel, click the dataset ①, click Multi-select ②, select multiple datasets ③, then click OK ④.

  

Clicking OK opens the Edit Dataset Combination dialog box.

Create a dataset combination

This example shows how to use a dataset combination in a dashboard.

• In the Edit Dataset Combination dialog box, configure the following parameters:

  

  Parameter	Description
  ① Dataset combination name	Enter a name for the dataset combination, such as "Sales Order Data".
  ② Primary dataset	Select a primary dataset.
  ③ Secondary dataset	Select one or more secondary datasets. Click the icon to add more datasets. Click the icon to set the corresponding secondary dataset as the primary dataset. Click the icon to remove the corresponding secondary dataset.
  ④ Association condition	Define the join conditions between the primary and secondary datasets. Fields with the same name are automatically associated. You can also click Add Association in the upper-right corner to manually add association conditions.

  Click OK to create the dataset combination.

  The new dataset combination automatically becomes the chart's data source. In this example, the dataset is changed to "Sales Order Data".

  

• You can click the icon next to the dataset to Preview Data or Go to Edit Dataset Combination.

  

  • Preview Data

    On the data preview page, you can preview data from All datasets or only from Used datasets.

    

  • Edit Dataset Combination

    Click Go to Edit Dataset Combination to open the Edit Dataset Combination dialog box, where you can modify the combination. For more information, see Create a dataset combination.

Dataset combination management

On the dataset combination management page, you can edit, copy, and delete dataset combinations.

Entry point

1. Click the icon next to a dataset combination as shown in the following figure.

2. The Dataset Combination Management page opens.

Actions

On the management page, you can view the combination's name, dataset details, and the number of charts using it. You can also edit, copy, or delete the combination.

• Click Edit to open the edit dialog box. For more information, see Create a dataset combination.

  

• Click Copy to create a duplicate of the dataset combination. You can rename the copy and make other changes as needed.

  

• Click Delete to remove the dataset combination.

  

  Note

  You can only delete a dataset combination if no charts are using it. To delete a combination that is in use, you must first modify the charts to use a different data source.

Build a chart

This example shows how to build a chart using a dataset combination in a dashboard.

• Select the dataset combination and drag fields to the configuration pane.

  In this example, select the Sales Order Data dataset.

  1. On the Fields tab, double-click or drag the Region field from the primary dataset (Order Data) to the Category Axis/Dimension area.

  2. Double-click or drag the Order Amount field from the primary dataset (Order Data) and the Profit Amount field from the secondary dataset (Sales Data) to the Value Axis/Measure area.

  3. Click Update to refresh the chart.

     

     Note

     When you use a dataset combination, the system sends a query to each data source, returns the results as aggregated data, and then joins and displays them. To ensure correct results, each dimension value in the primary dataset must have only one matching value in the secondary dataset. Therefore, all fields in the secondary dataset, except for the association fields, must be aggregated. Dimension values are aggregated as follows: A unique value is displayed directly, while multiple unique values are represented by an asterisk (*).

     For example, if the Province and Region correspond to only one Product Package value, the system displays that value (for example, Small Box). If they correspond to multiple Product Package values, an * is displayed.

     (Province and Region are association fields, while Product Package is a non-association field.)

     

• Click a secondary dataset to view the association status of its fields. You can manually enable or disable field associations.

  • The icon indicates that the field is currently an association field. Click it to remove the association.

    

  • The icon indicates that the field is not an association field. Click it to make it an association field.

    

    Note

    • When you add an association field to a chart, its association icon is automatically enabled.

    • You must enable at least one association icon to calculate the relationship between the secondary and primary datasets.

    • Toggling the association icon only affects the current chart and does not impact other charts.

Use cases

Limitations

General limitations

1. Dataset combinations are supported in a dashboard, on a data screen, and in a spreadsheet. They are not supported for self-service data retrieval or ad hoc analysis.

2. The following chart types do not support dataset combinations:

   • Detail table, metric decomposition tree, and metric relationship diagram in dashboards.

   • Detail data in spreadsheets.

   • Detail table on data screens.

3. The following features are not supported for charts that use a dataset combination:

   Drill-down, monitoring and alerting, dataset replacement, fluctuation analysis, auto-insights, and intelligent Q&A.

4. The following chart-specific features are not supported for charts that use a dataset combination:

   • Subtotal in a crosstab.

   • Add data comparison in a line chart.

5. A dataset combination displays a maximum of 10,000 rows of data.

Conditional limitations

1. During secondary aggregation:

   • Only sum, count, maximum, and minimum aggregation methods are supported.

   • YoY and MoM comparison and secondary metric for crosstabs are not supported.

   • Reference line is not supported.

2. The secondary metric feature is not supported for secondary dataset fields in a crosstab.

3. The grand total feature is not supported when rows and columns are mixed in a crosstab.