Scheduled Refresh Issue with Value.NativeQuery

[bdunzweiler]

We have several reports that utililze Value.NativeQuery for explicit queries that up until now had supported scheduled refresh in the Power BI Service.  We recently published a change to the report, unrelated to the native queries, and now the dataset can't be refreshed due to this error: You can't schedule refresh for this dataset because one or more sources currently don't support refresh.  We stripped the report down to the base queries and discovered that as soon as we add a Value.NativeQuery query into the mix, this issue appears.  If we remove it (or even disable it), the refresh feature reappears in the Power BI service. 

Did something change in the service that I missed a notice about?  Or is anyone else experiencing this?

[GilbertQ]

Hi there, it would appear that something is not 100% right.

I do have a potential work around, which is working for my SQL Sources, which is to change it from Value.NativeQuery to the following:

Source = Sql.Database("SQLServerName", "DatabaseName"
    , [Query=" 
          Select * from my Table 
    ", CreateNavigationProperties=false])

[bdunzweiler]

@GilbertQ Yes, using the inline query method does work for simple queries, but will require us to "stringify" the queries that have parameters.  I would like to avoid that since I believe the intent of Value.NativeQuery is to allow parameterization.  Thank you for the workaround suggestion, though.

I did speak with MS yesterday and they are researching. I was able to recreate the issue on a different Azure SQL Database server and in a fresh report.

It does seem like something changed on the service side.  This only affects models that are republished.  Existing reports on the service with the same types of queries are working fine.  Needless to say, we are hesitant to publish any updates to other reports until this is resolved.  We are having to resort to manual data refreshes in desktop and then publishing manually.

[GilbertQ]

Sure thing, hopefully they will get it resolved.

Just a quick question how did you connect to your data in order to create the Value.NativeQuery from Get Data?

[bdunzweiler]

@GilbertQ Here's a simplified version of what triggers the issue

let
    Source = Sql.Database("my-azure-server", "my-db"),
    query = Value.NativeQuery(Source, "select * from dbo.DateDetail where CalendarYear >= @someYear",
    [someYear=SomeReportParam)
in
    query

The issue triggers even if the parameters are removed.

[GilbertQ]

hi @bdunzweiler

Thanks for that I did not know you could do it that way.

This is the way I have done it in the past.

My Parameter is called "Start Date"

let
    Source = Sql.Database("my-azure-server", "my-db", [Query="
      Select * from dbo.DateDetail where CalendarYear >= "& #"Start Date" &" 
     ",CreateNavigationProperties=false])
in
   Source

[bdunzweiler]

FYI, MS identified this as a new issue as suspected.  It is on the https://powerbi.microsoft.com/en-us/support/ page

Some Users may fail to refresh settings after recently publishing models that use Native Query to fetch data.users may be able to avoid this issue by avoiding using Native Query during GetData, Avoid pasting Structured Query Language (SQL) queries, use stored procedures, Allow Power Query to create your query in Power BI Desktop. Engineers have found the issue and are working on a fix. Next update @ 04/11/2018 11:30 PM UTC

[bdunzweiler · Accepted solution]

I got notification from MS Support that the issue has been fixed in the service.  I confirmed it works as the dataset now shows the refresh features (without requiring a republish) and an ondemand refresh works.

[sdjensen]

Hi @bdunzweiler,

Thank you for keeping us updated - I just made a test to see if the issues had disappeared for us as well, but that isn't the case. We don't user Value.NativeQuery in our M code, but I made a test to see if it would make a difference and voila, once I changed my query to use Value.NativeQuery and published the model I was able to schedule refresh, so that could be a possible workaround, but I really don't want to make this change at all of our customers, so still hoping that MS will fix our issue as well.