Data Mart: Setting up Power BI

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

Prerequisites

  1. Visit https://www.microsoft.com/en-us/power-platform/products/power-bi/desktop to install the Power BI Desktop, if you haven’t already.
    • Power BI Desktop is also available in the Microsoft Store on Windows computers.
  2. 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. 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 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 a certificate bundle from Amazon. You will either need the specific bundle for the us-east-1 region or the AWS global bundle.
  2. 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. Right-click on the folder named Trusted Root Certification Authorities, select All Tasks → Import….
  4. Click through the wizard, browsing for the file downloaded in step 1 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 downloaded.
  5. Continue through the wizard, confirming the certificates will be placed in the certificate store Trusted Root Certification Authorities.
  6. 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 the PostgreSQL database as the connector. You will be prompted for the following.
  • Server – Use the format “<host name>:<port>”
    • Note the port is provided in this field along with the host name, separated by a colon (:) between the two.
  • Database

After clicking OK, you will be prompted for the user and password on your first connection.