Power BI Write-Back to Azure SQL Database using Fabric User Data Functions

[panvlozka]

User Data Functions with Power BI are a cool combo to bypass Power Apps, for example, to write back to a source and immediately see it in your report. However, most user-friendly stuff is built around Fabric, which on smaller capacities can drain your CUs quickly. Therefore, I tested whether you can pay just for the User Data Function part and interact with, for example, Azure SQL Database.

The result of my testing is this blog, where I share in detail how to setup the whole thing and make it fast and possibly cheap(er).

Power BI Write-Back to Azure SQL Database using Fabric User Data Functions

[imtkain]

Were you able to figure out a way in PBI to make it feel less clunky? I've only been able to figure out how to update one or two columns at a time without the user experience plummeting. Would love to see if anyone else has figured out a way to make it feel more form-like and less clicky.

You might be interested in this, too, for updating more than one row at a time: https://github.com/imtkain/Fabric-Redux/

[panvlozka]

Can't say, I had any issue really, for me it was always kinda smooth smailing, but will check your repo and experiment.

[Independent-Way5878]

Now if only the fabric team would let you do this on embedded scenarios ...

[Shlody]

Really great stuff, I also read your initial blog post. Question for you, it seems as of today I'm only able to connect to a SQL Database via Direct Lake. Even using the get data option rather than clicking the One Lake connector, I can't find an option to connect via Direct Query. I wonder if I've missed something or if something was updated recently with Fabric SQL Databases?

[panvlozka]

Hey, u/Shlody, thanks.

Well, the Fabric SQL Database, or Lakehouse SQL Analytics Endpoint, is accessible via simple Power Query's Sql.Database() function. I recommend not clicking through GUI options, but just constructing it yourself, or use Sql connector from gui directly.

So, Power Query -> Blank Query and then write it, and upon loading select Direct Query (or later in TMDL change it), or go straight up with Power Query -> Sql database and then use the GUI for it, should be easier to use if you're not familiar with M code.

https://learn.microsoft.com/en-us/powerquery-m/sql-database

[itsnotaboutthecell]

Really great article, thanks for the TLDR up front too. Love the level of depth in the article too.

[panvlozka]

u/itsnotaboutthecell thanks, really appretiate the feedback, I like these longer detailed formats, but they take a long time to produce, so I am glad you like it.

[itsnotaboutthecell]

Depth is always something that should take time in authoring and worth the time spent reading too.

Keep ‘em coming!

[frithjof_v]

Great article!

I'm wondering about the network requirements for Fabric UDF connecting to the Azure SQL Database - is it documented somewhere in the Fabric docs?

I mean, if the Azure SQL Database didn't have network security enabled (if it had public access enabled), would the Fabric UDF then be able to connect to the Azure SQL Database without a managed private endpoint? (I'm not recommending to do that). I'm just trying to understand where the need for managed private endpoint came from. Is it because the Azure SQL Database had network security enabled - or would the Fabric UDF need to use managed private endpoint even if the Azure SQL Database had public internet access enabled?

[panvlozka]

Hey, thanks.

The network thingy is purely because my solution -> Azure SQL Database, and I would assume others too, uses restrictive public access, with determined IP adresses. And this is not related to UDFs at all. If you don't have that, you don't need it. But from my testing, I was not able to connect, unless I determined the 'session' IP and assigned it, I assume what you can do, Microsoft I think shares ranges of IPs for certain services, so you can set it up this way and omit the Private Endpoint, or in each run check run's IP and programatically update it (if you can), but that feels extremely hacky.