Product Introduction

Product Overview

Strengths

Product Features

Use Cases

Purchase Guide

Billing Instructions

Purchase Guide

Upgrade Instructions

Renewal Instructions

Quick Start

Data Access

Creating a VPC Data Source

Creating Cloud Data Sources

Creating Proprietary Data Sources

Data Table Creation and Data Processing

Introduction to Data Analysis Basics

Data Table Creation

Data Processing

Data Analysis

Analysis Settings

Interactive Analysis

Ad-Hoc Analysis

Data Visualization

Public Style Setting Instructions

Filter Components

Chart components

Rich Text Component

Other Components

Mobile Terminal Layout Editor

Theme Settings Introduction

Page Layout Introduction

Dashboard Directory Management

Collaboration and Application

Push Channel Management

Report Push

Sharing

Embedding

Self-Service Data Retrieval

Metric Alarms

Data Security

Parameter Construction

Platform Management

Account and Permission Management

Statistics and Analysis

System Settings

Resource Migration

Practical Tutorial

Collection of Optimization Practices

Performing Trend Analysis

Performing Proportion Analysis

Performing Data Analysis with Excel Files

Creating Tables

API Documentation

History

Introduction

API Category

Making API Requests

Project Management APIs

Role APIs

Database Table APIs

Report Embedding APIs

Page Management APIs

Data Types

Error Codes

FAQs

Relevant Agreements

Service Level Agreement

Data Privacy And Security Agreement

Speeding up Dropdown Filter Loading

PDF

Focus Mode

Font Size

Last updated: 2025-09-19 15:30:18

When the dropdown filter list is sourced from a data table, the results need to be queried and aggregated from the table before being loaded into the filter. This process can lead to delays in loading or even cause failures, as shown in the figure below.
﻿
﻿
The following conditions may cause slow filter loading.
Excessive query data: For example, if the base table contains 1 million order records and you need to filter by province, the system will first need to query all 1 million records and then aggregate the records to extract information for over 30 provinces.
Excessive result data: For example, if the base table contains 1 million order records and you need to filter by salesperson, each database query might return 50,000 salesperson records, resulting in a long data transfer time to the BI service.
﻿
Therefore, the optimization approaches are based on the following aspects:
Reduce service queries: Avoid requesting the list unless necessary, suitable for fields with relatively fixed values, for example, selecting fields including month, province, and city.
Reduce database queries: Avoid querying the database unless necessary, suitable for fields with low real-time requirements. For example, selecting a department list does not require a fresh query each time.
Reduce data volume queries: Avoid querying detailed data unless necessary, suitable for fields with a large number of values but low real-time requirements. For example, selecting a vendor list does not require querying millions of data each time.
﻿
Select one or more approaches listed above to optimize performance according to the actual requirements.
﻿
Reducing Service Queries: Manually Entering Static Values
Applicable scenarios: The list contains a relatively small number of fixed values, for example, province selection.
Advantages and disadvantages:
Advantages: No need for queries. Data is loaded immediately when the chart loading is complete.
Disadvantages: Not dynamic. For example, new values require manual maintenance.
﻿
Operation Guide:
1. Go to the filter editor.
﻿
2. Set the data source to "Custom".
﻿
3. Set the dropdown option values.
﻿
4. Save to apply.
﻿
Reducing Database Queries: Using Filter Cache
Applicable scenarios: The list values do not require real-time data and can be updated periodically, for example, vendor selection.
Advantages and disadvantages:
Advantages: Faster than querying directly for the same query conditions.
Disadvantages: Initial loading may still be slow. Changes in query conditions may require cache rebuilding, which can also be slow.
﻿
Operation Guide:
1. Go to the filter editor, and select "Cache and Refresh" in the menubar.
﻿
2. Enable caching and set the cache refresh frequency to daily at 08:00 (no need to trigger queries before 8 AM the next day).
﻿
3. Save to apply.
﻿
Reducing Data Volume Queries: Maintaining Dimension Tables Regularly
Applicable scenarios: List values do not require real-time data and can be updated periodically, for example, product selection.
Advantages and disadvantages:
Advantages: Reduce the database load and decrease the query time for detailed data.
Disadvantages: Require data engineers to operate.
﻿
Operation Guide:
1. Create an entity table in the database. The following example code generates a MySQL table:
-- Create a product dimension table.
CREATE TABLE dim_list (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL COMMENT 'Product Name',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation Time',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
    UNIQUE KEY (product_name) -- Ensure product name uniqueness.
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product Dimension Table';
﻿
-- Initialize the data.
INSERT INTO dim_list (product_name)
SELECT DISTINCT Product Name 
FROM order_tab
WHERE Product Name IS NOT NULL
ON DUPLICATE KEY UPDATE product_name = VALUES(product_name);
* The above code retrieves the "Product Name" field from the order details table order_tab and generates a dimension table dim_list based on the product names.
﻿
2. Create a scheduled task to update the data regularly:
-- Create a stored procedure.
DELIMITER //
CREATE PROCEDURE update_dim_list()
BEGIN
    INSERT INTO dim_list (product_name)
    SELECT DISTINCT Product Name 
    FROM order_tab
    WHERE Product Name IS NOT NULL
    AND Product Name NOT IN (SELECT product_name FROM dim_list)
    ON DUPLICATE KEY UPDATE product_name = VALUES(product_name);
END //
DELIMITER ;
﻿
-- Set a scheduled task (execute at 8:00 daily).
CREATE EVENT IF NOT EXISTS daily_dim_list_update
ON SCHEDULE EVERY 1 DAY STARTS '2025-05-17 08:00:00'
DO CALL update_dim_list();
3. In BI, create a data source and data table that are connected to the previously created dim_list table (illustrated below using a data table as an example).
﻿
﻿
4. Associate the filter with the product name field in the data table.
﻿
﻿