cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Administration & Architecture
Explore discussions on Databricks administration, deployment strategies, and architectural best practices. Connect with administrators and architects to optimize your Databricks environment for performance, scalability, and security.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

What is the Databricks equivalent of a stored procedure that works with Power BI?

know-how
New Contributor III

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.

6 REPLIES 6

Brahmareddy
Valued Contributor II

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

know-how
New Contributor III

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.

Brahmareddy
Valued Contributor II

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

know-how
New Contributor III

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.

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 

db_20240821
New Contributor II

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 ? 

Connect with Databricks Users in Your Area

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