Azure SQL Data Warehouse, Power BI and old problems

Azure SQL Data Warehouse is a fantastic MPP solution that puts an incredible amount of data storage and processing power at your fingertips.  With all of the benefits of the cloud and great SQL Server compatibility as well.

Power BI has native connectivity to  Azure SQL DW, including via Direct Query, allowing interactive reports to be run over extremely large data volumes.

One niggle is the inherent limitation in Power BI of only being able to create one relationship between each table.  For one of my recent use cases with Azure SQL DW, this Power BI limitation meant it wasn’t possible to have Power BI generate queries that are optimum.

Generally speaking with Azure SQL DW, eliminating/minimising the data movement between the compute nodes in query plans is a good thing.  In my scenario, I could easily hand write a SQL query that minimised data movement by including an extra join condition.  Power BI can’t generate this extra condition however, so that plan isn’t so optimised.

All the details are here:

It’s a modern example of the age-old problem that handwritten code can usually be written that outperforms machine written code.  We have all shiny new technology, and incredible technology it certainly is, but often the age-old niggles are still there…

UPDATE  14th May 2018:  The Power BI team has added the COMBINEVALUES() function, which helps greatly with the above problem.  Details already blogged at:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s