Back to List

How to Retrieve Data from Excel Files and Other Data Sources in Power BI

Scott Hietpas Scott Hietpas  |  
Mar 05, 2019
Working with OneDrive for business is great for keeping copies of Power BI desktop reports. It can also be a good place for you to store any one-off data or backup versions of data sources in case the files break at some point.
Another reason to save Excel files in OneDrive is so Power BI can access it without needing a gateway. If you are pulling in Excel content from your local computer, Power BI can't find that file to refresh data. We'll see how using OneDrive allows us to easily pull in one-off data sources. If you do have a one-off data source like an Excel file, I recommend keeping that in OneDrive as well.
Beyond one-off excel data sources, there are lots of other data sources that Power BI content creators can pull in. That's where you can use Power BI Desktop to tap into all these data sources. When you're looking to bring in data to Power BI, you must determine how you bring the data in. We can connect to data that's in the cloud as well as data that's on-premises, whether that'd be on your laptop or a server that's within our network.

3 Connectivity Modes in Power BI

The data source we choose has a big impact on our ability to keep that data up-to-date. It impacts how big the Power BI Desktop files are and how often we can refresh them. There are three different connectivity modes within Power BI.

1. Live Query to Analysis Services

This is what we do as a best practice within Skyline. We have a curated data warehouse model in Analysis Services, and we allow anyone in the organization to connect to that model. The model already has calculations that are easy to navigate and meaningful to help users quickly do analysis. For the most part, this scenario may ring true for most of you. In this scenario, the data always stays up-to-date with the model’s current data.
There are two other ways that you can connect to the data. One is Direct Query and then there's Importing the data into Power BI itself. When you consider these scenarios, you have to think through the amount of data you're bringing in and who has access to it.

2. Importing Data into Power BI

If we're importing data into Power BI, we're uploading the data and the model all into Power BI. One downside to this approach is that Power BI now has no idea what the original security might have been around that data. It is then easy for a content creator to create a Power BI report with this data and share it with the rest of the organization, making this data visible to people who shouldn’t see it. Yes, this freaks out a lot of people, but it's no different than if I connect to that same model, copy and paste the data into Excel, and email the document to other users. There are any number of ways that people could take screenshots or export data in a way that removes the security. This is simply another way in which somebody could do that. Any time we're using our access to get to sensitive data and bringing it into another tool, we have to think about who we share that with.
Now, there are cases where importing data makes sense. Right now, it's the only way that you can mash up multiple data sources into one report. If I need to pull some data from a database, some additional data from Excel, and some additional data yet from a website, importing it into Power BI allows me to create a model that connects all three of those scenarios. There are situations where it makes sense. If there's a need to put additional security on top of that, then we take steps to reduce the security risk.

FAQ: What about governance, in general? It seems like this can get out of hand.

The truth is that governance needs to align with your culture. Skyline is very much self-service, and our workspaces reflect that. Anybody can create an app in an app workspace and start creating content and distributing it. In many ways, that's a good thing. But many of our clients want to restrict that functionality to a small group, and they have it be an IT-request process. Recently, we have discussed going that direction. One of the challenges is that Power BI app workspaces in Office 365 groups and team sites are connected. Right now, that approach is all or nothing. So, if we want that approach, we have to apply it across the board.

3. Direct Query

Direct Query is kind of a hybrid approach to gathering data. It lets you keep the data within the database, but you can do some additional modeling on top. Let's say you don't have an Analysis Services model that you can leverage, but you do have the ability to query a regular SQL Server relational database. You can use Direct Query and leave the data in the database, but there are some downsides to that.
A live connection to Analysis Services keeps the data and the model on the server. This is what we use at Skyline, and it is by far the most significant best practice that we teach to our clients. One of the main advantages of this is that, as individual users are accessing those Power BI reports, their usernames are being passed to Analysis Services and it's using the security on the server. Even if I build a report and I share it with users, if ultimately you don't have access to the underlying model, you won't see the data. The data remains secure.

Check out my other articles in this series:
  1. How Content Consumers Can Use Apps and Workspaces in Power BI
  2. How to Create Content for a Power BI App Workspace
  3. How to Retrieve Data from Excel Files and Other Data Sources in Power BI
  4. Useful Tips for Power BI Desktop
Data AnalyticsPower BI


Love our Blogs?

Sign up to get notified of new Skyline posts.


Related Content

Blog Article
Useful Tips for Power BI Desktop
Scott HietpasScott Hietpas  |  
Mar 19, 2019
There are several things within the Power BI desktop resources that I want to point out. One of the best things that happened in a few releases back is the help menu in the Desktop app. Within the help menu is access to Microsoft documentation and videos. Power BI’s blog speaks about this...
Blog Article
How to Create Content for a Power BI App Workspace
Scott HietpasScott Hietpas  |  
Feb 12, 2019
In each Power BI app workspace, you may have reports and data sets. If you want to create additional Power BI reports, then you can create them directly in the service. This is not recommended, as there are limitations to this approach. One of the larger limitations is that you won’t be...
Blog Article
How Content Consumers Can Use Apps and Workspaces in Power BI
Scott HietpasScott Hietpas  |  
Feb 05, 2019
If you're a Power BI content consumer, where do you go to find content that's available to you? Apps. If your app list is empty, you can click “get apps” to browse the apps available to you.   Not only are there apps, but there are also workspaces. App workspaces and...
Blog Article
How Content Consumers Can Unlock Actionable Insights in Power BI
Scott HietpasScott Hietpas  |  
Jan 29, 2019
I love working with Power BI and data, and I'm actively involved in helping develop content within Skyline. In that spirit, I’m going to walk you through how everybody in an organization can find content available to them, as well as useful tips to make best use of it.   Even if...
Blog Article
Identifying SSAS Tabular Processing Performance Issues
Cory CundyCory Cundy  |  
Dec 18, 2018
Have you ever developed or worked on tabular models that take longer to process than you expect?  Have you ever wanted to decrease the processing time so you can process the tabular model more often?   In my experience, I have answered "yes" to both questions.   In...