cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Avoid throttling when loading from SharePoint folder

I have a report loaded entirely from a SharePoint folder. The report uses around 400 files of small size (2MB). When loading the data in the desktop version, sometimes ( 1 out of 2 times) Sharepoint throttles Power BI, and the load gets blocked. I expected the throttle not to happen in Power BI Premium, but when requesting a refresh I run into:

The operation was throttled by Power BI Premium because there were too many datasets being processed concurrently.

 

Is there a best practice to avoid throttling from Sharepoint in such a scenario? (i.e. fewer files of bigger size, using dataflows, ...)

1 ACCEPTED SOLUTION

I have an environment with in excess of 18,000 individual files and multiple Dataflows running at the same time and yes it suffers from throttling but Microsoft support pointed me in the direction of buffering each Binary before you expand it and this significantly improved the performance.   

 

So I land these CSV files into Dataflows and then pull that into the model.

 

Source = Csv.Document(Binary.Buffer([Content]),[Delimiter=",", ...............

 

Does not completely get rid of the issue so I also schedule a number of refreshes.

 

Hope that helps. 

 

Regards

 

Andrew

View solution in original post

10 REPLIES 10
Regular Visitor

How do you load all these CSV files?

I used the Power BI SharePoint Folder connector but there I don't see the line:

Source = Csv.Document(Binary.Buffer([Content]),[Delimiter=",", ...............

 

Do you load every single file direct in Power BI?

 

If you are using the built in function then you will find it the fx part as shown below.

 

Source = Csv.Document(Binary.Buffer(Parameter1),[Delimiter=",", ...............

 

I have not tested this fully but hopefully this works for you

 

Screenshot 2020-11-09 091131.png

 

Andrew

@Timaru_Golf  thank you for posting this. 

 

I have have been struggling with similar SharePoint throttling issues (_api/contextinfo errors) however with Excel.Workbook instead of Csv.Document files.  I modified all transform file helper query sources with Binary.Buffer as you show.  This seems to function as before, so I believe it is correct as you show for CSV as well.  

 

=Excel.Workbook(Binary.Buffer(Parameter1), null, true)

 

You mention Binary.Buffer improved performance... as in time to refresh?  Reduction of refresh failuires?  Both?  Unfortunately I still get the same failures when trying to refresh and can't realize a difference in performance yet. 

 

Thanks

 

Hi,

 

It does not improve the speed (time) but it does reduce the number of times it fails a refresh as its basically saying get the whole file contents and then do your transformation and then repeat instead of give me all in one go.

 

To speed up ingestion if you have lots and lots I break the data into separate  Dataflows.

 

So say

 

Archive 1   would be 2019 Q1,Q2

Archive 2   would be 2019 Q3 Q4

Archive 3  would be 2020 Q1 Q2

 

Current  would be 2020 Q3 - now

 

When you are doing a refresh you only have to do this for the current one - which is faster an less likely to fail.

 

Then in your Model combine the Dataflows to get the full data set.

 

Hope that helps

 

Andrew

 

Community Support
Community Support

Hi @JonArgandona ,

You can refer the following documentations to optimize the model and report:

Optimization guide for Power BI

Optimize a model for performance in Power BI

Power BI Best Practices Guide

Best Regards

Rena

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

Hello @yingyinr ,

I have looked at the links, and they deal mostly with the model and the report (no mention to SharePoint).
I don´t have performance issues on the model side (it is well streamlined) nor in the load when I do it locally.
If I repoint my load to Sharepoint, I sometimes come up with a throttle issue (both locally and in the premium capacity).
My question is specifically related to SharePoint and what is the best way to design a load from it to avoid throttle.

Thanks a lot,

Jon

Hi @JonArgandona ,

Sorry for misunderstanding. Please check whether the following documentations can help you.

Retrieving data from SharePoint causing throttling errors

Avoid getting throttled or blocked in SharePoint Online

Best Regards

Rena

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

Thanks a lot @yingyinr 

I believe my issue is related to the first post.

There is no solution to it as of today so I'll follow up in that thread in case somebody was able to solve it.

Kind regards,

Jon

I have an environment with in excess of 18,000 individual files and multiple Dataflows running at the same time and yes it suffers from throttling but Microsoft support pointed me in the direction of buffering each Binary before you expand it and this significantly improved the performance.   

 

So I land these CSV files into Dataflows and then pull that into the model.

 

Source = Csv.Document(Binary.Buffer([Content]),[Delimiter=",", ...............

 

Does not completely get rid of the issue so I also schedule a number of refreshes.

 

Hope that helps. 

 

Regards

 

Andrew

View solution in original post

Thanks a lot @Timaru_Golf,
The load failed from the desktop version once (so I believe the issue can reappear sometimes), but for the first time, I was able to refresh the published version.

 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors