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

Writing on Insight Quest, Brett Powell explored the impact of PowerShell's general availability in March. Among its new features is the ability to do pipeline parallelization for ForEach-Object cmdlets, cutting the duration of these scripts. But nonetheless, there are still some situations where scripts loop over Power BI workspaces when they shouldn't. Powell accessed a Power BI tenant with over 1,500 active workspaces and 1,900 datasets.

He shared two scripts to GitHub, one with a non-parallel approach and a 28 minute runtime and the other adopting a parallel approach that took only eight minutes to complete. He wrote:

It's important to note that the ForEach-Object cmdlet requires piped input like the $ActiveWorkspaces variable in the above example. It’s easy to get them confused but the ForEach-Object cmdlet is completely distinct from the ForEach keyword used in the first example above. The performance impact for your scenario will of course vary based on the size of the tenant being accessed, threads available (I have 4 cores on my machine) and whether you use the optional -ThrottleLimit parameter.

Shifting columns into a display folder

Microsoft MVP Matt Allington shared a new article about Tabular Editor on the Excelerator BI blog about his experiences writing his first script. Users no longer need to save PBIX files as PBIT prior to editing and must be aware that for now backup PBIX files aren't supported. Opening the PBIX file in Power BI Desktop, Allington wrote out case sensitive scripts for moving columns into a display folder.

In a second round of scripting, he created a script to hide columns in fact tables using joins, following a best practice of hiding any column in a fact table that participates in a join. He wrote:

Consider the ProductKey column.  This column exists in both the product table and the sales table.  Once a relationship has been created you really should use the ProductKey from the Product table, not the one in the sales table.

Saving time with Power Query

According to Erik Svensen, users sometimes add an extra step to Power Query without realizing it. In a recent practice exercise, he imported FactInternetSales from the AdventureWorks database, filtering for a specific date. From there, he accessed the dropdown of product keys and selected OK. By clicking OK rather than specifying one, an extra step is added to Applied Steps.

Fortunately, users can quickly resolve this action by clicking cancel and wiping out the extra step, thus streamlining their queries.

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About MSDW Reporter

More about MSDW Reporter