Auto-refresh SQL/Python report + how it all interacts?

[BeautifulDash]

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? 

[BeautifulDash · Accepted solution]

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).

[Anonymous]

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

[BeautifulDash]

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.

[Anonymous]

Hi @BeautifulDash ,

Thank you for your insightful question. I understand your concerns about setting up a live data processing system in a corporate environment. It’s indeed a crucial decision that involves various factors such as cost, time, and technical capabilities.

Based on your requirements, I would like to suggest considering a trial of Microsoft Fabric. It’s a comprehensive analytics and data platform designed for enterprises, offering a suite of services including Data Engineering, Data Factory, Data Science, Real-Time Analytics, Data Warehouse, and Databases.

The advantage of using Microsoft Fabric is its integration and user-friendly interface, which simplifies your analytics requirements. Moreover, it operates on a Software as a Service (SaaS) model, bringing simplicity and integration to your solutions. A trial period would allow you to explore its features and assess its suitability for your specific needs without any initial financial commitment. This could provide valuable insights and help you make an informed decision.

 Fabric trial capacity - Microsoft Fabric | Microsoft Learn

In addition, please check if the content in the following links can be used as a alternative.

Build real-time dashboard with Azure Stream Analytics no-code editor, Synapse Analytics, and Power B...

Best Regards

[Anonymous]

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