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
fala70
Regular Visitor

DirectQuery with Gateway using SQLServer

Hi, I've problems to show data using DirectQuery, when I publish dashobard.

 

I installed the gateway on my server where is present an instance of SQLServer database, same instance used to design the dashboard with poweBi desktop. When I publish the dashbaord, it's doesn't work. I've other dashsbords that are working, but withoug using directquery.

 

Datasource on gateway is right configured, and server name and database is the same used when I designed dashboard. Looking log of gateway it show follow message:  

"Gateway has determined that DirectQuery is not supported for the connection"
 
What mean ?
 
 

 

16 REPLIES 16
lbendlin
Super User
Super User

change the cache settings of the browser where you display the dashboard. 

ok I disabled cache of browser, we'll see next update

same problem, dataset is updated and charts still old data. I need to refresh browser manully to see charts updated

lbendlin
Super User
Super User

That is working as designed. If you want the dashboard to update you need to refresh an import dataset, push data into a streaming dataset, use external tools like Display Monkey to refresh the screen for you, or wait for the standard refresh period.

 

Data refresh in Power BI - Power BI | Microsoft Docs

what i am trying to make you understand is that the dataset refresh is scheduled so i would expect the dashboard to refresh at the scheduled times, but that doesn't happen :

 

fala70_0-1627387856418.png

 

v-shex-msft
Community Support
Community Support

Hi @fala70,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestion to help others who faced similar requirements to find it more quickly.

If these also not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

After you published the report you pin its pages or visuals to a new or existing dashboard

Well I did a test. After published report, I pinned it and I see the dashboard, but all charts are still statics. If I refresh broweser then charts are updated with new data.

lbendlin
Super User
Super User

That is a report.  A dashboard is created on the Power BI service, made up of tiles which are sourced from report visuals or report pages. A dashboard is non-interactive (ish - you can hover but you cannot click. Well, you can click, but that takes you to the underlying report.)

 

Report = interactive, for end users to interact with

Dasboard = a page that is displayed on a hall monitor for example, updating automatically whenever the dataset changes. Not interactive.

Ok I need a dashboard without any interactive. I created this report with with PowerBi desktop and then I pubished it on PowerBi service, so it now should be a Dashboard  ? Or what is the right method to create a Dashboard ? 

lbendlin
Super User
Super User

no, that should not be necessary. Are you sure you are looking at a dashboard and not a report?

No, I am not sure. I honestly don't know the difference between report and dashboard. That is what I am doing:

fala70_0-1627056432495.png

 

lbendlin
Super User
Super User

No, that's not how it works. Dashboard contents will update whenever the underlying dataset is refreshed.  That could be done with a schedule, ad-hoc API refreshes, or via a streaming dataset.

 

Direct Query is used for user interaction with reports, not for dashboards.

Well because then, on dashboard, where I am using normal import data (no direct query), I scheduled resfresh dataset, but to update charts, I need to run manually refresh browser ? charts are not update automatically.

lbendlin
Super User
Super User

First off - it is not advisable to install the gateway on a machine that is used for something else.  The gateway cluster members should be independent and dedicated VMs with lots (LOTS) of cores and decent memory and disk size - IF you use the gateway for dataset mashups.

 

Direct Query means that the gateway "only"  acts as a passthrough.  In this case network performance becomes much more important on both the gateway and on the database server machine.  In addition you need to make sure that your database server can actually handle the onslaught of the thousands of little queries. So better have your indexes and statistics in order etc.

 

Now to your question - Is the SQL server connection your only data source, or do you have other data sources in your direct query data model too?

My main goal is have a dashboard where data and charts are update automatically each hour. If I understood good, to have chart where are update automatically, without run refresh browser, is use directy query. No at the moment I've ony my data source with 2 dashbaord, one use directy query and other use import data.

If there are other modes to update charts automatically without refresh manually browser are welcome

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.