Integrate Hologres with Power Bi to perform BI analysis - Hologres

This topic describes how to connect Power BI to Hologres and perform visual analytics on your data.

Background information

Power BI is a well-known business intelligence (BI) software from Microsoft. Hologres is compatible with PostgreSQL and lets you connect directly to Power BI Desktop for data analytics. If needed, you can also publish reports from Power BI Desktop to Power BI Service or Power BI Report Server to view the analysis results.

Notes

By default, Power BI does not sync foreign tables from Hologres. After you connect to Hologres, execute the following SQL command in the database to sync foreign table information from Hologres to Power BI.
```
CREATE EXTENSION foreign_table_exposer;
```
When you use Power BI to connect to a Hologres data source, note that Power BI sends a cancel request after each SQL interaction with the data source. This cancel request might affect subsequent SQL requests to Hologres, causing new SQL requests to be canceled and returning a "canceling statement due to user request" error. Upgrade your Hologres instance to V3.0 or later and set the following parameter:
```
-- Ignore cancel requests within 20 ms after an SQL query starts. If the error persists, change the value to 50 ms.
ALTER ROLE ALL SET hg_experimental_enable_cancel_after_query_start_ms = 20;
```
To upgrade, you can request an instance upgrade by joining the Hologres community group or perform an instance upgrade yourself. To join the group, see How can I get more online support?.

Connect Power BI Desktop to Hologres

Install Power BI Desktop

Install Power BI Desktop. For more information, see the official Power BI Desktop documentation.

Connect to Hologres

Open Power BI Desktop. In the upper part of the page, click Get Data > More.
On the Get Data page, in the Database category, select PostgreSQL database.

Click Connect. In the dialog box that appears, configure the instance connection parameters.

Parameter	Description
Server	The network address and port of the Hologres instance. Log on to the Hologres console. In the left-side navigation pane, click Instances. On the Instances page, click the ID of the instance. On the Instance Details page, view the endpoint and port number in the Network Information section. Important Select the correct network address and port based on the network environment where Power BI runs. Otherwise, the connection fails.
Database	The name of the database that you created in Hologres.
Data Connectivity mode	Set Data Connectivity mode to DirectQuery.
Advanced options	In this example, you do not need to configure this parameter. Keep the default value. You can also configure fine-grained settings as needed.

Click OK. Configure the username and password.

Parameter	Description
User name	The AccessKey ID of your Alibaba Cloud account. For more information about how to obtain an AccessKey ID, see Create an AccessKey pair.
Password	The AccessKey secret of your Alibaba Cloud account. For more information about how to obtain an AccessKey secret, see Create an AccessKey pair.

Click Connect. The tables in the database are displayed on the Navigator page. Select the table data that you want to load into Power BI Desktop for visual analytics as needed.
Click Load at the bottom to import the data into Power BI Desktop.

After you import the data into Power BI Desktop, you can query the data and create reports or dashboards for analysis. For more information about data operations and analysis in Power BI Desktop, see the official Power BI documentation.

Publish to Power BI Service

You can publish data to Power BI Service to view analysis results. Power BI Service communicates with local servers through Power BI Gateway. After you publish a report from Power BI Desktop to Power BI Service, you must install and configure Power BI Gateway.

Publish a data report from Power BI Desktop
1. Open the data report in Power BI Desktop. In the upper part of the page, click Publish.
2. In the Publish to Power BI dialog box, select the workspace to which you want to publish the report.
3. Click Select. Power BI Desktop publishes the report to Power BI Service. After the report is published, you can click the link on the page to access Power BI Service.
Install Power BI Gateway
Power BI Service communicates with local servers through Power BI Gateway. To install Power BI Gateway, see the official Power BI documentation. Note the following items during installation:
- Power BI Gateway does not need to be installed on the same machine as Power BI Desktop.
- Make sure that the server where Power BI Gateway runs has a stable network connection and can access the Hologres data source.
- You must install Npgsql 4.0.10. Click to download the Npgsql 4.0.10 installation package. During the installation, make sure to install Npgsql GAC Installation. After the installation is complete, you must restart Power BI Gateway.
Configure Power BI Gateway

After Power BI Gateway is installed, start Power BI Gateway on the server where the gateway runs. Then, double-click the Power BI Gateway icon to configure the gateway. For more information about the procedure, see the official Microsoft Gateway documentation.

Configure a Power BI Service data source

Log on to Power BI Service. In the upper-right corner of the page, click the icon and select Manage gateways from the list.

Select the target gateway and click Add data source to configure the parameters.

The data source configuration must be the same as the connection information in Power BI Desktop. The following table describes the parameters.

Parameter	Description
Data Source Name	Specify a name for the new data source.
Data Source Type	From the drop-down list, select PostgreSQL.
Server	The network address and port of the Hologres instance. Log on to the Hologres console. In the left-side navigation pane, click Instances. On the Instances page, click the ID of the instance. On the Instance Details page, view the endpoint and port number in the Network Information section. Important Select the correct network address and port based on the network environment where Power BI Service runs. Otherwise, the connection fails.
Database	The name of the database that you created in Hologres.
User Name	The AccessKey ID of your Alibaba Cloud account. For more information about how to obtain an AccessKey ID, see Create an AccessKey pair.
Password	The AccessKey secret of your Alibaba Cloud account. For more information about how to obtain an AccessKey secret, see Create an AccessKey pair.
Advanced settings	In the advanced settings, you can configure the following items: For single sign-on, you can select `Use SSO via Kerberos for DirectQuery queries`, `Use SSO via Kerberos for DirectQuery and Import queries`, or `Use SSO via Azure AD for DirectQuery queries`. You can keep the default value for Privacy level setting for this data source.

Click Add. The system tests the connectivity. After the test is complete, a message appears indicating that the data source is connected.

Configure the report gateway

After you publish the data report to Power BI Service, you must configure the report gateway to view your report and perform interactive analysis.
1. Log on to Power BI Service. In the navigation pane on the left, click My workspace.
2. Click the icon next to the target dataset and select Settings.
3. On the settings page, click Gateway connection and turn on the Use a gateway switch. Select the corresponding gateway and mapped data source.
4. Click Apply. A message appears indicating that the gateway is updated. This means the gateway is configured.
After you complete these settings, you can view your report and perform interactive analysis on Power BI Service.

Publish to Power BI Report Server

You can publish data to Power BI Report Server to view analysis results.

Install Power BI Report Server
To install Power BI Report Server, see the official Power BI Report Server documentation. Note the following items during installation:
- Make sure that the server where Power BI Report Server runs has a stable network connection and can access the Hologres data source.
- You must install Npgsql 4.0.10. Click to download the Npgsql 4.0.10 installation package. During the installation, make sure to install Npgsql GAC Installation. After the installation is complete, you must restart Power BI Report Server.
Publish data from Power BI Desktop
1. Open the data report in Power BI Desktop. In the upper part of the page, choose File > Save as > Power BI Report Server.
2. In the dialog box that appears, enter the server address and click OK.
3. Select a location to save the report and click OK. Power BI Desktop publishes the report to Power BI Report Server. After the report is published, you can click the link on the page to access Power BI Report Server.

Configure a Power BI Report Server data source

Log on to Power BI Report Server. Click the icon to the right of the target data source report and select Manage from the list.

On the data source page, configure the parameters.

The data source configuration must be the same as the connection information in Power BI Desktop. The following table describes the parameters.

Parameter	Description
Type	From the drop-down list, select PostgreSQL.
Connection string	This parameter is automatically generated. You do not need to specify it.
Authentication type	From the drop-down list, select Basic authentication.
User name	The AccessKey ID of your Alibaba Cloud account. For more information about how to obtain an AccessKey ID, see Create an AccessKey pair.
Password	The AccessKey secret of your Alibaba Cloud account. For more information about how to obtain an AccessKey secret, see Create an AccessKey pair.

Click Test connection. The system tests the connectivity. After the test is complete, a message appears indicating that the data source is connected.

After you complete these settings, you can view your report and perform interactive analysis on Power BI Report Server.

Encryption in transit

To use SSL to encrypt data in transit when you connect to a Hologres instance, you must first enable SSL encryption in transit for the Hologres instance and download the CA certificate. Then, install the certificate on your local machine and add it to the Trusted Root Certification Authorities directory. For more information about how to enable SSL encryption in transit for a Hologres instance, see Encryption in transit.