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

[know-how]

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.

[Brahmareddy]

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]

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]

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]

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.

[db_20240821]

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]

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 ?