{"id":4261,"date":"2021-06-11T09:11:00","date_gmt":"2021-06-11T09:11:00","guid":{"rendered":"https:\/\/viewmyprojects.com\/winwirewp\/?p=4261"},"modified":"2023-11-29T11:17:03","modified_gmt":"2023-11-29T11:17:03","slug":"publish-power-bi-paginated-reports-with-azure-postgresql","status":"publish","type":"post","link":"https:\/\/viewmyprojects.com\/winwirewp\/blog\/publish-power-bi-paginated-reports-with-azure-postgresql\/","title":{"rendered":"Publish Power BI Paginated Reports with Azure PostgreSQL"},"content":{"rendered":"\n<p>Paginated reports essentially began to flourish under SQL Server Reporting Services (SSRS) as part of Microsoft\u2019s previous BI stack and are mainly used for operational reporting, often delivered as PDFs emailed to users on a daily or weekly schedule.<\/p>\n\n\n\n<p>Whereas&nbsp;<a href=\"https:\/\/viewmyprojects.com\/winwirewp\/power-bi-performance-optimization\/\" target=\"_blank\" rel=\"noreferrer noopener\">Power BI<\/a>&nbsp;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&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/paginated-reports\/paginated-reports-data-sources\" target=\"_blank\" rel=\"noreferrer noopener\">paginated reports<\/a>&nbsp;and will highlight the limitation of paginated reports to connect to Azure PostgreSQL and solution for that limitation!<\/p>\n\n\n\n<p><strong>Limitation<\/strong>:<\/p>\n\n\n\n<p>When we connect to paginated report, we will see only few data sources by which we can connect and create the reports.<\/p>\n\n\n\n<p><strong>Data Sources which support Paginated Report:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Azure Analysis Services<\/li>\n\n\n\n<li>Azure Synapse Analytics<\/li>\n\n\n\n<li>Common Data Service (Preview)<\/li>\n\n\n\n<li>Enter Data<\/li>\n\n\n\n<li>Microsoft SQL Server<\/li>\n\n\n\n<li>Microsoft SQL Server Analysis Services<\/li>\n\n\n\n<li>Oracle Database<\/li>\n\n\n\n<li>Teradata<\/li>\n<\/ol>\n\n\n\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step by Step process to create Power BI Paginated Reports Azure PostgreSQL<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Connecting PostgreSQL in Power BI with ODBC<\/h3>\n\n\n\n<p>With built-in support for ODBC on Microsoft Windows, ODBC provides self-service integration with self-service analytics tools, such as Microsoft Power BI.<\/p>\n\n\n\n<p>To set up a new ODBC data source for PostgreSQL:<\/p>\n\n\n\n<p> <strong>1. Install the latest PostgreSQL ODBC drivers<\/strong><\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Download the latest 64bit driver installer from \u201c<a href=\"https:\/\/www.postgresql.org\/ftp\/odbc\/versions\/msi\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.postgresql.org\/ftp\/odbc\/versions\/msi\/<\/a>\u201d<\/li>\n\n\n\n<li>Unzip the file and run the psqlodbc-setup.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture1-1-4.webp\" alt=\"\" class=\"wp-image-4264\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture2-4.webp\" alt=\"Power BI Paginated Reports Azure PostgreSQL\" class=\"wp-image-4265\"\/><figcaption class=\"wp-element-caption\">Power BI Paginated Reports Azure PostgreSQL<\/figcaption><\/figure>\n\n\n\n<p><strong>2. Open the 64bit ODBC Administrator :<\/strong>&nbsp;Windows 10(64bit version) supports 32 and 64 sources \u2013 always use the 64bit ODBC Administrator.<\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Search for ODBC and choose ODBC Data Sources (64bit):<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture3-300x97-1.webp\" alt=\"Power BI Paginated Reports \" class=\"wp-image-4267\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Right Click and choose Run as Administrator.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture4-300x205-1.webp\" alt=\"Power BI Paginated Reports Azure PostgreSQL\" class=\"wp-image-4268\"\/><\/figure>\n\n\n\n<p><strong>3. Open the User DSN tab and click Add<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture5-300x211-1.webp\" alt=\"Power BI Paginated Reports Azure PostgreSQL\" class=\"wp-image-4269\"\/><\/figure>\n\n\n\n<p><strong>4. Choose the latest PostgreSQL ODBC driver.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture6-300x216-1.webp\" alt=\"postgres sql\" class=\"wp-image-4270\"\/><\/figure>\n\n\n\n<p><strong>5. Enter the ODBC credentials:<\/strong><\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Use any Data Source and Description you prefer.<\/li>\n\n\n\n<li>Enter PostgreSQL Server details in Server field.<\/li>\n\n\n\n<li>Enter the port number (Default: 5432).<\/li>\n\n\n\n<li>Enter Username and Password.<\/li>\n\n\n\n<li>Enter database details.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture7-1-1.webp\" alt=\"power bi\" class=\"wp-image-4271\"\/><\/figure>\n\n\n\n<p><strong>6. Test the data source connection<\/strong><\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Click Test:<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture8-300x61-1.webp\" alt=\"\" class=\"wp-image-4272\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>And if it works you are good to go<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture9-2.webp\" alt=\"\" class=\"wp-image-4273\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Click on Save to create the ODBC data source:<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture10-2.webp\" alt=\"Power BI Paginated Reports Azure PostgreSQL\" class=\"wp-image-4274\"\/><\/figure>\n\n\n\n<p><strong>7. Power BI Desktop<\/strong><\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Connecting PostgreSQL with ODBC from Get Data.<\/li>\n\n\n\n<li>In DSN \u00e0 Select Data Source which you have add in ODBC Data Source in 64bit which is (eg : PostgreSQL35W)<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture11-2.webp\" alt=\"power bi desktop\" class=\"wp-image-4275\"\/><\/figure>\n\n\n\n<p><strong>8. Extract required tables from Azure PostgreSQL Database<\/strong>&nbsp;and publish those datasets into workspace in Power BI Service.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture17-1.webp\" alt=\"power bi desktop\" class=\"wp-image-4277\"\/><\/figure>\n\n\n\n<p><strong>9. From Power BI Report Builder<\/strong>&nbsp;add the connection which is \u201cPower BI Dataset Connection\u201d to connect Power BI Dataset.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture18-2.webp\" alt=\"power bi database connection\" class=\"wp-image-4278\"\/><\/figure>\n\n\n\n<p><strong>10. Add a new dataset<\/strong>&nbsp;and select the data source and click on query designer to write DAX Query to extract the data from Power BI Dataset.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture19-1-1.webp\" alt=\"datasets\" class=\"wp-image-4279\"\/><\/figure>\n\n\n\n<p>11. After creating the data set you can create the&nbsp;<strong>reports from the datasets.<\/strong><\/p>\n\n\n\n<p>12.&nbsp;<strong>Publish this Paginated Report<\/strong>&nbsp;into Power BI Workspace in Power BI Service.<\/p>\n\n\n\n<p><strong><u>Limitation:<\/u><\/strong><\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Power BI&nbsp;does not support to&nbsp;get data from ODBC data&nbsp;source in a&nbsp;live connection&nbsp;or DirectQuery mode.<\/li>\n<\/ul>\n\n\n\n<p><strong><u>Disadvantage:<\/u><\/strong><\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>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.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Azure Database for PostgreSQL<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Data Gateway is not required for both (Import\/DrectQuery) mode in Azure database for PostgreSQL because it support Platform as a Service (PAAS).<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture16-1.webp\" alt=\"azure database for postgresSQL\" class=\"wp-image-4280\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>Extract required tables from Azure PostgreSQL Database and publish those datasets into workspace in Power BI Service.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture17-2.webp\" alt=\"power bi\" class=\"wp-image-4281\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>From Power BI Report Builder add the connection which is \u201cPower BI Dataset Connection\u201d to connect Power BI Dataset.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture18-1.webp\" alt=\"power bi dataset\" class=\"wp-image-4282\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>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.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture19-1-1.webp\" alt=\"Power BI Paginated Reports Azure PostgreSQL\" class=\"wp-image-4283\"\/><\/figure>\n\n\n\n<ul class=\"blog-detail-list wp-block-list\">\n<li>After creating the data set you can create the reports from the datasets.<\/li>\n\n\n\n<li>Publish this Paginated Report into Power BI Workspace in Power BI Service.<\/li>\n<\/ul>\n\n\n\n<p><strong>Note<\/strong>&nbsp;: Workspace needs to be Premium not Power BI Pro License.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/11\/Picture20-1.webp\" alt=\"Power BI Paginated Reports Azure PostgreSQL\" class=\"wp-image-4284\"\/><\/figure>\n\n\n\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Paginated reports essentially began to flourish under SQL Server Reporting Services (SSRS) as part of Microsoft\u2019s previous BI stack and are mainly used for operational reporting, often delivered as PDFs emailed to users on a daily or weekly schedule. Whereas&nbsp;Power BI&nbsp;is leveraged as a visual analytics tool to enable rapid, ad-hoc data exploration focused on&hellip; <a class=\"more-link\" href=\"https:\/\/viewmyprojects.com\/winwirewp\/blog\/publish-power-bi-paginated-reports-with-azure-postgresql\/\">Continue reading <span class=\"screen-reader-text\">Publish Power BI Paginated Reports with Azure PostgreSQL<\/span><\/a><\/p>\n","protected":false},"author":17,"featured_media":16404,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[23,60,33,59],"tags":[],"class_list":["post-4261","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-analytics","category-data-and-ai-blogs","category-cloud","category-blogs","entry"],"acf":[],"featured_image_src":"https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1.webp","author_info":{"display_name":"Nagoor","author_link":"https:\/\/viewmyprojects.com\/winwirewp\/author\/nagoor\/"},"views":4701,"uagb_featured_image_src":{"full":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1.webp",800,440,false],"thumbnail":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1-150x150.webp",150,150,true],"medium":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1-300x165.webp",300,165,true],"medium_large":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1-768x422.webp",750,412,true],"large":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1.webp",750,413,false],"1536x1536":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1.webp",800,440,false],"2048x2048":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1.webp",800,440,false],"post-thumbnail":["https:\/\/viewmyprojects.com\/winwirewp\/wp-content\/uploads\/2023\/10\/Publish-Power-BI-Paginated-Reports-with-Azure-PostgreSQL-1.webp",800,440,false]},"uagb_author_info":{"display_name":"Nagoor","author_link":"https:\/\/viewmyprojects.com\/winwirewp\/author\/nagoor\/"},"uagb_comment_info":0,"uagb_excerpt":"Paginated reports essentially began to flourish under SQL Server Reporting Services (SSRS) as part of Microsoft\u2019s previous BI stack and are mainly used for operational reporting, often delivered as PDFs emailed to users on a daily or weekly schedule. Whereas&nbsp;Power BI&nbsp;is leveraged as a visual analytics tool to enable rapid, ad-hoc data exploration focused on&hellip;&hellip;","_links":{"self":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts\/4261","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/comments?post=4261"}],"version-history":[{"count":2,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts\/4261\/revisions"}],"predecessor-version":[{"id":17982,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/posts\/4261\/revisions\/17982"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/media\/16404"}],"wp:attachment":[{"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/media?parent=4261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/categories?post=4261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/viewmyprojects.com\/winwirewp\/wp-json\/wp\/v2\/tags?post=4261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}