Dataset refresh issue in PowerBI Service using direct query

[Anonymous]

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

[v-lid-msft]

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]

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]

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

[Anonymous]

Hi Nicky,

yes see my initial entry in this question, there is a picture of the gateway setup between the datasource and the SQL Server DB.

So yes there is....

[v-lid-msft]

Hi @Anonymous ,

Let us make a conclusion above your situation, does the report based on the directquery mode  can not refresh in Power BI Service while the gateway is working fine? Do you have another data source in this report? 

As you mentioned there is about 3,000 records and the data source will update just twice in one day, we think you can use Import Mode as a temporarily workaroud till we found the issue why the report in directquery cannot refresh. It does not need to rewrite the entire report, we just need to keep the origin pbix file, make a copy and rename it as another pbix file, then enter the model view, change the table to import mode. Please remember that it is important to keep the origin pbix file that use DirectQuery,  because we can change DirectQuery to Import easily, but we cannot do change Import to DirectQuery. After change the storage mode, you can publish it into the Power BI Service and configure Scheduled Refresh twice ore more in one day as a temporarily workaround.

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]

Well, this looks funny: I checked and it seems that the mode is already set to import, but we are actually using Direct Query: in below screenshot you see that we create the table open orders by using the below query:

But if i go to the model tab and look at the properties of the tabel, it shows that we are using import as method:

so I can't change to import as it says it is import, but we use a query.

Any ideas, this seems to be the culprit.

[v-lid-msft]

Hi @Anonymous ,

First of all, Import Mode can also write the query, the best way to find the storage mode the table use is look the right panel of model view, 

But based on my test, if there are at least one table use import mode, the refresh setting in Power BI Service should be "Schedulde Refresh" instead of "Scheduled Cache Refresh" in your origin post, could you please check that is there any table use DirectQuery Mode in this pbix? Could you please also try to copy and rename this pbix file, publish to the service(do not replace the origin one), then check the refresh setting of it.

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]

Hi,

I have checked and this report has 7 tables, and all 7 tables are set to Storage mode being “Import”, BUT as stated before, we do use a specific query to pick up the data for certain tables.

When you say “if there are at least one table use import mode, the refresh setting in Power BI Service should be "Schedulde Refresh" instead of "Scheduled Cache Refresh"”, I don’t know how to do that, as when I go to the PowerBI Service Dataset, the only setting I see is for Scheduled Cache Refresh:

I am lost of what is going wrong here…

Regards,

Norman

[v-lid-msft]

Hi @Anonymous ,

Thank you for checking the storage mode for every table, we mean that if there are tables using import mode, the refresh setting for dataset should looklike following:

As we mentioned before , use specific query ( write sql in sql statement) may be not in the directquery mode, import mode can also use it. 

Now the situation is that the tables in your pbix file use import mode but the refresh setting look like direquery in service. Could  you please test that if rename the pbix file and publish to the service again ( do not replace the old one) and see if the new dataset can configue the "schedule refresh"?

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]

I downloaded the PIBX-file (and renamend it) my colleague had created and openend that one with PowerBI Desktop. 

I even took out one table that was not being used in the model and verified one more time in local PowerBI Desktop - model if the storage mode for all tables was import, which was the case.

I published the PIBX in my personal workspace, but still I only see the scheduled cache refresh option, and not scheduled refresh.

So this didn't work...

[v-lid-msft]

Hi @Anonymous ,

Thank you for your patient test, it looks like an issue as so far but we can not reproduce it on my side, we test changing some settings but none of them can make a published report with all tables imported have a "Scheduled cache refresh" setting, also based on this document, The Scheduled cache refresh section of the Datasets tab is not available for datasets in import mode. 

Sorry for occuping you so much time for testing the situation and we cannot found an effective solution. We suggest you to open a ticket here for help if you are a pro user: https://powerbi.microsoft.com/en-us/support/

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.

[nickyvv]

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]

@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