Creating gateways and refreshing reports in Power BI Service
Part two 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 (this blog)
  3. How connections work for Direct Query
  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 Power BI connections work

To understand how refreshing works in Power BI, you have to have a clear understanding of the connections involved.  Let's look firstly at what happens when you create and publish report, then at the connections created and used.

How Power BI Reports Work 

In a PBIX report, you import data from databases or files (in my case, from two files - one Excel workbook, and one CSV text file):

Data model in PBIX

I've deleted some columns from the data model, and renamed others.

You then extract data from this simplified data model to create a report:

Report and data model

Thus the report gets its data from the data model, and the data model gets its data from the underlying data source (in this case an Excel workbook and a CSV file, but this could equally well be a SQL Server or other database).

 

When you publish this report, you get two things in Power BI Service - a report and its associated dataset:

Power BI Service reports

I've got a report and a dataset, both called Simple report.

 

Here's a diagram of what's involved (Power BI Desktop is on the left, Power BI Service on the right):

Power BI data diagram

Note that in Power BI Service you can't actually see the relationships as shown above.

 

So far, so good - now let's have a look at what connections are involved in this diagram.

The connections used for Power BI reports, and how refreshing works

So what it comes down to is this.  In the Power BI Desktop report (the left side), you have a connection:

Connection to data

This connection knows which files to look for, and where, and also contains any security settings needed.

 

A typical connection specifies four things (the settings for our two files are shown as examples):

What Courses CSV file Delegates Excel workbook 
The type of file Text (comma-separated values) Excel 
Where to find file A folder called wiseowl on the C drive
File name Courses.csv Delegates.xlsx
Security permissions No user name or password needed

And finally, we get to the problem.  Here's what the published report has to do to refresh:

Refreshing a published report

The published report is irrelevant. It's the dataset which has to refresh, by somehow using a published version of the connection created in the desktop file to link to the underlying data files or databases.

So refreshing is all about how to get data across the vertical green line above, using the same connection settings as for the desktop report.

This blog has 0 threads Add post