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:

https://community.powerbi.com/t5/Integrations-with-Files-and/Does-Power-BI-fully-support-Azure-SQL-Data-Warehouse/m-p/90435

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…

Azure Increases SQL Database Performance

Microsoft have recently increased the performance across all service tiers at no additional cost:

  • Write performance is doubled for all service tiers
  • Read performance is doubled for the Premium service tiers.

The official announcement is here.

For the benefit of anyone reading the older posts below, please remember some of the numbers should (in theory) be doubled.