How to write query in power BI Desktop with Amazon redshift connection

[chotu27]

Hi can write sql queries when connecting to redshift from power bi in similar what is sqlserver having to write sql queries?

[v-yuezhe-msft · Accepted solution]

@chotu27,

When using the Amazon redshift connector in Power BI Desktop, it is not possible to write SQL queries, we are only able to input server name and database name to get data from Amazon redshift.


As a workaround for this issue, you can create a ODBC data source for Amazon redshift following the guide in this video, then connect to the ODBC data source in power I Desktop, and you can write SQL queries in SQL statement box.


Regards,
Lydia

[tomconnor]

Thanks for this, got it connecting using the ODBC driver. Has anyone been able to get it refreshing after uploading to powerBI app? My scheduled refresh fails because the app doesn't have the required driver.

Hoping someone has come up with a work around, we would like automatic refresh but are reluctant to covert a lot of code from SQL. Is this on the radar for Power BI? I haven't seen it listed as an idea but most of the other establised connections alow SQL. Any help would be much appreciated! Thanks

[Nuria_Carol]

same issue here. With this solution (connecting using de ODBC driver), it does not allow to use scheduled refresh. We need to be able to query directly using SQL, as we do with our data sources like MySQL. Our database is huge and we cannot import it completely and then filter. We use complexe SQL queries to query our data. This is preventing us from using PowerBI in this case. Using DirectQuery does not allow to do the kind of queries we use in SQL, and it also has the problem that we cannot use other data sources in same PowerBI file. All advantatges of PowerBI are useless if this cannot be done. 

[corbusier]

This is an issue here as well. Enabling writing SQL directly against the Redshift datawarehouse would bring it to feature parity with the other database connectors. As it is, I'm using a workaround of creating a view in Redshift, because the ODBC connector is not an option since we need to have it refresh.