Paginated reports essentially began to flourish under SQL Server Reporting Services (SSRS) as part of Microsoft’s previous BI stack and are mainly used for operational reporting, often delivered as PDFs emailed to users on a daily or weekly schedule.

Whereas Power BI is leveraged as a visual analytics tool to enable rapid, ad-hoc data exploration focused on visualizations. This blog will tell you how to connect Azure PostgreSQL in Power BI paginated reports and will highlight the limitation of paginated reports to connect to Azure PostgreSQL and solution for that limitation!

Limitation:

When we connect to paginated report, we will see only few data sources by which we can connect and     create the reports.

Data Sources which support Paginated Report:

  1. Azure Analysis Services
  2. Azure Synapse Analytics
  3. Common Data Service (Preview)
  4. Enter Data
  5. Microsoft SQL Server
  6. Microsoft SQL Server Analysis Services
  7. Oracle Database
  8. Teradata

So, it supports only few data sources to connect and can extract the data into report builder to create reports, if you have a need of connecting to other data source like PostgreSQL Server then below are the 2 ways that we can follow.

Connecting PostgreSQL in Power BI with ODBC

With built-in support for ODBC on Microsoft Windows, ODBC provides self-service integration with self-service analytics tools, such as Microsoft Power BI.

To set up a new ODBC data source for PostgreSQL:

  1. Install the latest PostgreSQL ODBC drivers

Power BI Paginated Reports with PostgreSQL

Power BI Paginated Report

2. Open the 64bit ODBC Administrator : Windows 10(64bit version) supports 32 and 64 sources – always use the 64bit ODBC Administrator.

  • Search for ODBC and choose ODBC Data Sources (64bit):

 

Power BI Azure PostgreSQL

  • Right Click and choose Run as Administrator.

 

Power BI Azure PostgreSQL DB

3. Open the User DSN tab and click Add

Power BI Paginated Reports with PostgreSQL

 

4. Choose the latest PostgreSQL ODBC driver.

Power BI Paginated Reports PostgreSQL

5. Enter the ODBC credentials:

  • Use any Data Source and Description you prefer.
  • Enter PostgreSQL Server details in Server field.
  • Enter the port number (Default: 5432).
  • Enter Username and Password.
  • Enter database details.

Azure PostgreSQL

6. Test the data source connection

  • Click Test:

Azure PostgreSQL

  • And if it works you are good to go

Power BI Paginated Reports with PostgreSQL

  • Click on Save to create the ODBC data source:

Power BI Paginated Reports PostgreSQL

7. Power BI Desktop

  • Connecting PostgreSQL with ODBC from Get Data.
  • In DSN à Select Data Source which you have add in ODBC Data Source in 64bit which is (eg : PostgreSQL35W)

 

Power BI Azure PostgreSQL

8. Extract required tables from Azure PostgreSQL Database and publish those datasets into workspace in Power BI Service.

9. From Power BI Report Builder add the connection which is “Power BI Dataset Connection” to connect Power BI Dataset.

Power BI Azure PostgreSQL

10. Add a new dataset and select the data source and click on query designer to write DAX Query to extract the data from Power BI Dataset.

Power BI Azure PostgreSQL

11. After creating the data set you can create the reports from the datasets.

12. Publish this Paginated Report into Power BI Workspace in Power BI Service.

Limitation:

  • Power BI does not support to get data from ODBC data source in a live connection or DirectQuery mode.

Disadvantage:

  • Power BI Data Gateway is required to configure and connect to ODBC data source if the data source supports Platform as a Service (PAAS) also.

 

Azure database for PostgreSQL

If your data source supports Platform as a Service (PAAS) then it is recommended to use Azure Database for PostgreSQL which supports DirectQuery mode to get live data into PowerBI Repots.

  • Data Gateway is not required for both (Import/DrectQuery) mode in Azure database for PostgreSQL because it support Platform as a Service (PAAS).

Power BI Paginated Reports PostgreSQL

  • Extract required tables from Azure PostgreSQL Database and publish those datasets into workspace in Power BI Service.

Power BI Paginated Reports PostgreSQL

 

  • From Power BI Report Builder add the connection which is “Power BI Dataset Connection” to connect Power BI Dataset.

Power BI Azure PostgreSQL

 

  • Add a new dataset and select the data source and click on query designer to write DAX Query to extract the data from Power BI Dataset.

Power BI Azure PostgreSQL

  • After creating the data set you can create the reports from the datasets.
  • Publish this Paginated Report into Power BI Workspace in Power BI Service.

           Note : Workspace needs to be Premium not Power BI Pro License.

Power BI Paginated Reports PostgreSQL

If you have a requirement to create a paginated report (or) migrate SSRS reports and if the data source is Azure PostgreSQL, you can follow 2nd scenario. If the data source is on-premises PostgreSQL then you can follow 1st scenario. You can effortlessly share reports based on ODBC data sources with other users in your organization using a Power BI Report Server through the above scenarios.

 

Nagoor
Nagoor
Sr. Software Engineer