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.

The Apache Doris community provides a Power BI DirectQuery custom connector based on MySQL ODBC, which supports Apache Doris's internal data modeling, data query, and visualization processing.

Precondition

• If you don't have Power BI Desktop installed, you can visit here to download and install Power BI.
• You need to obtain the power-bi-doris custom connector.
• If you don't have Mysql ODBC installed, you need to download and install Mysql ODBC and configure it.

Note

Use MySQL ODBC Driver 5.3

Power BI and Doris custom connector configuration

1. Refer to the path here: \Power BI Desktop\Custom Connectors folder, place the Doris.mez custom connector file (if the path does not exist, create it manually as needed).
2. In Power BI Desktop, select File > Options and settings > Options > Security, under Data Extensions, check (Not Recommended) Allow any extension to load without validation or warning.
3. Select ok and restart Power BI Desktop.

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 and select the Doris database in the pop-up window.

   

4. Configure the database connection information and enter host:port in the server input box. The default port number of Doris is 9030

   

5. After clicking OK in the previous step, fill in Doris’ connection information in the new connection window.

   

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