Connect Heroku Postgres to PowerBI

Question

I'm looking to connect Heroku PostgresSQL directly to PowerBI Desktop. Looks like there are several products on the market that will replicate from Heroku to Azure, but hoping to connect directly to PowerBI. Has anyone been able to accomplish this?

If there is an alternative DB option to use with the Salesforce Connect add-on that goes directly to Azure that would also be acceptable.

Answer 1

I had a task for my company to get Power BI service (app.powerbi.com) connected to our Heroku Hosted Postgres Database (HHPGD, for future use). Furthermore, we wanted to get scheduled refresh working.

Failures: I used to use the ODBC method to get the HHPGD connected on Power BI Desktop, and that worked, but broke for Power BI service and credentials couldn't be edited in the service... thus making scheduled refresh (or any non-desktop refresh-publish) impossible! I've also used the Npsql approach before, but it is too complicated and randomly stopped working one day.

Then I found the easy solution that just worked; A public beta Heroku feature called Enhanced Certificates.

Requirements:

• Heroku CLI installed
• Power BI Desktop installed
• Permission to publish models/reports from Power BI Desktop to your workspace
• Permission to edit credentials in app.powerbi.com on your workspace

Setting up the Enhanced Certificate:

1. Follow the Heroku official Documentation for reference.
2. Install the Heroku Data CLI Plugin
   1. heroku plugins:install data
3. Add Enhanced Certificate to Existing HHPGD
   1. heroku data:labs:enable enhanced-certificates -a example-app --addon=ADDON_NAME (example ADDON_NAME: postgresql-colorful-12345)
4. Display Enhanced Certificates Status
   1. heroku data:labs:list ADDON_NAME -a example-app
5. Wait until it finishes setting up. You can keep checking the status with the previous command. Wait an extra 5 minutes after it says it's finished as it didn't immediately work for me.
   1. Important! It takes about 15-30 minutes in total for this. Time to twiddle some thumbs.

Steps To Connect Power BI Desktop to HHPGD:

1. PowerBI Desktop: "Get data" > "PostgreSQL database" > Configure > "OK"
   1. "Server" example: ec2-xx-xxx-xx-xx.xxx.amazon.com
   2. "Database" example: abcdefg
   3. "Data Connectivity mode" > "DirectQuery" (that's what we use, haven't verified for "Import")
   4. SKIP the "Advanced options"... don't even touch them.
2. Input your READ-ONLY Database User name and Password. You don't need to use READ-ONLY role, but it's best practice. Grant the minimum number of privileges needed. > "Connect
   1. Don't mess with "Select which level to apply these settings to" unless you know what you're doing.
3. Select the data you want to bring into the Report.
4. Refresh.
5. Publish Report.

Steps To Schedule Refresh on Power BI Service:

1. Navigate to app.powerbi.com and login.
2. Click "Workspaces" on left panel > click the workspace you want > hover over the "Semantic model" you want to schedule, NOT the "Report" (see the "Type" column) > click "Settings"
3. Expand "Refresh" > Toggle On > Select Frequency > "Apply"
4. Expand "Data source credentials" > "Edit credentials" > Configure > "Sign in"
   1. "Authentication method" := "Basic"
   2. Set your READ-ONLY Database "User name" and "Password"
   3. Set your privacy level to as secure as you can get away with.

Voila! Congratulations on finding this post and benefiting from my trial and error to make this solution.

Concerns:

• Security. If you are using a Private Space or a Shield database, consider using MTLS