Hologres:Power BI

Usage notes

CREATE EXTENSION foreign_table_exposer;

Connect Power BI Desktop to Hologres

1. Install Power BI Desktop.

   For more information about how to install Power BI Desktop, see Power BI Desktop documentation.

2. Connect Power BI Desktop to Hologres.

   1. Open Power BI Desktop. In the upper part of the page, choose Get Data > More.

   2. On the Get Data page, choose Database > PostgreSQL database.

   3. Click Connect. In the dialog box that appears, configure connection parameters that are described in the following table.

      Parameter	Description
      Server	The endpoint of the Hologres instance. You can obtain the endpoint of the Hologres instance from the Network Information section of the Instance Details page in the Hologres console.
      Database	The name of the Hologres database.
      Data Connection mode	The mode in which Power BI Desktop connects to Hologres. Select DirectQuery.
      Advanced Settings	In this example, you do not need to configure the parameters in the Advanced Settings section. Use the default values for the parameters. You can also configure the parameters in the Advanced Settings section to configure the connection in a fine-grained way based on your business requirements.

   4. Click OK and configure the username and password.

      Parameter	Description
      Username	The AccessKey ID of the Alibaba Cloud account used to connect to the Hologres database. For more information about how to obtain the AccessKey ID, 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 about how to obtain the AccessKey secret, see Create an Alibaba Cloud account.

   5. Click Connect. On the Navigator page, the table information in the database is displayed. You can select the tables to be imported into Power BI Desktop for visualized data analytics.

   6. Click Load to import data into Power BI Desktop.

      After you import data into Power BI Desktop, you can query data and create reports or dashboards to analyze data. For more information about how to perform operations on data and analyze data by using Power BI Desktop, see Power BI documentation.

Publish data reports to Power BI Service

You can publish data reports to Power BI Service to view the analytics results. Power BI Service communicates with local servers by using Power BI Gateway. After you publish the data reports generated by Power BI Desktop to Power BI Service, you must install and configure Power BI Gateway.

1. Publish data reports in Power BI Desktop.

   1. Open the data report that you want to publish 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 be published.

   3. Click Select. Power BI Desktop publishes the data report to Power BI Service. After the data report is published in Power BI Desktop, you can click the link displayed on the page to access Power BI Service.

2. Install Power BI Gateway.

   Power BI Service communicates with local servers by using Power BI Gateway. For more information about how to install Power BI Gateway, see Power BI documentation. Take note of the following items when you install Power BI Gateway:

   • Power BI Gateway and Power BI Desktop do not need to be installed on the same server.

   • You must make sure that the server on which Power BI Gateway is installed can access the Hologres data source over the network.

   • You must install Npgsql 4.0.10. You can download the Npgsql 4.0.10 installation package and install it by following the on-screen instructions. You must install Npgsql GAC Installation when you install Npgsql 4.0.10. After the installation is complete, restart Power BI Gateway.

3. Configure Power BI Gateway.

   After Power BI Gateway is installed, you can configure Power BI Gateway on the server on which the gateway is running. To configure Power BI Gateway, start Power BI Gateway and double-click the Power BI Gateway icon. For more information, see Microsoft Gateway documentation.

4. Configure a data source for Power BI Service.

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

   2. Select the gateway that you want to use 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	The type of the data source. Select PostgreSQL database from the drop-down list.
      Server	The endpoint of the Hologres instance. You can obtain the endpoint of the Hologres instance in the Network Information section of the Instance Details page in the Hologres console.
      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 about how to obtain the AccessKey ID, 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 about how to obtain the AccessKey secret, see Create an Alibaba Cloud account.
      Advanced settings	In the Advanced settings section, you must perform the following configurations: You can configure Single Sign-On (SSO) for your data source. 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.

   3. Click Create. The system tests the connectivity of the data source. After the test is complete, a message is displayed, indicating that the data source is connected.

5. Configure a report 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, indicating 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.

1. Install Power BI Report Server.

   For more information about how to install Power BI Report Server, see Power BI Report Server documentation. Take note of the following items when you install Power BI Report Server:

   • You must make sure that the server on which Power BI Report Server is installed can access the Hologres data source over the network.

   • You must install Npgsql 4.0.10. You can download the Npgsql 4.0.10 installation package and install it by following the on-screen instructions. You must install Npgsql GAC Installation when you install Npgsql 4.0.10. After the installation is complete, restart the Power BI Report Server.

2. 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.

3. Configure a data source for Power Report Server.

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

   2. On the page that appears, configure the 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	The type of the data source. Select PostgreSQL from the drop-down list.
      Connection string	The connection string that is automatically generated. You do not need to specify this parameter.
      Authentication Type	The authentication method for the data source. Select Basic Authentication from the drop-down list.
      Username	The AccessKey ID of the Alibaba Cloud account used to connect to the Hologres database. For more information about how to obtain the AccessKey ID, 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 about how to obtain the AccessKey secret, see Create an Alibaba Cloud account.

   3. Click Test connection. The system tests the connectivity of the data source. After the test is complete, a message is displayed, indicating 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

If you want to use the SSL protocol to connect your application to a Hologres instance, you must enable SSL-encrypted transmission in Hologres and download the Certificate Authority (CA) certificate. Then, install the CA certificate on your on-premises machine and add the CA certificate to the Trusted Root Certificate Authority directory. For more information about how to enable SSL-encrypted transmission in Hologres, see SSL-encrypted transmission.