topic Re: Write-back functionality from PowerBi into Databricks in Warehousing & Analytics

Write-back functionality from PowerBi into Databricks

VCA50380 — Tue, 04 Feb 2025 16:03:10 GMT

Hello,

I'm not sure if it is the correct place to post this, sorry.
Migrating from an on-premise Oracle to Databricks, we are wondering about the following functionality:
. From the reporting tool in place (currenly, PowerBI), users are able to send back to the database some data / some texts. This data is then stored in an Oracle table and re-used later by the reporting.
How can we best reproduce this in Databricks?
What should be the process and the underlying objects in Databricks in order to have the same?

Thanks!
Vincent

Re: Write-back functionality from PowerBi into Databricks

Allia — Wed, 05 Feb 2025 05:46:14 GMT

@VCA50380 Greetings!

Can you elaborate more on the requirements here. Below is the documentation regarding connecting PowerBI with Databricks

https://docs.databricks.com/en/partners/bi/power-bi.html

Re: Write-back functionality from PowerBi into Databricks

VCA50380 — Wed, 05 Feb 2025 09:51:31 GMT

Hello,

Let's imagine we have a dashboard, currently done by PowerBI but it could be any other reporting tool, and in this dashboard there is a form, in which users can enter some free texts.
Then, users are clicking on a button and the texts in the form is sent to the database and inserted in an Oracle table.
As of today, we are on a on-premise Oracle, and some data are sent to the database either via some functionality like the native PowerBi Write Back, or some custom development via PowerBi Apps and/or via some classical Ajax POST calls (sending data back to the database).

So, what would be the process to reproduce the same in Databricks?

Thanks
Vinc.

Re: Write-back functionality from PowerBi into Databricks

Allia — Thu, 06 Feb 2025 06:38:43 GMT

@VCA50380 Have you explored Notebooks functionality in Databricks

Widgets provide a way to capture interactive input in your notebook.
Using that input, you can build SQL statements to update your backend Oracle database.
Connectivity Options:
- Use Spark’s JDBC connector for bulk operations.
- Use a Python connector like cx_Oracle for executing individual SQL commands, which is more straightforward for interactive, one-off updates.
- Then you can build Interactive dashboards based on the updates

This approach allows you to combine the interactivity of Databricks notebooks with the power of programmatically updating your Oracle database based on real-time user input.

Re: Write-back functionality from PowerBi into Databricks

VCA50380 — Sat, 15 Feb 2025 15:07:56 GMT

Hello Allia,

Thanks for your answer.
I'm not sure I've explained myself correctly enough so that you can understand my situation, sorry for this.
You are mentioning Oracle and "cx_Oracle ", ok, but this is currently what I have.
My point is "how can I do the same in Databricks?". Let's imagine all our Oracle environment is decommission, and so we have to forget about it. How do I do the same as before in Databricks?
Thanks
Vincent

Re: Write-back functionality from PowerBi into Databricks

Mantsama4 — Mon, 17 Feb 2025 14:00:52 GMT

Great question, Vinc! Databricks doesn’t provide a native write-back feature for Power BI, but you can achieve similar functionality using a combination of Power Apps, Azure Functions, or REST APIs to write data back to Databricks.

One common approach is to use Power BI with Power Automate or Direct SQL connections to write user inputs into a Delta Table in Databricks, which can then be leveraged for reporting. Alternatively, you could use a Databricks REST API with a structured workflow to capture and store user input dynamically.

Would love to hear if anyone has implemented this differently!

Re: Write-back functionality from PowerBi into Databricks

VCA50380 — Mon, 17 Feb 2025 14:25:45 GMT

Hello Mantu,

Thanks for your answer.
This is clear, and as our colleague on PowerBI is already dealing with Power Automate, he should be able to test this.
If we will be allowed to use Databricks REST API (our infra guys will tell us), I guess we will be happy to test that.

Thanks.
Vinc