โ08-19-2024 01:20 PM
Is there a way to query Databricks using Power BI, via a stored procedure or similar? In my use case the query will have multiple parameters, that the user will provide values for at runtime. I don't think Databricks notebooks can be consumed directly from Power BI dashboards, but I could be wrong.
Thanks for any help with this.
โ08-19-2024 02:19 PM
Hi @know-how,
Please give a try using the Databricks ODBC or JDBC driver. With this you should be able to run SQL queries directly from Power BI, including those with parameters.
Regards,
Brahma
โ08-19-2024 02:29 PM
Hi @Brahmareddy thanks for the reply. In what form should the parameterized SQL be saved in, on Databricks? Asking since stored procedures don't seem to be an option.
โ08-19-2024 06:04 PM
Hi @know-how,
If your query doesn't require too much dynamic behavior, you could create a view in Databricks that pre-defines certain logic. Then, you can apply filters in Power BI when querying this view. Just give a try and let me know if it works for you.
CREATE VIEW your_view AS SELECT * FROM your_table_name WHERE column1 = 'some_default_value' AND column2 = 'some_other_value';
Regards,
Brahma
โ08-20-2024 07:06 AM
Hi @Brahma , my query involves a self join of a large (fact) table, with each side of the filtering on a different date (it's comparing some measures across dates). The two dates are what the user will provide at runtime (plus a couple of other parameters for filtering), so a view may or may not work.
In an earlier SQL Server world I used to inhabit, this would take the form of a stored procedure that had these filtering options (the 2 dates and couple other filters) as parameters; the user would provide the parameter values at runtime to the proc (via the BI tool), which would return a precisely tailored subset of the data. In addition, the user had the ability to switch the underlying query (multiple queries were encased in the proc) by the use of one of the parameters.
I'm hoping to find that sort of query flexibility in Databricks.
โ08-21-2024 01:45 PM
A stored pocedure in sql server contains code that will join many tables, create temp tables then do inserts to another table etc, etc,..... Unfortunately DTABRICKS does not have the equivalent. HELP HELP HELP
โ08-21-2024 01:51 PM
Hi @Brahmareddy
Sorry ... But Stored procedures are much more than just views. This is a big loss for DATABRICKS users. Can they not implement something ? SOON ?
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโt want to miss the chance to attend and share knowledge.
If there isnโt a group near you, start one and help create a community that brings people together.
Request a New Group