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
10 KPIs Manufacturers Should Track for Operational Excellence
Paul FullerPaul Fuller  |  
Apr 18, 2019
How do you know if you’re truly improving quality and efficiency in your manufacturing operations? Do you know if your equipment is as effective as you think it is? Are your operating lines a bottleneck in getting orders delivered to your customers? How would you demonstrate that?  ...
Blog Article
Sorting Results in the Flattened 7-layer Recursive Hierarchy Salad
Bob CharapataBob Charapata  |  
Apr 16, 2019
In my previous article about Flattening a Recursive Hierarchy, I wrote about an approach that transforms existing recursive hierarchies into usable data constructs for analytics. This post builds on that article to show how to display the results in correct hierarchical order.After...
Blog Article
Flattening the 7-layer Recursive Hierarchy Salad
Bob CharapataBob Charapata  |  
Apr 02, 2019
Sometimes organizations must model a hierarchy with data, but they don’t know how deep it will be. Developers often create recursive hierarchy tables for transaction processing systems to solve this problem. Those tables have one column on the table that refers to the table's identity...
Blog Article
How to Use Power BI’s New AI Visual: Key Influencers
Marcus RadueMarcus Radue  |  
Mar 28, 2019
Microsoft has recently released a new Key Influencers visual in their February 2019 release of Power BI. This visual is part of Microsoft’s roadmap to continue to advance the Artificial Intelligence (AI) integration and features within Power BI. Microsoft has already introduced other AI...
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...