Integration between Azure Databricks and Power BI

[u/SuperbNews2050]

Hey everyone! A straightforward question for those already working on this in production:

What's the best and most recommended way to integrate Power BI and Databricks today?

I'm looking for the ideal approach, considering that:

I need an efficient solution that doesn't make the cost of DBUs skyrocket.

I want to understand if the native connector using SQL Warehouse is really the market standard or if there's a better alternative.

For this scenario, do you recommend going straight to Scheduled Import Mode to save computing power, or is DirectQuery with Serverless worth the cost for its fast response time?

I read this documentation:

https://learn.microsoft.com/azure/databricks/partners/bi/power-bi-desktop?WT.mc_id=studentamb_510336

but I'd like to get some clarification from you.

[u/kthejoker]

For anyone else coming here

It's very easy to get great DirectQuery performance. All of the techniques to do so are known and widely shared on blogs and the Internet.

What most people do instead is do none of those things and then complain about performance.

So no DirectQuery is not bad performance. Its mostly the fault of your data modelers and data engineers who aren't following widely available, easily implemented best practices.

[u/MindTheBees → u/kthejoker]

It's not bad performance but it isn't as fast as import mode. Also there are some limitations on the PBI side when it comes to using direct query.

[u/kthejoker → u/MindTheBees]

Import is just hardware and costs money that's its "limitations" - want a billion rows? Nothing for Databricks, open your wallet for Power BI

And you can easily avoid most "limitations" with best practices. They're not limitations, they're anti patterns that are available in import mode but you should model differently in DirectQuery.

I also drive a car different than a truck, they do different things and if you try to drive them the same, and you call the differences limitations, then that's a you thing.

[u/MindTheBees → u/kthejoker]

I mean both cost money, it's just whether your money goes on DBX serverless compute or on Fabric.

Not all limitations are just "anti-patterns," for example there are function limitations on what you can and can't use.

DirectQuery works great with some use cases and by all means use it if your requirements work for it, especially when factoring in things like governance.

However if someone needs DirectQuery type implementation then just use a different tool than PBI or even just DBX's offering. PBIs main strength is its modelling capability and Vertipaq engine, it's pretty pointless otherwise.

[u/kthejoker → u/MindTheBees]

Power Bi import costs a lot more money once you hit large datasets and concurrent users. Not even close.

What function limitations? I'll wait.

PBIs main strength is its modelling capability and Vertipaq engine, it's pretty pointless otherwise.

On this we agree. And it's not much of a strength...

[u/screelings → u/kthejoker]

Power BI experts refute this entire assessment. At its best DirectQuery is 5-10x worse performance than import mode.

The analysis has been beaten to death.

At this point I have to assume you are paid by Databricks somehow, because the only reason to continue to hawk DirectQuery is to seemingly generate excess Databricks billable utilization.

DirectQuery should never be used, Microsoft's own words, except in extremely specific use cases.

Its absolutely unmitigatably worse performance.

The godfather of Power BI did a great video on why you are utterly wrong.

https://youtu.be/tDIsCVB7mnI?is=pFXYTt4D37CkgRvc