Up next in 10
Do you know EXACTLY who is the most active Power BI user in your organization? Do they use Web, Excel, or Mobile? Most active report/app? Self-service adoption rate? Does your users with a paid license are active?
No? Not sure? Be ready for a big reality check.
This session aims to show you how to gather and analyze the required data to answer the questions above.
About Speaker:
Rui Romano is a Microsoft MVP on Data Platform and has over 15 years of experience working with Microsoft BI/Data Technologies.
Rui is passionate about data and a frequent speaker on Microsoft events.
Lives in Portugal and leads the BI & Analytics team at DevScope.
Conference Website: https://www.2020twenty.net/power-platform-virtual-conference/
C# Corner - Community of Software and Data Developers
https://www.c-sharpcorner.com
#powerplatform #virtualconference #csharpcorner #powerbi
Show More Show Less View Video Transcript
0:00
So one more time, thank you for attending
0:07
It's a great pleasure to be here. So Simon already introduced me
0:13
So I'm from Portugal, Microsoft and Power BI professional. I've worked with Microsoft BI all my career
0:22
I'm really passionate about everything related to data and communities. That's one of the reasons why I got this award that I'm very honored from Microsoft, the MVP award, because of all the work into the community
0:40
So, the session today, and it's about Power BI monitoring Y101. So the reason is this is something that I think it's
0:51
and I believe it's really, really important whenever you work with Power BI to monitor
0:57
what is happening inside your tenant. So I will start to describe why you need this
1:03
Then I will jump directly to the final demo. So show you the end result
1:10
and then we will discuss how to do it. This is a 50 minute session
1:15
So we won't have time to focus on the details, but please focus on the possibilities
1:21
not the how to do it. Because in the end you can send me an email
1:25
and I'll share my contacts with you and I will share the code and the Power BI files
1:30
So don't worry about understanding every detail, worry more about understanding why you need it
1:36
And then of course, the how to do it, I will describe, there are multiple ways to do it
1:44
I will show a very simplistic one with PowerShell scripts, but there are others and we will discuss them also
1:51
So why you need to do this? So if you work in a company and you are developing Power BI reports
2:00
or you are responsible for the Power BI tenant, if you are the IT admin and you cannot answer this question
2:10
So if you cannot answer what's the most active user in your organization and why you need to know the most active user or users is, for example, if you want to just activate a new feature, Power BI is adding new features every month
2:30
Maybe those are the set of users that you should speak to
2:34
And another very important question is how many users use Power BI in your tenants
2:43
How many distinct users use it per day and per hour? And this is very, very important
2:48
If you, for example, if you have a premium capacity, this is something that is very important to know
2:54
because this can determine if you need more or less vCores, If you are over buying or if you are or maybe you need more and you have 1000 distinct users per hour and then maybe one P1 is not enough
3:12
Another important question is how is the content being shared? So maybe you design some guidelines to use workspaces and data workspaces and report workspaces and then use apps to publish and broadcast your Power BI content to users
3:29
and are actually the developers following those guidelines? So not or no
3:38
So it's something. So how is the content being delivered to the end users
3:42
You can also monitor that. Do your users use published web and maybe you don't want them to use
3:51
or maybe who is doing published web and sharing those reports to the public
4:00
So it's something that maybe it's important for you to know. Even if you authorize published web in your company or in your tenants
4:10
it's important to know who is doing it. What do we exist in the personal workspaces
4:16
One of the things that it's not actually recommended as a best practice
4:19
is to create reports in the personal workspace and then share those reports with other users
4:27
So you can create reports on your personal workspace because, but if then you share those
4:35
reports then maybe you should think or issue the wonder, you should plan to put those reports in
4:43
the into workspaces, not the personal workspace, because if you leave the company then
4:49
that personal account will get deleted then you could lose some work there and
4:55
you can monitor of course what do exist in the workspace and if there is some
5:01
activity in those personal workspaces I've seen situations in companies where the most active report was actually a report from a personal workspace and
5:11
this is not good and you should and you need to know about this another thing is
5:15
the top use of data sources what are what what are the most active data source in your tenant
5:22
is files is one drive is sql server database is data lake maybe you could want to not authorize
5:31
the use of file system and force your users to use one drive or another cloud service because you
5:37
You don't want them to have some critical or Power BI developments that just connect to a local file system
5:46
Maybe you just want to. And then you can design some guidelines that you give to your end users
5:52
And if you detect this type of this kind of situations where you detect there is a data set with a data source with a C or D drive
6:03
then you can just provide those guidelines and they will be able to migrate their barbie development
6:09
So you can, you should be, you should look at the data set refreshes
6:17
So what's the trend? So do you have a data set refresh that is growing in the amount of seconds it takes to refresh every day
6:25
um and and you you should be able to look at this at the tenant levels or for all the data sets that
6:33
that exist in your tenants so what's what do your users use the most to access the power bi do they
6:40
come from mobile applications do they use excel uh from the browser which browser do they use
6:46
and and the browser is something that is important for you as a barbie i have mean to know because
6:51
Because I don't know if that still happens, but it happened to me a year ago
6:57
I used Firefox. And Firefox, I don't know why, it made Power BI slow
7:02
And I think it was the Power BI service that was slow, but that was not the case
7:08
And maybe you are getting complaints from users that Power BI is slow, and maybe that's because of the browser
7:14
so and you can react to this in two you can use this information to to when you get this complaint
7:22
to understand okay maybe they are using old browsers and could that could be the issue so which and another thing that is also important to monitor is which data sets and reports are not actually being used in more than one year
7:39
you should think into deprecating those those reports and last but not not the least
7:46
do you have users with a paid license that don't use Power BI in more than three months
7:55
or more than a year and you are paying for those licenses and then you have a collection
8:00
a set of 10 or 100 users asking you for licenses and you are not giving them a license because
8:08
you don't have more, but then you have users that they have a license that they don't use it
8:13
So this is a collection of questions that's really, really important. And I think that any, I won't say Power BI developer, but if you are any kind responsible for the Power BI tenant, you should be able to answer
8:29
And you should be able to, you should look at this, I won't say every day, but you should at least look at this data frequently and make some decisions and more informed decisions with this
8:41
And if you cannot answer these questions, then you are literally driving blind
8:47
And there is certainly a big reality check ahead of you. And I can tell you from experience that if you don't look at this data, what you think is important, maybe it's not
8:57
Maybe you are designing mobile reports and no one is using mobile
9:02
You are spending time and money designing those reports and it's not important
9:06
So, and maybe you should be, and I'm not saying that you shouldn't design mobile reports
9:13
You should, but maybe you should wonder why they are not using mobile reports
9:18
Maybe your end users just don't know about this. So, in this session, the main goal of this session is just to show you what you can do with this data
9:26
and to show you the importance of looking at this data. so before going into into the solution the custom solution there is not nothing out of the box with
9:40
the same level of deep ytical into the activity and the catalog out of the box but
9:47
there are some things out of the box that you should know of so the first one and it's actually
9:51
very nice but it's it has two main limitations it's the power yeah report users so if you go to
9:59
the power via report and you collect click on the open usage metrics this will open a report like
10:06
this and this will give you for the reports and all the reports in the in the workspace
10:13
some activity information so you will be able to see the report views you will be able to see some
10:17
telemetry data the time to open a report which is something that i can actually in my solution i
10:23
I cannot get because I cannot get that telemetry information with the APIs
10:30
But the limitation of this solution is it will only give you seven days of data
10:35
and it's workspace by workspace. So if you want to look at this at the tenant level
10:42
there is no way. So you need to create multiple data sets by workspace
10:47
And I work with companies with 1000 workspaces. and this is impossible to manage
10:54
Another, there is actually a tenant level monitoring solution at the admin portal
11:01
but in here you don't have any interactivity. So you cannot control the refresh of this data
11:10
I can actually sometime, I don't actually trust really much these counters here
11:17
I have some differences and I've checked and the API data is more correct than this
11:23
but nevertheless, it's something that is available out of the box and it will give you at least
11:30
the number of reports, the number of data sets, and you can at least get an idea
11:37
of what's happening in the tenant. So this is what is available out of the box
11:43
Now, let me show you what you can do you collect this data and the potential of what you can yze with it so this is a power bi report
11:55
so this is the power bi report that i built one more time i i can share this power bi file with you no no issue but let's start with the
12:06
activity one of the things that you can get from the power bi uh as an admin is what is happening
12:12
in your tent what's the activity the activity is if a user opens a report this will create a line
12:18
saying that user a at this time at this date it opened this report from this workspace from this
12:26
client ip address from this user agent so you get all that information um as a in a table that will
12:35
get you so this is the actual activity operation so you get a lot of information from the from the
12:42
activity one so you can get that you can you can take that information you can build some dashboard
12:48
like this where you will be able to for example just give you a trend so i actually have a positive
12:56
trend so i even have a kpi for that it's a positive trend it's a line sharp i can just see okay my
13:03
my activity my tenant activity is growing i'm also using some very cool feature from power bi
13:09
in line charts that is anomaly detection so i have five anomalies so what what is an anomaly is
13:15
something that uh so power bi expected and he's saying that over here it has a value of 1912 logs
13:27
but it was expecting this amount of logs so this is an anomaly and you can click on that anomaly
13:34
and you can see so for example in this anomaly on this point there was a lot of generated embed
13:40
tokens if i go to the next day this generated embed tokens that doesn't exist that's the reason
13:47
why it was that anomaly so this is something that just off the box a line chart it will just
13:54
generate that anomalies for you then i have three kpis that's i think it's important to monitor the
14:00
activity trend i just want to know okay am i growing or not in terms of activity for for
14:05
example if i switch this to the data view data views is actually trending it's staling so i
14:12
don't have a lot of more activity in data view but if i go to offering so if i want to measure
14:18
the creation the edit reports the elite of reports then the offering is growing um so the distinct
14:26
users so in my company since november so i have data since november last year i i i have 89 distinct
14:36
users accessing power bi but in a single day the average is 20. so on average i never add more than
14:44
20 users per day and another thing that i can see here on the right is per hour so and per hour
14:50
I have most of my activity during the morning and the afternoon
14:54
but I never have more than 60 in a single hour and on average I have for example at the most active hour is at 10 a
15:06
On average, there are six users in my tenant. So this is very important information for you to know, for example, for the planning some premium capacity
15:16
So how many distinct users you really have? And another thing that I didn't mention and it's really important about the activity is if you are not collecting and you are not storing this data, you are losing the data because you can only go back 30 days
15:31
So one of the things that you can get out of this session is if you are not collecting and storing this data, if no one is doing that in your company, you are losing some very, very valuable information that you can use in the future to plan
15:46
So just please collect and store it, even if it's on the file system or a drive
15:54
Just collect and store it because maybe in the future you will need it
15:58
Then this chart over here, it can also give me a lot of information
16:04
So I can see that I can see the data per month
16:08
Of course, this is Power BI. I can drill down per day
16:12
I can even drill down per hour if I want. but let's see this per month because it's more easy to to to it's more aggregated so i can see
16:22
a lot of data in here so i can see the trend per month so i can see the amount of distinct
16:28
users per month so in february i'm actually growing the amount of users from january to
16:35
february uh one thing that i like to do is i can come here and i can look at okay i just want to
16:41
look at data views so what is the data view it's a view of the report it's an yze in excel it's
16:48
it's view of a dashboard so and in the data views it's actually
16:56
it has a not a positive trend so it's not growing but if i want to look at altering because maybe i
17:03
i have done a lot of trainings in my company and i want to know uh if the activity is growing so i
17:10
have a i had a spike in december in january it kind of it's the same but in february it dropped
17:17
um and another thing that i can do in power bi that is really really interesting is i can
17:24
okay it dropped in january to february i want to know why and i can come here and say okay
17:30
explain me why it decreased why the logs of altering decreased from one month to the other
17:37
and part yeah i will just do some uh machine learning and try to to and and and do some some
17:45
detect some patterns in the data to to explain that increase and they will tell me things like
17:52
uh okay the the weekday five it was actually what contributed the most for this decrease
17:59
uh on friday uh simone which is a user and uh this demo user they used a lot in january but they
18:11
didn't use it a lot in uh in february i know that i really like to look at this visual like this
18:18
because everything that is on the green it actually has a positive impact everything that is on the
18:23
the group on the red it has a negative impact so like you can even just do a over the the the dot
18:30
and you will be able to see that from one month to the other simone in january had as 71 logs and in
18:37
in february only 21 in terms of altering so this everything it will get that all that entire context
18:45
so this this chart over here gives you a lot of information so it gives you an aggregation of the
18:54
operations it gives you the amount of distinct users per day and per month and you can of course
19:00
drill down as I showed you and then of course so this is where we are just looking at the time
19:08
patterns but you can also look sorry what's happening okay barbie it's a little bit slow right now
19:27
okay so you can of course look at the at the data let's see for for a user so i can click
19:33
click on my user and see all the activity that my user is doing. So I can see it by day. I can see
19:41
the activity for workspace. So Manager Cockpit is the most active workspace. I can see the by apps
19:48
Power BI apps. Manager Cockpit app has a lot of activity in my user and I can do the other way
19:56
around so I can click on the manager cockpit app and see which users use this application
20:04
And do they use the browser or they use mobile? So this the application, the reports that exist in the Power BI, in this Power BI application
20:13
it's important to design mobile reports because there are a lot of users that use mobile
20:18
One of them is the CEO. So it's really, really important to design a mobile application because the CEO doesn't
20:26
access the power bi from almost any other format than device than a mobile mobile but if we click
20:35
let's say on bi etl framework then i'm the only one that uses mobile so you can you can do this
20:44
you can yze this data whatever format you want because then it's easy this is barbie so
20:49
So this is very interactive. So you can just click and drag and click
20:55
and drill through to the data to make some specific ysis. So one thing that you can also do in here is you can click
21:04
I can click on this app, I can click on the user and then I can even go to the most granular detail
21:10
I can just right click drill through activity detail and this will jump to another page
21:17
that will give me for my user for that report, for that application
21:22
what every log that it has available. And of course we can export this data
21:28
and send it for let's say an audit operation. So maybe someone left your company
21:35
and it was asked to the Power BI admin, okay, I just want to know which reports
21:40
that that user just saw or viewed or exported data in the last three months
21:46
So you can very quickly, just with a few clicks, get that and provide that information
21:53
So activity overview. This is, there are many other things that you can do in here
21:59
I'm not going to show you every single one, but I think that you get the point
22:03
So you can just explore all the activity of what is happening inside your tenants
22:09
Then I also have a page I call the report activities. This report is probably the most user assets in Power BI
22:17
So one, the most important one. And this is just focusing on the reports because the activity over here this is not just reports So if I could just look okay let me show me who is doing some export activity events
22:35
And this is not users. So this is service principles or admin accounts or service accounts that they are doing export operations
22:43
So activity, don't confuse with a report activity or opening report. It's everything that is happening in the tenant, even from service accounts
22:52
So report activity in here, it's just reports. So I'm filtering this
22:57
I'm using the same data, but in this time, this time I'm just filtering what is happening on the reports
23:04
And I can see that, OK, what's the most active distribution method
23:09
What's the most active user of reports? I can click on the user and I can see which reports that user
23:14
is looking at. If I click the user, I will be able to see
23:19
what's the most active report. And does it have a positive or negative trend
23:26
And I can click it and I can see that, yes, it has a positive trend
23:31
And on every report I can click and see which are the most active device being used for that report
23:39
So this is just centered on the reports. Then another thing that you can get from the Power BI API
23:49
some another important data aside the the activity is the metadata what to exist in your tenants so
23:57
how many workspaces on dashboards how many reports how many data sets uh on the workspaces how many
24:05
personal workspaces do you have with power bi content in it and you can click and see those
24:10
personal workspaces do you have any office 365 old workspaces that you need to migrate to the new
24:17
to the V2 version, where are they? What do they have? And of course the normal workspaces
24:27
who is the most active user creating datasets in your company? So this is just catalog data
24:34
So in here we don't have any activity. This is just showing you what to exist in tenant
24:41
So this report it's very valuable If you go to the filters and someone complain about the report and you just want to know
24:49
more information from that report using the grid or using the name, you receive an email
24:56
on the premium capacity saying that specific dataset is generating a lot of performance issues
25:06
You can just filter that dataset and understand where that dataset is and know the way it's
25:13
workspace where it exists. Now another thing that you can also get from this
25:20
metadata is data sources. So you can get all the data sets and then for all the
25:26
data sets all the data sources that exist in the tenant and you can see for
25:33
example that in DevScope tenant we have a lot of data sources from files I can
25:37
click here and then be able to see which files, who is the creator of the data set, which workspace
25:47
and of course for SQL databases. I want to just know look at the SQL databases with which
25:52
databases are actually being used. And another thing that I also like about this report over
25:59
here is I can just come here and select and search for a specific server that I know and I want to
26:06
know which data set is connecting to those servers and I can just type the name and see the server
26:15
and the database or the data sets connecting to this database just by using a search. So this is
26:20
very useful when you just want to know, okay, I want to know which data sets are just connecting
26:28
to my production database and you put the IP address over there or the data source name and
26:34
just filter it. Another thing that it's, since we are speaking about data sets, it's really
26:42
important to look at is the refresh operation. So all the refreshes that is happening in your tenant
26:49
you can get those that refresh history. So this is the actual refresh history. So you can get
26:56
okay at what time it started what time it ended the duration it took if there is an error or not
27:05
if it was on demand or scheduled and you can take this information and you can look at it so you can
27:13
see okay I want to know I want to I want to so let's say click on this report over here and I
27:23
I want to know how many refreshes it's run in the last couple of days
27:31
how much time it took. So this data set, it's not a big deal
27:36
So it's actually taking the same level or more or less the same amount of time to refresh
27:42
If it failed, you can get the logs of the failure. I can get the actual per hour activity
27:50
So that this data set is refreshing through the day for every two hours or so
27:58
But another thing you can look at this more in a smarter way like, OK, I actually created a
28:06
calculation column that is a standard deviation of each refresh. And I can see I can say, OK, I want to look at data sets with the standard deviation from
28:16
the mean between 700 and 1000. And why this is important because
28:22
okay, it selected these datasets over here, and then I can just click on the dataset and then we'll be able to see
28:30
that these datasets Power BI governance, it has a strange refresh time
28:37
So sometimes it's 2000 seconds, seconds, other times is 300. Maybe these datasets have some incremental refresh that, by the way
28:46
doesn't so this is not normal so you can look at this data from a top-level view and just okay
28:55
calculate some measures that will do those that that standard deviation and it and you can spot
29:02
some strange things that are happening on your tenant but to be honest the most active usage
29:08
that i get from here is just selecting a workspace or a data set so this is actually
29:14
it has a drill down from the from the data set to the from the workspace to the to the data set
29:21
i can click okay on this data the workspace here and you can see all the data sets so each line
29:27
is a data set and see the trend of the refresh every refresh that happened so this is something
29:33
that helps me a lot um the last thing that is also important as a metadata to look at
29:43
is the users and roles. So every workspace, it has a specific set of permissions
29:49
So on every workspace, you can have admins, contributors, members and views
29:54
And this report here, it will get the data for the user configuration of each
29:59
workspace and it will give you which what's what's that configuration so this
30:07
workspace for bi portal it has nine users those nine users these are the
30:16
actual users so from those nine users seven is admin we have three service
30:22
principles or four service principles configured and I have one security group. And this is very important for also for auditing
30:30
And sometimes I have customers that just take this report and every time someone is
30:36
asking the what's the distribution of users between the users roles and workspaces and
30:42
export this data and provide it to someone. But this can give you this
30:49
You can look also at this report as a best practice. So, for example, we have a lot of things, a lot of admins
30:56
So maybe this is not normal and we should look at it
31:00
So we have a lot, a small amount of viewers, which in our case, it's normal because we are, we do a lot of consultancy
31:09
And most of the time we just create a workspace and we are the admins of the workspace
31:16
The last thing that I want to show you is users and licenses
31:20
So if you take, and this is not coming from the Power BI API
31:26
so you need to go to the Graph API and you will get from all the users in your company
31:31
you will get the licenses that they have. And then you can cross this data
31:36
and you can relate this data to the activity. And this is where you get the most value
31:40
because in here, now I can, okay, let me just show, I want to show users without Power BI activity
31:48
So these users, they don't use Power BI and they have a paid Power BI license
31:54
So these users, they never used Power BI since ever and we are paying a license for them
32:04
So maybe we should consider just removing the license and assign to someone else
32:12
So this is another thing that when you relate the activity with the licensing
32:17
you can make some better decisions on the on the licensing assignments okay so
32:26
and this is a report so well I'm sure that I missed some some ysis he
32:32
ysis or ysis here but I think that you get the point of what you can
32:38
do so in one more time you can look at this in many perspectives you can look
32:46
at this at the admin perspective, you can look at this at the user or the reports
32:51
created perspective like I am the manager of a team and I want to just
32:59
monitor if my end users if my team is using the report that I built so this is
33:05
not only for admin so you can and you can apply a role level security and just give access to the report creator to the activity of their report okay so this is the end
33:16
result so you cannot go this is what you you can do with the data of course that
33:23
you can build your own reports and you can do whatever other ysis that you
33:27
want so let's let's Simon can you can you how much time do I have
33:35
You have around 10 minutes, 8 minutes, 7 to 8 minutes. Okay
33:42
Okay. So, one more time. This is what you, I showed you the end result
33:50
So, how can you do this? How can you collect this data and store this data
33:54
So, this is one example of the architecture that I can share with you, and you can just
34:00
start running it today. The only thing that you need is a service principle
34:04
and it's create a service principle on your asia I will have on the slides I
34:09
have steps to do it and I have three scripts so one or four scripts so two
34:17
scripts to collect the metadata to PowerShell scripts to collect the metadata
34:21
then another PowerShell script to collect the activity what is happening in your tenant and another PowerShell script to collect the users and the
34:30
licenses. In the end, you can store that data in the local in the file system and Power BI will
34:36
connect to that folder and just give you the same report that I was showing you. Of course, that if
34:44
you want to do it in a more professional way, that's not the case. If you want, I would recommend
34:51
you to use Azure for it. So you can, but you can do this with whatever language or cloud that you
34:58
want. The only requirement is you need to call API, you need to be able to call an API and store data
35:04
So normally in my customers, what I do is I use an Azure function that will collect this data every
35:10
hour, it will start it in the data lake and then RBI will connect to the data lake. You can also use
35:16
another tools like data factory, there is a blog post by just the link is on the slides that it has a
35:24
blog series in how to collect and store this data using data factory so if you are more into data
35:29
factory i will recommend you to to to look at it um so this is the apis that you need to to use
35:37
so for the power bi metadata you need to use a new power bi admin scan apis that will just do a
35:45
scanning over all your tenants it's really awesome it's a really awesome api because it's and the
35:51
the first time it will get everything, but then you can provide a timestamp
35:56
and it will only give you the workspaces that change it since the last time that you collected the data
36:02
There are some things that I still need to use the old get groups as admin API
36:08
that will give me all the workspaces in the tenants. And I need to loop those workspaces because for example
36:15
the power of the data API that is no way to get the refresh of the data sets as an admin So you need to actually get the groups as the admin and then loop and for each data set and workspace and call the refreshes API for those workspaces
36:34
And you need to make sure that the account that is running the script, it has a permission to get the data
36:41
The activity log is another API from the Power BI admin called activity events
36:46
the graph for the users and licenses i'm just using the graph api so these apis they are pretty well
36:55
uh documented so i have the links on the slides but for example for the activity this will show
37:02
you what type of get statement you need to do get get request you need to do uh and it will show you
37:09
the payload that will reply one more time you can call this in whatever language that you want
37:14
you just need to be able to call this API. So one thing that it's important to discuss is the authentication
37:23
So all these APIs, they use OAuth 2.0 to do the authentication
37:28
My recommendation is to use the client credential flow. The client credential flow will force you to create something
37:35
that is called the service principle. So you need to go to your Azure Active Directory
37:39
you create a service principle, and after you create that service principle
37:43
you save the application ID secret and the tenant ID. You need to create also a security group
37:50
and add that service principal to the security group. And on the Power BI admin portal
37:55
you need to allow that security group. And of course the service principal
38:02
to access your Power BI admin API. So, and after you do it
38:08
and there is a lot of blog posts from the community that explain how to create the service principle
38:15
but it's these eight steps. And after you do it, you don't need
38:19
so you have everything you need to run the scripts that I can share with you to collect this data
38:24
and extract the data and store the data. So for the starting the data
38:29
I will just briefly go through the scripts. So the four scripts, the barbie, the fetch activity
38:36
one more time, you can only go back 30 days. So if you're not collecting this data, you'll lose the data
38:40
the catalog. So this is the any none of these scripts is too complex
38:48
So it's PowerShell because I'm more comfortable with PowerShell. But one more time, you can do it whatever language you want, or you can use
38:56
another tool that can call an API like an Azure factory to collect the data
39:00
So I have those four scripts, the catalog, the activity, the refresh. For the refresh
39:08
it's important to state that there is no admin API to collect this data
39:13
So you need to make sure that the service principal has access to the workspace
39:17
There is also another script that I share is that it's a script
39:22
that will go to all the workspaces as the admin and make sure that the service
39:26
principle has access to the workspace before running the script If you want to if the refresh operation is something that you want to yze And of course the Graph API the Graph API is for Microsoft
39:40
You can it's very it's a very important API to just collect to your Azure Active
39:45
Directory and to connect to your Office 365 information and collect these users
39:51
and licenses information from there. One thing to be in mind is the API throttling
39:56
So all of the Power BI APIs and the Graph API, they have throttling enabled
40:02
So you might get this 499 error to many requests. All the Power BI APIs, whenever they have throttling
40:11
they state the amount of requests that you can make per hour
40:14
It's a generous throttling. So I only got throttled in tenants with
40:22
let's say 50,000 workspaces with a lot of datasets. So on a normal tenant, I would say 1,000 workspaces
40:31
and you don't get this throttling. But if you get this throttling in the code
40:36
in the PowerShell code that I provide you, I actually handle the throttling
40:39
So whenever you get the throttling, you need to wait to make the next request
40:44
So, and after that, you collect this data and you store the data, then you have the datasets
40:49
On the dataset, in the Power Query, I just want to mention something that is important. This Power Query, the Power BI implementation, might sound too complex
40:58
but one of the things that it's important just to notice is I use a technique called the file proxy
41:05
And this Power BI file is ready if I want to switch the location where the files reside
41:11
if I want to switch the location from the files from the file system to a data lake
41:15
I just need to switch one Power Query called the file proxy
41:19
and I just uncomment one and comment the other, and I can switch the location of the files
41:25
between a folder and the data link. So this is my way to have a single development
41:31
for the multiple data sources. I have a video, there is a link here
41:35
that explains that where I explained this technique. So the Power BI dataset is very simplistic
41:41
I have two kind of two datasets in one, one focusing on data catalog
41:46
another focus it on the activity and then the report it's what I showed you I
41:53
want to go into too much details on it so I already explaining you all the all
41:59
the pages and what you can do with them and this is it so it was I know it was
42:07
really really fast in the in the part of the code but I'm sure that if you have
42:13
someone that can write code or just understand PowerShell, it will be very easy to understand
42:19
it. But my only take from this session is if you are not doing it, if Power BI is important to you
42:27
just do it because it will be worth it. And thank you
#Business Operations
#Computers & Electronics
#Data Management
#Software
#Monitoring Software