Power BI

Power BI Introduction

Power BI is a collection of software services and application connectors that can connect to multiple data sources, including Excel, SQL Server, Azure, Google Analytics, etc., so that users can easily consolidate and clean their data. With Power BI's data modeling, users can create relational models, data analysis expressions, and data relationships to support advanced data analysis and visualization. Power BI offers a wealth of visualization options, including ICONS, maps, dashboards, and custom visualization tools to help users make a more intuitive sense of data.

Apache Doris is highly compatible with MySQL protocol and can be connected to Power BI and Apache Doris through MySQL Driver. At present, internal data modeling, data query and visualization processing of Apache Doris have been officially supported in Power BI.

Precondition

If you do not have Power BI desktop installed, you can download it from https://www.microsoft.com/en-us/power-platform/products/power-bi/desktop

Connector configuration of Power BI and Doris

Note

Currently verified using MySQL JDBC Connector version 8.0.26

Download and installation MySQL Connector Download link: https://downloads.mysql.com/archives/c-net/. Select version 8.0.26. There are incompatibilities in higher versions

Load data locally and create models

1. Start the Power BI Desktop

2. Open the Power BI Desktop screen and click Create Report. If a local report exists, you can open it.

   

3. Click get data. In the dialog box that is displayed, select MySQL database.

   

4. Configure the database connection information and enter ip:port in the server text box. The default port number for Doris is 9030.

   

5. Click OK in the previous step and select "Database" in the new connection window to connect, and fill in the connection information of Doris in the username and password.

   

6. Load the selected table to the Power BI Desktop

   

7. Configure statistical compass

   

8. Save statistical compass to location

   

Set data refresh automatic

1. Download the On-premises data gateway. Download address: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-personal-mode

2. Install the On-premises data gateway

   

3. Log into Power BI Online and import the local model in your workspace

   

4. Click the model to set the automatic refresh time

   

5. The data refresh configuration requires a gataway connection. After the gateway is enabled locally, you can see the started gateway in the gateway connection locally. Select the local gateway.

   

6. Configure the refresh schedule to complete the automatic data refresh on Power BI