AnalyticDB:FineBI

Step 1: Download the sample data

Download the Summary table of commodity sales file.

Step 2: Create a table in AnalyticDB for MySQL

Design the table with the distribution key and partition key suited to market basket analysis:

• Use Document No as the distribution key. Purchase data is distributed relatively randomly by document number, and JOIN operations in basket analysis are performed on document numbers.

• Use Date as the partition key (level-2 partition). No data is imported to this column, so a date partition keeps the schema flexible.

• Set the lifecycle to 100.

Run the following statement to create the table:

Create Table `demo_sales` (
  'Date' date,
  'City' varchar,
  'Document No' varchar,
  'Store name' varchar,
  'Commodity class' varchar,
  'Commodity name' varchar,
  'Province' varchar,
  'Cost' double,
  'Gross profit' double
  'Quantity' bigint,
  'Sales' double,
) DISTRIBUTED BY HASH ('Document No')
PARTITION BY VALUE ('Date') LIFECYCLE 100
INDEX_ALL='Y' STORAGE_POLICY='COLD' COMMENT='Summary table of merchandise sales'

For more information about table schema design, see Table schema design.

Step 3: Import the sample data

Use Kettle to import the local Excel data into the AnalyticDB for MySQL demo_sales table. For instructions, see Use Kettle to import data to Data Warehouse Edition.

After the import completes, run the following query to verify the data:

SELECT COUNT(*) FROM demo_orders

The query returns 40514, confirming that all records were imported.

Step 4: Create self-service datasets in FineBI

1. In the navigation pane, choose Data Preparation > Real-time Data. Add a business package named adb.

2. In the business package, create a self-service dataset and select the AnalyticDB connection_demo_sales fields (all fields of the demo_sales table in AnalyticDB for MySQL).

Step 5: Build the dashboard

Add data to the dataset by following the examples in the FineBI documentation to generate the market basket analysis dashboard.

No real-time data option in Data Preparation

Symptom: The Real-time Data option is missing from the Data Preparation hierarchy tree.

Cause: The standard version downloaded from the FineBI official website does not support direct connections to AnalyticDB for MySQL.

Solution: Contact FineBI technical support to obtain the version that supports real-time data.

External database not configured during initial setup

Symptom: FineBI initialized without an external database, and you now need to configure one.

Solution:

1. Start the data-based decision system. Choose Management System > System Management.

2. On the General tab, click To Be Configured.

3. Enter the external database parameters.

4. Click Enable New Database.

External MySQL database configured, but data import fails

Symptom: The external database connection succeeds, but importing FineBI metadata fails.

Cause: Typically a version or encoding mismatch. FineBI supports MySQL 5.x and requires the utf8 character set.

Solution:

1. Check whether your MySQL version is 5.x. For details, see Configure external MySQL database.

2. Check whether the MySQL database uses the utf8 (UTF-8 unicode) character set. If not, create a new database with the correct encoding:

   CREATE DATABASE finedb CHARACTER SET utf8

Connection to AnalyticDB for MySQL fails during external database setup

Symptom: You configured AnalyticDB for MySQL as the external database, but the connection fails.

Cause: The external database must be a MySQL database — not an AnalyticDB for MySQL database.

Solution: Reconfigure the external database using a MySQL server, such as ApsaraDB RDS for MySQL.

Data not synchronized between FineBI and AnalyticDB for MySQL

Symptom: Changes in AnalyticDB for MySQL are not reflected in FineBI.

Solution: See Section 3.2 in the FineBI real-time data documentation for synchronization settings.

No data displayed after updating data

Symptom: After updating data, no data appears — either in a single row or an entire column.

To diagnose the issue:

• If no data appears in a single row: The calculation result for that row may be null.

• If no data appears in an entire column and a function is applied to that column: The function may be incompatible with AnalyticDB for MySQL.

Solution: If a column function is causing the issue, contact AnalyticDB for MySQL technical support.

Cannot preview or save data

Symptom: Previewing or saving data fails with an error message.

To diagnose the issue, check the error message format:

• Error message contains a string of numbers: This is a process ID from AnalyticDB for MySQL. Provide the process ID to AnalyticDB for MySQL technical support for troubleshooting.

  

• Error message contains only an error code: The error originated in FineBI. Contact FineBI technical support.

If neither pattern matches, check whether a function is applied to a column with incompatible data types. For example, AnalyticDB for MySQL returns an error when Chinese characters in a city column are converted to a numeric value for absolute value calculations.