DocsICP FilingConsole
官方文档
首页

Power BI

更新时间：
复制 MD 格式
产品详情
我的收藏

This topic describes how to connect Power BI to Hologres for data visualization and analysis.

Background information

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

Usage notes

  • By default, Power BI does not synchronize foreign tables from Hologres. To enable synchronization, run the following SQL command in your database after you connect to Hologres:

    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. This request may interfere with subsequent SQL queries to Hologres, causing them to be canceled and return a “canceling statement due to user request” error. We recommend that you upgrade your Hologres instance to V3.0 or later and set the following parameter:

    -- Ignore cancel requests that occur within 20 ms after a SQL query starts. If the error persists, you can increase this value to 50 ms.
ALTER ROLE ALL SET hg_experimental_enable_cancel_after_query_start_ms = 20;

    To upgrade your instance, you can either request an upgrade through the Hologres support group or perform an instance upgrade. To join the support group, see How can I get more online support?.

Connect Power BI Desktop to Hologres

  1. Install Power BI Desktop

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

  2. Connect to Hologres

    1. Open Power BI Desktop. In the ribbon, click Get data > More.

    2. In the Get Data window, select Database > PostgreSQL database. Then, click Connect.

    3. Click Connection. In the dialog box, configure the instance connection information. In the PostgreSQL database dialog box, enter the Server and Database information. For Data connectivity mode, select DirectQuery, and then click OK.

      Parameter

      Description

      Server

      The network address and port for your Hologres instance.

      Log on to the Hologres console. In the left-side navigation pane, click Instances. Click the target instance. On the Instance Details page, you can find the network address and port in the Network Information section.

      Important

      Ensure that the network address and port are accessible from the environment where Power BI is running. Otherwise, the connection will fail.

      Database

      The name of the database that you created in Hologres.

      Data connectivity mode

      Select DirectQuery.

      Advanced options

      For this example, you can keep the default settings. You can also adjust the settings as needed.

    4. Click OK and configure the username and password information.

      Parameter

      Description

      Username

      The AccessKey ID for your Alibaba Cloud account. For more information, see Create an access key.

      Password

      The AccessKey Secret for your Alibaba Cloud account. For more information, see Create an access key.

    5. Click Connection. The Navigator page displays the tables in the database. Select the tables that you want to load into Power BI Desktop, such as public.lineitem and public.orders.

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

      After you import data into Power BI Desktop, you can run queries and create reports or dashboards. 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 a Power BI gateway. After you publish a report from Power BI Desktop to Power BI Service, you must install and configure a Power BI gateway.

  1. Publish a data report from Power BI Desktop

    1. Open your data report in Power BI Desktop and click Publish in the ribbon.

    2. In the Publish to Power BI dialog box, select the workspace to which you want to publish.

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

  2. Install a Power BI gateway

    Power BI Service communicates with local servers through a Power BI gateway. Install a Power BI gateway. For more information, see the official Power BI documentation. During installation, note the following:

    • The Power BI gateway does not need to be installed on the same machine as Power BI Desktop.

    • Make sure that the server that runs the Power BI gateway has a stable network connection and can access the Hologres data source.

    • Install Npgsql 4.0.10. To download the installer, click Download Npgsql 4.0.10. During installation, make sure to select Npgsql GAC Installation. After the installation is complete, you must restart the Power BI gateway.

  3. Configure the Power BI gateway

    After installing the Power BI gateway, start it. For detailed steps, see Microsoft's official gateway documentation.

  4. Configure the Power BI Service data source

    1. Log on to Power BI Service. In the upper-right corner, click the settings 管理 icon and select Manage gateways from the list.

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

      The data source settings must be consistent with the connection information in Power BI Desktop. The following table describes the parameters.

      Parameter

      Description

      Data source name

      Enter a name for the new data source.

      Data source type

      Select PostgreSQL from the drop-down list.

      Server

      The network address and port for your Hologres instance.

      Log on to the Hologres console. In the left-side navigation pane, click Instances. Click the target instance. On the Instance Details page, you can find the network address and port in the Network Information section.

      Important

      Ensure that the network address and port are accessible from the environment where Power BI Service is running. Otherwise, the connection will fail.

      Database

      The name of the database that you created in Hologres.

      Username

      The AccessKey ID for your Alibaba Cloud account. For more information, see Create an access key.

      Password

      The AccessKey Secret for your Alibaba Cloud account. For more information, see Create an access key.

      Advanced settings

      In the advanced settings, you can configure the following items:

      • For 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.

      • You can keep the default value for Privacy Level setting for this data source.

    3. Click Add. A message will confirm a successful connection.

  5. Configure the report gateway

    After you publish a data report to Power BI Service, you must configure the report gateway to view and interact with your report.

    1. Log on to Power BI Service. In the left-side navigation pane, go to the My workspace page.

    2. Next to the target dataset, click the More options 设置 icon and select Settings.

    3. On the settings page, expand Gateway connection and turn on the Use a data gateway toggle. Select the corresponding gateway and map it to the data source.

    4. Click Application. A confirmation message appears, indicating that the gateway is successfully configured.

    After you complete these steps, you can view and interact with your report in Power BI Service.

Publish to Power BI Report Server

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

  1. Install Power BI Report Server

    Install Power BI Report Server. For more information, see the official Power BI Report Server documentation. During installation, note the following:

    • Make sure that the server that runs Power BI Report Server has a stable network connection and can access the Hologres data source.

    • Install Npgsql 4.0.10. To download the installer, click Download Npgsql 4.0.10. During installation, make sure to select Npgsql GAC Installation. After the installation is complete, you must restart Power BI Report Server.

  2. Publish a report from Power BI Desktop

    1. Open your data report in Power BI Desktop. In the ribbon, choose File > Save As > Power BI Report Server.

    2. In the dialog box that appears, enter the report 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 successfully, you can click the link to access it on Power BI Report Server.

  3. Configure the Power BI Report Server data source

    1. Log on to Power BI Report Server. To the right of the target data source report, click the More options 报表 icon and select Manage.

    2. On the data source page, configure the parameters.

      The data source settings must be consistent with the connection information in Power BI Desktop. The following table describes the parameters.

      Parameter

      Description

      Type

      Select PostgreSQL from the drop-down list.

      Connection string

      This is automatically generated.

      Authentication type

      Select basic authentication from the drop-down list.

      Username

      The AccessKey ID for your Alibaba Cloud account. For more information, see Create an access key.

      Password

      The AccessKey Secret for your Alibaba Cloud account. For more information, see Create an access key.

    3. Click Test Connection. A message will confirm a successful connection.

    After you complete these steps, you can view and interact with your report in Power BI Report Server.

Encryption in transit

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

Previous：MetabaseNext：Qlik