Power BI Insights: Data refresh; Parallel scripts; Moving columns; Power Query

April 11 2020

Microsoft Power BI pros share their latest insights on data refresh, parallel scripts, moving columns into display folders and Power Query efficiency.

Making sense of Power BI data refresh

Chris Webb followed up on a recent blog looking at XMLA endpoints, turning his attention to the role that SQL Server Profiler can play for assessing data refresh times in combination with Power BI Premium. Refresh performance often differs between Power BI Service and Power BI Desktop, because Power BI Service often has to go through on-prem gateways to access data sources.

Users need to deploy two sets of trace event commands, one Command Begin/End and the other Progress Report Begin/End. Once the report gets running and users launch a refresh it will begin to populate with events. For now, there are a lot of options in Analysis Services that aren't yet available in Power BI. Among the outputs, the most important categories are ones such as EventClass, EventSubclass or Duration. He wrote:

Finding the amount of time needed to refresh individual tables is not so straightforward because…refreshing a table consists of a number of different jobs. The easiest thing to do is to look for a Progress Report End event with EventSubclass 59 where the TextData column starts with “Finished processing partition…” followed by the name of your table…

In another recent but unrelated post, Webb turned his attention to the problem behind the error code "Expression.Error: The key didn't match any rows in the table."

Managing parallel Power BI scripts

About MSDW Reporter

More about MSDW Reporter