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: