Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Native query for Amazon Redshift - not working in the Service

We found the new native query for Redshift feature works great, in the October version of Power BI Desktop. But after publishing the .pbix (in DirectQuery mode) to a workspace in the Power BI Service (in Premium Capacity), we get an error when trying to render the report in the Service. I contacted support, and they could only tell me: "it might take some time for this new feature to be available in your region." Is that right? ... Our region is 'West US (California)'.

They also referenced the Import data from a database using native database query page, and noted that Amazon Redshift is not listed there, yet. That doc will need an update, maybe.

 

Please help? Should we expect to wait for this to roll out to our region? How long could that take? Or, could this be some kind of bug?

Status: Accepted
Comments
kevhav
Continued Contributor

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/

 

v-chuncz-msft
Community Support
Status changed to: Investigating
 
v-chuncz-msft
Community Support
kevhav
Continued Contributor

@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?

modi123p
Helper III

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

Tried publishing in  "My Workspace" 

zazaalaza
Frequent Visitor

Can confirm, facing the same issue.

kevhav
Continued Contributor

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 @modi123p, 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? 🙃

jenka93
Regular Visitor

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

 

jenka93_0-1635199273574.png

 

kevhav
Continued Contributor

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

But, it's strange...

kevhav_0-1635442089231.png


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.

modi123p
Helper III

@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