Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everyone,
I have about 10-12 reports that load the same Calendar, a SSRS report, in their PBI reports. These PBIX are published in the Cloud and are scheduled to refresh themselves overnight as well as during the day. We have found from SSRS logs that the Calendar SSRS is being called a lot of times, which is unexpected as the Calendar query within PBIX has its “Include in report refresh” disabled. Is that not how it works, that is, disabling this option will stop PBI engine from going back to the data source, given there are no appends and merges within this query nor in other queries?
Thanks
kaz
Solved! Go to Solution.
@Anonymous,
" These PBIX are published in the Cloud and are scheduled to refresh themselves overnight as well as during the day."
"Include in report refresh" option only works for Power BI Desktop, it doesn't impact the refresh process in Power BI Service.
In Power BI Desktop, when you disable "Include in report refresh" option, data will not update in report view of Power BI Desktop after you click "Refresh" button in Desktop.
Regards,
Lydia
@Anonymous,
" These PBIX are published in the Cloud and are scheduled to refresh themselves overnight as well as during the day."
"Include in report refresh" option only works for Power BI Desktop, it doesn't impact the refresh process in Power BI Service.
In Power BI Desktop, when you disable "Include in report refresh" option, data will not update in report view of Power BI Desktop after you click "Refresh" button in Desktop.
Regards,
Lydia
I'm very confused with this.
So it means "Include in report refresh" option only affect PBI desktop, but when you refresh dataset in PBI service, the table still be refreshed, right?
Is that still correct now?
The "Include in report refresh" option affects both Power BI Desktop and the service equally. If unticked, the table will not be refreshed on either.
When I last tired it (see Post) it prevented the refesh also in the service. But it would still need access to the datasource.
So if you have a datasouce that's just accessbile for your PBI Desktop, the refresh would still fail. Since the check which tables to refesh was done only after the check if there is access to the source.
But I have not tried since then.
Ya, but my purpose is to refresh table which is NOT Include in report refresh. Ofcourse, normally I don't want it to refresh (both clicking refresh dataset manually and schedule refresh), but sometimes there is an update, that I want to refresh that table in PBI service, so how to do that?
@anhngv If you have a dataset that is flagged as DO NOT INCLUDE IN REPORT REFRESH, I think the only way to refresh it is manually in Power BI Desktop. You must right-click on the table (in the Fields pane on the right) and select Refresh Data. Then you must re-deploy that refreshed model to the service. I don't think there is a way to manually refresh it from within Power BI Service.
Hint: This is a great way to refresh only ONE table at a time during development or testing, when you don't want to wait to refresh ALL of your datasets.
One other option is to take that dataset that you don't want to refresh and persist it as a DATAFLOW in the workspace. Dataflows can have their own Refresh schedule apart from other dataflows and models. In fact, the Dataflow need not have a Refresh schedule at all. This is a great work-around for data queries that don't change that often, like Zip Code lists, Employee lists, or Product lists.
Hope that helps
Proud to be a Super User! | |
Thank you @ToddChitt
I consider this is a shortage of Power BI service. We obviously have demands to do so but PBI service just cannot serve that. The way I am doing now is refresh it manually in desktop version and re-deploy it as you said and it's not convenient. That's why I'm here to ask for another way.
For DATAFLOW way, I have try creating but it is apparently not an easy way.
So seemingly I have no choice sticking with re-deploy way and wait for next update of PBI service.
Hello @anhngv What did you mean by "For DATAFLOW way, I have try creating but it is apparently not an easy way."?
What is the data source type you are trying to make into a Dataflow? SQL? Excel? CSV? Something else? What have you tried and what are your pain points?
Proud to be a Super User! | |
It's just complicated and I have read some articles in MS but still don't know how to use, what to do. I have REF (reference) table which is not usually refresh. I have done the report in the traditional way, import everything and create in desktop version and deploy to service.
Now I've done creating my REF in dataflow (import a table from xlsx file). Then I don't know how to use it, how to link it to my report.
Consuming Dataflow inside a Power BI report is pretty straight forward. Click the Get Data and select "Power BI dataflows". Assuming you are logged in to Power BI Desktop with the same credentials that has access to the Workspace and Dataflow, it will ask you to pick a Workspace and Dataflow, and from there, the actual dataset.
From this point on it is just like any other data source with one key exception/benefit for your case: When you set up Scheduled Refresh for the model in Power BI Service, queries sourced from Dataflows are not listed. You don't have to worry about them. They can have their own refresh schedule defined.
Hope that helps.
Proud to be a Super User! | |
hi @ToddChitt
I have tried get data from PowerBI dataflow and get this:
- Upload xlsx file to workspace as Data flow
- From Power BI desktop, import a table from Get data > Power BI Data Flows
- In Power Query Editor, uncheck "Include in report refresh" > Close & Apply
- Save and publish file to the workspace
- In the workspace (Power BI service), set up credential for the dataset, so that report can be refreshed
When I update the table in Data flow, then refresh the report, data in the report is not updated (as I expect)
It does not solve my problem. The thing I want is to refresh the table which is NOT "Include in report refresh". Because that table rarely changes, but when it does, I want to refresh it in service.
Have you looked at incremental refresh in data flow? Your other option is to put some custom code to detect change in the data source and then call a REST API to trigger a refresh.
@Anonymous
Just so many complicated trick to do this. In desktop mode, we can easily refresh a single table manually. I give up with this PBI service and hope Microsoft team will do some update on this.
Hi @ToddChitt
I tried getting data from data flow, but there should be a mistake that table is empty. Could you check and suggest where should I miss?
My table in dataflow in PBI service:
But when I get data from PBI desktop, it says the table is empty
Thanks
@v-yuezhe-msft @Anonymous
Am I the only one reading this and thinking, "What the heck? That's POOR DESIGN by Microsoft!"
Disabling the "Include in Report Refresh" should mean just that, "don't try to refresh this data sourcr". It should NOT mean, "don't try to refresh this data source ONLY if you are using Power BI Desktop. But if you're using the Power BI Service, then try to refresh it!"
So the ONLY way to have a data source that does NOT get refreshed is to put it in a "Enter Data" table manually?!?
Sorry to say, but I'm VERY disappointed in MS for this design choice!
Proud to be a Super User! | |
I totally agree with you. I also tried my luck with support and had none...
Now I created a Idea for this topic. Hope I can get some Upvotes on it to get it going. -> https://ideas.powerbi.com/ideas/idea/?ideaid=44f7ca13-e8c9-eb11-ba5e-281878bdaa58
Hi All
i am happen to saw this post.
I like to know if I have all my CSV raw data at DropBox , And I need the PBI to perform a schedule refresh or reload during 3 Am. Can it be done ?
Hope some one can share with me.
paul
@Paulyeo11 Not sure how getting/refreshing data from DropBox is at all related to the "Include in Report refresh" feature. For best response from the community, you may want to start a new thread, or even SEARCHING the forums for a similar topic. I fount several. Perhaps this thread will help:
https://community.powerbi.com/t5/Desktop/Data-source-hosted-on-Dropbox/m-p/48233#M19081
Best of luck.
Proud to be a Super User! | |
Not a very smart solution, I agree. But the most likely solution would be to provide a never changing datasource like an Excel and always load static data from there. You'd have to create an additional gateway data source though and might have a lot of unnessesary network traffic...
@SokonSorry, but that's known as 'defect support system'
What happens if the location of that static data moves? You don't NEED to refresh it, but Power BI Service FORCES you to, and then FAILS because it's not there.
<rant>
This is a horrible design spec, where the desktop tool does something different than the service.
<\rant>
Sorry, but it's just my opinion.
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |