Forum Discussion

bdunzweiler's avatar
bdunzweiler
Helper 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.

GilbertQ's avatar
GilbertQ
Super User
8 years ago

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's avatar
bdunzweiler
Helper 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
    Super 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
      Helper 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
        Super 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