Power BI Service can't refresh my Redshift data source

[kevhav]

I've been having a weird issue refreshing a Redshift data source from the Power BI Service.

For troubleshooting, I created a new PBIX file with Power BI Desktop, from scratch. It has just one query, to get the contents of a single, small table from my Redshift cluster. Then, the Power BI query would rename the columns, and change their data types. It is in "Import" mode. It always refreshes successfully in Power BI Desktop. But after publishing, it cannot be refreshed from the Power BI Service. 

When trying, I was getting errors like...

Processing error: The column 'xxxxxxxx' of the table wasn't found.

...where 'xxxxxxxx' was the original name of a column, directly from the Redshift data source. And with each refresh, it would give a different column name, each time, at random!

So, I tried removing the steps in my Power BI query that renamed the column. Now, every time I try to refresh in the Power BI Service, I consistently get this error:

Processing error: The table has no visible columns and cannot be queried.

I have verified the credentials in the dataset settings page, in the Power BI Service.

(And still, when I am in Power BI desktop, I can successfully refresh the data every time.)

In Power BI Desktop, my Redshift connection string is formatted like this:

clustername.nnnnnnnnnnnnnnn.us-west-2.redshift.amazonaws.com:5439;dbname

I checked the security settings of my Redshift cluster. The AWS Security Group in which my Redshift database lives has this inbound rule, which should allow all TCP traffic…

• Type: Redshift
• Protocol: TCP
• Port Range: 5439
• Source: 0.0.0.0/0

Any ideas? Please help! (I've sent in a support ticket, but haven't heard from them in a couple of days.)

…I wonder if the Power BI Service uses a different "protocol," other than TCP? While Power BI Desktop does use TCP? I'm no networking expert, but that's the only thing I can think of.

Thanks!

[kevhav · Accepted solution]

Oops, I had old credentails saved in Power BI Desktop, when I was developing the dataset, there. 

Then, I entered different credentials in the Power BI Service.

When I updated the credentials in Power BI Desktop, and re-published, then everything was okay.

[ymouman]

It seems that when a report is connected in the same time with a Redshift datasource and a MySql one for example, the refreshes will fail as the MySql datasource needs a Data Gateway (Personal). But the error logged in my case was missleading as it was talking about the Redshift connection.

[kevhav]

Here is another update. (Still no solution!)

First, to test, in AWS, we temporarily changed the AWS Security Group (the one that is applied to our Redshift cluster) to allow all traffic, for all protocols and all ports, from all IP addresses. It didn't change anything. Still getting "The table has no visible columns and cannot be queried." So, it does not seem to be an access issue.

Second, it seems to do this same thing, regardless of which Redshift table I try to pull into Power BI.

Third, I tried publishing to a different workspace, like My Workspace instead of the app/group workspace I had been using. It made no difference; still getting the same error, regardless of workspace.

Fourth, I wondered what it would do when in DirectQuery mode, instead of trying to "refresh" while in Import mode. I understand DirectQuery works with Redshift. So, I tested that. Again, I started with a fresh, brand new PBIX file. (I even downloaded the latest version of Power BI today, first, because it appeared that was a new version.) My test dataset consists of just a single query, to get the contents of one small table; plus one simple Table visualization. Again, it all works fine in Power BI Desktop: the visualization is successfully rendered, with the data from the Redshift table. Then, I published the PBIX file to a workspace in the Power BI Service, in DirectQuery mode. Then, in I tried to open the Report in the Power BI Service, to render the visualization and have it execute the query. And again, there was an error. When trying to load the visual, it shows: "Can't display the visual. See details." Drilling down to the details, here is what I got:

Couldn't load the data for this visual

DirectQuery error: DirectQuery may not be used with this data source - please consider moving to a supported data source or upgrading the SQL Server data source to the latest available version.
Please try again later or contact support. If you contact support, please provide these details.

Very strange! Could our Power BI tenant somehow not received the update that allows it to connect to Redshift? Could my Redshift cluster/database be configured incorrectly somehow, for the Power BI Service to be able to use it? I'm stumped.