Power BI report server real time streaming data from Postgresql and embedding into HTML

[Jessica_17]

Hello All,

I have created a direct query report from Postgresql and have added into report server, but when there is a change in data , i have to manually click it and even though it is direct query and when I embed it and try auto refresh feature, it is loading the whole page, I would want it to work as auto refresh or real time streaming data should flow from reprot server reports. Can anyone please help me in resolving this.

[v-jtian-msft]

Hi,@Jessica_17 . I am glad to help you.
It seems that you are confused about the refresh characteristics of the pbix file for the direct query connection mode set in the reportserver.
I hope the following suggestions will answer your questions.
1. For the real refresh characteristics of reports in direct query connection mode (uploaded to Power BI Report Server/Service).

pbix file uploaded to report server local server (connection mode using Direct Query)
Reports cannot be set up for Scheduled Refresh(in report server), the data is real-time. It is important to note that the real-time feature of Direct Query is a pointer to the visual on the power bi report, when the user interacts with the visual in the report, such as clicking on the slicer, filtering the data etc. Through the direct query connection mode, the system will directly send a real-time query with filter conditions to the data source, and then the data source will pass the data to the report for display, so the real-time feature is for the user to interact with the report, always send a new query request to display the latest data, rather than for the entire report to refresh the data.

Currently for Power BI, whether in Power BI Services or Report Server, even if you set up a high-frequency data refresh schedule for your report or even use DirectQuery, the report page can not instantly display the latest data, because the report page is displayed before you open the report page of the latest data cache This is because the report page shows you the cached results of the latest data before you open the report page, and subsequent updates to the data will only return the latest results if you manually refresh the page or reopen the report.
This is due to the design of power BI, which displays cached results.

Note that the data source you are using supports Direct Query connection mode in Power BI Desktop, but for the official documentation, PostgreSQL does not support Direct Query connection mode (Live Connections) in Report Server.
In Power BI Desktop(RS)

URL:
Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn
 

Due to my work environment I am not able to use PostgreSQL data source to try to reproduce your problem, if your problem might be affected by this limitations, you can try to use import mode and set Scheduled Refresh.

If you have embedded the report in HTML and need to wait for the whole page to load every time you refresh the report page, due to some limitations of the report server, I recommend you to try the following suggestions.

Solution1:
Install a plug-in on your browser that refreshes the page at regular intervals. You can add a plug-in called “Auto Refresh”, and then you can set the page refresh interval:
When you use this way to refresh the page, the whole page needs to be reloaded, the whole report screen will appear briefly in each refresh white page, if you are an hour to refresh the page is nothing, if it is less than a minute to refresh the page, then the experience is quite poor.

Solution2:
The second method is the result I got through the Internet search, you can try to see if it is effective.
Setting refresh parameters on HTML

You need to automatically refresh the page of the report with an iframe embedded in the HTML, so that you can achieve a partial page refresh, so that it only refreshes the report itself, the result of the test is that it improves the page load speed of each refresh, but still can not avoid the white page stays. So what we really want is not to refresh the page, but to have the report backend perform a new calculation to return the latest results, as if we clicked the “Refresh” button:
Some people may have already thought of a way to set up a timed script to click the “Refresh” button, but a better way is to find the element where the button is clicked and write a simple javascript in the browser backend to execute it. First open the report in the web portal and right click on the element, we can find the element id of the “Refresh” button:
But there is a problem, the element id is not one-to-one with the “Refresh” button, when you execute the element, it actually executes “Edit in Power BI Desktop”. So you need to change its id, for example in this case I changed it to “c”.

Finally, open the console and enter the following js code:

setInterval(function(){document.getElementById("c").click(); }, 5000);

Where “5000” means 5000 milliseconds, that is, the Command Browser performs a “Refresh” every 5 seconds, and then execute the command, the command report will automatically refresh the data every 5 seconds, the effect is the same as you click the “Refresh” button, no need to reload the page. And this method is applicable to Power BI reports with any type of data source, not only Direct Query connection mode.

Simply put, due to some of the current limitations of the report server's functionality, you can start by refreshing the browser page to see if you can achieve a similar effect, bypassing some of the current limitations of the report server's functionality

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jessica_17]

HI @v-jtian-msft 

Really Thank you so much for the help, I have implemented the above suggested solution, while it works in the power BI report server web link, but while embedding it into HTML page, it does not work at all, it says  "Button not found" , I found many javascript methods to do so, but somehow while refreshing the PBIRS report in HTML, it is loading the whole link every 5-6 seconds, which seems like this as screenshot below which not user friendly at all, and not able to make it seemless also.

Can you help in making it seemless as Client said they need it live and do not want any delay in refresh too.

[v-jtian-msft]

Hi,@Jessica_17 .Thank you for your reply.

If your report is part of a front end (embedded in a web page)

I would recommend trying to refresh the page tag where the report is embedded in your html via Java Script (I'm not sure if this is the way you mentioned in your reply)
like this:

function refreshDiv() {
    // Get the div element that needs to be refreshed
    var divElement = document.getElementById('yourDivId');

    // Clear the current content of the div
    divElement.innerHTML = '';

    // Reload new content, this can be new data fetched from the server or static content
    var newContent = '<p>This is the new content</p>';
    divElement.innerHTML = newContent;
}

// Set an interval to refresh the div content every 60 seconds (60000 milliseconds)
setInterval(refreshDiv, 60000); // Refresh every 60000 milliseconds (60 seconds)

Note that if the embedded report has a very large amount of data, then you need to expand the time interval (e.g., perform a refresh once every two minutes) to avoid refreshing too often and keeping the entire embedded report consistently loading (i.e., the screenshot shown in your reply).
The above suggestions are for your report is embedded as a front-end page element in your html, looking forward to your reply.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jessica_17]

Hello @v-jtian-msft 

Thanks for your constant support, I tried adding this piece of code in html part as well as tried in the console too. But it is not refreshing at all, let me know what I am doing wrong, here is the error of the console

and the html code on which I had embedded

<html>
<head>
<title>Seamless Power BI Report Refresh</title>
</head>
<body>
<h1>Embedded Power BI Report</h1>
<p>The report below will refresh automatically without reloading the page.</p>

<!-- iframe to load the Power BI report -->
<iframe id="reportIframe"
src="http://192.168.1.2/Reports/powerbi/Demo_DirectQuery?rs:embed=true"
title="Embedded Power BI Report"
style="width: 100%; height: 500px; border: none;"></iframe>

<script type="text/javascript">
// Set the refresh interval in milliseconds (e.g., 5 minutes = 300000 ms)
var refreshInterval = 6000; // 5 minutes

// Function to reload the iframe content
function refreshIframe() {
var iframe = document.getElementById('reportIframe');
iframe.src=iframe.src; // Refresh the iframe by reassigning the same source
}

// Set the interval to refresh the iframe content seamlessly
setInterval(refreshIframe, refreshInterval);
</script>
</body>
</html>

[v-jtian-msft · Accepted solution]

Hello,@Jessica_17 .Thank you for your reply.
You will need to contact a technician on your team who is proficient in Java Script (and other front-end page development languages) to assist you in embedding reports via front-end timed refreshes.
If your report is part of a front-end layout, I hope the following article will help.
I hope the following articles will help you.
URL:
How to Reload DIV in JavaScript | Delft Stack
Auto-refresh DIV every few Seconds using JavaScript and Ajax (encodedna.com)
 
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jessica_17]

Thanks @v-jtian-msft 

Thanks for the solution, This might help me solve the issue.