Laiq on 21 Nov 2017 13:08:29
In our firm, we are using Power BI to connect Redshift and SQL Server through import and Direct Query. We used to create SQL queries in Advanced option through power BI to connect with SQL Server. This helps to limit the data size as we have billions of record on the source file. But unfortunately this option is not available in redshift. Right now, we are spending lot of time as a workaround to fill this gap. It really helps if something created for this.
Administrator on 18 Apr 2022 20:31:26
2021 Wave 1 Road Map Item - https://docs.microsoft.com/en-us/power-platform-release-plan/2021wave1/data-integration/amazon-redshift-connector-support-native-query
- Comments (11)
RE: Advance Query option for Redshift
We used to create SQL queries in Advanced option through power BI to connect with SQL Server. All our data migrated to redshift. All the existing reports were used custom queries to build the reports. It is very helpful for us to have writing query in advanced option. We are expecting to have this function ASAP
RE: Advance Query option for Redshift
Until this gets implemented, as a work around, I use an ODBC connection in Power BI utilizing Amazon's Redshift ODBC driver. With it, I am able to write custom queries.
RE: Advance Query option for Redshift
this is planned in 2021 wave 1, unfortunately I still don't see it included. this feature is a deciding factor to use power BI vs other BI solution. Please add this feature.
RE: Advance Query option for Redshift
I'd request for two things to be fixed with this.
1. Advance query option so we can write custom queries.
2. Ability to set up direct query with a view on Redshift.
Right now I only see an option to import all data from Redshift when using views on to PowerBI & then set up a refresh cycle. This is not going to help organizations that want to implement tool agnostic views. Many would be better served with ability to have a direct query to the Redshift views too.
RE: Advance Query option for Redshift
Hi, here is how I tackled this problem:
I created a Database View with desired select query in Amazon Redshift and then I loaded this view in Power BI Desktop. Since Database View don't take any memory, there is no load on Database and my purpose of querying Redshift Data is also served.
Hope it will work for you too
RE: Advance Query option for Redshift
This is a problem for me also. A good work around is just to create an SQL View on the Redshift server and pull the data into PowerBI from your newly created view. Not very good in terms of distributing the report logic onto different platforms... but it does work!
RE: Advance Query option for Redshift
A must have, otherwise you are limited to 1m rows via the On-premises Gateway refreshed on schedule.
Is it a commercial decision that they support DirectQuery on desktop but not on enterprise gateway for Redshift as it's AWS?
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-data-sources
We have the DirectQuery equivalent on server option for Redshift on our our very old Tableau reports for 4+ yeas.
RE: Advance Query option for Redshift
Yeah. We really need it.
RE: Advance Query option for Redshift
We really need this. It's a feature that will make us evaluate other visualization tools. For other things we were used to Qlikview but now we prefer PowerBI. However, in this case we cannot use it because we have a large database in Redshift and we need to do queries to simplify data. We have lots of products in the database and our dashboards are focused per product, so it is not feasible for us to load full tables. Performance is terrible. We cannot use DirectQuery because we need to use different data sources and this does not allow it... Please can you implement it or letting us know if you are planning to do it? Support in MySQL is great but now that we have a new database in Redshift we need to change the visualisatin tool again.
As the other comment above, I cannot believe Microsoft has not thought about this..
RE: Advance Query option for Redshift
Please add the option to do SQL queries in Redshift. For another case, we are using PowerBI with MySQL and the SQL query is working great. In Redshift, we also have millions of rows of data in each table, and we need to do complex SQL queries to get the data we need. It's not feasible to Import data. Using DirectQuery is not an option because we need to cross data from other data sources, and apart from that it does not allow us to do complex queries like we are doing in SQL.
I cannot believe Microsoft has not though about that.