Get started with the Power BI Gateway

yo Adam Sachs and gynae Cuban in this video we are gonna look at the power bi gateway what it is and how you can get started with it and we're starting right now if you're finding us for the first time be sure to hit that subscribe button to stay up to date with all the videos from both Patrick and myself all right the power bi gateway formerly known as the on-premises data gateway this is a way that you can actually connect to data sources that are sitting on premises as the Gateway would indicate so the idea here is that alright I've got a sequel server maybe it's an excel file or something that isn't in the cloud per se so if you're using something like Azure sequel database or after data breaks or maybe you've got an online SharePoint list or just a web page out there you don't necessarily need a gateway to do that the whole point of the gateways to get to resources that power bi generally can't connect to when it's on-premises it doesn't know anything about it but this on promises data gateway can be used for more than just power bi so let's take a quick look at that this can be used not only with power bi but also Azure analysis services power apps power automate and as your logic app so all of those go through you can use that one single installed gateway to be used with all of these services so it's not just power bi in itself so I mentioned that you have to install that power bi gateway or the on-premise data gateway it's gotta go on a machine somewhere right on Windows machine and so some of the questions that we end up hearing is alright well where does it need to go and how close do I need to get it to the actual server that I'm trying to connect to or the resource that I'm trying to connect to and the whole idea here the whole point of that is to reduce latency reduce network latency right we want to get it we want to get the Gateway as close to the data source as possible and I kid around with people but I say like look how close can you get it to a sequel server it's about 6 inches that's about how close you can get a kid a kid what you really want to do though is just from a network perspective want to get it as close as you can you could try installing it on your actual physical sequel server itself your DBA is probably not gonna like you if you're actually trying to connect to a sequel server the other option is to get a machine that's in the same subnet or same network region as that given item let's take a look closer look at this so when we think about placing the Gateway one thing to consider is where's the power bi region for your tenant and in my case for my tenant it is west us so that's sitting on the west coast so if I have an actual on-premises server database server that's sitting in California then when it tries to go and connect to this resource the latency should be pretty good everything's on the west coast so that's about as close as I can get without moving my data center or my database to the cloud so the other thing to consider here is what if I have my database server sitting in Australia but my tenant is actually in West US in this case you're gonna have really high latency which is going to take the time of your refreshes and direct queries and really make them last a little longer so this is something you really need to pay attention to let's take a look at another option here and this is if my database is actually in the cloud and so maybe I've got an azure VM where I've got a sequel server or maybe it's an Oracle server sitting in that Azure VM and I spin up another VM for the Gateway that's sitting in West US as well if everything is contained in West US then you've eliminated the Internet from this equation so you've gotten rid of all that Layton sees everything is sitting with inside of the West US data center should be pretty fast so again we don't want to troubleshoot Network latency if it's slow I want to be able to to my database or my actual data source not have to worry about that network latency so that's something to keep in mind as we go through this okay so with that being said we've got to go download and install that gateway so what I'm gonna do is let's switch over to my computer and actually take a look so I've got a report here it's connecting to a couple different data sources a CSV file a sequel server power bi data flow and a web page so it's trying to hit all the check boxes in this so I'm gonna go over I told you not to do this or your DBA won't like you but for this purposes I'm actually going to install the Gateway on my database server so the way that you can get the download for the Gateway is go to power bi you'll see this little download area up in the top here and we can come down and choose data gateway that will actually download the actual resource onto your local machine we can hit download gateway yep and it will go and download that so I've already done this on my database server so let's go over there real quick we'll go and install it yeah we're next alright so the first choice you're gonna have to make here is that you can choose the default the recommended is the enterprise mode of the on-premises data gateway this is the one that can be used with all the services I mentioned before it supports scheduled refresh it also supports direct query and also live connects to analysis services so this is something you want to use typically if you're gonna go in a production environment this is the option you're going to use if you're just testing things and you're kicking the tires or you know something of that nature and then what you are going to use is the personal mode of the gateway itself right so I can install this this is actually set up on my main machine and I can use this for refresh if I need to for testing it's limited only to power bi and also you can only do like normal refresh of import model so you can't do direct query can't do live Connect so be aware of that alright so I'm gonna go with the enterprise one we're gonna it next I am alright you can set the path that you want to install it to I'm going to say accept all right yes yes all right once that's done we have to supply an email address so this is an account we're gonna bind this gateway to our tenant so it can't be used with more than one tenant you may if you do have multiple tenant you're working with you off to install different gateways for each well let's sign in alright you're gonna have a couple options here one is to restore or register a new gateway or we can migrate restore takeover and exist Gateway we'll talk about that in a later video for now we're just gonna register a new gateway all right we'll give it a name another option we have here is the ability to add to an existing gateway cluster again the it's something to be aware of that a gateway cluster can have multiple physical gateways assigned to it if we're just creating a new gateway it will create a new gateway cluster we'll go into that more detail in a later video and then we have to give it a recovery key please please please remember this recovery key you will thank me later on down the road figure it BAM it's ready to go I can use it with all these other services now the other thing we can do here is this is the unprecedented gateway dialog you have on the machine where it's installed couple of things we can do here one is that we can actually change the service account for the Gateway itself so this is really useful if you're trying to get through a proxy and you need to use a domain user account to do that this is where you can go ahead and change that there's also some diagnostic items here so we can export the Gateway logs and then we can also update some Network items as well as configure for custom connectors those are all options available to us all right cool so with that let's head back over to the power bi service we need to set up a few more things but first let's jump over to where the data set is we're gonna datasets here I want to set up schedule refresh now when we come into gateway connections we'll see here that use the gateways off and then I see personal gateway which is green and then I also see my contoso gateway which is not configured correctly right and so there are some other items I have to do here if I expand that a little bit you'll see the different data sources that we have and you'll see my CSV and sequel server these are the two I need to configure the other option this power bi option is actually my data flow connector so it knows how to get the power bi and that's not a problem and then my web is just out on the internet and I'm gonna use anonymous access for that so not a big deal so to configure these we have to go up to the gear icon and we have to click manage gateways and here we're gonna see our Gateway itself there is additional out options here that you can configure we'll talk about this in a later video you can assign other administrators to this gateway instead of just yourself and then we can add a data source in one of two ways up top here we can see add data source and then also if you look really closely you can see here that this ellipse there isn't ellipse if you hover over it so you can select that and then say add data source this is also where you can remove gateway let's go ahead and hit that and we're gonna add a new data source it's called sequel hopefully you give it a more descriptive name come down a sequel so one thing you need to be careful of here is the server name in the database these have to match what you actually supplied inside a power bi desktop the string exactly server important so maybe you put the IP address into power bi desktop but you want to use the regular name here or vice-versa these have to be identical if you use the IP address and power bi desktop you have to use the IP address here on the Gateway configuration that's how it doesn't match up between them the users are also important but before we do that let's go and hit add says we're good to go on the users tab you want to make sure anyone that's gonna use this data source of the gateways from a publishing perspective is listed here the consumers of the report do not need to be listed but the actual users that are publishing and taking advantage that own a data set and setting up schedule refresh they need to be listed here alright cool so we need to add one more data source here let's go and do that we'll call it CSV and we'll choose file type we need to get the path so one thing to be aware of of this file path is that this is in the context of the Gateway itself so I put in a C colon backslash 1/2 here the Gateway this needs to be on the C Drive of the Gateway machine if this is how I'm specifying it so if the so if the file is on my local machine but the Gateway is on a different machine this path would give me an error and so you would need to use like a UNC network path as part of that so like Wack Wack server name wack whatever ever that path is so everything there let's go ahead and add that says we're connected we're good to go don't forget the users tab all right back over to our workspace let's goes the data set go back in the schedule refresh we'll say yep we're gonna use the Gateway that's on and now we can see that these items are available so for file and sequel server what I'm going to do is choose those items that I set up and then we'll hit apply and now though for those two data sources they're gonna use the Gateway with the other data sources I have to specify those credentials so for pbi I'm just gonna come in and hit sign in yep my account and then for the web server it's gonna be anonymous sign in good to go all right cool so now we can come back and we can do refresh now and this will go ahead and set up the refresh and it looks like it completed let's go back and schedule refresh we'll look at refresh history and we can see here that it succeeded or it succeeded in refreshing which means it actually went out to my sequel server and the CSV file pulled that through from the Gateway and everything worked as it should which is amazing rad so that's a quick look of how you can use the Gateway what it is and why you would use it hopefully this was helpful to you we're gonna continue this gateway series with a couple more videos to go into more of the details about how certain things work like SSO and those types of things so stay tuned on those also I've got a free PDF that you can download down in the description below that just has some highlights and things I'm gonna call it a cube sheet that looks at some of the Gateway items that you can just have as a thank you I don't want to pass this off to you what do you think did this help you get started with the Gateway did you maybe learn something that you didn't know let me know down in the comments below if you like this video be sure to hit that big thumbs up button smash it if you so desire if it's your first time here hit that subscribe button and as always from both Patrick and myself thank you so much for watching keep being awesome and we'll see you in the next video

Channel: Guy in a Cube

Views: 80,611

Rating: 4.9689922 out of 5

Keywords: power bi gateway, power bi gateway cluster, power bi gateway configuration, power bi gateway explained, power bi gateway on premise, power bi gateway requirements, power bi gateway setup, power bi gateway tutorial, what is power bi gateway, power bi, power bi data gateway, power bi for beginners, power bi on premise gateway, power bi on premises data gateway, power bi tutorial for beginners, power platform, on-premises data gateway, business intelligence, gateway

Id: Vlv1tbuZNcM

Length: 13min 18sec (798 seconds)

Published: Thu Mar 05 2020