Semantic model schedule refresh is taking so long

[Ynr225]

Hello All, 

I have trying to publish over an existing Dual Storage star schema report for the last 40 minutes, and I added a every 30 minute refresh schedule, although I am not sure if that was necessary, since I am not sure how the Dual storage function works, which I have now turned off. The report used to be direct query and I changed it to dual and I added the refresh schedule later

And it looks like there as been a queue for two refreshes, and the report won’t publish.

Here i am using Oracle as a data source in Power BI desktop and created report, In Power BI desktop refresh is taking around 1 minute but in Power BI service taking so long.

Any suggestions on this

[V-yubandi-msft · Accepted solution]

Hi @Ynr225 ,

Thank you for your inputs, @bhanu_gautam . In support of @bhanu_gautam  response, I would like to add a few points for further clarification.

1. This mode enables tables to function as both Import and DirectQuery, enhancing performance but also increasing refresh management complexity. Ensure your dataset is optimized according to your reporting requirements.
Use storage mode in Power BI Desktop - Power BI | Microsoft Learn

2. If the report is not publishing, check for ongoing refresh processes in Power BI Service that might be causing conflicts. Try disabling the scheduled refresh, publishing the report again, and then re-enabling the refresh once the issue is resolved.
Configure scheduled refresh - Power BI | Microsoft Learn

3. Refresh times in Power BI Desktop vs. Power BI Service can vary due to network latency, resource constraints, and query execution methods. If your Oracle queries are fast in Desktop but slow in the Service, consider.

Optimizing Oracle database indexing and execution plans.

Minimizing Power Query transformations to reduce processing overhead.

Data refresh in Power BI - Power BI | Microsoft Learn

4. Review the refresh history in Power BI Service to identify errors and performance bottlenecks. If your queries involve transformations, ensure query folding is enabled so that transformations are processed directly in Oracle instead of Power BI.

I hope this helps...

[bhanu_gautam]

@Ynr225 

It seems like you are experiencing issues with publishing and refreshing your Power BI report after changing the storage mode from Direct Query to Dual. Here are a few suggestions to address the problem:

Understand Dual Storage Mode: Dual storage mode allows a table to act as both Direct Query and Import. This means that some queries can be answered using cached data (imported) while others can be answered using live data (direct query). This can improve performance but also adds complexity in managing refresh schedules and query execution.

Refresh Schedule: Since you have set a refresh schedule for every 30 minutes, it might be causing a queue if the previous refresh hasn't completed yet. You might want to check the refresh history in Power BI service to see if there are any errors or if the refreshes are overlapping.

Performance in Power BI Service: The difference in refresh time between Power BI Desktop and Power BI Service can be due to several factors including network latency, server load, and the complexity of the queries. Ensure that your Oracle database is optimized for the queries being run and check if there are any performance bottlenecks.

Publishing Issues: If the report is not publishing, it could be due to the ongoing refresh queue. Try turning off the refresh schedule temporarily and then attempt to publish the report again. Once published, you can re-enable the refresh schedule.

Check Data Source Configuration: Ensure that the data source settings in Power BI Service are correctly configured and that the credentials are up to date. Sometimes, issues with data source authentication can cause refresh failures.

Monitor Resource Usage: Power BI Service has resource limitations, and heavy queries or large datasets can cause delays. Monitor the resource usage and consider optimizing your queries or reducing the dataset size if possible.