PowerBI incremental refresh - 1st refresh timeout

[Anonymous]

Hi, 

I have a current PowerBI report which uses an Import connection to a SQL server. One of the fact tables has ~40Million rows. The report currently sits on the AppService and does a full refresh daily taking avg. 50 minutes. 

We have tried setting up an incremental refresh for this report, using the steps on the Microsoft blog. The issue we are facing is that the report times out on the first refresh on the service after publishing with incremental refreshed enabled. The error we get is the following:

We have already tried increasing SQL command timeout minutes as suggested by this blog. We have also tried to optimize the dataset and ensured query folding works by checking native query. The idea still persists even after performing the above. Any thoughts on what must be the issue?

[Anonymous]

Yes, I think we hit the timeout limit. The issue though is that the report without the incremental refresh, undergoes the full refresh without any trouble under an hour. Curious how addding only the step of incremental refresh can break things?

[ibarrau]

That's strage. I haven't applied in real world scenario the incremental refresh yet, but it shouldn't take more 😞

I know that the first time is a complete refresh and then it's incremental. 

Try watching some videos to see if you miss something. Otherwise I'm glad you can completely refresh it in less than an hour yet.

Regards,

[MyroslavaM]

Could you please specify how did you manage to resolve the issue? Thanks.

[ibarrau]

Hi. Power Bi Service has a 2 hours timeout limitation. Your data and transformations must refresh in that timeline. Sometimes the databases can't resolve complex queries in short time (adding resources might help), and other times the model size is too big for it. If your query is fast, then try downsizing techniques for the Power Bi data model. You can check details of it with DAX studio with some youtube videos helping there.

If you still have more than 2 hours with the smallest way of the model and the database runing fast, then you have to consider your model too big for Power Bi and start thinking in alternatives like Power Bi Aggregations composite or Analysis Services.

Hope this helps,