Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Unable to Connect to VIEW on Amazon Redshift

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.

Status: New
Comments
v-jiascu-msft
Employee

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

a_garnett
Frequent Visitor

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.

Anonymous
Not applicable

I am also having this issue.  Same error message.

Anonymous
Not applicable

 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.

Anonymous
Not applicable

  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.  

Anonymous
Not applicable

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

a_garnett
Frequent Visitor

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/