How to understand this?
For editing and management permissions of resource objects, there is a dependency on the permissions of upstream resources. Therefore, it is necessary to trace back to the upstream objects to confirm whether at least view permission is granted. For example:
User A is the manager of dataset X, as well as the manager of the data connection XLink on which dataset X depends upstream. At this point, when A grants user B the "manage" or "edit" permission for dataset X, it is also necessary to grant user B at least view permission for the data connection XLink. Otherwise, when user B edits the dataset, they will not be able to access or introduce XLink for dataset adjustments.
User A is the owner of charts P and M in the dashboard, and is also the manager of dataset X, which charts P and M depend on. When user A needs to grant user B not only view access to the dashboard but also allow user B to edit charts P and M, it is also necessary to grant user B at least view permission for dataset X.
In other words, to obtain management or editing permissions for the current resource object, it is necessary to trace back and ensure at least view permission for the upstream objects that the resource object depends on.
Solution approach
Generally, if there is an insufficient permission error, following the above principle and backtracking to obtain the necessary permissions will usually resolve the issue.
Another type of permission issue is: sometimes, in the visual query interface, you may see gray capsule fields in some charts. This occurs because the chart uses fields saved by others as personal dataset fields in the dataset.

For permission issues, see Authorization Operations Manual.

How to understand this?
A dataset is essentially a business repository that stores required data from upstream business data sources. It serves both as an intermediate transformation and storage tool for synchronizing data to DataWind, and as a business data warehouse customized based on business needs.
The base table storage of the dataset is the storage and computing engine of DataWind, which connects to multiple data sources to shield the specificity of data formats and standards from different source databases, achieve a certain degree of data cleansing, and enable downstream visualization query functions to ignore SQL heterogeneity among data sources, so that all upstream chart creation and analysis can be implemented with a unified data format and function library.
The dataset decouples BI from direct dependence on business source databases, supporting both offline complex analytical queries and direct, fast, single-table ad hoc query capabilities.
It is an upstream dependency for BI charts, and also serves as the output node (data warehouse) of the visual modeling project.

2.1 Model logic: #

The most critical aspect of maintaining the dataset is model management and editing; the model determines the scope of extracted data and the data cleansing logic.

2.2 Join #

When the data volume is large, duplicate statistics may occur during queries, which often confuses business users and is not well understood.
Troubleshooting logic: For the field (dimension/metric) being evaluated in a limited number of rows, check whether it is the primary table in the model for (left/inner/full) joins, and in the joined table, according to the join relationship, if the relationship between the primary table and the joined table is 1:N, then in the generated dataset base table, each row of the primary table will be duplicated N times as a Cartesian product to connect the fields of subsequent tables.

At this point, if you directly count the primary table (such as the Name field in the figure above) in the concatenated dataset base table, you will get 10 rows, which is more than the original 7 rows of the name field in the A-Role table; or when you filter for how many roles have the role "Role 1", you will get 2 instead of 1, but in fact there is only one role, Character 1.
The solution to such problems is as follows:

• Change count(X) to uniq(X); as shown in the figure above, X can be ID, Name, or Role_ID.
• Check the model relationships, select several additional fields from the joined table, and then change the chart type to "Detail Table". This approach eliminates the interference of aggregated fields or aggregated metrics with the actual row data, making it easier to identify which fields in the primary table are being split and duplicated due to associations with other fields.

For the differences in usage between left join, right join, inner join, and full (outer) join, see Model Configuration.

2.3 Dataset synchronization failure #

Dataset synchronization frequently fails, but there are no errors reported in the model configuration.
The main causes fall into the following scenarios. Troubleshoot according to the actual situation:

• After initially creating the dataset model or modifying the model, an extreme logical model may occur where a small table is joined to a large table. This can result in a geometric increase in the size of the Cartesian product, which triggers data expansion detection, exceeds the threshold, and causes the system to abort.

A small table joined to a large table means that, based on the join field relationship, the data between the left and right tables shows a 1:N mapping, where N is greater than or equal to 50.
For example, the common case of Product Type join SKU, or Type join ProductInstance.

• After initially creating the dataset model or modifying the model, an extreme data skew may occur in the logical model. This results in an overly skewed data value distribution in the wide table generated from the base table after modeling. During data synchronization, this triggers the threshold and causes the system to abort. For example, field X in table A has 100 enumeration values from 0 to 99, but in the corresponding data, there are 100,000 rows where X equals 1 or 2, while other enumeration values have only 1 or 2 rows.
• The business table in the upstream data source has undergone structural changes. The current dataset previously ran successfully, but the dataset model was not updated accordingly, resulting in failure to run now. Check whether the fields in the original dataset have been affected by changes in the source table, such as field type, existence, field name, and so on. At this point, viewing the [Log] of the frontend task often shows field parsing errors, with log entries such as SQL xxxx error.
  4. A migration was performed at the source, resulting in changes to the database type of the data source or the connection's IP+port or JDBC; this leads to dataset synchronization failures. When checking the [log] of the frontend task, messages such as DataX...Schema... or Access Denied are often displayed, indicating errors or timeouts in obtaining the database connection.

2.4 Data source fields and dataset fields #

Data source fields and dataset fields
The name of a data source field must be the name of the source table field and cannot be modified. If duplicate field names appear, the source table name will be automatically appended, in the format: source field name [source table name]; the data source fields retained in each node of the model are approximately equivalent to the model fields stored in the CK base table.
Dataset fields are derived from data source fields. When a dataset is generated for the first time, fields are automatically generated according to the model, with the name equal to the source field name. However, users are also allowed to add or modify field names and field expressions (that is, to apply specific transformations to source fields) in [Field Configuration].
The purpose of dataset fields is to flexibly encapsulate and load the necessary fields into the chart analysis process from the business layer, enabling users to redefine the meaning of data fields or to unify and integrate a set of fields that better align with business BI requirements from the perspective of downstream business BI data warehouses or data marts.
It is precisely because of the above principle that the following situations may occur:
A. After the structure of the upstream visual modeling task changes, users may wonder why their dataset fields are not automatically updated. This is because the output of visual modeling is linked to the table structure of the downstream direct-connected dataset in Clickhouse, and does not directly modify the [dataset fields] of the downstream dataset, which are the fields encapsulated and decoupled at the business layer.
B. After the dataset model is updated but before data is resynchronized, BI queries based on the original dataset can still retrieve consistent data, rather than immediately encountering data unavailability due to the model update.
Recommendation:

• In BI visual query analysis, use dataset fields whenever possible, and name dataset fields to better reflect their business meaning.
• When adding new custom fields, reference existing dataset fields in expressions to ensure logical consistency and facilitate modification and maintenance. This way, if the dataset fields at the root node change due to changes in the source table's data source fields, you only need to modify the data source fields in the first layer of references.

2.5 Special data format handling #

Fields with special formats require format conversion; otherwise, they cannot be displayed correctly. Common examples include:

• Date-time values recorded as long integers in Unix16/32 format appear as date-time locally, but after synchronization to DataWind, these fields display as long integers. Use date functions such as toDate, toDateTime, or FromUnix in the field editor to handle these cases.
• Time-type fields in Feishu spreadsheets require special format handling in DataWind:
  toDate((lark_time * 86400000 - 2209161600000)/1000
• After the data type of a dataset field is directly recognized from the data source, there may be some discrepancies. Manual confirmation and adjustment are required in the dataset's field configuration; otherwise, it may not be possible to use the correct aggregation expressions or perform statistical analysis during chart analysis.

• Hive struct-type fields cannot be directly retrieved and stored as CK fields.

When directly importing tables with struct-type fields, the common issue is that the field type is recognized as unsupported and cannot be stored as a CK field to generate a dataset. The solution is as follows: instead, use Custom SQL and use the select statement to import individual struct elements as separate fields.
As shown in the example below, this approach allows the elements within the struct in the source table that need to be referenced to be converted into independent fields and stored in the CK dataset.

• Compared to the dataset model used by DataWind, there are differences in the functions supported by various source databases. It is important to check and confirm in the standard CK function set whenever possible; otherwise, errors may occur. For example:
  • For counting unique values, use Uniq() instead of Count Distinct.
  • For date functions such as now or today, it is recommended to use now() as the preferred option. Although ClickHouse syntax supports the today() function, some source databases may only support now() and not today(), so it is best to use now().
  • The dateDiff() function calculates the date difference and rounds the result up, which differs from MySQL's timestampDiff, which rounds down.

How should this be understood?
Visual modeling can be understood as a dataset with more advanced and complex processing capabilities. It provides multidimensional and more flexible data preprocessing, cleaning, or aggregation capabilities that cannot be achieved within a dataset. Visual modeling is essentially a data preprocessing project. Once the project is completed, it becomes a data production pipeline. After the modeling task completes data synchronization, the raw data is processed through each stage of the project into the final desired dataset, which is a wide table that better meets BI business needs. The output dataset supports both CK and Hive types.
In simpler terms, the output of visual modeling can be considered a lightweight data warehouse. This data can be used directly for BI or can serve as input data for downstream modeling projects.

3.1 Unit testing method #

The essence of the unit testing method is to break down large-scale modeling into unit tests, with each new processing node added only after the previous stage has been verified as correct. Try to avoid building all nodes at once and then verifying data synchronization, as this can waste too much time and make troubleshooting difficult. There are two modes for the unit testing method:

• After executing the current node, use preview exploration to analyze sample data at each stage of the modeling project, determine whether the data preprocessing meets expectations, and check for any issues at that stage.
  
• Based on each node at the [connection_xx] location, after validation, directly link to the output node to build the output dataset and quickly complete synchronization. Use visual queries on the dataset to validate the data modeling logic. Continue this process by gradually adding subsequent node steps, and add the next node only after testing each step, until the entire modeling logic is complete. (Trick: To avoid long synchronization times when testing single-step modeling outputs, add appropriate filtering conditions—such as region, business date, or category—at the relevant source input nodes, restricting the extracted sample data to only certain enumerated values under these conditions. This ensures that repeated unit tests use small data volumes (within 1,000 records), allowing for quick synchronization of the required datasets.)

3.2 How to avoid data skew or data expansion #

Generally, the more connection nodes there are in a modeling project, the greater the coefficient of data skew or data expansion. This is because the detection mechanism calculates the ratio of data between the source node and the final node. If the expansion coefficient exceeds a certain threshold, the system will forcibly terminate the process.
Two optimization strategies:

• Refer to the data processing method for "Logical Issue 2" in the "Dataset Management" section, and try to avoid data tables or data connection logic that are highly specific.
• Break down complex projects into multiple basic, independent modeling projects. First, output the intermediate datasets separately, then use a new modeling project to take the intermediate datasets as input nodes and merge them into the final dataset output.

3.3 Upstream dependency issues #

Visual modeling allows an existing dataset to be used as an input node, thereby establishing a business dependency between two data synchronization tasks. In general, check whether the visual modeling contains the aforementioned dependency if, during execution, the dataset remains in a waiting state and, upon reaching the synchronization start time, its status is found to be 'waiting for upstream readiness'.
If such a dependency exists and causes the task not to execute, the most common scenario is that, within the business date range configured for the upstream task on which the current business is dependent, the upstream task has not completed data synchronization. For example, if the current task depends on the previous day of the upstream task, open the upstream task to check whether the partition data for the previous day has been synchronized.
To ensure that the current modeling data depends on upstream data in a T+0 (that is, same-day) mode, a feasible approach is to set both the upstream dataset or model and the current model as hourly tasks. Otherwise, for daily-level tasks, the minimum dependency is on T-1 (that is, the previous day) data.

3.4 Custom SQL execution errors or slowness #

In some scenarios, using custom SQL with complex aggregation logic at the input node can lead to data errors or slow synchronization.
This is because input nodes typically perform local data queries on the source database tables specified via 'data connection' during data synchronization. For example, a MySQL input node actually executes MySQL statements, translated by the engine, on the remote source database, returns the data results to DataWind, and then passes them to downstream nodes for processing until the output dataset is generated.
This process is constrained by both the limitations of remote communication bandwidth and the execution efficiency of the source business database. The more complex the processing placed in the synchronization interaction process, the slower the processing speed. (Imagine this: Is it faster to deliver two pounds of raw beef immediately after payment, or to deliver four plates of braised beef after payment?)
Once this point is understood, you can:

• When using custom SQL for input nodes, keep it as simple and efficient as possible. Only query specific fields, and do not perform aggregation or data cleansing operations, to ensure that the SQL executed at the source is as efficient and straightforward as possible.
• Perform complex aggregation or data cleansing operations in the second layer of modeling or in non-input node layers.

3.5 Presto data source modeling retrieval or execution failure #

Data is stored in the enterprise data warehouse, and Presto, as a query engine, is integrated. When connecting to DataWind, a Presto-type data connection is configured. Sometimes users encounter the following issues:

DataX schema file does not exist or format is incorrect: Failed to retrieve DataX sample: DataX schema file does not exist or format is incorrect: Failed to request DataX service: {"code": 20003, "msg": "[ERR-SQL precompilation exception] Access Denied: Cannot select from columns [banfn] in table or view eban_1d_a","advice":null,"data":null}

Generally, the user configured in the current data connection does not have sufficient permissions, and needs to be replaced with a user who has permission to execute SQL. Simply modify the user and password for the data connection.

How should this be understood?
Aggregate and calculate all statistical values that satisfy the metric formulas under different combinations of dimension conditions, and output the results.

4.1 Dimensions and metrics #

The fields that affect statistical classification or calculation classification are dimension fields, which correspond to the commonly discussed concept of "what is the statistical dimension"; metrics are scalars, and are always calculated based on the range of enumerated values that match the results of dimension or dimension combinations, such as sum, count, and so on.
For example: COLOR = red | yellow | blue, SIZE = large | small. If the dimension combination is COLOR + SIZE, and the metric to be calculated is the total number of CarName, that is, sum(CarName), then the sum(CarName) will be provided for the following six dimension result distributions:

Therefore, when checking whether data statistics are accurate or whether there is duplicate counting, the following points are usually considered:

• Data sampling: Use key classification fields in business data, select one or two enumerated values, and use them to sample and validate the data.
• Fields observed under different conditions must be set as metrics. The system uses different aggregation methods for metrics and dimensions; numerical calculations apply only to metrics.
• Dimensions have a direct impact on the data distribution of metrics. Metrics are grouped and calculated according to the enumeration values of dimensions, similar to groupby; metrics do not directly affect the distribution of other metrics.
• When there are too many dimensions, data errors or unexpected results may occur. Generally, there are two troubleshooting methods:
  • Using method a (if 'method a' is defined elsewhere, refer to that definition), switch the chart to a detail table to check whether metric fields from the primary table are being double-counted after joining with the secondary table. If so, use the key field to remove duplicates.
  • Follow the principle of adding dimensions in order of hierarchy or importance, and analyze the results of metrics step by step by incrementally adding dimensions. This helps identify at which step the data changes unexpectedly.

3.2 What to do when a function is applied incorrectly #

When a metric field is aggregated using a certain function formula, and the function is used correctly without errors, but the query result does not meet expectations, the issue must be an error in the mathematical logic design, not a function or feature error.
The fastest way to verify is to write the same processing logic using equivalent formulas in Excel and validate the results in Excel. In most cases, if the logical expression in Excel is equivalent to the CK function logic, the result in Excel will be the same as the metric result shown in the visualization chart. The essence of this approach is to have the user return to the data analysis logic to first verify or rule out misunderstandings of the data logic or errors in formula usage.
Similarly, a solution can be found in reverse: first, find a formula in Excel that produces the correct result, then translate this logic into an equivalent CK function in DataWind and apply it to the corresponding field.

3.3 Null value handling #

When handling special null values, sometimes, due to the mechanisms of the source database, certain fields allow null values. Although these fields appear empty when queried in the local database, after synchronization to DataWind, these null values may sometimes be displayed as "null" and sometimes as empty values when queried. This is because of differences in field formats and various data ingestion mechanisms.
Simple detection and preprocessing methods: Use isNull(X) or empty(Y) to test and determine the value. isNull is mainly used to check for NULL values, returning 1 as the result, and is often used for counting the number of null rows. The empty function is mainly used to detect empty string values, returning 1 for empty strings and 0 for non-empty strings. It is often used with if statements to perform escape processing for empty values.

3.4 Pivot table #

A pivot table is mainly used for hierarchical or categorical statistics of data with limited dimensions, allowing you to see the proportion or quantity of big data metrics classified under different levels. It is not intended for presenting detailed data for combinations of row and column dimensions.
Many users, upon seeing that a pivot table can display a matrix layout with both row and column dimensions, may find it more visually appealing or structured, and thus use a pivot table to display charts with many dimensions or metric fields similar to a detail table. However, this actually results in very slow data queries. This is because the underlying logic of a pivot table involves aggregation and pivot calculations similar to Order by and Group By. Therefore, when a pivot table is misused to display large volumes of detailed data, the calculations may freeze or time out.
Similar to the logic of a pivot table in Excel, only statistical or distribution calculations with fewer than four dimensions are suitable for pivot tables. For example, to calculate the product sales values for different cities under each province and for different brand dimensions, a pivot table can be used.

3.5 Detail table #

A detail table reflects each specific row of data in the actual wide base table stored in CK, eliminating the merging of data rows caused by statistical formulas or metric aggregation. Therefore, in most scenarios, this allows us to review the original data structure and characteristics of the synchronized dataset, helping us identify any subjective misunderstandings or omissions.
The main purpose is to facilitate tracing back and analyzing the 'Cartesian product' effect caused by [Join], which leads to the splitting of rows in the original table.
For example: COLOR = red|yellow|blue, SIZE = large|small. If the dimension combination is COLOR+SIZE, and the metric is the total count of CarName, that is, sum(CarName), then the results will be distributed across the following six dimension combinations, providing sum(CarName) under each distribution, as follows:

At this point, if you simply count the total number of colors based on column A, you will get a count of 6, whereas in reality, using a unique count to get 3 would be correct. Alternatively, sometimes the fields split by the Cartesian product include certain numeric metric fields, such as date, money, or score. In such cases, you can use the detail table plus multi-column field analysis method to identify which fields caused the table rows to be split (duplicated). Then, during metric aggregation, you can choose to use the average, maximum, or minimum to avoid the interference of duplicate values.

3.6 Too many null values in data results #

The issue of many rows with null values in data fields generally arises as follows:

• When using outer joins, many rows with null fields are often generated.
• If the source table is a spreadsheet, there may be invisible empty rows, and this issue is amplified after multiple table aggregations.

3.7 Filtering and summing logic #

Why do some aggregate metrics or summary statistics change when the filter conditions are modified? Or why do some [analysis] results change and not meet expectations? For example: single product share, sales share
Here is an example:
This is because, at this point, the logic is not to first perform the calculation based on the dimension and measure expression logic and then filter the results. Instead, the data rows in the dataset that meet the filter criteria are filtered first according to the specified fields, and then the calculation is performed according to the dimension/measure logic expressed on the page. Therefore, the calculated values for this type of analysis will change.
Similarly, datasets that use row-level permissions follow the same logic, so special attention is needed regarding how aggregation or analysis fields are handled.
One approach is to use Level of Detail (LOD) calculations to add a new measure field that stores the target calculation result. By using table-based calculation functions such as Fixed or Exclude, you can generate fixed values, ensuring that this measure field is not affected by filters at the specified field level during queries.

This section mainly involves graphical design or layout work, which is more about repeated aesthetic adjustments or the accumulation of design experience.
Three aspects need to be specifically clarified:

4.1 Understanding and applying global filters #

The purpose of global filtering is to facilitate the need for multiple charts to be filtered uniformly based on a certain field, using a single filter to achieve global filtering. Common issues here include:
Different charts may have fields with the same meaning or name, but their underlying dataset models are not the same. In this case, global filtering may produce inconsistent results for charts from different datasets. Therefore, it is recommended to group charts from the same dataset together and apply the same global filter.
For combinations of charts from different datasets, divide them into sections and use different filters for control.

4.2 Application and configuration logic of chart interactions #

In dashboards, a particularly important and unique feature is chart linkage. Common issues include:
a. The data linked between the upper and lower charts may appear visually unrelated, especially when the two charts are sourced from different datasets. In such cases, it is necessary to carefully analyze the fields used for association between the two charts and check for any data issues (such as consistent formats or whether the data ranges can be matched).
b. It is necessary to analyze whether the data filtering logic between the linked charts is reasonable (this logic is equivalent to formula logic in Excel), such as whether fields need to be combined to obtain correct results, and whether the required fields are included in the current chart. For the values of the primary chart fields, determine whether they are used as index keys or business keys in the secondary chart.
c. For the same dataset (especially complex wide-table datasets), it is necessary to generate charts of different dimensions based on this, and there should be a causal relationship between the charts, which is suitable for expression using chart linkage. For charts sourced from different datasets, since their underlying data models and data content are often inconsistent, chart linkage is generally not meaningful and is not recommended except in special cases.
d. In most scenarios, for drill-down analysis within the same dataset, chart linkage can be used to achieve this. Moreover, in terms of data representation and scenarios involving repeated analysis, chart linkage provides a clearer and more explicit expression than drill-down.

4.3 Use of dynamic parameters #

Parameters are generally defined at the project level and are divided into public parameters and personal parameters. Common issues include:
When setting parameters, it is necessary to configure the dataset to which they are applied. The principle is that they take effect on the chart results of the applied dataset.
Therefore, when referencing parameters in a dashboard and finding issues with certain charts, first check whether the dataset associated with the chart has applied the parameter.

For setup methods, refer to Best Practices for Building Dashboards.