Forum Discussion
Dataset refresh issue in PowerBI Service using direct query
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....
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.
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.
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.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