Naturally you want the reports that you publish to Power BI Service to be up to date, but the rules are fiendishly complex. This blog explains when and how to create the two types of gateway, and how to ensure your reports refresh.

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

How connections work for Direct Query

The previous part of this blog describes how normal report connections work.
Direct Query is a bit different.

To explain this part of the blog, I've taken my
Excel workbook and CSV file and put them into
two SQL Server tables (you can download script
to generate these tables here,
if you want to follow along).

Importing data using DirectQuery

When you are importing a set of tables from a single database (often Oracle
or SQL Server), you can use Direct Query:

Select DirectQuery to get all of your data directly from the underlying tables, without an intermediate data model.

Direct Query has
several limitations: you can only use data from certain databases, all your
tables must come from the same database there are
limits on what measures you can create, and there is no intermediate data model
(and hence you can't have calculated columns).

Spot the difference? In a report using Direct Query, there is no
Data tab.

The main reason to use DirectQuery is when you have huge amounts of data,
which would overflow the Power BI Desktop 1 gb limit, or take too long to load
into a data model.

How connections work in DirectQuery

The report diagram for Power BI Desktop when using DirectQuery is much
simpler:

There is no data model: reports get their information directly from the underlying database tables.

When you publish a report using DirectQuery, you only copy to Power BI Service
the report and a connection to the data (and not the data itself):

When you publish a report using DirectQuery, you'll see this message if you haven't set up a gateway to allow data to refresh.

When you view this report in Power BI Service, there is an associated dataset:

There is a dataset for the report, but you can't see any data within it unless you set up a gateway to allow your report to refresh.

Viewing published DirectQuery reports without first setting up a gateway can be
a bit boring!

If you can't connect to the underlying database, you can't display any visualisations!

So the diagram from the previous part of this blog series now looks like this:

You can only see data in a report if you can set up a connection to cross the green line between Power BI Service and your source database.

Thus refreshing data is useful for a normal report, but essential for one
which uses DirectQuery to get at its data.