Create a dataset - Data Modeling| Alibaba Cloud Documentation Center

When datasets are created, data tables in data sources or user-created SQL statements for ad hoc analysis are added to Quick BI. Datasets are the basis for visualized data analysis. You can create a dataset based on a data source table or by using an SQL statement for ad hoc analysis.

Create a dataset based on a data source table

Log on to the Quick BI console.
Click the Workspace tab. In the left-side navigation pane, click Data Sources.
On the Data Sources page, click the required data source in the My Data Sources list.
You can upload local files only to MySQL, SQL Server, MaxCompute, and Oracle databases. In this example, mysql is selected.
Click the Tables or Uploaded Files tab based on your business requirements.
- If you want to create a dataset from a data table in the database, click the Tables tab.
- If you want to create a dataset from an uploaded file, click the Upload Files tab.
  You can upload local files only to MySQL, SQL Server, MaxCompute, and Oracle databases.
Find the required table and click the icon in the Actions column.
In the Create Dataset dialog box, specify the dataset name and save path, and click OK.

After the dataset is created, you are redirected to the Datasets page. The new dataset is marked as NEW.

Create a dataset by using an SQL statement for an ad hoc query

This topic describes how to create a dataset by using an SQL statement for ad hoc query to implement some complex logic for data modeling. Ad hoc queries support dynamic parameter passing to SQL statements. Modeling analysis based on dynamic parameter passing to SQL statements meets the requirements for complex data analysis.

Log on to the Quick BI console.
Click the Workspace tab. In the left-side navigation pane, click Data Sources.
On the Data Sources page, click the required data source in the My Data Sources list.
In the right section of the page, click Ad Hoc Query.
On the Ad Hoc Query page, perform the following steps:
- Use an SQL statement to retrieve a table from a specified data source.
  1. On the Ad Hoc Query page, specify a data source.
  2. Enter an SQL statement in the SQL statement input box.
    Sample SQL statement:
```
SELECT  *
from    company_sales_record
```
  3. Click Run to execute the SQL statement.
  4. View the execution result on the Result tab.
    Click Create Dataset. In the Save Custom SQL dialog box, specify the dataset name and save path, and click OK. After the dataset is created, you are redirected to the Datasets page. The new dataset is marked as "NEW".
- Execute an SQL statement to add parameters to a table in the data source.
  1. On the Ad Hoc Query page, specify a data source.
  2. Enter an SQL statement in the SQL statement input box.
    Sample SQL statement:
```
SELECT  report_date,
        order_level,
        shipping_type,
        area,
        price,
        order_number
from    company_sales_record
where   ${report_date :report_date}
and     ${order_level :order_level}
and     ${order_number :order_number}
```
  3. Click Run to execute the SQL statement.
  4. View the execution results.
    View the execution results on the Result tab.
    1. Click the Result tab.
    2. Click the History tab to view the SQL statement for the ad hoc query and its execution time and execution duration.
      
      You can click Copy in the Actions column to copy the SQL statement to the SQL statement input box.
      
      You can click Create Dataset in the Actions column to use this statement to create a dataset.
      
      You can click the icon to hide the execution result.
    SQL statements for ad hoc queries support dynamic parameter passing. When you use SQL statements for data modeling, you can append parameters to the WHERE clause of an SQL statement in the format of ${Physical field name:Parameter alias}. The parameters can be referenced by the query control widget.
    
    Note The parameters are not displayed in datasets but are displayed in the query control widget.
    Sample SQL statement:
```
SELECT  report_date,
        order_level,
        shipping_type,
        area,
        price,
        order_number
from    company_sales_record
where   ${report_date :report_date}
and     ${order_level :order_level}
and     ${order_number :order_number}
```
  5. Configure parameters.
    You can click Parameter Settings in the upper-right corner to add variables and change variable types. The following variable types are supported: String, Number, Date - Year Month Date, Date - Year Month, Date - Year and Week, Date - Year, and YYYYMMDD HH:MM:SS.
    - You can click Add Variable in the Parameter Settings dialog box, and specify variable names and types. The variable name must be added to the WHERE clause of an SQL statement in the format of ${Physical field name:Variable name}.
    - You can click Extract Variable to obtain the variable aliases in the SQL statement. The default variable type is String, which can be changed.
    - You can click Format to format SQL statements.
    Click Create Dataset. In the Save Custom SQL dialog box, specify the dataset name and save path, and click OK. After the dataset is created, you are redirected to the Datasets page. The new dataset is marked as "NEW".
  6. Use SQL parameters in the query control widget.
    1. On the Datasets page, find the required dataset and click the icon in the Actions column.
      
      Note If you are using Quick BI Enterprise Standard, select Standard or Full Screen as the dashboard type. In this example, Standard is selected.
    2. Click the icon.
    3. Click the New filter icon in the middle of the query control or in the upper-right corner of the query control.
    4. In the Set Query Conditions dialog box, specify the name of a query condition and select the datasets that contain the SQL parameters to associate with the query control. The drop-down list of the filter field contains the dimensions, measures, and SQL statements of the datasets. The SQL parameters are displayed in orange.
      
      Note If you select an SQL parameter of the STRING type as the associated field and set Display Mode to Drop-Down List (Former "By Value"), we recommend that you do not set Source to Auto Parsing. If you set Source to Auto Parsing, the query control does not return query results.
      
      For information about how to query data based on a date field, a numeric field, and a text field, see Query data based on a date field, Query data based on a numeric field, and Query data based on a text field.
To modify the SQL statement for an ad hoc query, perform the following steps:
1. On the Datasets page, find the required dataset, click the icon in the Actions column, and select Modify SQL.
  You can also right-click the dataset and select Modify SQL.
2. On the SQL statement edit page, modify and execute the SQL statement, and click Save.