Integrate Hologres with Power Bi to perform BI analysis - Hologres

This topic describes how to connect Power BI to Hologres and visually analyze Hologres data in Power BI.

Background information

Power BI is popular business intelligence (BI) tool from Microsoft. Hologres is compatible with PostgreSQL. You can directly connect Power BI Desktop to Hologres to perform data analysis. You can also publish Power BI Desktop reports to Power BI Service or Power BI Report Server to view the analysis results.

Usage notes

By default, Power BI does not synchronize the information about foreign tables of Hologres. To enable this synchronization, you must execute the following SQL statement in your Hologres database after connecting to Power BI.
```
CREATE EXTENSION foreign_table_exposer;
```
When using Power BI to connect to a Hologres data source, note that Power BI sends a cancellation request after every SQL interaction with the data source. This may affect subsequent SQL requests sent to Hologres, resulting in the "canceling statement due to user request" error. To prevent this issue, use Hologres V3.0 or later and configure the following parameter:
```
-- Ignore the Cancel requests that occur within 20 ms after the SQL execution starts. If the preceding error persists, you can further adjust this value to 50 ms.
ALTER ROLE ALL SET hg_experimental_enable_cancel_after_query_start_ms = 20;
```
If you use earlier Hologres versions, you can upgrade your instance manually or get online support.

Connect Power BI Desktop to Hologres

Install Power BI Desktop.
For detailed instructions, see Get Power BI Desktop.

Connect Power BI Desktop to Hologres.

In Power BI Desktop, in the Select a data source or start with a blank report section, click Get data from other sources.
On the Get Data page, choose Database > PostgreSQL database, and click Connect.

In the dialog, configure connection parameters as described in the following table. and click OK. 获取数据

Parameter	Description
Server	The endpoint and port number 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 endpoint and port number based on the network environment where Power BI is running. Otherwise, the connection will not work properly.
Database	The name of your Hologres database.
Data Connectivity mode	Select DirectQuery.
Advanced options	Skip this section for this example. You can configure advanced settings for more granular control as needed.

Enter the username and password.

Parameter	Description
Username	The AccessKey ID of your Alibaba Cloud account used to connect to the Hologres database. For more information, see Create an Alibaba Cloud account.
Password	The AccessKey secret of your Alibaba Cloud account used to connect to the Hologres database. For more information, see Create an Alibaba Cloud account.

Click Connect. On the Navigator page, select tables to be imported into Power BI Desktop for visualized data analytics.
Click Load.
After you import data into Power BI Desktop, you can query data and create reports or dashboards to analyze data. For more information, see Power BI documentation.

Publish data reports to Power BI Service

You can publish reports to Power BI Service to share and view analytics results. Power BI Service uses an on-premises data gateway to communicate with data sources such as Hologres. Therefore, after publishing a report, you must install and configure the gateway.

Publish data reports in Power BI Desktop.
1. In Power BI Desktop, open the data report you want to publish and click Publish.
2. In the Publish to Power BI dialog box, select the workspace to be published.
3. Click Select. Power BI Desktop publishes the report to Power BI service. Once it's published, click the link in the success message to open your report in Power BI service.
Install a Power BI gateway.
Power BI service communicates with local servers by using a Power BI gateway. Keep the following points in mind during installation:
- The Power BI gateway and Power BI Desktop do not need to be installed on the same server.
- Ensure the server where the gateway resides has network access to your Hologres database.
- Install Npgsql 4.0.10. During its setup, ensure you select Npgsql GAC Installation. Restart the Power BI gateway after the installation is complete.
Configure the Power BI gateway.
For more information, see Use the on-premises data gateway app.

Configure a data source for Power BI Service.

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

Select the target gateway and click New.

The configurations of the data source must be consistent with the connection configurations of Power BI Desktop. The following table describes the parameters.

Parameter	Description
Data source name	The name of the data source.
Data source type	Select PostgreSQL database.
Server	The endpoint and port number 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 endpoint and port number based on the network environment where Power BI Service is running. Otherwise, the connection will not work properly.
Database	The name of the Hologres database.
Username	The AccessKey ID of the Alibaba Cloud account used to connect to the Hologres database. For more information, see Create an Alibaba Cloud account.
Password	The AccessKey secret of the Alibaba Cloud account used to connect to the Hologres database. For more information, see Create an Alibaba Cloud account.
Advanced settings	In this section, you can configure the following: Single Sign-On (SSO): 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`. Use the default value of Privacy Level setting for this data source.

Click Create. After the connectivity test is complete, a message is displayed to indicate that the data source is connected.

Map the dataset to the gateway.
After you publish data reports to Power BI Service, you must configure a report gateway to view and analyze your reports in interactive mode.
1. Log on to Power BI Service. In the left-side navigation pane, click My workspace.
2. Find the dataset that you want to configure, click the icon, and then click Settings.
3. On the page that appears, click Gateway connection, and turn on the Use a data gateway switch. Select the gateway and the data source to be mapped.
4. Click Apply. A message is displayed to indicate that the gateway has been configured.
After you complete the preceding steps, you can view and analyze your reports in interactive mode in Power BI Service.

Publish data reports to Power BI Report Server

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

Install Power BI Report Server.
For more information about how to install Power BI Report Server, see Power BI Report Server documentation. Keep mind of the following during installation:
- Ensure the server on which Power BI Report Server is installed can access the Hologres database over the network.
- Click to install Npgsql 4.0.10. During the setup, select Npgsql GAC Installation. After installation, restart Power BI Report Server.
Publish data reports in Power BI Desktop.
1. Open the data report that you want to publish in Power BI Desktop. In the top navigation bar, choose Files > Save as > Power BI Report Server.
2. In the dialog box that appears, enter the endpoint of the Power BI Report Server and click OK.
3. Select the path for storing the data report and click OK. Power BI Desktop publishes the data report to Power BI Report Server. After the data report is published, you can click the link displayed on the page to access Power BI Report Server.

Configure a data source for Power Report Server.

Log on to Power BI Report Server, find the data report that you want to configure, click the icon, and then click Manage.

On the page that appears, configure the following parameters:

The configurations of the data source must be consistent with the connection configurations of Power BI Desktop. The following table describes the parameters.

Parameter	Description
Type	Select PostgreSQL.
Connection String	The connection string is auto-generated.
Authentication Type	The authentication method for the data source. Select Basic Authentication.
Username	The AccessKey ID and secret of the Alibaba Cloud account used to connect to the Hologres database.
Password

Click Test connection. After the test is complete, a message is displayed to indicate that the data source is connected.

After you complete the preceding steps, you can view and analyze your reports in interactive mode in Power BI Report Server.

Encrypt data transmission

To encrypt data transmission by using SSL, first enable SSL encryption in Hologres and download the CA certificate. Then, on the machine that runs the Power BI Gateway, install the certificate by importing i t into the machine's Trusted Root Certification Authorities certificate store. For detailed instructions, see Transmission encryption.