Power BI REST API’s 101 & Useful Techniques (with Rui Romano)

[Music] [Music] [Music] hey rui how's it going hi rin help with you as well yeah yeah happy friday happy friday yeah thanks for tuning in i'm um you know anytime i have a chance to dive into some i'd say uh more advanced topics and stuff and especially with the apas i'm always something of an exciting topic for me so i appreciate you joining for sure today so thank you a pleasure it's always a pleasure to nerd out about this stuff so yeah i mean i i think my my first introduction to you at my very first mvp conference way back in the day i remember was matt matt ellington working with you to uh to basically create auto scaling for premium when auto scaling didn't exist and you helped him out i know some of the the power automate um codes back in the day yeah i remember that i think it was at the mvp mvp summit yeah using anything yeah 2018 2019 i know i got nominated in 2018 so that would have been the like february of 2019 that that happened um but yeah i just i remember matt telling me i remember matt telling me about you though it's like rui's the individual who knows has a lot of knowledge about a lot of different platforms you were very um he had a lot of skill sets scattered across a lot of different software and tools and in code and he's like he's the guy you go to if you want to figure out how to automate something rui's really good at automation yeah and uh i consider myself a little bit lazy so try to try to automate the things that gave me some work go ahead go go go ahead go ahead i was just gonna say there's a phrase around this it's the uh you want something uh if you want something done um give it to an employee if you want something done efficiently give it to a lazy employee yeah yeah yeah whenever i do something like 10 times repeating a pattern i try to automate it if it's possible sometimes it's not possible but uh whatever it's possible it's uh it's definitely worth it and the apis and not only the power bi apis but asian apis all of that uh one of the one of the use cases is actually automate stuff not only admin operations but exactly and there and i think today we'll certainly be able to get through and see a lot of good use cases not only for like the um i think some of the obvious ones but uh i think some of the the more unique scenarios as well to utilize these things so looking forward to that i i my favorite live streams are the ones for sure when i get uh get a little bit of popcorn and come out of the of the stream as well with a bit of new knowledge on some new use cases for that uh like last year some of my favorite ones were anything without groups where i learned a bunch of new ways to utilize those because they're such a diverse tool and i'm sure i'll get a couple takeaways here today as well with apis yeah yeah call groups is definitely one of the one of the then gems in power bi people try to to think on calculated population groups only for intelligence and aggregations but it's much more than that you can even use it to be lazy so one of my favorite use use cases of calculation groups is um transform a card so if you if you put a measure on the on a visual card and sometimes you want to show contacts for that major say for example sales amount but you want to show the comparison between sales amount and last year and you can use calculation groups to switch the value of the of the of the current measure and by a text that will show you that that will do the the same period last year and you'll show the variation um in a single visual so you don't need to so if you want to do that you need to you need to use two measures you need to put two visuals or three to show the the variations and you can do that with population groups yes exactly i i remember coming across i don't know if you you wrote it or somebody else did but i've i've for a long time i've used cards where i'll often create a measure that's a concatenated string where it'll usually be an actual maybe a forward slash variance an arrow indicator of up or down but you can create a calculation group that basically takes current measure you know has a variable for a cat you know it basically does all that stuff with an output and all you have to do is take any measure you know filter to the group onto that boom like you said you immediately get that and like okay you know i think with any of those examples i think it's just such a good way to be lazy with the and efficient with the model just because you can reproduce this logic that otherwise would require a list of 500 measures but i i kind of think of three three things in power bi that microsoft built to be so flexible that there will probably be a lot of ways that they didn't expect or necessarily want people to use them and it's bookmarks calculation groups and now field parameters those are those things that are so uh so configurable that there's gonna be a thousand ways people continue to use those and i love muhammad who was on last month uh who talked about like because bookmarks for their uses today has a very bad design interface the output is fine for the end user but it's not easy to develop with them and it's like well it's because people use them for none of most of their not intended purposes they were designed to filter and people use them for pop-ups and all this other stuff and we didn't really build them to do that we just made them so customizable that people found creative ways to utilize these in the product yeah and visual fields it's it's amazing so actually it can kill like 80 percent of the use cases of bookmarks that's just to swap the measure in a in an individual or swap the field that's you want to see by ear and you want to see by now and uh i mean yeah exactly you can do it yep that was one of the most common reasons that i used uh bookmarks is to do kind of a visual swap where you had two cuts of a visual that couldn't quite be done with a measure because usually the axis or something needed to be changed and field parameters let you do that and i used to have a bookmark that did a customer wanted a one-click hierarchy switch so they had five visuals all with calendar hierarchy they wanted to be able to click a button that switched everything from quarter to year to month you know each all the same time and bookmarks did that i can now do that with field parameters so i considering how hard it is to develop bookmarks every time a new feature comes out that removes the need to do bookmarking for something i am happy for that because usually the new feature is better designed for that purpose muhammad even mentioned i think that they're long-term goals so i'm going to guess that's at least one to two years out but apparently there will eventually be a dedicated way to link a button to the visibility of a visual without having to use bookmarks the visibility he's mentioning is a long-term goal to provide some other interface that will be better than using bookmarks to toggle show and hide so when that comes out i think that will eventually lead to bookmarks being used for its one primary purpose which is just simply a macro to apply multiple filters at once yeah yeah honestly i try to avoid marks as whatever i can it's just because it's a lot a lot of work oh i i agree to do it and to manage it especially the thing that stress me out is you build all of that into bookmarks and then using so you build all those interactions using bookmarks and and then you start to think hey i need to change something i need to go and change all of these again which is a lot of work 100 um i'll make one final comment and then i do want to bring it back to uh give your chance a little uh give yourself a little bit of a chance to introduce yourself but the final thing that i'll just at least say on bookmarks is i have multiple reports where clients have asked for so many requirements for them and a page has like 20 plus of them and i've i've been very transparent like if i continue to build this i probably can't hand this off to anyone in your organization to manage because i don't think they'll figure out how to and they're like and says nope they'll be fine that you know this person's an expert they'll know what it is i build it i try to hand it off like actually no we need to keep you on retainer to manage this now because you're it's too complex like i built it to your requirements though and they're fine with it but it's kind of like a golden handcuff you know it's you're you're locked into having to be the owner of it now just because it's beyond the scope of what i would say an average power bi developer can matter just because of how layered the bookmarks are with each other yeah yeah but i like i said i'd love to give a chance for you to introduce yourself a little bit um for the people tuning in you've heard that uh we've known each other for a few years and he used to be an mvp before moving over to microsoft um but yeah why don't you share a little bit about yourself i'd love to hear it yeah sure so i live in portugal for starters so i'm working in a data stack and always in microsoft stack for the list like this like last 15 years i started as a developer went through all the stages of developer architecture one of the reasons for example why matt said that i have some background on development and multiple platforms was because 10 years ago if you want to do for example mobile bi you needed to write your own web application there wasn't many solutions to do that so i done all of that stuff uh the last five six years uh i managed an amazing team at devscope i also had the experience as a manager and last year i joined microsoft and the amazing rbi cad team where i have i'm having a blast working with the brightest minds in this in this industry it's really really an honor uh i'm also a very community driven person i was an mvp for the for for five years before joining microsoft i also co-founded a power bi user group that it has a lot of a lot it is growing a lot for the last last year so it's more than three thousand five hundred members uh we do meet ups every month it's a very very engaging community you need to you need to you need to to do a session for us as well i will invite you uh and um and i also do uh one of the one of the things that i'm doing the most lately is open source work so i will share a few repos that i that i when i do contributions especially around apis which is a topic that i am very passionate about and monitoring and governance and yeah so this is it about me excellent yeah and i i would happy to be happy to come on at some point for a meetup i am still planning on going to portugal uh i almost decided to go um a friend was inviting me after uh after the next steps conference for the weekend but my general thing with traveling is anytime i visit a country for the first time i want to spend at least a week there because if i don't come back for five to ten years uh i don't want to feel like i've missed out anything so um when i when i do come probably in the next year or two i'll be making sure to hit portugal southern spain and i think i will go down to casa casablanca as well don't forget to visit the north of portugal where i live and oh yeah i'll do the whole a whole circuit in portugal i'll make sure to see everything and then um i'll be hitting you up to show me the best spots to go hang out at so sure um but yeah uh as far as like you know some of the api stuff what do you want to start the conversation today so uh first of all i think it's important to to talk a little bit what are the apis i don't know if you are sharing my screen i can do that now i am okay so i have a deck okay i prefer to put some content in a in a onenote uh so first of all apis um it's a it's a set of rest apis so apis in power bi is a collection of rest apis you can call it within anything that can make a http request and they are mostly important for two type of users so for admins they are really really important to administer provide do administration tasks on your tenant to monitor your tenant to govern your tenant to to do security operations like assigning yourself or assigning someone else to uh to a workspace developing takeovers and they are also really really important for developers because for developers maybe not for managing maybe to manage their own work but for a power bi developer they can be really really important to automate stuff to uh to uh for example if you spend some time publish every time you you have two three data sets two pdi x files and uh five ten rbi pdix that are live reports connecting to a data set you cannot and you are publishing that manually you can automate that task and do the publishing automatically by using a script and you can do rebinding that's one of my favorite developer apis that allows you to rebind a report to a drift different data set and uh without doing that on power bi desktop that will be a slow operation because you need to open the report sometimes it will give you an error and then you need to publish it again you can you can automate those those kinds of uh operations using using the apis and to find out about the apis you go to microsoft docs rest api api rbi and all all the apis will be listed in here and they will be grouped so you have uh i don't know why my zoom it is not working let's see what happened you do exactly what i do worst case you do that yeah yeah search on the windows uh screen to get it turned on yeah yep so all the apis they are grouped by the typos api so if you are an admin you will probably looking into this group of apis and then they are organized by uh artifact types so you have apis around that apis around capacities apis around data sets apis around groups or workspaces the reason it's called group because before rbi before calling workspace uh it was groups uh so but or maybe for legacy it's still on the api still called and then for example on groups if you want to learn about apis around groups you can expand and you will see for example this one uh i can have an api as well and use it to a group i have an api to create a group uh or a workspace to get the users and update the permissions on the on the workspace this is something also very very useful uh especially when you are dealing with large enterprise and deployments instead of managing the permissions manually going to the workspace and assigning the permissions you can have a an excel spreadsheet or a database that defines all of your security and ever whenever you want to update you just run a script and it will do everything for you uh believe me on a large enterprise deployment this can save you hours and it can also bring you trust another thing about using the apis and automating a certain task is not only it will save you time it will also make the operation more uh trustable because believe me you will you will you will get tired you will you will make mistakes but the code doesn't make mistakes if it runs okay now it will run okay tomorrow uh maybe there there might be some api errors but if it runs and if it gives you if it gives you the okay it's okay uh especially managing permissions it's very normal to make some mistakes uh i see that happened a lot and and whenever you wanna so let's say you are interested interested about the api so let's say i wanna i wanna get all my workspaces that i have access this case is the get groups uh i can see in here the description of what the api does the type of permissions that you need uh the url and the and the http verb that you need to follow this case is a get and this is the url we need to you need to call and some of some apis have parameters other apis they don't have parameters in this case the get groups you can you can specify a filter for example to filter groups that start with a certain certain character you can also only especially when you have a lot of workspaces you can apply a top that will only return the first and end results and under documentation you can also see an example of the request an example of the response that you will uh that you will get from the api uh so even defining and using the api this can be useful to understand if this api will be useful or not uh for you and you have a ton of them so one one of my favorite ones i really love this api i think my head is always bubbling with ideas when i think about this api it's the execute queries api so it's an api at the data set level execute queries in group it's basically an api that allows you to execute the dax query over a dataset it has a lot of it has some limitations so you shouldn't think of this api to call like hundreds of times per second or per minute it has some limitations one of the limitation that we actually we added this to the documentation this week so you can only call it 120 times per user per minute uh but it's i i can't do a demo of what you can do with this api but things like data quality tests integration with power ultimate uh and bring all of that power platform uh integration so you can have a power automate executing a query on the data sets and to do uh for example uh uh an alerting system whenever a specific measure goes over a certain amount generate an email i'm doing a i'm working with a with with a colleague from from the cat team and we are uh using the i have a power bi monitoring solution and and building something with power ultimate and this api to do policy to provide that means a policy uh control over their tenants so things for doing things like i have data mart enabled like to today you cannot enable data marks with certain group of people but you can you can use the the activity api and uh to get everyone that is executing using data marts and and have a policy hey is anyone using data mods other than these people and report back to the admin and the admin take some action and talk to those users and say yeah you shouldn't use that we don't have it we don't surprise it uh yet yeah and there's a i'll just mention too is the it isn't i think one of the coolest things to come out for sure to be able to actually extract data out now um actually you could do those dexterities and uh stephan um rizzo was uh on my channel about six weeks ago and did a stream on that basically the week after it came out as a launch feature so it is an amazing um new functionality tool for sure and i think it adds a lot of power to the api but um for those listening that drop the chat in the link that walks through that but there's a good like hours demo that actually goes through a lot of the capabilities of that as well the thing here here is this is not something new so before the rest api you could use the xmla endpoint and some c-sharp code to execute the queries and honestly actually that is a very that required premium though right for the xmlma you had a premium but with the api you can do a can't you run the execute queries just against pro uh actually i'm not sure if you can i'm 80 sure i it's mentioned in the video but i thought with the api that doesn't require a premium workspace uh to be able to use that to execute with that but obviously the xmla endpoint would require premium workspace yeah yeah i'm not sure if it requires a baby or not uh honestly but uh you also need to take into consideration some of the limitations of the execute queries api and that is okay last week he wrote this blog post on i'm comparing the two so and and the the takeaway here is if you want to build something to scale xmla endpoints it is it is what you should do if you want to uh if you if you want to execute a query and you and you don't want to execute it multiple times per second or if you don't want to to build a web application to expose data from a power bi data set that and that web application can be used by thousands of users rest api is not the way to go but the thing about the rest apis maybe i'm not sure about that premium and shared capacity but for sure the main thing for me is the the the the flexibility it gives you so because one thing is calling an xml endpoint you need to have some that.net code another thing is you just need to have a tool or a local solution that can make an http request because you can you can use a rest api on a jupyter notebook you can use a rest api and power ultimate you can use the rest api whatever you want and this can open and make the the scenarios of users to to of data that exists in the car the other set much much more broader uh so the interoperability that that you can get with the rest api in in this specific case it's uh it's uh it is amazing and um but if you i i wouldn't i wouldn't use this api for data integration scenarios especially those let's say you have a power bi data set and this you can easily have a part of the other set with millions of millions of rows and you will use the rest api to extract those millions of rows every day or every hour don't do that but because you you will hit one of the limits one of the limits is it can only return one billion values per query uh but if you say okay i don't want to do data extraction i want to do a snapshot of my sales kpi for store with a comparison with the value on the on the current month and uh and the comparison to the previous year and uh and the last 12 months average that's the type of thing that you can do and and it's very easy to to build a power automate that will snapshot these values into a data lake just for snapshotting the the the value because then power bi whenever you refresh and if the value changes it changes so you cannot it doesn't keep the history and if you wanna if you wanna do some snapshotting then the api could be uh could be definitely useful uh yeah and your tier commenter like the the yeah it's it's maximum of uh yeah hundred thousand rows million values unique values in the in the frame um it's only 15 megs uh data per query so it isn't super big uh i do just want to double confirm i did i checked the documentation and like the article that you made at scale basically like it's kind of a grow up story for for basic data outputs at a smaller scale this does work with any version of a version 2 workspace that is pro or above um but the second you need to do enterprise type stuff you're probably not going to want to rely on this versus the xml endpoint which has a lot it has the ability to scale up to a much larger capacity definitely definitely and i've seen that in a few customers so whenever things start to scale we start to have a lot of users a lot of it's xml endpoint is the way to go and and others you need to care about other stuff like action pooling and uh it's not it will be more challenging so rest apis so definitely for the for the let's say easy stuff and uh and things that are not going to be called hundreds of times per second or per minute but def but you can do some very very interesting uh another thing for example data quality so you could build a power automate or a logic app that will go to a data set and learn some data quality tests on the data sets things like uh i want to i want to make sure that the count of stores is 100 because i only have 100 stores and if it's not 100 maybe something happened in the space upstream right in my upstream data source uh or my atl that didn't load the data to the data urls and you know my probably i didn't said i have the wrong i i could have wrong numbers so you can use this api for later quality which is a very good use case also so uh i don't know if there is any questions so i can i can move on there there was one clarifying thing i just wanted to double check so rafael pointed out and um all i'll reiterate is the that the these kind of connections for the uh the executive queries just have to use the new version two workspaces uh are there still classic workspaces that exist or has the migration auto migration happened yet i know at one point there was a there was that alert that microsoft was going to automatically update any legacy workspaces after a certain date i'm not sure if that ever if that's if that's going to happen or has happened i think it's had it uh it's happened uh yeah i'm not 100 sure yeah at some point i thought it happened yeah i thought it was like a year maybe a year or two ago that the uh they they did an auto migration with an alert to customers that were basically gonna upgrade all legacy workspaces uh there is a question actually that i can pop up let me throw this here [Music] would this work with the data set connected to azure analysis services but that's uh switched to direct query so i'm assuming this is for the execute queries conversation uh can you can you can you repeat again i was yeah so what could you do and execute queries against a data set and power bi so basically it's a data set in power bi and i'm assuming maybe a report in power bi technically that's connected to an azure analysis service model that switched to direct query i would assume no because the power bi doesn't contain the data set anymore no uh yeah actually i am almost positive that it is listed on the on the limitations [Music] yeah only data sets in your workspace data sets are also in asian analysis services aren't supported okay yeah that's what i thought because i'm guessing the data has to be some type of an import or composite model that is kept in power bi uh within the the actual um yeah okay and oh yeah here we go uh one other one that i'll pop up to you since we're on a couple of questions excellent uh robin had one um i'll read it out since i don't think it's on your screen so any idea if access management revoke will be available anytime soon loving the endpoints and are using them extensively for automation okay access management's revoked so it sounds like you the ability to use the api to revoke um access from users using the api so one thing that you can do for groups and workspaces you can delete and add a user as an ad so i don't know if this can be considered as the revo but this is only for workspaces i i you don't get the same thing for reports and uh uh and actually for the data set as well and part of that i guess the curiosity is it they that that command is delete user as admin i'm wondering it's because it sounds like that means you can add or delete a user from the workspace as an admin for users that already exist in the workspace i'm wondering if there's able you're able to just change their their permission level from your reader viewer contributor reader admin i don't think so you need to remove it and add it again but you also have the same thing for the for the data set so at the data set level you can also change the permissions of the read read the read and write and the bill permission i don't think that is the same one for the reports uh so data sets and uh and uh and workspaces okay but there is there's a different set of uh options in between the data sets in the okay workspaces gotcha okay cool and that's that's a typical uh admin operation it's actually providing sometimes themselves uh access to the to the to the workspace to debug or to to uh to diagnose uh some some issue uh one thing that is really important when you add yourself to the workspace and you remove so just for the security and governance perspective uh you can do that so imagine that there is a workspace with data sets with salary data and the company doesn't want to keep access to anyone just the to the the finance or the hr team a power bi admin can view that data if he adds himself to the workspace but that information will be audited so it's possible to the to uh to someone to audit if if someone if anyone added them in themselves to a workspace and a distinct you the distant list of users that access that workspace so from the auditability perspective no one can delete that that audit operation of adding and removing themselves from a workspace or dataset whatever excellent all right yeah i'm loving learning some of these new uh what would you call them would you call these uh commands or functions uh for some of the stuff that you can do with the api i always confuse them between coding languages the the term to call the the actions that you can do with um with the language i usually call them operations on apis okay so yeah but you get it i'm sure that other people call it don't have other names so some uh end points or rest endpoints because it could be also a valid a valid name but you can see from this list that there are a ton of them and certainly i don't have so sometimes so for example i only a few months ago discovered that there was an api to update the data set for missions uh sometimes they just pop up in here and uh and it's important for you especially if you are an admin to uh to be to have a frequent let's say to give a frequent look into what's new and what can help you on your on your day-to-day job uh what i can tell you is this can make you way way more productive and it's definitely worth the invest even if you don't know how to write code or if you don't know authority code you can use the apis on a power automate that is a local uh solution or you can ask some someone help to to build some scripts for you but um it's definitely worth remind me is there a um i know with power bi embedded they've they've done a good job of building those like sand demo sandbox environments where you kind of like drag and drop code to see what it looks like when it renders um i i can't recall is there any kind of uh environment for the apis that kind of you basically let you do a no code low code build out solution to show what outputs come out of it or no no okay as far as i know no uh i can i can show what i usually do uh i wanna use an api um so the easiest way for you to use an api is my favorite one but there is others so if you are more into python there is also a python library if you are more into dotnet you can that is also not at sdk the one that i use the most is powershell just because i'm more comfortable with it uh so that is this powershell module called microsoft power vi management and using powershell and everyone has powershell in there in their laptop you just need to type this install module and the name of the of the of the module just need to copy and paste this name over here you run this and this will install the module i think i already have it installed so i will skip it and then this will give you access to a collection of commandments so command what's in powershell it's just a function that you can call uh and one of them is the uh evoke rbi rest method and in here let's say you want to get all the workspaces from uh from your from that you have access to as a user so get group so this is probably the easiest api so this is the this is the the url we need to call but because the the commander already knows how to interact with the api the only thing that you need is this last part over here so the groups let's copy let's go to the powershell window and let's type it here groups and url live url groups so sorry and another thing is the method so the method is this first part here so the the gets because this is a get operation if it was an operation to make some changes it will be probably a post or a put in this case it's a get we need to you need to type that down get and you call it and it will give you an error because the first thing that you need to do and i made that on purpose is you need to log in to your power bi account and you only need to do it once on the powershell session on a script or in this case i'm i'm running a powershell session so you need to call and it gave you that one you need to call this login for the eye service account so if i call it plug in power bi service account this will do something interesting so this will pop up a window in my browser to do the authentication with my powerpi account so if i do that and let's go back to the powershell window now how it will tell me that i'm not indicated with my account on this tenant uh i can also do the authentication service principles that's something that i will also like to spend like two to three minutes to demystify some things about the service principle uh but doing the login uh the normal the normal thing that you want to do is you want to use the api as if with your account so and and if you want to do that this is the way to go don't put credentials and usernames and passwords on the scripts so do it like this right you can write a script that does some specific tasks and the first thing you can do is log in and it will do that pop-up window to ask for your authentication and now if i type that again this will execute and it will get me all the [Music] let's just do some formats tables i don't know why it's not working but okay it just returned in this case all the workspaces and the information about that workspaces that exist in my tenant so the id the configuration of the workspace if it is on dedicated capacity what's the capacity id uh if it has a data store a data full storage or not the type of the workspace if it's v1 v2 for um personal workspace or or not so uh this is how i usually do it so if you just as a recap so you need to install the module you need to have powershell you will have powershell on your if it is a windows um a windows laptop uh and then authenticate so using the login um the login command and then you can call the invoke for vrs method on any api so let's let's try it with another one just as an example so if i call it for example uh let's say i want to get the data sets so that is an api called get get data setting group now this one is a little bit different because it's groups it's the group id and the data sets so i wanna if i wanna get information about the data sets i need to provide the workspace id but it's exact exactly the same thing so instead of being groups i need to put here a data a workspace id let's go in to this workspace and what's the workspace id so if you open a workspace in power bi the workspace id is this over here this id so let's copy and go back to powershell paste it and slash datasets now this will return uh information about the the the data sets in the in my uh in that workspace so excellent it's returning adjacent value with information about the data set in here it's a ton more information and so if it is binded to the gateway the user's configurations for its for its data set for the user permissions so you get the idea so it's basically you it's a it's uh for me it's the quickest way to just try an api with the data from from your tenant quick uh follow-up question just from uh from donald um that you can clarify so he wants to know that um and confirm that a normal user versus a power bi admin um can execute these uh apis of these queries yes certainly so if you are a normal user uh in here i'm executing the api i'm a power bi admin but i'm executing the api with my user uh because i'm not using an admin api so i'm getting so groups uh data sets from this group but i need to add to the words gotcha okay oh looks like rui just froze though let's see if i can get this i thought below be located okay good yeah you froze for like two to three seconds so good you're you're back i was just about to pinch you on the sky okay you're all good now all right okay so all the ap if you are not an admin you can execute all the apis that are not under this admin uh space over here so all of these apis you can execute as a regular user now the thing is you will only see what you are authorized to see so if you call the groups api or the get workspaces api it will get you the workspaces you have access to not all the workspaces but if you are an admin you can call this admin apis and then and the admin api that is this get group says admin that it's almost the same of the of the other one get group says admin this is one that i want but with a big difference so instead of of starting with group the admin apis they start with admin and the admin apis uh you need to be an admin so to call anything that starts with admin you need to be a power bi administrator or you need to have a service principle with permissions to call the admin apis in this case in in in this case we don't know but you don't need to be a power bi admin to use the apis that's why i say that as a power bi developer the apis can still be very useful to automate uh some of your some of your tasks yeah like in we've just talked about the the goal with any of this stuff is automation right you want it you want to save yourself from any of the repetitive tasks of the desk monkey yeah certainly okay so i already showed you how to call the apis one of the one of one one of the one of the methods is using the powershell mode it's my favorite one uh i also know if you are more into dotnet probably you can use the s the c sharp sdk and and also call the api from there so but everything that can make an http request can uh can call uh an api um other thing that is important to to discuss about the apis is the limits and especially the admin apis this let's say this get groups api get groups as an admin it's a it's a very useful api that will list you it will get you from it it will get workspaces as an admin so in here it will list all the workspaces even the ones that you don't have access to or you are not assigned in there but the one of the limitations of this api it's really really important to take this into consideration when building some script is you can only call this api 50 times per hour if you call it more than 50 times per hour you will get throttled so the api will return with a 429 error and probably it will force you to wait before well there we go again interesting it's freezing just a little bit all right let's see if it kicks back in for rui now up to 5 000. there you go yeah i did it take up for just a quick second so maybe just repeat the last three three sentences that you said okay i was saying that the limitations uh one of the limitations of this api is you can only call it uh 50 times per hour but you can use this api to get 5 000 workspaces for requests so if you wanna so doing the math so you can only return without throttling 50 plus multiply it five five by five thousand workspaces uh per request uh so if you have a really really large tenant you'll probably eat throttling but you can wait for one hour and then resume after that um after the throttle excellent okay so then there there's at least a window that it will uh um reduce reduce the the speeds and throttle it but after an hour it resets so that's good to know yeah yeah but the thing is every time you are going to use an api if that is this limitations [Music] paragraph in here or section read the limitations understand what they do and basically take them into into consideration we have been talking about the execute uh the execute queries api that this is one of one of the apis that have a lot of limitations in here uh and you need to take the concentration you won't be able to return more than one million you won't be able to call it more than 100 100 and time 120 times per minute uh but it's it's it's important to to read them and uh and take them into consideration what you want to do otherwise you will you'll get you'll get surprised uh after developing the code and that's that's not nice for sure exactly good to know i mean i think both the art of possible but also these the ceilings are limitations as you mentioned that you can hit are good to know to prevent that happening um before it hits production and then it um it uh screws up the production line in some way so yeah and since we are talking about this api let me show you a quick demo of what you can do with this excellent one of my favorite use cases so i will show you your power automate so power up mate it's a is a [Music] a low code why you're not showing me either okay i have this power ultimate here called pbi set tax query and email and this power ultimate it's using the dax the the the execute queries api that today you don't need to do it like this you have a much much easier uh solution but before this that is using the connector called executes queries from power bi uh finding it but there is a uh right now there is a connector called execute queries that you can call you don't need to um you don't need to to call the api but if you want to do some something or so it's run a query against the data set you just need to select the data set and the workspace and the access and write the back statement in here and but before we before this connector uh got released uh there was only the dax api uh the the http api and this is an example of one of the things that you can do so the idea is i i'm running this every day i have a dax query that will compare it will get the sales for each employee and every time or every day that an employee is selling less than last year so it's underperforming i send an email to the manager with all the employees with a list of all the ads are underperformed the that data sets the data set id that you want and i want to do the query and the execute queries api um you parse the the the body so that the reap all right guys it looks like the internet's hiccuping a little bit and i should have on my email so something like this so this is a one example of how you can use that api with power automator or whatever tool you want to run some dax queries over the data sets and in the end send an email like an alerting system to to uh to other users and and this is one of the use cases but you can do for example for data quality and just do a better quality check on on your data set so this is uh an example of how that uh that api can can be really really useful how are we on time we're good on time i was just uh messing with the channel there's a internet is being a little glitchy at the moment so there's every once in a while it was pausing and buffering but i think we're back to good again um we have about another i'd say 10 or 15 minutes um that we can go through a few other demos so okay another thing i want to mention is uh so sometimes you you are a developer and uh you want to try the apis and you want to try the service principles and i want to try the admin operations um but you are not an admin so you don't have uh you don't have the admin permissions on on your tenant on your company tenants a way that you can that you can be an admin is you can create your own tenant and this so that is this program called microsoft 365 developer uh i can i can share the link and you just need to join the microsoft 365 developer program and this will give you a tenant so this will create you will be able to create a tenant with 20 or 25 e5 licenses so you get power bi power ultimate you get the entire thing so you'll beat the admin you will be the global admin of that tenant so you'll be able to create whatever you want in it uh and this is not a trial so this is something that if you use for development purposes and believe me they do monitor that if you are doing if you are using this for development purposes or you are using it to uh to actually do some work uh or production work but if you use it for development purposes this will be renewable every 90 days so this is a really nice way for you to have your own tenant uh where you'll be in admin you can try all that all those admin knobs and you can turn whatever you want you can all the admin operations you can build your your solution using this sandbox uh that will give you the the the pro licenses uh for free and uh and it's it's definitely something that it's if you if you uh if you don't have it and and you are not an admin and you want to try those stuff look at this this is very very very useful excellent excellent yeah like it like as i was kind of talking about a little bit earlier anytime there's any kind of a sandbox environment to play around with i think that uh that makes it a lot easier for exploration and discovery for sure yeah now another another thing that i want to talk is service principles so whenever let's say if you want to build a script show you if you want to build a script like let's say example i want to get all my all my data sets so this is let's just check if this is running it is so i have this simple script over here that is connecting so i'm logging in with my account and i'm calling the admin data set it's an api that will return all the data sets that exist in the tenant but every time i run this this is going to pop up for the for my authentication now if i if you want to build a script that is unattended so a script that will run without asking for the authentication for example a script using one of my friends so once whenever you do a scheduled refresh on a data set it will refresh all the tables and all the partitions inside of that data set but using the analysis refresh api you can specify the the tables that you want to refresh you can specify the partitions you want to refresh if you have incremental refresh turned on for example it's uh and this works in pro and premium so if you want to refresh a single table in a shared in a pro workspace this is the only way you can do it because you don't have the xmla endpoint so this enhances refresh api it's a it's a very useful api to automate the refresh of of a data set without using the schedule refresh you might be using this api after your etl workload or after a certain operation and you want to make sure that my data is ready to refresh and now i can i want to refresh my my data set so this type of api you don't want it to be running and asking for the conventions because this will be running on the server it will be running on on something that will that that won't have someone going come on there and want to talk about this because i usually see people going wild on the creation of the of those of those service principles they create a service principal things doesn't work and they go wild and they gave all the permissions until it worked and i will tell you exactly what you need to to do whenever you want to use the service principle and what type of permissions you need to give it on your power bi tenant and i think refreshes i think are a great feature for sure like you said there's there's often dependencies that you have um to do those and through the api and there's also some stuff in power automate which allows for you know uh just being able to intelligently determine when something is refreshed to kick off something else which from the native power bi interface doesn't really give you nearly that flexibility yeah so the nsl refresh api it's uh it's it's an awesome api you can you have there is a ton of of advantages in going into that path of course it's it's a more advanced uh uh way but you can uh refresh that's another big advantage so if you if you are triggering your refreshes using the nasal refresh api that will give you a refresh id that you can cancel it uh if it's running for two hours you can go and cancel it you can do batch and transactional comments batches transactional is you want to want to refresh keep the data on the others and i want to roll back everything yep yeah you have you have parameters for automatic retries so you have um you can apply this free try count if something fails you can say okay try five times and another really really really important parameter you get from the anasazi refresh api is the max parallelism and this can be used to increase the performance so imagine you have a data set with 30 tables you can put the max parallelism up to i believe is up to 30 or 20. uh so this will by default i think is six i'm not sure but you can so you can increase the parallelism uh of the of the refresh so it will if you put ten it will refresh ten tables at the time or ten partitions at the time okay but so this can this can be used to increase the performance but this can also be used and this is also a very common use case to protect your data source because for a very uh let's say a very busy data source or database having six previews or six expensive queries running at the same time can maybe kill the database or because there are other workloads refreshing or there are multiple data sets refreshing at the same time so using the max parallelism and the and then the the an asset refresh api you can say okay i want to refresh my data set i don't care if if it's going to take a few minutes or even an hour but i want to make sure that even if i have 20 tables i won't be eating the database more than two previews at the time so think as a max parallelism as a way to increase the performance but it will also increase the the the the demands on the data source but also to protect your data especially if you are for example using a a a database with uh that doesn't allow too many active connections at the same time so you might be might want to protect your data source using this i um have one question that i wanted to pop up as well is there is there a course or anything uh or documentation a place like that that you'd recommend to kind of get up to speed with service principles keys key vaults uh everything kind of related to a lot of these things uh so on the docks i'm sure that is i'm i'm not sure if i have a new link in here but i'm sure there is some uh some documentation about the service principles on the blogs i can share that later yeah share that with me and i'll make sure to update the the video description everything you've been showing um i've been trying to uh and try to pop those links into the uh chat as well yeah i remember i know that is uh that is some talk with i think is this one use power bi with service principles so that is this blog post and in in here that is certainly uh a reference to the to the docs uh it should be this one that that that walks you through the the what you need to do to create a service space but let me do it and i will show you what you need to do to use this service principle with the apis and uh less than five minutes that'll be great yep and i uh just uh through a couple of the links of what you just showed um so i have a few things done for the people to check out so first thing you need to do is you need to go to your asian followers so porno.com you go to the azure active directory and in here you will find something called app registrations so you click on app registration and click on new and re give it a name it's service principle and that's it for this purpose so if your purpose is to either create a service facebook to use on a powershell script okay and you should take a note of the following the application client id and the directory tenant id so let's copy this into my notepad so app id for the service principal id and the tenant id and they give all sorts of permissions you don't need to do it this will make things insecure so you don't need to do it if you just want to call the api using the service principle so if you want to you use part of your embedded maybe you will need to do it but if if that's not the case if the only thing that you want to do is call an api using the service principle you don't need to give any kind of api permissions you can leave it by default you don't need to do anything else so with that saved let's go i have a script over here called okay microsoft rbi management and let's paste those values here app id and this there is only one step missing but i will i i will get there in a minute excellent okay okay and the secret here it is now the difference is when you have a service principle instead of calling the connect for bi service account that will pop up for the authentication prompt you call the connect for the i service account with a switch parameter called service principle you provide the tenant id and you provide a credential that it's basically the app id and the secret you know in an object call in a bs prevention function and with that let's call it the authentication is that okay i was able to authenticate using that service page now let's try to call the api let's try to call the the the group's api without service principle and this will give me an error and the error is unauthorized is this and this is normally where a non-informed user will go crazy unauthorized i cannot call the api service principle then is given not in the service principle but for that to get that authorization you need to be a power bi admin so you need to go to your power bi portal you need to be a power bi admin to to give that permission so you don't you don't you you may be using a service principle without being a power bi admin but to give that permission to the service basically you need to be a power bi admin so you go to admin portal tenant settings and in here here there will be an api called a tenant setting allow service principles to use the power of the ips this one and and in here you you need to have uh you need to create or or we use a security group where that service principle is inside it so i already have a security group called ppi settings service principles by the way this is one of the best practices i also do so uh is whenever i change uh uh or i wanna i wanna trim down the security on the tenant setting i always create a a security group in this case it's pbi settings service principles if i go to let's say create workspaces i have the tdi settings workspace created try to avoid using the same group for everything because this might lead into a also an insecure uh rpm attendant so for the setting service principles it's a security group i already have if i didn't have a security group i would need to create one so security group is just a laser active directory group let's search for obvious settings service principles and if i add that service principle i created it service principle now i should be good to go if i call this again sometimes this takes a minute to work hints it's still failing but maybe i need to again still giving you an authorized so this normally takes a minute uh oh no it's working there we go okay i said to catch up i no longer i no longer get the unauthorized but i don't get any groups because that service principle is not a member of any group now if i if i go to my power bi portal and let's say to this workspace a big one with big data so this workspace and i give access to that service principle and let's try again there is okay so now now i i got so that serious principle is a member of that workspace it's authorized to call the api uh by the power bi admin and uh and it will just work so and it will get i'm calling the groups api so i get all the groups that my services has access to um so you don't need to uh go to those web api permissions and also just as a recap we created the service principle we didn't change any configuration of the services principle everything is by default and the last uh the last operation using the using that tenant setting the service principle allows service principle to call one to call the party apis and we make sure that the service principle is inside of one of these security groups you could of course as an admin enable this for the entire organization and that extra step won't be needed but my recommendation is especially for this setting is use the have a security group and don't allow every service principal to use the power bi api this is also a best practice to protect your power bi tenant from any service principle to have permission and to call the api and i think just in general when like when giving permissions access or anything else um outside of maybe admins any other uh organization that is it's almost like usually the rule of thumb is to use some type of a security group or office group or something to be applied for these because it reduces the overhead right you know now the office your your o365 admin can can manage those from there and rather than having to continually update the portal so um great great recommendation yeah yeah another one another tenant setting also related to service facebook is this one it's allow service principles to use read-only admin apis and this one is it's to allow a service principal to be an admin in read-only so the service principle will be able to admin apis as an admin they won't be able to change anything because it's only read only and for this i created another security group not not use the same because otherwise if i use the same all the service principles will be will have rights to call the diamond apis and you won't have you want you don't want to do that because this is a much more restrictive uh permission uh i created another one rbi setting service principles admin so and everyone every service principle that is in here can call those uh admin apis a very common use case is using service principles to to get monitoring data so get the scanning of the all your power bi tenants and get all the workspaces all the data sets and the configurations and also uh the activity so what's happening on on on your tenant and this is a very very common use case of service principles as an admin that is with writing a script or a power automator a data factory that will go through these apis and extract the audit log from power bi or or or scan the whole tenant for uh monitoring purposes what i'm uh i'm hoping moving forward is that the uh some of the integrated administration administrative uh reports and tools and everything in power but i will start to leverage those um a bit more because it's like i've seen people build out some really nice uh monitoring solutions for sure activity and usage and and all that other stuff but it it does take some time and love so uh i think your app source type reports uh things like that uh built by microsoft would be nice to see uh going forward where people can plug and play it yeah there are a few things out of the box uh like the capacity metrics app the usage metrics inside of the workspace but to be fair and honest it's not enough uh we do have a backlog and a roadmap to to provide uh all this information to admin without requiring any any effort uh so it's a feature called admin monitoring um but until then um the only if you are if you are a power bi admin in a large organization and you wanna you need to monitor your tenants and you need to know what's happening on your tenant uh you need to rely on these apis and you have to two options so option number one is you can write your own solution or you can reuse one of the uh many open source solutions i i'm showing this this is one uh that i built so i i want to call it all the rvi monitor uh but there are many others so i know that just just plain back i i don't know how to pronounce his name uh from denmark he built one with using using azure data factory uh so mine is using powershell and you can deploy this in azure function uh that will store this data in a block storage and in the end you get a comprehensive rbi report to to monitor your tenants but you are right this will you need to deploy this on your laser subscription or your digital machine if you want to run locally it will give you some some work and you need to manage it and support this uh but honestly it is something that is very very important to to do uh even if you you need to do it by by yourself so if you if you have the skills or you have someone in the team that can call these apis and and store this data do it because it's it will it will be worth it in the end you will get the history of usage of your tenant what's happening and this will also allow you to to get a much better priority into what's happening because maybe you are giving you are supporting a data set or a workspace with a with tons of reports that no one uses and every time you do something you want to do some change you are going there and making making sure that report is still not with the latest layouts and but no one is using that report for more than a year and and of course making sure that you give attention to the right users only on your organization maybe there is a user that is using party every day it's uh he has a ton of good user stories and you don't know know him because it's he's in another country and uh but he's he's maybe the guy that is he's building the most amazing stuff and everyone loves what he builds exactly so looking at this data can make you shift your priorities so it's definitely worth to but i think too it can also like i mean one final take away from that is the power bi does automatically turn off uh refreshes for data sets that don't get used after what do you know the time limit for that is it like 30 days no maybe a little longer like 30 or 60 days but you can build your own yeah you can trigger your own uh instances with this you can basically do you know usage tracking and maybe you actually care to know about that after 15 days if a report has not been accessed for 15 days you can create data off of that to create an alert to you know probably using metrics or something to help you get better telemetry into when you should take action against usage or you know anything related to those so a great example that you showed so another another thing that i want to share my gita uh that is i got a repo called pbi script uh please share it on the shed so this this is a repo it's like a playground for me so whenever i'm working with a customer or a colleague and they they ask me something that i know that i can solve with api i usually instead of sending the script i i usually publish it here on on this repo and i have a ton of them uh i will show one that is related to the monitoring so that that solution that i showed pvi monitor it's a more comprehensive solution you need to deploy it but of course it will handle all of that throttling it will it will be it will scale i can tell you that it works with the workspace with a 100 works a tenant with 100 workspace is 100 000 but a lot of code is in there and it's it's complex uh but let's imagine that you just want to scan your tenant you just wanna okay i wanna i wanna know what exists in my schedule my tenant i wanna get this into a file and do and build my own part of the report but i have a sample here workspace scan that have a few parameters you can see that this is 200 lines of code it's not nothing it's not it's not something very very complex and and most of the most of the code is actually handling the the 429 error that limitation on when you get throttled i can tell you right away that if you have a tenant with more than 50k workspaces you will get throttled less than 50k workspaces you can use the scanner api and you won't get throttled uh so the script is it's basically uh so it has a few parameters of what you want to return if you want to return the dax measures or if you just want to get the workspaces and the data sets and the content of each workspace and when you run this in the end i can actually try to run it see so might it's not a really large tenant it has 200 workspaces but look at the speed that this script runs using the one of the most used apis it's the scanner apis so this script is going to scan my whole tenant extracting for each data set all the tax measures so it just fetched 170 workspaces and it's run in less than less than 20 seconds and in the end i have a json file in here with a scan of with the information of my of the entire tenant of course if if you have a a huge test with like 10 000 or uh 100 000 workspaces this will take a while but it won't take hours especially if you if you go if you go under the 50 000 uh workspaces this will run in a in a matter of minutes and it will scan the whole tenant so you will get the the information for each workspace all the reports the sensitivity labels we will modify i do publish it that's that report or data set the measures the tax measures so the tax code of every measure of every data set on the tenant so you you can get a lot of value from this data so you can enforce naming conventions for example you might have a so you are not you have a large team of rbi developers or you open self service and you get trainings to people to to give proper names to and follow a naming convention and you can have a script that will scan the tenant every day and if you follow and you write some some uh regular expressions over this json file and if you found that there are majors not following your naming convention you can pop up an alert and send the guidelines again to that developer uh one more time ultimate okay so many things that you can do with this uh you can even get the power query code with the scanner apis so scanner api is definitely worse what's a book i think though yeah i mean there's so many possibilities for that i mean i as far as even just like some of the appsource stuff and template apps uh the ability to make things like these they can plug and play in and let users pull the data into their tenants with uh um you know out-of-the-box uh reports that can just connect i think there's a lot of possibilities for people to build out stuff that can be shared and distributed to other people so yeah and i i i i have a ton of things to show you i i i could be here for two hours just yeah exactly this script let me just say let's do one more thing and then we need to wrap up yeah let me show you another of my favorite ones this is the export api so uh the export api is uh it's this api over here so export file in group this works with paginated reports and power vr reports so it is an api that you you need to reference a report so you need to specify the workspace of that report and the id of that report and in the body you can specify some uh some configurations like uh okay what's what's the pages that you wanna you wanna export which what's the identity that you wanna wanna use so you can call the export api with the service principle or or a power bi user that is an admin on the workspace and you you you can run that report using other identity and get the the report as if it was of the user um you can specify the pages you can specify the filter so this can be used like data driven subscription so this is this api and and it's also important to look into the limitations so you have a few limitations considering the premium capacity that you are you are using so uh that is a table over here that will tell you so if you are uh on a p1 you can only execute 55 uh pages for uh in parallel so it's important to take this into consideration but this api allows you to do and it's it's a very simple api to use so i have the script it's on my repo export power vr report so the script has as a parameter the workspace id and the report id and the format or the output format that i want so you have a pdf and you have the powerpoint for power bi reports but if this is a paginated report you can you have excel csv and all the others and and the scripted 55 lines of code so the idea is calling that export to api and waiting because this is an asynchronous api so it's an api that will execute immediately and then it will give you an id and then you can you have another api to check the status of that of that id and when it's succeeded you can get you can get the result now let's run this let's run this all my scripts they ask for the authentication and this will be this just started the the exports of the of this report over here on on on a workspace sometimes the the performance is the first request can take a while but after you make the first request especially for the uh report and data set the next the subsequent will be will be much much faster so in the end here it is export successful let me just show it so i should have on my output folder here it is so i have a pdf without the last timestamp with my rbi report export as a video so you can think of this api to use with scripts or power automate and build your own data driven subscriptions and of course that you need to have premium to use this to use this api but uh but it's a really really useful api and uh i'm sure that it can unblock a ton of scenarios data driven snapshotting reports that's another very useful use case i just want to snapshot my report every single day because i want to see that report how how it was uh last year exactly exact numbers yeah yeah i think i i just i love the snapshot shout out but to be able to basically do the export as pdf that's super useful um and i mean i know they're weren't you involved in that that website that lets you do that the power bi robot or um what was it there's the i was watching yeah okay yeah i was like i'm like it's i'm connecting the dots but i remember yeah i thought you you helped build that or something but it's the website that does the automatic uh basically exporting email um before that even was a thing in power bi in any capacity yeah yeah this one is uh the power bi robots it's a much more comprehensive solution and this is let's say something that you want to do specifically for a report and you want to control everything because using you using the api the advantage that gives you you can control whatever you do you want so you can do you can export it as a pdf or an image and you can build the you can save that image and you can build an email you can store it in a data lake but you need to write all that code to do that but the the the robot was before this api the api basically make this uh this operation uh a lot more easy i'll have uh just a comment i wanted to throw up from that's here it is yeah i can throw that up there he's mentioning auto snapshotting of reporting or measure values is every business uses dreams so very useful thing that you showed right there uh to kind of i think wrap up the the demos for the day but that was fantastic and uh and rafal just just one thing if you want a snapshot and measure i wouldn't use the the export or vr report i would use the x the execute queries uh api because if you if the the goal is i want to snapshot my measure values every day you just need to write the dax query and store that uh that results in a data mic so you don't need actually good to know but yeah really this has been super great and informative i like i said i learned quite a bit today and it's it's always nice to see the flexibility of uh and the the sheer amount of operations that you can do to fetch nearly anything from the administration and management perspective uh automation of exports or even down to the data level of specific uh entities of data that can be extracted from the report so it's i mean at the level and the capacity that the api has today basically can get almost any data that you might need for for any type of um use cases out of the power bi tenant so it's a great tool for sure yeah it was uh thank you it was a pleasure to be here thank you for having me just as a recap avoid so for for everyone so whenever you are doing some repetitive work in power bi regarding operational stuff like publishing rebinding there is probably your api to do it so and it's very useful to have your own collection of reusable scripts i can tell you from experience this can be a great productivity boost absolutely thank you for coming on i'm sure and i would definitely love to have you back on some point in the future to dig into this again as you mentioned you can talk about these things for hours there's so many different things to show so i think this is a this is a great broad coverage of the topic um but yeah thanks for joining on your friday evening and i hope you also enjoy the rest of your weekend as well yeah thank you all right everybody cheers thank you so much for watching please consider hitting that like and subscribe button and if you want to help support this channel take a look at our channel memberships as well and last but not least please consider sharing this video on social media to help pass on this awesome content and to help the channel grow so until next time

Channel: Havens Consulting

Views: 8,398

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Power BI Service

Id: 0HpAJM0Qs7M

Length: 90min 1sec (5401 seconds)

Published: Sat Jul 23 2022