Incremental Refresh

Follow up: I jumped into a YouTube live session for the legendary Guys in a Cube last Saturday to see if I could get any ideas on how to tackle this. This is what I posted to those guys:

“Hey there! I have a 200 megabyte .pbix. The refresh times out after two hours on PBI service. There are multiple queries in the report being merged and appended into a main query.

I need to bring in a year of data whereas now I’m only bringing in 4 months. I have tried paring down the model by eliminating tables and unused columns (which I tried to identify using Dax Studio and Power BI cleaner but I think my model has so many relationships and DAX references that it didn’t work well). It’s hard to identify what is used.

I then tried disabling auto date/time as per your videos, but that didn’t have a large impact. Now I’m trying to implement incremental refresh in hopes that I could avoid timeouts, but I’m getting long gateway errors instead:

"DM_GWPipeline_Gateway_MashupDataAccessError" . . . {"type":1,"value":"invalid query locator"}}

I’m using a Salesforce Object connection, and I verified using one of Patrick’s videos on inc. refresh and native queries that my queries are being folded and sent to server.”

They suggested I read this article (https://blog.crossjoin.co.uk/2020/06/03/optimising-the-performance-of-power-query-merges-in-power-bi-part-2/) and start thinking about how I could use dataflows to do some of the database querying prior to my main refresh query rather that having all queries run at refresh runtime.

I’m looking into that now as I have multiple queries being called and merged and appended into a main table in Power Query. That way I could get what I want without needed incremental refresh I believe.  

I will follow up with any inroads I make.