Forum Discussion

bdunzweiler's avatar
bdunzweiler
Icon for Helper I rankHelper I
8 years ago
Solved

Scheduled Refresh Issue with Value.NativeQuery

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, ...
Need Help
  • bdunzweiler's avatar
    bdunzweiler
    8 years ago

    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.

bdunzweiler's avatar
bdunzweiler
Icon for Helper I rankHelper I
8 years ago

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's avatar
GilbertQ
Icon for Super User rankSuper User
8 years ago
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's avatar
    bdunzweiler
    Icon for Helper I rankHelper I
    8 years ago

    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's avatar
      GilbertQ
      Icon for Super User rankSuper User
      8 years ago

      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's avatar
        bdunzweiler
        Icon for Helper I rankHelper I
        8 years ago

        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