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


Deep Dive Into How the Power Platform Can Benefit Your Organization
Oct 16, 2019
Location: 833 East Michigan Suite 860 Milwaukee, WI 53202
Blog Article
Impactful O365 Business Apps in Low Code/No Code Power Platform
Libby FisetteLibby Fisette  |  
Oct 15, 2019
In this blog series, Libby Fisette, Director of Skyline Technologies’ Modern Workplace team, explores the current state of the digital workplace and some best practices for leveraging the Office 365 toolset. For a full overview on this topic, check out the original ...
Blog Article
Cloud Data Storage Architecture: Pros and Cons
Scott HietpasScott Hietpas  |  
Oct 08, 2019
In this blog series, Scott Hietpas, a Principal Consultant with Skyline Technologies’ data team, explores the pros and cons of different data storage architecture. For a full overview on this topic, check out the original Cloud vs On-Premises Architecture Webinar.   Like the on...
Blog Article
On-Premises Data Solutions: Pros and Cons
Scott HietpasScott Hietpas  |  
Sep 24, 2019
In this blog series, Scott Hietpas, a Principal Consultant with Skyline Technologies’ data team, explores the pros and cons of different data storage architecture. For a full overview on this topic, check out the original Cloud vs On-Premises Architecture Webinar.   The goal of this...
Blog Article
How to Handle Refactoring in a Self-Service Analytics World
Scott HietpasScott Hietpas  |  
Sep 10, 2019
In this blog series, Scott Hietpas, a Principal Consultant with Skyline Technologies’ data team, and Matt Pluster, Data Analytics and Data Platform Team Director, explore the advantages and disadvantages of different analytics management approaches. For a full overview on this topic, check...