Skip to main content

Batch Modify the Database Type of Database Datasets

Scenarios

1. The same data is stored in two different types of databases. After creating and using an extract dataset in BI for some time, you find that the data update speed is not ideal and want to switch to another database.

2. During enterprise database migration, all data must be fully migrated from one database to another database of a different type, and the original datasets need to switch databases as a whole.

Implementation

Batch switch databases for datasets by modifying the data account. This avoids creating new datasets and modifying associated ETL jobs and cards one by one.

Prerequisites

The storage paths and data structures of the data tables in the two databases should be exactly the same. Otherwise, after switching, modifying SQL query statements requires significant work and is error-prone.

Steps

  1. Open Data Center - Data Account, and click to enter the edit page of the data account to switch.
  2. From the Account Platform dropdown list, select the target database type to switch to, and fill in the new data account configuration information.
  3. Click Test Connection to ensure the data account is connected.
  4. After saving, the data account icon changes to the target database. The following figure uses switching from TiDB to MySQL as an example.

image.png

  1. Click the number under Dataset Count to enter the dataset list page. Because SQL syntax differs across databases and may not be universal, open the dataset overview page for each dataset and check or modify the following areas.

image.png

1. Extract datasets, or Guan-Index datasets

    1. On the Model Structure page, check and modify the SQL query statement, preview it again, and save it after confirming the preview is correct.
    2. On the Data Update page, if incremental update is configured, the incremental update SQL statement also needs to be modified, previewed, and saved.

image.png

2. Direct datasets

    1. SQL query statements in Model Structure.
    2. Row and column permission formulas in Data Permissions.
    3. Calculated fields created on the Overview page, specifically the parts that use functions.
    4. Calculated fields in all cards that depend on this dataset, specifically the parts that use functions.

image.png

Notes

  1. Modifying the database type of a single dataset is not supported.
  2. If only one dataset needs to be modified, create a new dataset. Then go to the Cards tab on the original dataset overview page, select multiple cards, and batch switch the dataset. Go to Associated Creation, find the associated ETL jobs and view datasets, and open each edit page to replace the dataset.

image.png