Forum Discussion
Auto-refresh SQL/Python report + how it all interacts?
Set up:
- I have a Power BI report pulling in data from two sources: i) SQL database with over a million records of historic electricity consumption, and ii) a Python that requests the last 24 hours of usage (as the database only gets updated daily at midnight).
- The report visualises the data using a chart (drill-down, drill-through).
- The report is published on My Workspace (which is a Windows Virtual Machine being run on Linux).
- The report uses a SQL database on a local machine (a Raspberry Pi at 192.168.x.x) as a Source that does not allow connections originating from outside the LAN.
How do I make it so that, when I consult the report, for instance on mobile, it automatically refreshes the report? I think Direct Query might help for the SQL database, although I'll need to find how to change the Source from Import to it, but I'm unsure if it'll also re-execute the Python 'live' script.
Related: Could you explain how this all interacts? I don't understand how Power BI functions. Does the report sit in a Microsoft cloud server somewhere? If I consult the report when out and about, on 5G, can it still connect?
From the linked threads, I understand that Python requires your PC to be online, and there is no workaround. I, however, found a workaround. Instead of using Python as a source, I use Web as a source, and set up a custom web server on a Raspberry Pi using socat that, when prompted by http://IP:PORT, runs the Python script and then serves up the pandas generated <table> html, that Power BI can easily interpret. I still need to test it, but I assume that solves it, and I can then use the manual Refresh button in the Power BI mobile app to get the latest data (in addition to the SQL historic data).
5 Replies
From the linked threads, I understand that Python requires your PC to be online, and there is no workaround. I, however, found a workaround. Instead of using Python as a source, I use Web as a source, and set up a custom web server on a Raspberry Pi using socat that, when prompted by http://IP:PORT, runs the Python script and then serves up the pandas generated <table> html, that Power BI can easily interpret. I still need to test it, but I assume that solves it, and I can then use the manual Refresh button in the Power BI mobile app to get the latest data (in addition to the SQL historic data).
Hi BeautifulDash ,
Thanks for sharing your method here. It will be very helpful for the others in the community if they have the similar requirement as yours. Your contribution is greatly appreciated.
Best Regards
Sure. Socat as a utility is finicky so I recommend setting up a Python Flask server instead, after testing. A question from my end; in a corporate environment, what would be the correct way of processing live data (from Python calling an API endpoint)? I was researching this for a few days and the solution seems to be: Microsoft Fabric, which costs money and time to set up. My solution does not feel right for a production environment either, however.
Hi BeautifulDash ,
You can refer the following blog to switch the connection mode from Import to Direct Query, which allows the report to query the database in real-time rather than importing the data into the report. This means that any changes to the database will be reflected in the report immediately.
Power BI Switch From Import To Direct Query Mode
For the Python script, you can refer the following links:
Solved: Refreshing Python script on PowerBI Report - Microsoft Fabric Community
Solved: Power BI refresh using python script - Microsoft Fabric Community
Best Regards
