AnalyticDB:Power BI

Step 1: Install the MySQL ODBC driver

1. Download and install the Microsoft Visual C++ 2010 Service Pack 1 Redistributable Package MFC.

2. Download and install version 5.1.13 of the MySQL ODBC Driver.

3. Open ODBC Data Source Administrator (64-bit). On the Drivers tab, verify that the MySQL ODBC Driver appears in the list.

Step 2: Install the AnalyticDB for MySQL connector

1. Download the AnalyticDB.MySQL.mez connector.

2. Move AnalyticDB.MySQL.mez to the Custom Connectors folder in your Power BI directory. The default path is [Documents]\Power BI Desktop\Custom Connectors. If the folder doesn't exist, create it.

   Important

   - [Documents] is the Documents folder for the current Windows user account. For the Administrator account, the default path is C:\Users\Administrator\Documents\Power BI Desktop. - Power BI loads connectors only from the current user's folder. Install and maintain connectors separately for each account.

3. Open Power BI and go to Options and settings > Options.

   

4. Click Security. Under Data Extensions, select (Not Recommended) Allow any extension to load without validation or warning, then click OK.

   

5. In the Change Requires Restart dialog box, click OK, then restart Power BI.

Step 3: Connect to AnalyticDB for MySQL

1. Open Power BI and click Get data from another source.

   

2. In the Get Data dialog box, select AnalyticDB MySQL(Beta)(Custom) and click Connect.

   The connector is labeled Beta, which means it may have functional limitations or change in future releases.

   

3. In the Connecting to a third-party service dialog box, click Continue.

   

4. In the AnalyticDB MySQL database dialog box, configure the connection parameters.

   Parameter	Required	Description
   Server	Yes	The endpoint and port of the cluster, in the format <Endpoint>:Port. Use the VPC address if Power BI is on an Elastic Compute Service (ECS) instance in the same virtual private cloud (VPC) as the cluster; use the public endpoint otherwise. The port is fixed at 3306.
   Database	No	The name of the database in the cluster.
   Connection timeout in seconds	No	The timeout period for connecting to the AnalyticDB for MySQL cluster. Unit: seconds (s).
   Read timeout in seconds	No	The maximum time (in seconds) allowed for a read operation. Unit: seconds (s).
   Write timeout in seconds	No	The maximum time (in seconds) allowed for a write operation. Unit: seconds (s).
   Don't cache results of forward-only cursors	No	When selected, Power BI skips caching query results when using forward-only cursors.
   Force use of forward-only cursors	No	When selected, Power BI always uses forward-only cursors for queries.
   Native query	No	A SQL query statement. If left blank, all tables appear in the Navigator dialog box.
   Data Connectivity mode	Yes	The data connection mode. Select Import to load data into Power BI, or DirectQuery to query the cluster directly without importing data.

   

5. In the AnalyticDB MySQL dialog box, enter your credentials and click Connect.

   Parameter	Description
   User name	The database account for the AnalyticDB for MySQL cluster.
   Password	The password for the database account.

   

6. In the Navigator dialog box, select the target table, preview the data on the right, then click Load.

7. After loading, start analyzing your data. For more information, see the official Power BI documentation.

   

Step 1: Download a driver

Choose either driver based on your environment:

• Microsoft Spark ODBC Driver

• Simba Spark ODBC Driver

Step 2: Create a data source

1. Open ODBC Data Source Administrator (64-bit). On the User DSN tab, click Add.

2. In the Create New Data Source dialog box, select the driver you downloaded and click Finish.

3. In the Microsoft Spark ODBC Driver DSN Setup or Simba Spark ODBC Driver DSN Setup dialog box, configure the parameters.

   Parameter	Description
   Data Source Name	A name for this data source. Choose any name.
   Description	An optional description for the data source.
   Host(s)	The endpoint of the interactive Spark resource group. For details, see Preparations.
   Port	The port of the interactive Spark resource group. Fixed at 10000.
   Database	The name of the AnalyticDB for MySQL database.
   Mechanism	The authentication method. Select User Name and Password.
   User Name	The database account and resource group name, in the format Resource group name/Database account name. For example, if the resource group is testjob and the account is test, enter testjob/test.
   Password	The password for the database account.
   Thrift Transport	The connection method. Fixed at SASL.

   

4. Click SSL Options. In the SSL Options dialog box, clear Enable SSL and click OK.

   

5. Click Advanced Options. In the Advanced Options dialog box, select Get Tables with Query.

   

6. Click Server Side Properties. Clear Apply properties with queries and click OK.

   

7. Click Test to verify the connection. A SUCCESS message confirms the ODBC connection to the interactive Spark resource group is working.

   

Step 3: Connect Power BI to the data source

1. Open Power BI and click Get data from another source.

   

2. In the Get Data dialog box, select ODBC and click Connect.

   

3. In the From ODBC dialog box, select the data source you created in Step 2 and click OK. In this example, the data source is adb_test.

   

4. In the ODBC driver dialog box, click the Windows tab, select Use my current credentials, and click Connect.

   

5. In the Navigator dialog box, select the target table, preview the data on the right, then click Load.

6. After loading, start analyzing your data. For more information, see the official Power BI documentation.