Creating gateways and refreshing reports in Power BI Service
Part three of an eight-part series of blogs

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.

  1. Getting Power BI Service reports to refresh
  2. How Power BI connections work
  3. How connections work for Direct Query (this blog)
  4. Setting up a personal gateway to allow data to refresh
  5. Creating an on-premises data gateway for everyone to use
  6. Gateway requirements, limitations and troubleshooting
  7. Manual and scheduled refreshing of data
  8. Refreshing data within the cloud (Azure, OneDrive)

This blog is part of a much longer series, which together comprise a full online training course in Power BI Service.  You can see get details of our classsrom Power BI training courses here.

Posted by Andy Brown on 03 July 2017

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.

Two SQL Server tables

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:

DirectQuery option

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).

Missing data tab

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:

Report and data

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):

Publishing using DirectQuery

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:

Power BI Service 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!

No data visible

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: 

Refreshing data using DirectQuery

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.

This blog has 0 threads Add post