Semantic Model Takes a Long Time To Refresh

[TARUGOKING]

I created my first PBI report using desktop version and published it on the web. The report has 2 datasets,  DS1 has approx 4K records (DimDate), DS2 has has approx 4K records and contains my transactions.

I scheduled a daily refresh at 8AM. Reviewing several runs, I was surprised to see that it takes approx 15mins to update the Semantic Model. It just reads the dataset from the SQL tables (separate SQL job populates the table-). Any ideas on what I need to look into?

TIA

[v-pnaroju-msft]

Hi TARUGOKING,

Thank you for sharing the updates. Please keep us informed.

We are also checking to see if the information we shared helped you solve your problem.
If you have any more questions, feel free to ask the Microsoft Fabric community.

Thank you.

[WishAskedSooner]

15 mins? I wish I were you. I have a SINGLE semantic model that is also scheduled to Refresh at 8:00 AM every weekday. I have never seen it refresh faster than 90+ mins.  That's an hour and a half! Nearly two hours! 90+ mins! Let me repeat. 90+ mins!

I don't care if you are loading multiple GBs of data on a refresh (I am not), 90+ mins is RIDICULOUS!!!!!!!!!!!!

Problem is I don't even know where to begin to troubleshoot. In my mind, it is DOA. In many ways the PBI Service just plain sucks—a half baked product that Microsoft expects its paying customers to beta test.

[MasonMA]

Ecosystem lock-in. I'd say you get used to it 🙂 

[TARUGOKING]

Update (done yesterday): 

• Gateway version is now the latest
• Gateway server now has 16GB RAM

The 8AM scheduled run took 14 mins. I scheduled a 9:30 AM run to see how long this will take because it looks like at 8AM, another report update was scheduled to run as well.

An on-demand refresh takes +/- 15 secs.

[v-pnaroju-msft]

Hi TARUGOKING,

Thank you for sharing the updates and the action plan.

We are checking to know if the information we shared helped you solve your problem.
If you have any more questions, please feel free to ask the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi TARUGOKING,

We are checking in to know if the information we provided helped in resolving your issue.
If you have any further questions, please do not hesitate to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @pborah, @MasonMA, for your response.

Hi TARUGOKING,

We appreciate your question on the Microsoft Fabric Community Forum.

From what I understand, the main reason for the 15-minute refresh time is the processing delay on the Power BI Service side, not the data source or how complex the data transformation is. This is because the SQL query runs in less than 1 second, Power Query steps are few, and the data volume is low (about 4,000 rows).

Please try the following steps to reduce the refresh time:

1. Turn off the Auto Date/Time feature. This feature can create hidden date tables for datetime fields, which can increase the model size and refresh time.
2. Check if Query Folding is working. Even if your transformations are simple, Power BI may not send all calculations to SQL. In Power Query Editor, right-click each step and choose “View Native Query.” If this option is not available, it means Power BI is processing data locally, which takes more time.
3. Refresh delay can happen if your dataset and Power BI workspace are in different regions.
4. Since your SQL job finishes at 7:59 AM, start the Power BI refresh at 8:05 AM to avoid heavy load on the backend during peak time.
5. You are currently using two datasets. Combining them into one can reduce metadata load and speed up refresh.

Also, please check these useful links:
Troubleshoot refresh scenarios - Power BI | Microsoft Learn
Optimization guide for Power BI - Power BI | Microsoft Learn

If you have any more questions, feel free to contact the Microsoft Fabric community.

Thank you.

[TARUGOKING]

I'll check on this - thanks.

[MasonMA]

@TARUGOKING 

Would it be possible you have many transformation steps in Power Query, like nested loops etc. ? or if all queries can be folded to SQL DB, could you run SQL query directly in SSMS and see how long they take? 

Also, if i'm in your situation I would consider consolidating these 2 datasets into a single semantic model.

[TARUGOKING]

On-Prem SQL job runs daily at 7:59AM, truncates and populate transaction table, duration 2 secs. Direct SSMS query is 00:00:01. PBI semantic refresh just reads off table where I have 2 renamed columns, 1 column change type, and 2 new measures.  Thanks

[MasonMA]

Weird.. 🤔

[TARUGOKING]

Just an update:

• We have 2 reports in Prod. I found out that the other report was running a long time as well. 7+ minutes. 
• Found out that our on-prem gateway is version 3000.202.13 (DEC 2023)
• The gateway is running on a Win 2019 Server, 8GB RAM with 2 Processors. The server handles SSO functionality.
• The Fabric capacity shows 64 Capacity Units and is it a Trial?

Action plan:

• Will update the gateway.
• Will beef up the server. 
• Researching why this is on Trial.

More to come - thanks.

[pborah]

@TARUGOKING if your 8AM power bi refresh is clashing with some other loads/processes on the SQL machine around the same time, that could be a culprit.

[TARUGOKING]

I'll check on this - thanks.