Advancing Spark - Databricks SQL Analytics & Power BI

[@drummerboi4eva]

Super tutorial Simon, thanks for sharing !!

[@amlucius]

Great tutorial. Is there a reason why they can't have a SQL statement option on the Power BI connector? Is that something that *can* be rectified in the future?

[@l_combo]

So about the cube / OLAP - we are looking at things like Kylin / Kyvos / At Scale etc. to act as a more traditional OLAP / Cube between DB and PBI and similar tools. Do you see SQL Analytics meeting any of those needs or just making it easier to have simpler / less performant reporting between BI and Delatlake / DB? Things like intelligent / automated push down, drill down / through are important and I still see that being a gap without something like a Kyligence layer in between.

[@AdvancingAnalytics]

There's definitely some interesting stuff in there that could hint at SQL Analytics being used for more stuff - there's an "external datasets" section which hints at being able to use it as a data serving/virtualisation layer, and it's caching ability & photon will hopefully come along to speed up some of the regular BI requests. Will it be able to push-down effectively on external datasets? How well will it reactively cache? How does caching work across the multi-cluster load balancing?? All things we will find out eventually!

[@VINITSANSARE]

Nice decoration...👍

[@ali76685]

What is the difference between SQL Analytics SQL and writing the query in Python notebook using %sql. Are this SQL Analytics just another workspace with additional features?

[@AdvancingAnalytics]

From a query execution point of view, there's not too much difference yet - the workspace is a slightly better IDE, you get better SQL typedown, metadata browser etc, more visualisation options etc, but it hits the cluster in the same way. However, with the Photon elements, there should be quite a few improvements around execution speed, and the multi-cluster load balancing is going to be pretty big.

[@Advancing_Terry]

Soooooo festive. Love it

[@rachelrdyer]

Great video, thank you so much. We ran into a problem with data types all being interpreted in Power Query as "general," and couldn't get something that came in looking like a date, e.g. 4/2/2020, the Power BI couldn't recognize as a date. Wondered if you ran into that.

[@AdvancingAnalytics]

Nope, not a problem we've seen! I've just double checked one of our sample models and datatypes are being correctly read from Hive. The timestamp columns in Hive are correctly coming through to Power BI as Date/Time etc. I would double check you are using the Azure Databricks connector rather than the older spark one, then check the hive tables in Databricks SQL and make sure datatypes are properly registered there? Simon

[@BehzadBayat]

How does acces to public preview works? I have created new worspace in premium through azure but I don't see the SQL analytics option.

[@AdvancingAnalytics]

It's a gated public preview - you need to request access, have a chat to someone from Databricks to explain your use case then they will enable your account. The form to fill in is here: databricks.com/p/event/sql-analytics-waitlist

[@snigdhanarayan9754]

I have a situation where I have to connect from Databricks SQL analytics to Power bI in 2 modes : One as a Direct Query and One as Get Data / Import. How do I do that?

[@AdvancingAnalytics]

Hi! That's what's known as a composite model in Power BI - it's all configuration on the Power BI side. There's more detail over on the Power BI blogs, or hit up the GuyInACube channel! docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

[@akhilannan]

Will the photon engine appear eventually to the normal Databricks workspace as well or it will remain a SQL end point only feature?

[@AdvancingAnalytics]

Can't say for certain, you'd have to ask Databricks! I'd assume it would go into the core spark engine eventually, but I imagine that getting the whole engine working in a single language (SQL) is a good first step for trialling a new execution engine, then they can roll out to python/scala/R etc once the engine has been hardened with some real-world usage?

[@renatofreitas3877]

Very good Simon. SQL Analytics is Redash incoporated into Databricks?

[@AdvancingAnalytics]

Yep, pretty much! But they're doing lots with the clusters, photon engine to tighten the integration and add a load more stuff in there

[@thisiszico2006]

Noice

[@zycbrasil2618]

hohoho :)

[@AndreasBergstedt]

Looks like Databricks is overselling their shiny new product.

[@andersboje]

Curious to hear what you think they are overselling?

[@AndreasBergstedt]

@@andersboje based on the message on their website they are clearly overstating their capability and their use case. databricks.com/product/sql-analytics

[@AdvancingAnalytics]

It's tricky - it's definitely not going to replace your existing BI tools, if that's the messaging then I agree it's oversold. But with photon as one of the big pushes, it's definitely going to change how we serve data to those layers, and how performant dashboards directly over a lake can be... and that's a pretty huge message. I think big picture, it has a lot of potential, current view, it's a small step forward :)

[@AndreasBergstedt]

@@AdvancingAnalytics guess it's hard for me to be impartial :)