Optimizing power bi report with sharepoint

[Anonymous]

I have built a report for customer where the data source come from customer on premise SharePoint server.
In the report many "append", "merge" and "expand" been use to get the required data.
The final report takes about an hour to pull in power bi desktop and when I upload to power bi service and configure a power bi gateway the scheduler fail after 20 minutes.
Any solution or suggestion is greatly appreciated. Thanks.

[Anonymous · Accepted solution]

Having worked with on-premise sharepoint in the past, the first optimisation i'd recommend is to check whether you are getting the data through SharePoint's front end (the SharePoint connector) or directly out of the database (SQL Connector).

I found going directly to the database was much much quicker as you could use query folding to speed up the execution time.

Its a little tricker to set up, because the database isn't as easy to use as the front end.  You'll need to know the report GUIDs so you can filter the data table and only get the records of the list/library you want.

[Anonymous]

will try look into it, any more suggestion?