Advance Query option for Redshift
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.
Ganesh Krishnan commented
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.
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
Shaun Roberts commented
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!
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?
We have the DirectQuery equivalent on server option for Redshift on our our very old Tableau reports for 4+ yeas.
Yeah. We really need it.
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..
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.
Nate Seulgi Lee commented
This Function must need.
AWS is the most popular and also has highest market share.
Our company is also one of company which join to AWS last month.
before AWS, We use MS-SQL and i'm one of Power BI evangelist in the company.
but now, situation is changed.
I've got a mail from Microsoft that
Power BI doesn't have plan to provide query option for Redshift.
For Data analytics, It doesn't make a sense without query option.
I'm afraid that AWS user will not use Power BI then also lose Loyalty about Power BI.
please provide this function ASAP.