0:01 many people who are just starting out
0:03 with powerbi are working with files on
0:05 their local computer and to refresh
0:07 their reports they are clicking the
0:10 refresh button in the toolbar up here
0:12 and that can be okay for a while however
0:14 eventually most people decide that they
0:16 want to automate this process by
0:19 scheduling refresh and I'm going to go
0:20 through a few best practices as well as
0:22 we go along to help you get started out
0:24 on the right track because it's a lot
0:26 easier to start from the right place
0:28 rather than go back and fix it later and
0:29 at the end of the video I'm going to go
0:31 through couple ideas for you to take the
0:34 automation one step
0:37 further the first step is to look at
0:39 where your file and folder sources are
0:41 right now and move them to SharePoint
0:43 online I have tutorials that explain
0:45 exactly how to do this step by step I'm
0:46 going to link those in the video
0:48 description there's one for individual
0:50 files and one for folder sources because
0:51 the process is a little bit different
0:53 between the two this is actually pretty
0:56 straightforward and it is necessary
0:58 because in powerbi you schedule a
1:00 refresh in the Cloud Server and the
1:02 cloud service does not have access to
1:04 your computer you can technically give
1:06 it access to your computer by installing
1:08 a Gateway but then you have to leave
1:09 your computer and the Gateway running
1:11 all the time for the scheduled refresh
1:13 to work and you want this thing to keep
1:15 running if you go on vacation anyway
1:17 okay and let's talk about one drive for
1:19 a second here too your one drive is like
1:22 your personal SharePoint storage you can
1:24 technically put your files in there for
1:26 this purpose but it's better to put it
1:27 in a SharePoint team site because your
1:30 one drive only exists as part of your
1:31 account so if you leave your
1:33 organization everything in there gets
1:35 deleted step two so you have your source
1:39 files in SharePoint and your PX file is
1:41 pointed at them now you need to publish
1:43 your report to the powerbi service
1:44 publishing your report is going to be
1:46 how you let other people view it without
1:48 having powerbi desktop installed this
1:51 will not Grant access to your report
1:54 except for people who are members of the
1:56 workspace that you publish it to so
1:58 don't worry about that it does need to
1:59 be published in order to schedule the
2:01 Refresh on it so we're going to do that
2:03 now you'll notice you have a personal
2:04 workspace to publish to this my
2:07 workspace here you can use that if you
2:10 absolutely need to however it is better
2:13 to use a shared team workspace if you
2:15 don't already have a shared workspace to
2:17 publish to you can create one so for me
2:20 if I go to workspaces on the left here
2:22 and then click on new workspace it will
2:25 let me create one and click apply and
2:27 you can add members with this manage
2:29 access button if you need to you get to
2:30 choose the role so whether they're a
2:33 viewer contributor or member by the way
2:34 if you're looking to Grant access to
2:36 your report don't be adding the
2:38 individual users as viewers of the
2:40 workspace that's not how you grant
2:42 access in powerbi it's much better to
2:44 share the specific report that you want
2:47 to Grant access to or to use an app so
2:49 I'm going to publish this
2:52 thing and when it's done publishing it's
2:54 going to give you a link to open in the
2:55 web service so I'm just going to click on
3:00 that and then to to get to the place
3:02 where I need to schedule refresh I click
3:05 on this ellipses menu up here go to view data
3:07 data
3:09 set and then I go to the refresh menu
3:11 here the little carrot next to it and
3:13 click on schedule
3:16 refresh you'll notice right away that it
3:17 says failed to test the connection to
3:19 your day Source please retry your
3:21 credentials this is totally normal for
3:23 SharePoint online sources what you do is
3:24 you have to set those credentials that
3:26 it's using because the web service
3:27 doesn't automatically know which
3:29 credentials it should be connecting to
3:31 the data with so we're going to click on
3:32 edit credentials and this is usually
3:34 pretty quick and easy you do need to
3:36 change this authentication method it
3:37 always defaults to Anonymous change it
3:40 to ooth 2 and then set your privacy
3:41 level this is usually going to be
3:43 organizational this is basically just
3:45 saying what type of data is this and
3:48 then click on sign
3:51 in select your
3:53 account and we're done with that part
3:55 there is a gateway section here if
3:57 you're using SharePoint online you do
3:59 not need to use a Gateway that is only
4:02 only necessary for data sources that
4:05 live on premise so SharePoint server SQL
4:07 Server that kind of thing now go down to
4:09 the refresh section and turn on your
4:12 refresh schedule here daily is a good
4:14 one to start with but you can also
4:16 choose weekly if you want and then set
4:19 your time zone and for the times it's
4:22 worthwhile to note that it's going to
4:24 automatically do your refresh at
4:26 midnight if you want it to refresh at
4:28 another time you need to specify that
4:30 here and it's a good idea to stagger
4:32 these so that not everything in your
4:34 organization is refreshing at the
4:36 default refresh time right because if
4:38 everything refreshes at the same time
4:39 it's much more likely to fail because
4:41 you have limited resources available to
4:44 work with on those refreshes so next set
4:46 a refresh failure notification it's
4:49 going to automatically go to you it is a
4:52 good idea to also email other people so
4:54 for instance a distribution group is a
4:56 good option here something where members
4:58 can be added and removed over time where
4:59 you don't have to go back into every
5:01 single data source and add and remove
5:03 people from the specific notifications
5:06 people very frequently skip this step
5:07 but it is actually really important
5:10 because if you leave your organization
5:12 your data refreshes are going to fail
5:15 and when they fail if the notifications
5:17 are only going to your account nobody
5:19 will know until your users are wondering
5:21 why the data is stale okay so one thing
5:23 that I kind of glossed over there that's
5:25 very important is that this refresh is
5:27 using your account this will work for
5:29 smaller projects short-term project
5:31 projects that kind of thing if you have
5:33 something that you want to outlast
5:35 yourself you probably want to look into
5:38 getting a service account that is used
5:40 to run the refresh so service account is
5:43 a licensed account that is not attached
5:44 to any one particular person so you can
5:47 schedule refreshes with the service
5:49 account you just take over the data set
5:50 essentially there's a takeover button
5:52 when the account views it if you do this
5:55 make sure to share any of the sources
5:57 that you are using with the service
5:59 account because it needs a read
6:01 permission on those to run the refresh
6:03 so if you're using a SharePoint Source
6:05 add that user to the SharePoint site it
6:07 would also need to be a data set owner
6:09 so the owner level of permissions on the
6:10 workspace in order to take over the data
6:12 set so click on apply in this section
6:14 and there's one more thing we need to do
6:16 before we're done here and that is to
6:19 share our report so this report that we
6:22 published is private to just the
6:24 workspace until we share it so if you
6:26 want other people to be able to see this
6:28 report as a viewer you need to share it
6:30 first I clicked on the report in the
6:32 left navigation if it doesn't show up
6:34 here for you just go to your workspaces
6:36 list under workspaces here and then go
6:38 to the workspace that you published to
6:40 and click on the share icon next to the
6:42 report so that's the one that says type
6:45 report next to it your options are
6:47 people in your organization with a link
6:49 so that's anybody who happens to have
6:52 the link can use it or specific people
6:53 and you can decide whether or not you
6:55 want them to be able to share the report
6:57 also or whether you want them to be able
6:59 to build with the content in the report
7:02 it's notable that the feature called
7:05 analyze with Excel which lets you open
7:07 the data set in Excel requires build
7:09 permission so if you want people to be
7:11 able to do that check this box and just
7:14 as an FYI you can let people export
7:16 things from your report to Excel without
7:18 this permission level it's just if you
7:20 want them to be able to connect to the
7:22 entire data set in Excel that this needs
7:24 to be done so click apply and then enter
7:25 the people's names that you want to
7:27 share it with distribution groups will
7:29 work here active directory groups will
7:31 work here Microsoft 365 groups will work
7:34 here or individual names so our refresh
7:37 is scheduled you can tell because this
7:40 column that says next refresh has a date
7:42 and time in it if it weren't scheduled
7:44 you wouldn't see anything here what I
7:46 like to do right away when I schedule a
7:48 new refresh is just test it in the
7:49 service to make sure it works so I click
7:51 on this refresh button here just to make
7:53 sure that it runs without any errors so
7:55 what this is doing when it refreshes is
7:57 it's going back and checking those Excel
8:00 files that we put in our SharePoint site
8:02 it's always better to work directly with
8:03 database sources when you're using
8:06 powerbi if you have that available but
8:07 I'm assuming if you're watching this
8:09 video that you are working with file
8:11 sources out of necessity so I want to
8:14 mention that it is possible to automate
8:16 the Excel export and upload process
8:20 using Microsoft 365 tools for example um
8:23 Power automate desktop is a tool that
8:26 has a screen recorder where it can
8:28 record what you are clicking on in a
8:30 browser and all of the steps that you do
8:33 to export files from a system I'm
8:35 planning on doing a video on that in the
8:37 future so if you want to be notified
8:38 when that comes out make sure to
8:40 subscribe thank you for watching and
Chrome Extension