All Products
Search
Document Center

AnalyticDB for PostgreSQL:Use Power BI to connect to AnalyticDB for PostgreSQL

Last Updated:Aug 09, 2023

Power BI is a uniform, extendable self-service and enterprise business intelligence ( BI) platform. You can use Power BI to connect to data sources for data modeling, visualized analysis, and personalized reporting. This topic describes how to use Power BI to connect to AnalyticDB for PostgreSQL.

Prerequisites

  • A Power BI client is downloaded and installed. For more information, see Microsoft Power BI.

  • An AnalyticDB for PostgreSQL instance is created, and a privileged account is created for the instance. For more information, see Create an instance and Create a database account.

  • A public endpoint is obtained for the AnalyticDB for PostgreSQL instance. For more information, see Manage public endpoints.

  • The IP address of the device where the Power BI client is installed is added to an IP address whitelist of the AnalyticDB for PostgreSQL instance. For more information, see Configure an IP address whitelist.

  • A table is created in the AnalyticDB for PostgreSQL instance, and data is inserted into the table. In this example, the customer table is used.

Procedure

  1. Start the Power BI client.

  2. In the top navigation bar, click Home. Choose Get data > More.

  3. In the Get Data dialog box, choose Database > PostgreSQL database and click Connect.

  4. In the PostgreSQL database dialog box, specify the database connection information and click OK.

    Note

    If the connection fails, check whether the connection information that you specified is correct and whether the IP address of the device where the Power BI client is installed has been added to an IP address whitelist of the AnalyticDB for PostgreSQL instance. Then, try again.

    Parameter

    Description

    Server

    The public endpoint and port number that are used to connect to the AnalyticDB for PostgreSQL instance.

    Example: gp-bp152460513z****o-master.gpdb.rds.aliyuncs.com:5432.

    Database

    The name of the AnalyticDB for PostgreSQL database.

    Data Connection mode

    Valid values:

    • Import: copies data to Power BI.

    • DirectQuery: dynamically requests data from the connected data source.

    Note

    DirectQuery is more suitable in the following situations:

    • The data source contains a large amount of data.

    • Quasi-real-time data is required.

  5. In the dialog box that appears, specify your username and password credentials and click Connect.

    Parameter

    Description

    User name

    The database account that is used to connect to the AnalyticDB for PostgreSQL instance.

    Password

    The password of the database account.

    Important

    If the Encryption Support message appears, click OK.

  6. In the Navigator dialog box, select the customer table that you want to import and click Load.

  7. In the Visualizations pane of the Report View tab, select a chart type. In this example, Clustered Column Chart is used.

  8. In the Fields pane, select the columns that are contained in the customer table. In this example, age and login_time are used.

  9. Click the 保存.png icon in the upper-left corner to save the report.

  10. (Optional) On the Home page, click Publish in the upper-right corner.

    Note

    If necessary, sign in to the Power BI service.

References

Power BI documentation