DirectQuery with Gateway using SQLServer

[fala70]

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 ?

[lbendlin]

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

[fala70]

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

[fala70]

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

[lbendlin]

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

[fala70]

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 :

[Anonymous]

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

[lbendlin]

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

[fala70]

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]

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.

[fala70]

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]

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

[fala70]

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

[lbendlin]

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.

[fala70]

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]

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?

[fala70]

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