Native query for Amazon Redshift - not working in the Service

The error message is:

Underlying Error: PowerBI service client received error HTTP response. HttpStatus: 400. PowerBIErrorCode: QueryUserError
QueryUserError: Expression.Error: Native queries aren't supported by this value.. #table({"Name", "Data", "Kind"}, {}). . The exception was raised by the IDbCommand interface.
Correlation ID: ________________
Activity ID: ________________
Request ID: ________________
Time: Fri Oct 15 2021 13:02:07 GMT-0700 (Pacific Daylight Time)
Service version: 13.0.16934.66
Client version: 2110.1.08076-train
Cluster URI: https://wabi-west-us-b-primary-redirect.analysis.windows.net/

kevhav 

You may take a look at https://docs.microsoft.com/power-query/connectors/amazonredshift#capabilities-supported.

v-chuncz-msft - thanks for your reply. I looked at that link, yes.

However, please refer to these two sources:

• This page, which says "Amazon Redshift connector support for native query" went into general availability on 2021-09-20.
• The October 2021 Feature Summary, which says "We are excited to announce support for native database queries for the Amazon Redshift connector. Similar to SQL and Snowflake connectors, this will allow you to input an Amazon Redshift native query and build reports on top of it."

The above sources seem to indicate that native queries with Redshift should be working now, with no limitations. Right?

This page (that you referenced) was last updated on 2021-09-17; I think it should be updated to reflect the new capabilities supported.

And this page was last updated on 2021-07-13; I think it should be updated to add Amazon Redshift to the list of connectors that support native database queries.

And native queries with Redshift do work in Power BI Desktop, now, with the October version only. (It did not work in Desktop before the October release!) But why isn't it working in the Service, with DirectQuery?

I encountered the same problem for Import Queries too ....

Tried publishing in  "My Workspace" 

Can confirm, facing the same issue.

I also confirmed we have the exact same issue with a .pbix in Import mode. We get that same error when we try to refresh it from the Service (though it works fine in Desktop). 

I tested publishing to My Workspace per Anonymous, and other workspaces not in Premium Capacity. This does not make a difference; I still get the same error like "Native queries aren't supported by this value."

v-chuncz-msft / Microsoft, can you please investigate further / explain what is going on?

Now that I look at the blog again, it does say, the release of the features in this section may be delayed in some regions. We are working hard to ensure they are available in all public regions as soon as possible. Is that all it is? We just need to wait some more days or weeks? Any ETA? 🙃

I can confirm also this issue. Even after updating the on-premises data gateway to the latest version (October 2021), in the USEAST2 region.

I see the Amazon Redshift connector reference doc was updated last week -- that is appreciated.

But, it's strange...

It says "DirectQuery (Power BI Desktop only)" ... but that is not completely true. DirectQuery with Redshift works in the Power BI Service, if the .pbix does not rely on the "native database query" feature. This has been the case for a long time.

It is true, currently, that when you are relying on the "native database query" feature, then DirectQuery does not work in the Power BI Service (but it does work in Power BI Desktop). 

And it's also true that for a dataset in Import mode, which relies on the "native database query" feature, it cannot be refreshed in the Power BI Service.

I see this doc was also updated: it now lists Amazon Redshift as a connector that supports native database queries. (Nice!)

But it still doesn't work in the Service! What should we expect?

• Will Amazon Redshift with native query work in the Service, in Import mode (to refresh the dataset)? When?
• Will Amazon Redshift with native query work in the Service, in DirectQuery mode? When?

Amazon Redshift with native query + DirectQuery mode would be super-valuable to us! We've been waiting eagerly for this ... in fact, we've even been planning our development around it, assuming it was going to be available around September, 2021.

Because this page says "Native query support in the Amazon Redshift connector will enable users to build DirectQuery and import mode reports on top of a custom SQL query specified in the connection experience." And it says this has already gone into general availability.

kevhav 

Thanks for following up on the issue.

v-chuncz-msft 

Even I have been waiting for support for native query in import and direct query mode.

Support for native queries makes a lot of difference to us as we need to frequently execute joins/ groupings. Cannot ask DB guys to create separate views for each and every reporting need.

Moreover the incorrect/ incomplete documentation makes it hard to explain to our clients that something is not feasible at the moment.

Regards,

Mannu