2 Dec’15

BIPower BI Connectivity to PostgreSQL

Prerequisite:

Power BI Desktop Tool, PostgreSQL Database, pgAdmin III, Visual Studio 2008 or higher

Introduction

Power BI supports connectivity to different databases such as SQL Server, MySQL, Oracle and many more (list of all supported databases given here ). By providing necessary details such as server name, database name, username & password, you can easily connect to database and start exploring data using different visualizations in the form of reports/dashboards.

This article will walk you through the necessary steps required to configure in your system before establishing connection of Power BI Desktop Tool to PostgreSQL Database.

After installation of PostgreSQL database in your machine, if you try connecting Power BI Desktop Tool to PostgreSQL Database, you will get following error as shown in the screenshot below:

1

 

Following are the steps required to connect Power BI Desktop Tool to PostgreSQL Database:

  1. Setup PostgreSQL Client
  2. Connect Power BI Desktop Tool to PostgreSQL

Steps to set up PostgreSQL Client:

Step 1: Download and unzip the Npgsql package from the link mentioned here

You will find two DLL files named as Npgsql.dll and Mono.Security.dll as shown in screen capture below:

2

 

Step 2: You need to have Visual Studio 2008 or later. Open visual studio command prompt using administrative rights as shown in the screen capture below:

3

 

Step 3: Locate and Open machine.config from the location given below:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config \machine.config 

.Net framework and Npgsql version might vary depending on the version of installation. Add the following below <DbProviderFactories> tag:

<add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>

Find the screenshot given below for this step:

4

 

Step 4: Now in your visual studio command prompt, use the following command to successfully move DLL files to C:\windows\assembly

gacutil -I "C:\Users\ajagd_000\Downloads\Npgsql2.0.1-bin-ms.net3.5sp1\Npgsql2.0.1-bin-ms.net3.5sp1\bin\Npgsql.dll"

Where C:\Users\ajagd_000\Downloads is the file location for the Npgsql package downloaded.

Similar command you can run for Mono.Security.dll file

gacutil -I "C:\Users\ajagd_000\Downloads\Npgsql2.0.1-bin-ms.net3.5sp1\Npgsql2.0.1-bin-ms.net3.5sp1\bin\Mono.Security.dll"

Step 5: Once this is done, you can now try connecting Power BI to PostgreSQL. However you will notice below error

5

 

Once you restart your machine, you can now connect to PostgreSQL and start playing with your data.
 

Steps for connecting Power BI Desktop Tool to PostgreSQL:

Step 1: Open pgAdmin III interface to create database, table and few records to display in your report as shown in screen capture below

6

 

Step 2: Open Power BI Desktop Tool and click ‘Get Data’ and select PostgreSQL Database as shown below

7

 

Step 3: Enter server name (in our case it is localhost), database name, username and password as shown in below screen capture

8

 

Step 4: Select required table from database and click load as shown in screen capture below

9

 

Step 5: Once data is loaded in model, you can start playing around with the data and create beautiful visualizations/reports as shown in image below and later publish it to Power BI Online Service.

10

 

Written by

Team Member

CloudFronts

One thought on “Power BI Connectivity to PostgreSQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more