Switch SQL based Live Connectors in Bold BI - Using Migration Utility

Published:

When managing data sources in Bold BI, there may be instances where you need to switch SQL based live connectors. Although Bold BI does not have a direct feature to swap SQL live connectors, this can be accomplished by using a migration utility application.

Step 1: Prerequisite and Preprocessing

Obtain the migration utility application compatible with Bold BI.

Please contact the Bold BI support to request a Migration Utility. You can access the utility application for v7.8 and above On-premises versions.

Take a backup of your app_data folder. For Windows deployment,
[INSTALL_DIR]:/BoldServices/app_data
Extract the Migration Utility application, copy the folder containing the Migration Utility application and paste it into your Bold BI deployment location.
<:INSTALL_DIR>/BoldServices/bi

Step 2: Set up the application configuration file

In the Utility, open the appsettings.json file and input the required details specified below.

In other environments, modify the “boldbi_path” in the application settings file to “/var/www/bold-services/application”.

connection_type

This property indicates the type of data source connector that has been established.

destination_connection_type

The property indicates the type of data source connector that can be exchanged.

The values for connection_type and destination_connection_type should exactly match the name of the connector’s Provider type. Refer below,

SQL
PostgreSQL
MySQL
Redshift
Snowflake
AzureSQLDataWarehouse

old_connection
It is necessary to input the current SQL connector details in the old_connection attribute.

"old_connection": {
	"ServerName": "",
	"Database": "",
	"UserName": "",
	"Password": "",
	"Schema": ""
},

Note:

It is necessary to fill all the properties in the old connection. Failing to do so could have an impact on other dashboards and data sources if they are not fully filled.
For MYSQL, the database name must be specified in the “Schema” property because MYSQL does not have a separate schema property.

new_connection
It is necessary to input the new SQL connector details in the new_connection attribute.

"new_connection": {
	"ServerName": "",
	"Database": "",
	"UserName": "",
	"Password": "",
	"Schema": "",
	"Port": ""
},

Note

The new connection property will be successfully swapped only if the old connection properties matches with the actual data source connection details. Otherwise, the swapping will fail
In order to establish a new connection, you must specify all properties.
Currently, the new connection details are not validated. It is important to verify the connection before running the utility. Failure to do so may impact the data source and dashboard.
In order to switch the connector from one type to another (such as SQL to PostgreSQL or MySQL to SQL), you must provide all connection details, including the port number if necessary.
For MYSQL, the database name must be specified in the “Schema” property because MYSQL does not have a separate schema property.

Additional Options:

Tenant:

You are required to indicate the tenant in the tenant property. Otherwise, we will not be able to allow changes to the connector.
The Tenant represents the unique identification of the client that can be found on UMS sites.
Please follow the below image to set the Tenant list property in appsetting.json
Once the all the above configuration done based on your requirement, save the appsetting.json file.

Filter:

It is not required, but you can use it to swap a specific data source. If you wish to provide multiple data sources, separated them by semi colon.

It is advised to keep the Filter property blank so that the new connection details can be modified wherever required.

Step 3: Utilize the Migration Utility

Run the BoldBI_ConnectionUpdator.exe
Choose option 3 to swap the SQL connector.
Please share the URL for the tenant along with the necessary login information once prompted.
1. Server URL - https://{yourdomain}/bi/api/site/<site_identifier>
2. Username: Bold BI username
3. Password: Bold BI password
Please input the ‘Y’ or ‘y’ value to proceed.
The output will indicate whether the swapping of the data source was successful or unsuccessful.

Output Screenshots

Before Swapping

After Swapping

Limitations in using a migration utility application

It is not applicable for Bold BI Cloud.
The Migration utility application exclusively supports SQL based Live connectors for now (MySQL, PostgreSQL, SQL, AzureSQLDataWarehouse (Synapse Analytic), Snowflake, Redshift) and does not support Extract and Web Live connectors.
It does not support customized expressions added in the existing data source. Expressions need to be recreated manually after swapping the connector.
It does not change the tables and columns in the existing data sources. Changing the case sensitivity of tables and columns is not permitted. If there is a discrepancy in the old connection, it will cause the data source and dashboard to stop functioning properly. - Refer How to rename Columns or Tables in a data source through Rest API to rename columns or Table in Data source Through REST API.
It does not support the code view mode.
The use of Stored Procedures is not possible in MySQL, therefore it is not feasible to swap the data source using Stored Procedure to MySQL.
It does not support modifying the query within Dashboard parameters.
You need to specify the tenant or site id in the application.json file. You should not execute multiple tenant site execution simultaneously due to IMDB details its required for this case.
Only the specified connection properties can be modified. Currently, the options for Advanced Connection and Command timeout are not available.

Was this article useful?

Dislike

Written by Baskaran K

Updated:

Comments (0)