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

Power BI - 图1Note

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.

    start page

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

    get data

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

    connection information

  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.

    uname and pwd

  6. Load the selected table to the Power BI Desktop

    load data

  7. Configure statistical compass

    create compass

  8. Save statistical compass to location

    save file

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

    gateway install

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

    upload

  4. Click the model to set the automatic refresh time

    click module

  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.

    config gateway

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

    make plan