Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dataset refresh issue in PowerBI Service using direct query

Hi,

 

We have build a report using direct query in PowerBI Desktop. The data being used is in an SQL server, and once we hit refresh in the desktop-environment, the data refreshes correctly.

 

When we publish the report to the PowerBI Online service, the data is not being refreshed. We use a data gateway which is setup and working correctly:

 

 

 

But even after a refresh, the data doesn't correspond with the data in the SQL server. Hitting the refresh button on the report doesn't update the data, while refreshing the data in the desktop version does update and mimics the SQL Server data.

 

 

Is there any reason why the refresh of the PowerBI Online version is not working, do I have to set some specific setting. Even after the hour-cache refresh, which seems to work correctly, threre is no change is data. We update our SQL server twice per day, and the update is not reflected after the update of SQL Server data (it works in desktop, but not online).

 

 

I checked the table and the direct query output has roughly 3000 records, so I don't believe there is a limitiation-issue.

 

Any help would be appreciated!!

 

Norman

Need Help

13 Replies

  • nickyvv's avatar
    nickyvv
    Most Valuable Professional
    6 years ago

    That sounds like a strange issue.

    Have you tried SQL Server Profiler to check which queries are send to the database? That way you make sure those queries are correct. You can then execute the same query to the database in SSMS and compare the results?

     

    Did this help you or did I answer your question?
    Then please give kudos or mark my post as a solution!
    My blog: nickyvv.com

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      nickyvv , I don't expect this to be an issue, as the refresh of the desktop version is going perfectly, and that data in SQL Server and PowerBI desktop is 100% match.

       

      The only problem is that the published version on the web is not refreshed. I would expect a gateway-problem, but the gatway is online and valid, so I don't understand the issue.

       

      I actually don't know how I can trigger a refresh of online-data in the report and simultaniously check the query. One strange thing Inoticed is that when i do a refresh of the dataset in online version, it takes on second. But I believe that is intended behavior and the scheduled cache refresh should take of fresh data. But for some reason, this isn't working.

       

      Any ideas are welcome

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    First of all, we suggest you to remove the email information in your screenshot.

     

    Based on this document: https://docs.microsoft.com/en-us/power-bi/refresh-data#understanding-data-refresh 

     

    Power BI does not import data over connections that operate in DirectQuery mode. Instead, the dataset returns results from the underlying data source whenever a report or dashboard queries the dataset. Power BI transforms and forwards the queries to the data source.

     

    Because Power BI does not import the data, you don't need to run a data refresh. However, Power BI still performs tile refreshes and possibly report refreshes, as the next section on refresh types explains. A tile is a report visual pinned to a dashboard, and dashboard tile refreshes happen about every hour so that the tiles show recent results.

     

    So If you are using the DirectQuery Mode, the report should query data source to get the newest data when you access the report or refresh the report, but the title you pin to the dashboard will be affcted by the "Scheduled Cache Refresh".

     

    Can you access the report when use the Direct Query? Does the data change when you refresh the report page after data source changed?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      thanks for the tip to remove my email-address, which I did now:)

       

      We don't have a dashboard but a report setup, and the report uses various visuals. If hit the refresh button in the desktop-version, than it refreshes properly and I see the correct data from our on-premise SQL Server. 

      But in the published report to the web, at renewed opening OR even if I hit the refresh-button at the top of the report-page, nothing changes. So anybody who doesn't use the desktop-version, but consumes the data from the published report doesn't see the updated data.

       

      The problem is really in the refresh of the published online-version, and that one is used by almost everybody oin the organisation. But if that doesn't refresh the data, there is no point in using the report.

       

      Any idea if I am doing something worng? I am almost at the point that I am going to re-write the entire report (multiple pages, multiple visuals etc) which would take me a lot of time. I would like to understand if I am doing something wromng or if there is a seeting that I need to set in order to get the latest data from our on-premise SQL server (that is being refreshed twice per day) in  the published online report.

       

      Any help would be super helpfull.

       

      Regards,

      Norman 

      • nickyvv's avatar
        nickyvv
        Most Valuable Professional
        6 years ago

        Norman,

         

        long shot, but is there a Power BI gateway entry for the datasource?

         

        Did this help you or did I answer your question?
        Then please give kudos or mark my post as a solution!
        My blog: nickyvv.com