Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bas
Frequent Visitor

SQL On-Prem with Enterprise Gateway refresh issue

Hello,

 

I have a question. I created a report in Power BI Desktop and used a SQL On-Prem server as source. On the server the Enterprise Gateway is intalled (and working). In Power BI Desktop I used te Get Data option and choose DirectQuery. Next I published the report to the Power BI service with a Pro account.

 

Because I'm using the Enterprise Gateway and DirectQuery, the data will be refreshed automatically. 

I want to show the report and dashboards on a big screen which is always turned on.

 

But the data is not automatically refreshed without clicking the Refresh button in the ribbon.

 

Is it possible to refresh the data automatically without clicking the Refresh button?

 

Thanks in advance.

1 ACCEPTED SOLUTION
wonga
Continued Contributor
Continued Contributor

@Bas

 

As @ankitpatira mentioned, because your dataset uses DirectQuery, the only time data on a dashboard and/or report will change is when it is accessed and a query is sent to your on-premises data sources through the enterprise gateway.

 

It sounds like your question is referring to having a dashboard showing at all times and seeing real-time changes as either new data or pre-existing data changes. This is not possible unless a query is being sent from the Power BI Service to your on-premises data source through a gateway.

 

In your example, clicking the "refresh" button is equivalent to sending a query(s).

 

You can either click the refresh button or possibly set your dashboard webpage to refresh on a certain interval through your web browser in some way.

 

Hope this helps clarify things.

View solution in original post

15 REPLIES 15
ankitpatira
Community Champion
Community Champion

@Bas If you've connected to on-prem sql server using DirectQuery then you don't need to worry about data refresh as data is not stored in power bi. With DirectQuery you are connecting live to sql server. Everytime you access the report queries are sent to on-prem sql server and data is returned back for those queries. 

 

What makes you say that data is not refreshed automatically ? You also don't need to worry about clicking Refresh button as it is live connection.

 

 

@ankitpatira Thanks for your reply. I understand that the data is automatically refreshed with DirectQuery. But my question is: Is it possible to refresh the data without clicking the refresh button on the ribbon?

@Bas Direct Query implies "automatic", but it isn't really like that unless you are actively using the reports. For display purposes, if you are showing your dashboard on large screen the tiles should be refreshed every 15-30 minutes, although I've seen a great number of threads that say it can take longer... depends.

Is there a way to "schedule" the refresh of tiles? "No"

Hopefully that answers the question, but throwing out "automatic" and "refresh schedule" in the same sentence is confusing as the two don't really have anything in common when talking about this connection type.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
wonga
Continued Contributor
Continued Contributor

@Bas

 

As @ankitpatira mentioned, because your dataset uses DirectQuery, the only time data on a dashboard and/or report will change is when it is accessed and a query is sent to your on-premises data sources through the enterprise gateway.

 

It sounds like your question is referring to having a dashboard showing at all times and seeing real-time changes as either new data or pre-existing data changes. This is not possible unless a query is being sent from the Power BI Service to your on-premises data source through a gateway.

 

In your example, clicking the "refresh" button is equivalent to sending a query(s).

 

You can either click the refresh button or possibly set your dashboard webpage to refresh on a certain interval through your web browser in some way.

 

Hope this helps clarify things.

Bas
Frequent Visitor

@wonga Thanks for your reply. My question is answered now. I wil use a plugin in my browser to refresh the report/dashboard. Unfortunally it is not possible (at te moment) to schedule the refresh on the gateway (like using the Import option). 

@Bas: Hi, could you please tell me what is your process of using the plugin from website ?:
1/ the name of your plug-in
2/ you refresh the report page or the dash board page? By refreshing the web page (f5) or click on the refresh button of dashboard/report
3/ and any other points that you think thus is important to know before implementing the solution.

thanks very much,
DAN
Bas
Frequent Visitor

Hi @DAN,

 

I am using Google Chrome as browser. I installed the plugin Revolver Tabs (https://chrome.google.com/webstore/detail/revolver-tabs/dlknooajieciikpedpldejhhijacnbda)


This plug-in is automatically scroling through the different tabs opened in Google Chrome. Every tab is showing a page of my report. Before opening the next tab in Google Chrome, the plug-in automatically refreshes the page. This ensures the recent most recent data is showed.

 

I hope that this (https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10861638-slideshow-in-full-screen...) idea will be launched soon. Because our reports are showed on a big screen.

 

Does this answer your question?

Hi @Bas,

 

Thanks for your suggestion, it works perfectly for me except that:

I want display this in a big screen, so i dont want others elements of Power BI Sites show off (<=> i only want my report active).

Do you have the same problem?

 

Thanks,

DAN

 

P/s: i tried to refresh the link come from "publish to web", but it seems not working...

Bas
Frequent Visitor

Hi @DAN,

 

I also have the same problem. I hope this idea (https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10861638-slideshow-in-full-screen...) wil solve this problem. This idea is to add a 'full screen mode' to Power BI. I think this wil solve our problem.

 

Did you install a gateway on the database server and configured the datasource? Is your refresh showing an error?

 

 

Hi @Bas,

 

Thanks for your active.

I hope so..

 

I installed the gateway of another machine of the server (who can communicate with the server ofcourse).

I configured datasource.

=> I passed this 2 points very easy. What's your problems?

 

DAN

SteveM
Regular Visitor

Hi, same isue, or perhaps confused over the expectation of how this would work.

Gateway installed and working

Direct queries to SQl setup and working

Withn Power BI Desktop I have Date filters on the data that is retrieved

I can view the reports via the office 365/bi interface BUT it never updates

 

The only way I can get the data to update is to open in Desktop, "Refresh" and then publish again.

 

The Datasource names etc are the same as per the instructions .

 

My expectation is that i wont need to do anything and the reports via the Web interface will be updated from the SQL source data automatically

Hoping if some can tell me if this is the expected behaviour. 

The queries setup use the "Today" filter on the date column.

This seems to be the only way to automatically retrieve the data for TODAY, this does not seem possible when done in the report/dash itself.

This "Seems" to be the issue with the data not refreshing correctly

Each morning I have to open the reports in Desktop, refresh and publish. from then on the data is nicely updated via the direct query/gateway throughout the day.

When tomorrow comes, the data set does not update and the reports are stuck on the previous days data until I refresh and re-publish again.

Assuming this is the expected behaviour then how do we set the filter in the reports to only show date from "Today" without having to refilter the data manually, I cant see how.

I would have thought having the filtering done at the query level would have been the more efficient way to do this ? 

Hi Steve,

When you use Power BI to filter the imported data source, they apply M like language on that source, for more details, Power BI will apply all the "STEPS" defined in the Power Query Editor each time they refresh the Power BI 's file.

 

So when you try to apply what you call "filter today" by the interface, they will calculate and save "the today "(ex: 28 july 2016) in your Power BI file and then they only keep "filter on 28 juyl 2016" when the refresh processes.

 

For the answer, is it possible? Totally yes. and how?

1/ By Power BI main language: DAX. Try yo use "Calculated Measure" or "Calculated Column" by apply the DAX's formula "measure = CALCULATE(yourMeasure; Date = TODAY() )

2/ By Power BI editor : find what happened (technically, not through the interface) when you click on "filter today" in "Advanced Editor" in the "Advanced Tab", it will show you the language M windows (which contains all the code behind the Power BI editor interface)

 

Have a nice day,

Hi Bas,
Ill implement your solution right now and ill keep in touch with you for the result.
Thank you very much 🙂
Have a nice day,
DAN
ankitpatira
Community Champion
Community Champion

@Bas If you've connected to on-prem sql server using DirectQuery then you don't need to worry about data refresh as data is not stored in power bi. With DirectQuery you are connecting live to sql server. Everytime you access the report queries are sent to on-prem sql server and data is returned back for those queries. 

 

What makes you say that data is not refreshed automatically ? You also don't need to worry about clicking Refresh button as it is live connection.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors