Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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, ...)

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

10 Replies

  • MacJulian's avatar
    MacJulian
    Advocate I
    5 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       

      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

       

       

      Andrew

      • AgTooOldForThis's avatar
        AgTooOldForThis
        Frequent Visitor
        5 years ago

        Anonymous  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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Anonymous ,

      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