Press "Enter" to skip to content

Month: September 2020

Optimizing Cross Join Performance in Power BI

Published 2020-09-01 by Kevin Feasel

Chris Webb tries an experiment:

In a late addition to the series of posts that I started here a few months ago, I thought it would be useful to look at how to optimise the performance of a rare (but nonetheless useful) type of merge: the cross join. Cross joins are not available as a join kind in the Table.Join or Table.NestedJoin M functions but there are two ways of creating them in Power Query:

1. Creating a custom column in your query that contains the query that you want to cross join with and then expanding, as Matt Allington describes here
2. Creating a custom column that contains a single value, such as the number 1, on both your queries and then using that column to merge the two queries on (something I mentioned a long time ago here)

Which one gives you the best performance? 

Chris has the answer to that question and the tests to prove it.

Comments closed

Testing Columnstore Data Loads on Eight-Socket Servers

Published 2020-09-01 by Kevin Feasel

Joe Obbish puts on the lab coat and safety goggles:

I elected to use a high concurrency CCI insert workload to compare performance between a four socket VM and an eight socket VM. Quite conveniently, I already had a test columnstore workload that I knew pushed the SQL Server scalability limits in terms of memory management. To perform the threading I used the SQL Server Multi Thread open source framework. I wanted all sessions to go to their own schedulers. That could have been tough to manage with tests up to 200 threads but the threading framework handles that automatically.

For those following along at home, testing was done with SQL Server 2019 with LPIM and TF 876 enabled. Guest VMs were built with VMware with Windows Server 2019 installed. The four and eight socket VMs were created on the same physical host with about 5.5 TB of RAM available to the guest OS in both configurations.

Read on to see how an eight-socket server fared in comparison to a four-socket server in this task.

Comments closed

Power BI Migration Documentation

Published 2020-09-01 by Kevin Feasel

Melissa Coates announces some new documentation:

I’m really pleased to announce that new Power BI Migration documentation that I authored is published on the Microsoft Docs site. Although it was written from the perspective of migrating to Power BI from another platform, there’s no doubt that a lot of the content applies to a standard Power BI implementation project.

Click through to learn more and follow Melissa’s link to get the whole document.

Comments closed