Manage Datasets

Method 1: Create a dataset from the top menu bar of the console

1. Log in to DMS 5.0.

2. Move the pointer over the icon in the upper-left corner and choose All Features > Data+AI > Data Application > Data Analysis.

   Note

   If you use the DMS console in normal mode, choose Data+AI > Data Application > Data Analysis in the top navigation bar.

3. On the Data Analysis page, click the Datasets tab.

4. Click Create Dataset and configure the following parameters:

   Parameter	Required	Description
   Name	Yes	The name of the dataset. The name must be unique for each user.
   Description	No	A description of the dataset.
   Database	Yes	The database where the query is executed. You must have query permissions for the database.
   Variable	No	When a dashboard is displayed, you can use variables to filter data. Pass variable values dynamically to replace the variables referenced in the dataset's SQL.
   SQL	Yes	The SELECT SQL statement for the data query. You can enter any valid query SQL statement. Note To filter data when you display a chart, you can reference a variable in the SQL statement. The format for referencing a variable is $variable_name$.

5. At the bottom of the page, click Execute.

6. Adjust the dataset model.

   After the SQL statement is executed, a data preview is displayed. In the data preview area, you can adjust the data type and visualization type of each field.

   • Data type: String, Number, Date, Province, City, Postal Code, or Boolean.

   • Visualization type: Dimension or Measure.

   Note

   After an SQL statement is successfully executed, the Data Analysis feature of DMS suggests a dataset model. The model is inferred from the SQL data type of each field and the query results, and distinguishes between dimensions and measures. You can then verify and adjust the data types and visualization types. For example, you can change the data type of a field to a geographic type, such as Country, Province, or City. You can also use the recommended model for the next step.

7. Click Save. The dataset is created, and you are returned to the Data Analysis page.

Method 2: Quickly create a dataset from the SQL Console

1. Log in to DMS 5.0.

2. On the DMS console home page, in the instance navigation pane on the left, select the target instance and double-click the target database to open the SQL Console page.

   Note

   You can also choose SQL Window > SQL Window from the console menu bar, select a database, and then click Confirm.

3. In the SQL editor, enter an SQL statement and click Execute.

4. In the execution result area, click One-Click Visualization.

   The system creates a dataset and a dashboard collection based on the SQL query and automatically navigates to the dashboard editing page.

   Note

   The names and descriptions of the dataset and dashboard that are automatically created using the one-click visualization feature use the following formats:

   • Dashboard name: `Untitled Dashboard Portal_Timestamp`.

   • The dashboard description is empty by default. You can add a description as required.

   • Dataset name: `New Dataset_Database ID_Timestamp`.

   • Dataset description: `SQL Console One-Click Visualization Dataset`.

5. In the upper-left corner of the dashboard page, click the icon to return to the Data Analysis home page.

6. Click the Datasets tab to view the created dataset.

Purpose of variables

• Make dataset query SQL statements dynamic.

• Specify variable values in a dashboard to adjust the SQL statement of the dataset.

• Use dataset variables in a dashboard to filter data.

Define a variable

1. Log in to DMS 5.0.

2. Move the pointer over the icon in the upper-left corner and choose All Features > Data+AI > Data Application > Data Analysis.

   Note

   If you use the DMS console in normal mode, choose Data+AI > Data Application > Data Analysis in the top navigation bar.

3. On the Data Analysis page, click the Datasets tab.

4. Find the target dataset, and in the Actions column, click the icon to open the Edit Dataset page.

5. On the Edit Dataset page, click Add to add a query variable.

6. In the Edit Variable dialog box, enter the variable parameters.

   Variable	Required	Description
   Name	Yes	Enter the variable name.
   Alias	No	Enter an alias for the variable.
   Value Type	Yes	Select the value type, such as String, Number, Date, or SQL Expression, and configure the variable for that type. Note When you reference a string variable in an SQL expression, you must enclose it in single quotation marks (''). For example, name ='$var1$'.
   Use Expression	No	In addition to directly adding a default value, you can use an SQL expression to define the default value of a variable. The following example uses a Date value type: DATE(DATE_SUB(now(),INTERVAL 7 DAY));
   Default Value	No	Click Add to add a default value of the specified type.

7. Click Confirm to create the variable.

Use a variable

• If you set a default value for a variable in the Edit Variable dialog box, you can use this variable directly in a new SQL dataset. The format for using the variable is $variable_name$.

  The following code provides an example:

  SELECT * FROM `example_table` WHERE region = $region$;

• If you do not set a default value for the variable in the Edit Variable dialog box, you can use a conditional branch in the SQL field on the new SQL dataset page to filter the variable. The formats for using the variable are $if(variable)$ and $endif$.

  The following code provides an example:

  SELECT * FROM `example_table`
  WHERE 1 = 1
  $if(region)$        
  and region = $region$
  $endif$

  The if/elseif logic works as follows: If Level 1 is met, data is returned. If Level 1 is not met, the system checks whether Level 2 is met. If Level 2 is met, data is returned. If neither level is met, the if statement is not executed.

  • Level 1: The variable is associated with a global filter on the dashboard.

  • Level 2: The variable has a default value set.

• If you set default values for some variables in the Edit Variable dialog box, you can use a conditional branch in the SQL field on the new SQL dataset page to filter variables. The formats for using the variables are $if(variable)$, $elseif(variable)$, $else$, and $endif$.

  The following code provides an example:

  SELECT * FROM `example_table`
  WHERE 1 = 1
  $if(region)$
  and region = $region$   /*Condition 1: If one of the logic levels is met, the system checks if the field value in the table matches the variable's default value and returns the matching data from the table.*/
  $elseif(date)$
  and order_date > $date$  /*Condition 2: If one of the logic levels is met, the system checks if the value of the order_date field in the table matches the variable's default value. If they match, all matching data is returned.*/
  $else$  
  and order_date is not null   /*If neither Condition 1 nor Condition 2 is met, the system returns data where the order_date field is not null.*/
  $endif$

  The if/elseif logic works as follows: If Level 1 is met, data is returned. If Level 1 is not met, the system checks whether Level 2 is met. If Level 2 is met, data is returned. If neither level is met, the if statement is not executed. Level 1: The variable is associated with a global filter on the dashboard. Level 2: The variable has a default value set.