Optimizing Data Refresh in Power BI with Online Sources

Let's talk about refreshing data on local files versus scheduling refreshes on the Power BI service. And we're gonna connect this to online data sources such as Microsoft Excel files stored in OneDrive, or things may be stored in Google Docs. So let's talk about how data refreshing works.

When we think about refreshing data, we can go to Power BI, and we can manually refresh with local files. And then when we publish, the publishing process will upload not only the report, but also a copy of the data, which is called a semantic model. And that is the data plus all the modeling, the measures, calculated columns, the relationships that you've set, all of those things are all put into the semantic model.

Now, when we think about publishing, right? We upload our stuff to the Power BI service, app.Power BI.com. When we think about refreshing that data, we can refresh locally from our local file. But if it's an online data source, then the Power BI online service, right? Think about that as an internet service. It could go to another online service without having to come through your computer.

If you store your data in something like OneDrive or Google Sheets, or if it's in a database, if it's an online data source, then Power BI as a service, not to be confused with the desktop app, but the service, the service, which is on the internet, can talk to another internet service. Now, if you want to only store things locally, or if you have something on, let's say a local server, that's not generally internet accessible, maybe it's on an intranet only, then you can configure a gateway, which is basically a software that you install on a, kind of you're making your own server, essentially. So it can be installed on any computer that's gonna be left online, connected, booted up all the time, like this would not be something good to install on, let's say, your laptop, which is gonna be disconnected or running on battery.

We're not gonna get into configuring that in this training. You can learn more if you click this link and check it out on the Microsoft website, or there are also videos that you can find online as well on how to set up and configure a gateway, which you'd have to work with your local IT department on getting that installed, setting that all up. But when we think about when the service is connecting into something, essentially, it needs to be an internet accessible service.

So the gateway is making something that's on your local computer or local server accessible to the internet, basically turning your computer into a server. So I'm not gonna show it on creating a gateway, connecting to local data sources. I'm gonna show it with already online data sources.

So instead of having to create your own server, essentially using the gateway, you can put things on, let's say, OneDrive or Google Sheets. And then I can show the scheduled refreshes so that the service can actually go in on a schedule and directly refresh the data if you're storing things in something like OneDrive. So let's see how all of this kind of process works and talk about refreshes and storing things.

You would think that because Microsoft creates OneDrive and Microsoft creates Power BI and they create Excel and everything, you'd think that it would be just easy to browse OneDrive and choose the data to put it into your report, and it would automatically sync and refresh, but it does require some setup. You have to know exactly how to do this. So let's see how to do this.

First of all, my data. I'm gonna show this with OneDrive, although it's very similar to doing this with Google Sheets, and in the PDF of the slides, I have instructions on how to do this with Google Sheets as well, if that's where your data is stored. But I'm gonna open up this local Excel file.

So I've got this in my Power BI class, Ice Cream Sales, we've seen this in a previous video, and I'm gonna store this in OneDrive. So I'm gonna go to file, I'm gonna save as into my OneDrive. Just gonna store it out here in the main folder, although you could put it in a subfolder, but just for the ease of this, I'm gonna save it there.

Now this is a file stored in OneDrive. So that means the OneDrive website has a copy of this. And the way that OneDrive works is as a syncing service, essentially.

When you're using the OneDrive app, it is uploading a copy from your local computer to OneDrive. So I'm not gonna be using the one that's in this local folder anymore. When I look in my OneDrive, I can see that this file is here and it's synced.

So there is a local copy, but the important thing to understand about syncing services is it is just that, it keeps things in sync. It is synchronized, meaning there's an online version and a local version. So, with OneDrive, essentially, you have a folder on your local computer that has a copy of the files, and those are synchronized with the cloud version or internet version of this.

So there's also a second copy of this in the online OneDrive, and they keep them in sync. If other people have a copy on their computer in their OneDrive, which you've shared with them, then everybody's working on their local copy, and the changes get synchronized through the online version. We all have our own copies, and they're just staying in sync.

This is very important to understand that we're not actually editing the one that's online unless we're in a browser editing it directly there. We're editing a copy that gets synchronized, but this is a local copy, which means that if you go to Power BI and you create a new blank report to put this in, if you go to Excel workbook and you browse your local drive, even though you might think you're browsing your internet OneDrive, this is the local file and you will be linked to a local file on your C drive. Let me show you this.

I'm gonna hit open, and I'm not gonna do any transforms here because I just wanna show you that this isn't the correct way to bring in something truly from OneDrive. This will not work with automated data refreshes. So I'm just gonna bring in one of these sheets.

I don't even need to bring all of them in. I'm gonna skip transform. I'm just gonna go right to load just to show you how you can see that this was brought in wrong because I'm not actually connected to the online OneDrive.

Let me show you how you can tell this. I can tell this by going to transform data, the menu, not the button, and looking at my data source settings. In here, I can see that I'm connected to my C drive and notice that in my users folder, on my local C drive, there's a folder called Dan, and then there's a folder called OneDrive, and then there's the file in that.

So this is linked to a local file. Notice it's not an internet address, which means that if I upload this to the service and I try to get the scheduled refresh to refresh, how's it gonna find this file? This file's on my computer. It's not on the service.

So while I myself on my computer can come here and hit refresh because I have access to that local file, and then I could publish if I wanted to again, which would upload to the service, I would still have to manually refresh this and manually publish those changes. I want to automate this. So I don't want this.

So I'm just gonna go and create a new report. So, a new blank report, and I'm gonna show you the correct way to do this. And I'm gonna go to this other file here, and I'm gonna close that because I don't want that file.

Now, before I add this to Power BI, I need the correct link. So that means in my OneDrive, I'm gonna open up this Excel file. So this is the OneDrive version, and I need the web link.