How To Keep Your Data Fresh With PowerBI Gateways

  • Article

powerbi-getting-started PowerBI.com from Microsoft is a great tool for manipulating and visualizing data in powerful ways. There is a desktop client (Windows only currently) and a browser based platform for creating and viewing reports. A number of schools in New Zealand have started exploring how PowerBI can be used and St Andrew's College in Christchurch have published a number of blog posts with videos showing some of the dashboard reports they have made. If you have never seen PowerBI in action check out the blog posts here.

One of the biggest challenges to using any data analytics tool is keeping the data fresh and up to date with the minimum of effort. When you’re getting serious, you usually opt for the ETL process and data warehousing of some sort, but for the smaller users who do not have access to high level technical skills, what are the best options to use?

Enter: PowerBI Data Gateway

This product has undergone a few name changes over the last year or so (Personal Gateway, Enterprise Gateway etc) but now it is nice and simple and consolidated into one tool which you can use on your own laptop/desktop or install on your server to keep your data synchronised with PowerBi:

With the on-premises gateways, you can keep your data fresh by connecting to your on-premises data sources without the need to move the data. Query large datasets and benefit from your existing investments. The gateways provide the flexibility you need to meet individual needs, and the needs of your organization.

Why is this a game changer for schools? Well many schools will be storing their data in simple Excel or CSV documents, perhaps even an Access database and will be making regular changes and updates to these files. In other products, users would need to re-import the modified file back into the data tool analysis tool to refresh the data set and visualise the changes.

Here is a tutorial showing you how you can set this up in 5 minutes:

A couple of things to note:

  1. You need to have installed the PowerBI Data Gateway first and that it has permissions to go through any firewall restrictions you may have in place.
  2. Take care where you store your original data source file (the Excel spreadsheet in this case). Once you have published the report to PowerBI.com from PowerBI Desktop, I’ve not found a way of changing the path to the source file for the scheduled refresh through the Data Gateway.
  3. The default time for the data refresh is midnight so you may want to add another time/times for this refresh to take place (PowerBI free can only be refreshed daily, PowerBI Pro is hourly – see here for more info). This can be easily done as below for 9am:

[gallery ids="7535,7515"]

This is an awesome way to keep things simple for schools and assists them in avoiding the dreaded manual reloading of data.