Setting up Power BI

Connect Power BI Desktop to the PEARS data mart using the built-in PostgreSQL connector.

The PEARS data mart can be used in combination with Power BI as follows.

Prerequisites

1

Install Power BI Desktop

Visit https://www.microsoft.com/en-us/power-platform/products/power-bi/desktop to install Power BI Desktop, if you haven't already.

TIP: Power BI Desktop is also available in the Microsoft Store on Windows computers.

2

Confirm firewall access

Check with the PEARS Client Success team to ensure the IP address of the computer or server from which you will be connecting has been added to the data mart firewall.

3

Confirm network access

Check with your IT staff to ensure incoming and outbound traffic on the data mart port is allowed to the computer or server from which you will be connecting.

4

Obtain credentials

Obtain the host, port, database, user, and password from the PEARS Client Success team.

Setup

Power BI can connect directly to the data mart using the PostgreSQL provider, npgsql. This provider and its necessary drivers are installed with Power BI Desktop and Power BI Gateway. However, before a trusted connection can be established with the data mart, Amazon's RDS certificates must be imported on each device into the Trusted Root Certification Authorities store.

1

Download the certificate bundle

Download a certificate bundle from Amazon. You will either need the specific bundle for the us-east-1 region or the AWS global bundle.

• Global (any AWS region): https://truststore.pki.rds.amazonaws.com/global/global-bundle.p7b

• US East (N. Virginia): https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.p7b

2

Open Certificate Manager

Open the Certificate Manager on Windows.

• Administrators can import certificates for the entire local machine by going to Manage computer certificates in the Control Panel.

• Otherwise, any user can import into their own stores by going to Manage user certificates in the Control Panel.

3

Start the Import wizard

Right-click on the folder named Trusted Root Certification Authorities, then select All Tasks → Import….

4

Browse for the certificate file

Click through the wizard, browsing for the file downloaded in the first step when prompted for a file name.

NOTE: The default file type in the dialog is X.509 Certificate. You will need to change this to PKCS #7 Certificates or All Files to find the certificate file you downloaded.

5

Confirm the certificate store

Continue through the wizard, confirming the certificates will be placed in the certificate store Trusted Root Certification Authorities.

6

Verify the import

Once finished, you should see a message indicating success, such as "The import was successful."

Usage

Once you are ready to connect in Power BI, follow the link Get data from another source on the startup page, or from the toolbar: Get Data → More…. In the Get Data window that's presented, select PostgreSQL database as the connector. You will be prompted for the following:

• Server — Use the format <host name>:<port>.

  • The port is provided in this field along with the host name, separated by a colon (:) between the two.

• Database — The data mart database name provided by the PEARS Client Success team.