This topic describes how to create a dataset by using ad hoc SQL queries 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 increases the depth of scenarios supported by Quick BI. This meets the requirements for complex data analysis.

Procedure

  1. Click Ad Hoc Query in the upper-right corner of the Data Sources page.
  2. On the Ad Hoc Query page, specify Data Source.
  3. Enter an SQL statement in the SQL statement input box.
    Example: 
    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}
  4. Click Run to execute the SQL statement.
  5. View the execution result.
    1. Click the Result tab to view the execution result.
    2. Click the History tab to view the SQL statement for the ad hoc query and its execution time and duration.
      • If you click Copy in the Actions column that corresponds to the SQL statement, you can copy it to the SQL statement input box.
      • If you click Create Dataset in the Actions column that corresponds to the SQL statement, you can use this statement to create a dataset.
      • Click the icon to hide the execution result.
    SQL statements for ad hoc queries support dynamic parameter passing. When you use SQL 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}
  6. Configure parameters.
    You can click Parameter Settings in the upper-right corner to add variables and change variable types. Quick BI supports five variable types: String, Number, Date - Year Month Date, Date - Year Month, and Date - Year.
    • If you click Add Variable in the Parameter Settings dialog box, you can specify variable names and types. The variable name must be added to the WHERE clause of an SQL statement in the ${Physical field name:Variable name} format.
    • If you click Extract Variable in the Parameter Settings dialog box, Quick BI obtains variable names from the SQL statement. The default variable type is STRING, which can be changed.
    • You can click Format to format SQL statements.
  7. Click Create Dataset. In the Save Custom SQL dialog box that appears, set Name and Save To for the dataset that you want to create. After the dataset is created, you can view the dataset on the Datasets page. The new dataset is marked with "NEW."

    You can right-click the dataset and select Edit SQL to modify the SQL statement for an ad hoc query.

    Note Ad hoc queries do not support DDL statements or the table aliases of MySQL 5.7 databases.

Use of SQL parameters in the query control widget

  1. Click the Query control icon on the dashboard edit page. A query control appears in the display area of the dashboard.
  2. Click the New filter icon.New filter
  3. In the Query condition setting dialog box that appears, specify the name for a query condition and select the datasets that contain the SQL parameters to associate with the query control. The filter field drop-down list contains the dimensions, measures, and SQL statements of the datasets. The SQL parameters are displayed in orange.Query condition setting
    Note If you select an SQL parameter of the STRING type as the associated field and set Display type to Drop-down list (formally enumerated filter), we recommend that you do not set Option value source to Automatic parsing. If you set Option value source to Automatic parsing, the query control does not return any query results.

    For details 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.