Using Local or On-Premises Data in Power BI

Power BI is a cloud-based service, and it can use hundreds of different data sources as long as the data is in a consumable format. It’s not unusual for organisations to have a local data source stored on-site, rather than in the cloud. If this is your situation, you’ll need to take a couple of extra steps to be able to analyse that local data in Power BI, in a secure way. In this article, we’ll explain how you can connect local data with your cloud-based Power BI.

Why do I need local data in Power BI?

Power BI is a data analysis tool that pulls information from numerous data sources. It is used to visualise data sets in order to transform the information into charts and graphs. Often compared to Excel, Power BI claims faster processing and a more visually appealing dashboard, and offers a more comprehensive set of data comparison tools. It allows users to drill into the data, drag and drop various graphs and charts, and filter the results to show different comparisons. For example, you can filter your graphs after you’ve built them to show things like customer locations, buyer postcodes, active/inactive accounts, etc.

If you’re trying to build a comprehensive picture of your organisation’s statistics – sales, customer demographics, financials, etc. – then you need to have all the information. And that won’t work if half of the data is in the cloud, and the rest is stored in an on-premises server or protected behind a corporate firewall. 

You could have landed in this scenario for a multitude of reasons. Your organisation may have been created pre-cloud storage, so local storage was the only option. Perhaps you’ve been enjoying a hybrid model as your company has grown. There could be security reasons for you wanting to keep some information separate from other datasets. Regardless of the reason, there is a way to make sure that data gets included when it comes time to crunch the numbers in Power BI. 

To connect these two sets of data, you’ll need to build a bridge between them. This is where we bring in gateways.

2 men on laptop looking at version control on documents in Microsoft 365

Creating a gateway

You don’t need a gateway for online data sources, so if you’re just pulling from a cloud-based CRM then you’re off the hook. For on-site data sources, read on. 

A gateway connects your local data to the Power BI Cloud service. You can use Power BI Data Gateway for this purpose, which will connect to the on-site data source in order to retrieve the information you need. The Power BI data gateway integrates with a variety of other cloud-based data sources, including Power Apps and Power Automate. They will be able to use the same gateway without needing to adjust the specifications. 

Within the organisation, most departments will benefit from the data analyses provided by Power BI. However, it’s not uncommon for your IT team to own generating reports and sharing them with the relevant people. Having one team in charge of pulling, refreshing, and compiling the data also helps with security and compliance requirements.  

Creating a gateway also allows you to send reports to people who aren’t on the corporate network. It will connect the corporate network to the cloud where you can control permissions and sharing.

Collecting live data

So you’ve connected your local data to the cloud, and Power BI has pulled all the relevant information. Power BI stores all of the data you import, so your historical statistics are always available for analysis. But how do you check today’s stats against tomorrow’s, or next month’s without starting this whole process over again? 

One of the perks of Power BI is that it facilitates live data refreshing. Using the gateway you’ve created to your local data server, you can establish a scheduled refresh for Power BI to retrieve the most up-to-date information. This could be once a day, once a week, annually… you get the gist. You’ll be able to instantly compare the current numbers to past performance and make crucial decisions based off those results.

Setting up your local data gateway

If you’ve got a savvy IT team, they should be able to configure the bridge between your local data server and the cloud-based information. But if you’re keen on some assistance from the professionals, Propelle’s consultants have got you covered. We can set up the gateways between all of your data sources to ensure that you’re getting the most accurate, up to date information about your organisation. 

Let’s build this bridge together – get in touch today.