SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, tutorials, tools and downloads for SQL Database Programmers, Developers, Windows and Office Users


Connect Amazon Redshift from Power BI Desktop Report

In this short Power BI tutorial I want to show how BI Business Intelligence developers can connect to Amazon Redshift data source to fetch data for their reports. In this tutorial I will be using Power BI Desktop to connect Redshift database for sample report.

Microsoft Power BI Desktop is an easy to use and powerful tool to visualize data

Launch Power BI Desktop tool.

Power BI Desktop reporting tool

On the initial screen, click on Get data to start the wizard for identifying and reading the data from its source.

get data for Power BI Desktop report

Click on Databases from the list of possible data categories.
On Databases section select Amazon Redshift which is the target data source for this tutorial's report data source.

Amazon Redshift database as Power BI report data source

After you select Amazon Redshift as data source, click Connect button to continue with connection properties

Type server host and database name.
Server host is the DNS name or the IP address of the master node of the Amazon Redshift cluster
Database name is the Redshift database name.

Amazon Redshift connection properties for Power BI report

Click OK to continue with credentials required for a successfull connection.

Type Redshift database user name and password for the user to connect from Power BI report.

Redshift database user credentials for connection from Power BI report

Press Connect button to test Amazon Redshift database connection within Microsoft Power BI report tool

It is possible that the report developers can experience problems with Amazon Redshift connections in Power BI Desktop tool.
This error is also mentioned and the solution is described at Power BI Community portal.

The solution for the error occurred at Amazon Redshift connection can be summarized with below steps that we will take soon in this tutorial:
1) Open Data Source Settings for Amazon Redshift
2) Edit Permissions for Redshift connection for encryption and privacy level options

On Power BI main menu, follow the menu selections "File > Options and Settings > Data Source Settings" as seen in screenshot below

Redshift data source settings and options for Power BI report

Data Source Settings menu options will display the list of connections we have defined previously using Power BI Desktop tool.
Select the data source connection to Amazon Redshift which failed to connect previously in this tutorial.

permissions setting for Amazon Redshift data source connection

Click Edit Permissions... button.

Clear "Encrypt connections" checkbox.
Set "Privacy Level" to Public from dropdown list.

Redshift data source permissions for Power BI connection

Click OK and Close button on subsequent screen to close Data Source Settings window

Then to connect to Amazon Redshift database as the data source for your Power BI Desktop report and read the data, repeat the steps to get data again by following menu options "File > Get Data > More..."

get data for Power BI report from data source

I will not continue with the repeating steps afterwards in this Power BI tutorial. This time using the data source connection which troubled the report developer to connect to Amazon Redshift database will be successfull and the report developer will be able to access to database tables and views for his/her report visualization




Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.