Connect to OceanBase Database in Power BI and obtain data

Power BI provides a collection of software services, apps, and connectors that work collaboratively to turn your unrelated data sources into coherent, visually immersive, and interactive insights. Your data can be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses. Power BI allows you to easily connect to your data sources, visualize and discover what's important, and share that with anyone you want.

This topic describes how to connect to a MySQL tenant of OceanBase Database in Power BI for data display.

Prerequisites

• You have registered with Power BI. For more information, see Power BI.

• You have installed OceanBase Database and created a MySQL tenant.

• You have prepared a dataset. The sample dataset used in this topic is sourced from Kaggle.

Procedure

Step 1: Obtain the OceanBase Database connection string

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string. The following is a connection string example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

The parameters are described as follows:

• $host: the IP address for connecting to OceanBase Database. It is the IP address of OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

• $database_name: the name of the database to be accessed.

  Notice

  The user for connecting to a tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the tenant account. For connection through ODP, two account formats are supported: username@tenant name#cluster name and cluster name:tenant name:username. For direct connection, the username@tenant name format is supported.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase Database tenant by using OBClient.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Import data and create a table

1. Log in to OceanBase Database by using the connection string obtained in Step 1 and create a table by using the following statement:

   CREATE TABLE `test` (
   `invoice_no` varchar(50) DEFAULT NULL,
   `stock_code` varchar(20) DEFAULT NULL,
   `description` varchar(200) DEFAULT NULL,
   `quantity` int(11) DEFAULT NULL,
   `invoice_data` datetime DEFAULT NULL,
   `unit_price` decimal(8,2) DEFAULT NULL,
   `customer_id` int(11) DEFAULT NULL,
   `country` varchar(50) DEFAULT NULL,
   `sales` float DEFAULT NULL
   ) DEFAULT CHARSET = utf8mb4
   

2. Use the LOAD DATA statement to import the prepared dataset to the table. Here is a sample statement:

   LOAD DATA INFILE '/path/superstoredata.csv'
   INTO TABLE earthquakes
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   IGNORE 1 LINES
   (invoice_no, stock_code, description, quantity, invoice_data, unit_price, customer_id, country, sales);
   

   If the dataset contains a large amount of data or the data to be imported needs to be converted, you can use obloader to import the dataset.

Step 3: Connect to OceanBase Database from the Power BI console

1. Log in to the Power BI console and click Create in the left-side pane. On the page that appears, select Warehouse to create a warehouse and name it oceanbase.

2. On the page of the new warehouse oceanbase, choose Get data > New Dataflow Gen2.

3. In the window that appears, search for mysql and select MySQL database from the search results.

   Set the following parameters based on the connection string obtained in Step 1. For example, if the obtained connection string is obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest, the parameters should be set as follows:

   • Server: Enter xxx.xxx.xxx.xxx:2881.

   • Database: Enter the database to which the table created in Step 2 belongs.

   • Username: Enter test_user001@mysql001.

4. Click Next. On the page that appears, choose Get data > MySQL database and double-click your database. The table data is displayed, as shown in the following figure.

5. Click the Reporting tab and then click Manage default semantic model to create a report if you want to experience the report feature of Power BI.

6. In the Manage default semantic mode dialog box, select your table and click Confirm. A report is automatically generated within a few minutes.

   The following figure shows a sample generated report.

What to do next

Power BI will be ready for use after the MySQL tenant of OceanBase Database has been added as a data source. For more information about how to use Power BI, see Power BI documentation.