Can you auto-refresh data in Microsoft Power BI using Microsoft OneDrive? (With Video)

Can you auto-refresh data in Microsoft Power BI using Microsoft OneDrive? This is one of those weird answers of “Yes, but No” or “Yes, sort of” or “No, but Yes.”

This is one of those weird answers of “Yes, but No” or “Yes, sort of” or “No, but Yes.”

So none of the answers above really answer the question. No one can answer it, it’s a trick question (those of you who love the movie “My Cousin Vinny” will be laughing now. Let’s see why it is a trick question.

Belinda Allen, Microsoft Dynamics GP MVP shares tips and information on Microsoft Power BI.
My Cousin Vinny (1992)

We’ll start with OneDrive for Business. Since OneDrive for Business AND Power BI are both cloud applications and they would use the same login information, Microsoft Power BI will automatically refresh data created in the Service (Microsoft Power BI Web Application) automatically. So if you:

  1. Log into your Microsoft Power BI Service (www.PowerBI.com)
  2. Add a Dataset using “Import or Connect to Data | Files”
    1. Get
    2. OneDrive – Business
    3. Select File and Connect
    4. Import (Connecting brings in the whole Excel Workbook, usually you just want the data so you can create visualizations.)
  3. Create your visualizations using “My Workspace”
  4. Pin new Visualizations to your dashboard.
  5. Refreshing will automatically occur every hour.

That was the Yes part. The no part? Someone has to refresh the file on OneDrive for Business. This is a great way to share data with many people in your company. Only one person has to refresh the file (of course that folder needs to be shared with the other Power BI users as well, in OneDrive for Business.

 So I admit the answer is probably really only no, it doesn’t auto-refresh but come on. One person keeps that file current and everyone who accesses it has up to date data without having to click refresh? That’s pretty cool. This is a super, low budget method or sharing data and using Power BI efficiently.

For OneDrive for Personal? The same pretty much applies EXCEPT your login information is not likely to be the same as your OneDrive for Business. This means when you log in to your OneDrive for Personal, you need to make sure you mark the option to stay signed in.

I like this approach. For most ERP users, a data refresh of weekly is sufficient. Using this easy to setup methodology, you can control data being released as well.

Happy Dashboarding!

 

Author: Belinda Allen

Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 24 years. Belinda began implementing ERP systems so long ago that Windows was not an operating system, but an application. Larger businesses used old Main Frames with monitors that were Green and Black and smaller business did accounting by hand. Having seen the evolution that has taken place over the years from pieces of paper to analytics, Belinda still gets excited every time she helps a business improve. Belinda is also well known for her Blog www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.” In her time off, Belinda enjoys sailing, woodturning, crochet/knitting, sewing/quilting, reading and playing the ukulele (Belinda often participates in Ukulele Jams in NYC.)

2 thoughts on “Can you auto-refresh data in Microsoft Power BI using Microsoft OneDrive? (With Video)”

  1. Belinda,

    We are just getting started with using Power Bi in connection with Microsoft Dynamics GP. Is there a specific connection used to get to the actual tables in GP? I have connected to the server and apparently am connected to the reports but not the source data.

    The IT department is looking into this as well. Any help would be appreciated.

    Dan Haney

    Like

    1. Hi Dan,
      if you cannot see tables and views when you connect, you probably do not have access to them in SQL. Below is a response from Lyn Barr from Informed Systems in Blue Bell, PA. Her response was regarding Excel Refreshable Reports, but the same concept follows (only you’ll need to grant security to tables and/or views.

      By default, only users who have administrative credentials on the server will have access to the deployed Excel reports. Since you probably don’t want to give all your users that kind of access, you can use the following as a workaround:
      *Note* Users would need access to the shared network location where the reports are deployed, but it sounds like you’ve already done that, so I won’t bother with instructions for that.
      Create domain users in SQL Server Management Studio, for all users who should have access to the deployed reports. (i.e. DOMAIN\UserName).
      Open SQL Server Management Studio, expand Security, and then right-click Logins and choose New Login.
      Create a Windows logon (using Windows Authentication) for the user for whom you want to assign security.
      Assign those users access to the company database(s), and then give those users the database role that would be applicable to the level of security you wish to assign.
      Click User Mapping and assign database access to the necessary databases. For each database, assign the appropriate database role. All the roles that govern access to the Excel reports begin with “rpt_”

      http://community.dynamics.com/gp/f/32/t/30127

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s