Manual Refresh - Stuck on x rows from (Azure SQL endpoint)

[ING_BT]

Hello all, 

I'am facing an issue with the manual refresh - below is the scenario and all the information: 

I have two sources:
1. Azure Databricks connector to an SQL Endpoint with a personal token

2. Google Sheet

I have an incremental refresh on several tables. When I do change the Start and End dates in power query and hit Apply & Load, it loads the table very fast without any issue. 

When I hit the "refresh" a specific table, there is no issue (tested tables from both sources, with or without incremental). 

However, when I hit the "Refresh" from the top nav - it loads all rows from all tables, and then it is just stuck on "xxx rows from {"host":...etc"

What have i tried already:

• I'm using the latest version (june-23) // I also tried using the may version (without success)
• I tried using another computer, just in case
• Cleared all the cash
• Increase parallel loading cash (900 out of 1828MB)
• Clear permission from all sources and reconecting 
• Deleting some problematic tables
• Changing Google's privacy setting from organizational to public
• I tried this loop canceling technique (maybe not applied correctly) link here

Note that, the organization removed my personal token access, so I had to do a few refresh by using the Auth0 sing in. Which worked on desktop but has issues with very large data sets on service. So we got back to a personal token and now this issue persists. 

NB: Impossible to share the pbx files. 

Thank you fro your help! 

Best,

BT

[v-shex-msft]

Hi @ING_BT,

In fact, the backend query tables are previewing result for query steps processing on the snapshot of remote data source. They not real pull data to the power bi query editor side at the present.

The 'xxx' row loaded means power bi are recognize and processing these backend query table steps. (getting data  from database and convert them to valid format to store into the AS tabular data model)

Due to above reasons, you may face the backend query table can be refresh quickly but the same refresh operation will be pending on the front side. 

You can also check following options which will affect the refresh performance:

1. Data source and network status: check if the data source is slow, the network connection is slow, or the Power Query is slow.
2. M Query functions and operations complexity: check if the query complexity is high, and try to simplify the query.
3. Data source table structure: check if the data source has missing indexes or statistics, and add them if necessary.
4. Data source key and relationship logic: check if data source has locks, and try to optimize the queries to avoid exclusive locks.
5. Device system availed resources: check if the resources (CPU and memory) on the On-Premises Data Gateway or the Capacity are sufficient.
6. Data transfer status: check if the data travel from the data source to the PBI report is efficient and try to reduce the latency.
7. Dax query complexity: check if the DAX queries refreshes are optimized, and try to reduce the number of DAX queries.
8. Parallel loading: Use the new setting "Parallel loading of tables" in option to optimize PBI engine performance in dataset refresh.

Regards,

Xiaoxin Sheng

[ING_BT · Accepted solution]

Hello and thank you for your message and explanation about what is really happening! 

Indeed, for the manual refresh, it seems that #2 is the issue for one table.

Concerning the service issue, I have created a ticket with Microsoft which was very efficient. It seems that when changing the data-source credentials in Destkop and pushing it to service it does not update them as well in service automatically (still set to Auth0 instead of Key) which was the main issue of the refresh. 

Best,

BT

[ING_BT]

Moreover, After pushing to power bi service, I look at the sql enpoint query history - I notice that some are repeted several times.. probably going in cercle..