Unable to Connect to VIEW on Amazon Redshift

Status: New
by a_garnett Visitor on ‎12-11-2017 03:10 PM

I am using the Amazon Redshift connector in Power BI. While I am able to connect to tables, I am not able to connect to views. I receieve the following error message:

 

DataSource.Error: The table has no visible columns and cannot be queried.

 

I have not had any difficulty connecting to this view using other client software. Likewise, I have not had any difficulty connecting Power BI to views on other database systems (such as Oracle). This seems to be a limitation in Power BI's connection to Amazon Redshift. In general I expect to be able to connect to a view the same way I connect to a table.

Comments
by Community Support Team
‎12-12-2017 12:27 AM - edited ‎12-12-2017 12:32 AM

Hi @a_garnett,

 

1. Did the account that you used to connect to Redshift have enough permission?

2. Did all the views have the same issue?

3. I would suggest filing a support ticket here.

create ATicket

 

Best Regards,

Dale

by a_garnett Visitor
on ‎12-12-2017 07:18 AM

1. Yes I have permission. I can query views with other clients using the same credentials.

2. Yes all views have the same issue

3. I will also submit a support ticket. I posted it here because I was guided to report issues on this forum.

 

I'd be interested to hear if others are having the same issue querying Redshift views using Power BI.

by st3vemartin Frequent Visitor
on ‎02-28-2018 02:39 PM

I am also having this issue.  Same error message.

by davidbrown100 Regular Visitor
on ‎06-19-2018 01:33 AM

 i am also having this issue.

it is very frustrating because i can find no way of querying redshift other than by connecting to an entire table.  The problem with this is that when your table contains millions of records, the power bi desktop refresh becomes unworkable due to the time taken to download the data.

i really need a solution to be able to query subsets of data in redshift.

by st3vemartin Frequent Visitor
on ‎06-19-2018 05:43 AM

  Hey davidbrown100,

 

I doubt Microsoft will address this issue anytime soon - here are workarounds.

 

My solution - right now I am moving data into a "datawarehouse" schema to manage the table import process - using Fishtown Analytics dbt tool as an ETL tool.  And it works well after installing it - basically is a select into type ETL to summarize my data before import.

 

Quicker solution - I would look into wrapping up SQL in the m query language - under edit queries, connect to a table - then under advanced editor go in and paste in SQL based on code found via the internet.  Probably a trial and error process ... for me at least.

 

I would NOT do direct Query - the search on slicers can't find jack crap in redshift based on case sensitivity.  

by davidbrown100 Regular Visitor
on ‎06-19-2018 05:53 AM

i have since tried with ODBC which does work successfully, not tried large data volumes yet.

The only "problem" is that i now need to set up windows vm somewhere to host a data gateway, to allow it wto work on the Power BI Service

by a_garnett Visitor
on ‎06-19-2018 05:54 AM

Since my original post, I have identified a solution. Apparently it is possible to query Redshift views if the "GRANT SELECT" permission is set for your user. For some reason this is not required when querying with other clients and visualization tools.

 

 

This does require that you create a view on your Redshift instance, naturally, but that is a fairly straightforward process. There are other advantages of using views as opposed to custom SQL statements for any type of database source. Chiefly, it enables query folding. For others, see Marco Russo's article: https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

Idea Statuses