This topic was translated by AI and is currently in queue for revision by our editors. Alibaba Cloud does not guarantee the accuracy of AI-translated content. Request expedited revision

FAQ about Datasets

Updated at: 2025-01-21 01:53

This topic summarizes common issues encountered when creating datasets.

Custom SQL errors

If you encounter errors while executing or saving your custom SQL, please check for the following common issues.

1. Incorrect custom SQL syntax

Quick BI partially processes custom SQL before it is executed by the database. This preprocessing includes parsing placeholders, applying row limits with limit statements, and inserting SQL comments. Consequently, errors from the underlying database may occur if the custom SQL syntax is incorrect.

To troubleshoot custom SQL errors, try executing the SQL statement directly in the database to identify any syntax issues. You can review previously executed SQL statements in the "history".

image

2. Other statements written before the SELECT statement

To prevent performance issues due to large data volumes, Quick BI appends a 'limit 200' clause to custom SQL during execution and saving. Therefore, custom SQL must begin with a SELECT statement. HINT statements cannot be directly written before the SELECT statement, as this will result in errors.

To use HINT statements, please set them through the [Set HINT Statement] option provided.

For a list of data sources that support HINT statements, see: Data Source Function Item List.

image

3. Placeholder used without a default value

Quick BI's custom SQL allows for parameter passing via placeholders. When viewing reports, placeholders enable dynamic data analysis by passing values to the underlying SQL through query controls. For usage scenarios and methods, see the document: Placeholder.

A default value must be set for any placeholder used in the SELECT statement to avoid incomplete SQL and execution errors.

For instance, in the example below, if the placeholder $val{profit_range} lacks a default value, the "order level" cannot be calculated, leading to custom SQL errors. By assigning a default value of 100 to the placeholder, the SQL executes correctly and displays the data.

To prevent report errors due to unassigned placeholders, it is advisable to set a "global effective" default value for the placeholder after the SELECT statement, rather than a "dataset only" effective default value.

SELECT report_date, order_level, shipping_type,price,order_number,area,
   case when profit_amt< $val{profit_range} then'Loss' 
   when profit_amt> $val{profit_range} then'Profit'
   else 'Flat'
   end 'order_level'
from company_sales_record
where $expr{report_date :report_date}
and $expr{order_level :order_level}
and $expr{order_number :order_number}

image

image

4. Others

Standardizing the use of comments and aliases in custom SQL is recommended to avoid errors that may arise from special field names returned by the underlying database or placeholder parsing issues.

Also, it is unnecessary to add a semicolon ";" at the end of custom SQL.

Common causes of errors in calculated fields

  1. Calculated fields reference physical fields. To convert field types, you must use the appropriate conversion function. Manually changing the field type does not alter the underlying physical field type, which can lead to errors due to type mismatches.

  2. Dimension calculated fields cannot utilize aggregate functions such as SUM and AVG. To perform aggregation calculations, you should save them as measures.

  3. Text-type fields are limited to COUNT and COUNTD for aggregation. They are incompatible with aggregate functions like SUM and MAX. Convert these fields to numeric types prior to performing aggregation calculations.

How to convert Unix timestamp type fields to standard date-time type fields?

If a time field is stored as a Unix timestamp and the Data Type is set to Text or Numeric, you must use the from_unixtime function to convert it.

  1. Create a calculated field on the dataset editing page by following the steps illustrated in the image. image

  2. After saving the dataset, refresh it as depicted below. image

How to set null or empty values displayed on the dashboard?

1. Configure the display style for empty values within the dataset.

image

2. Configure the display style for empty values on the dashboard itself.

image

How to achieve flexible calculations of proportions using parameter SQL?

Quick BI currently lacks native support for direct proportion calculations. However, you can utilize parameterized SQL to construct a dataset for this purpose.

Consider a table with the following structure: date, province, city, sales amount. To calculate the sales amount ratio by city within its respective province, you must: 1. aggregate by the city dimension; 2. allow selection of any date range and enable multi-selection of provinces and cities.

Below is an example of how to build a dataset model using parameterized SQL:

select a.city,sum(fenzi)/sum(fenmu) as ratio
from
(select province,city,sum(order_amt) fenzi
 from  zhanbi_test
 where  $expr{date:date_para}
 and    $expr{province:province_para}
 and    $expr{city:city_para}
 group by province,city
)a
left  join
(select province,sum(order_amt) fenmu
 from  zhanbi_test
 where  $expr{date:date_para}
 and    $expr{province:province_para}
 and    $expr{city:city_para}
 group by province
)b on a.province=b.province

The provided SQL example aggregates data by the city field, but you can apply similar aggregation to other fields. When using SQL in this parameterized form, ensure to convert the date field to a date type within the dataset's parameter settings to successfully create a dataset and visualize it on the dashboard.

How to achieve cumulative calculations using parameter SQL?

Configuration options currently support various cumulative calculations, including for the current year, previous years, monthly, quarterly, and custom periods. When a dataset is set up with a fiscal year and the date field corresponds to a fiscal year, daily granularity allows for cumulative calculations over the fiscal year and fiscal quarter. For more information, see Date Cumulative.

To configure, select a day field of the date type within the dimension, as illustrated below. image

To select any time period and calculate the cumulative value from a specified start date for each period, parameterized SQL is required. The following is a reference statement for monthly cumulative calculations:

select  a.mon_date,avg(a.order_num) order_num,sum(b.order_num) add_num
from (
select date_format(report_date,'%Y/%m') mon_date,count(distinct order_id) order_num,max(date_format(report_date,'%Y/%m')) max_mon_date
from  company_sales_record_copy
where  $expr{report_date:month_date}
group by date_format(report_date,'%Y/%m')
)a
left join(
select date_format(report_date,'%Y/%m') mon_date,count(distinct order_id) order_num
from  company_sales_record_copy
where  $expr{report_date:month_date}
group by date_format(report_date,'%Y/%m')
)b on a.max_mon_date>=b.mon_date
group by a.mon_date        

With this setup, as long as the dashboard query conditions are linked to parameter fields, you can retrieve the cumulative value for each month starting from the selected month by filtering different month ranges.

How to query data for the last n days by entering a single date using parameter SQL?

In Quick BI, the default behavior is to enter a single date and display data for that specific date. To view data over a range of dates, you must enter the start and end dates of the range. However, if you need to filter data differently across two charts for instance, one chart showing data for a specific day and another chart showing data for the last three days beginning from that day you can accomplish this using parameter SQL. Below is an example SQL for reference:

select   report_date,area,product_type,count(distinct order_id) order_num
from   company_sales_record
where  area in ('Southwest','Northwest','North')
and    ( $expr{dateadd(report_date,1,'dd'):date1}
or      $expr{dateadd(report_date,2,'dd'):date1}
or      $expr{dateadd(report_date,3,'dd'):date1})
group by area,product_type,report_date

Is row-level permission supported?

Yes, it does. For more information, see Row-level Permissions.

How many rows of data does the dataset display by default?

By default, the dataset displays 100 rows of data.

Does the dataset support paging?

No, the dataset does not support paging.

Why can't the new field be displayed on the right?

Aggregate calculated fields are not displayed during data preview.

image

How to apply geographic data to map charts?

On the dataset editing page, you can transform geographic data into the corresponding geographic information by using the dimension type switch feature.

image

For more information, see Create and Manage Datasets.

How to set the use of descriptions as field names?

In your workspace settings, you can opt to use either table names or descriptions as field names when creating datasets.

Additionally, you can select multiple fields and choose 'Use Description as Field Name' in the Batch Configuration tab during dataset creation.

image.png

Note

Note: If a field's description is empty, it cannot be used as the field name.

How to quickly update the dataset when fields in the physical table change?

If a physical field is missing, it might still be utilized for analysis or displayed on the dashboard. Quick BI does not automatically remove fields. You can select the table on the canvas, examine the field modifications in the right-side panel, and promptly delete any invalid fields with a single click.

How to copy a dataset to another workspace?

You can copy a dataset to a different workspace using the cross-space dataset copy feature. For more information, see Cross-space Copy Dataset.

How to configure association models to achieve multi-table association analysis?

For more information, see Build a Model.

Configuring association models is akin to using the Join statement in SQL. You can set up association models on the dataset editing page. For more information, see Build a Model. image

What is the use of enabling query result caching for datasets?

Enabling caching for a dataset can speed up report access and minimize database queries. For instance, when caching is activated for a dataset and a user runs a query on a report derived from that dataset, the resulting data is stored in the cache. Subsequently, if other users access the same report within the cache's specified duration, Quick BI can instantly present the data without re-querying the database.

How to optimize when SQL dataset query time is too long?

1. Refine SQL logic to utilize indexes effectively, or establish views within the database to bypass complex joins and accelerate query execution.

2. Set global default values for SQL placeholders to prevent full table scans when dealing with extensive datasets.

  • On this page (0, M)
  • Custom SQL errors
  • 1. Incorrect custom SQL syntax
  • 2. Other statements written before the SELECT statement
  • 3. Placeholder used without a default value
  • 4. Others
  • Common causes of errors in calculated fields
  • How to convert Unix timestamp type fields to standard date-time type fields?
  • How to set null or empty values displayed on the dashboard?
  • How to achieve flexible calculations of proportions using parameter SQL?
  • How to achieve cumulative calculations using parameter SQL?
  • How to query data for the last n days by entering a single date using parameter SQL?
  • Is row-level permission supported?
  • How many rows of data does the dataset display by default?
  • Does the dataset support paging?
  • Why can't the new field be displayed on the right?
  • How to apply geographic data to map charts?
  • How to set the use of descriptions as field names?
  • How to quickly update the dataset when fields in the physical table change?
  • How to copy a dataset to another workspace?
  • How to configure association models to achieve multi-table association analysis?
  • What is the use of enabling query result caching for datasets?
  • How to optimize when SQL dataset query time is too long?
Feedback
phone Contact Us

Chat now with Alibaba Cloud Customer Service to assist you in finding the right products and services to meet your needs.

alicare alicarealicarealicare