Get started with the On-Premises Data Gateway in Microsoft Fabric

[0:00]Yo, Adam Saxton with Guy in a Cube and in this video, I want to look at how you can get started with the on-premises data gateway for Microsoft Fabric including Power BI. Before we get into all of it though, 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. Why would you use an on-premises data gateway? The first obvious reason is because you've got data that's on premises, meaning it's on a machine, maybe it's in your office. Maybe it's in a data center within your organization, but it's not what we would say in the cloud or in Azure, in something that's publicly accessible from the internet perspective. A great example of this is an on-premises SQL server, so I have a SQL server installed on a machine, and it's not Azure SQL database, this is actually SQL server. So like SQL Server 2022 and I want to access that data and leverage that data inside of my reports inside of Power BI or within inside of Microsoft Fabric and I need a gateway to be able to connect to that. Okay, the gateway itself is usable across more than just one thing. I already mentioned this is usable within inside of Microsoft Fabric, which includes Power BI, and it also includes things like pipelines, data flows, shortcuts. Other things that can leverage the on-premises data gateway include things like Azure analysis services, some items from the power platform, so thing Power apps, power automate, you got Azure logic apps. So there's a wide variety of things that can actually leverage this one gateway. So let's look at how to do all of it and where to go from here. So enough all this talking, you know we like to do it here in guy in a cube, let's do what? Let's head over to my machine. All right, I'm inside of workspace inside of Microsoft Fabric and I've got a report and data set uploaded. This data set includes data from an on-premise SQL server as well as a CSV file. And so let's go see what is going on here. If we go down to settings, we come to gateway and cloud connections, you'll see these are gateways that are configured within inside of my Microsoft Fabric tenant. None of these are actually usable right now for various reasons, but if I expand one of these, you can actually see the connections that are actually listed in this data set itself or semantic model. This is really the only way you can do it. Now, what's interesting is this file path, this local files actually showing as green on this gateway because that data source was configured on that gateway itself, but we're going to ignore that for right now and then this local SQL server is not configured. It's inviting you to actually go configure that. So in order to really do something here, let's step back and let's pretend we don't have any gateways and let's go ahead and create a gateway. So let's go back to home. Up on the top here you'll see this download item and we can download data gateway. When I do that, it'll take me to a page and I can optionally download the standard mode or the personal mode. Standard mode is also referred to sometimes as the enterprise gateway. This is typically the gateway you're going to go use, especially for a production scenario. The personal gateway, that is something that allows you to just do something on your own, so you can refresh things, like if you wanted to install it on your laptop or just a personal machine. Understand that the personal gateway can only be used with Power BI. So that's not going to work with the other items inside of Microsoft Fabric, it's also not going to work with the other services that are out there like Azure analysis services, power apps, power automate, so typically just go with the standard gateway and that's what you're going to use. All right, so I've got it downloaded, so let's go ahead and install it. All right, we'll accept, we'll install. And then once the install's done, it's going to bring you into the configuration screen. And so this is where you'll need to sign in to the Microsoft Fabric service in order to configure this gateway. Once you've signed in, then we can go ahead and we've got two options, we can register a new gateway or we can migrate, restore, or take over an existing gateway. Not going to get into migration or restore in this video. I'll do a separate video for that. For the purposes of this video, we're just going to create a new gateway. All right, so we've got register a new gateway on this computer selected, we're going to hit next. So first what we want to do is give this gateway a name. This also gives you the option of adding this to an existing gateway cluster. Again, I'll save that for another video, but the idea here is a cluster is a collection of physical gateways. And so we're installing a physical gateway, like it's being installed on a machine. And so you could have multiple gateways on different machines combined into a gateway cluster. And when you define data sources and what not, those are all assigned to the cluster, not a specific physical gateway, so just be aware that that can exist. For the purposes of this video, we're just dealing with a cluster and a single physical gateway. If you're just installing for the first time and you don't check that add to an existing cluster, this install will create a cluster along with the physical node. All right, and then we need to give it a recovery key. Be sure you remember this recovery key, so this comes into if you want to migrate or restore, you're going to need this key, so please, please, please be aware of it. And we're going to go ahead and configure it. And when we're configuring, what this is actually doing is it's talking to the service, and it's actually registering that in the service so that you can now use it with inside of Microsoft Fabric and those other services. So it's doing all the internet things right now to get it all wired up and set up and register this physical machine locally with the fabric services as a whole. And bam, once it's done, then we will see, look, it can be used with all of these items, which is amazing. And it gives you the current gateway version. This is the May 2024 version, you know, it's effectively ready to go at this point. Another thing to note is the gateways get released every month, so be sure to go update that when it comes out. I recommend not being more than three months behind. Also know that there is not an automatic way to update the gateway. If that is something that interests you, go to ideas.fabric.microsoft.com to vote that up. I know it's out there. Some things to look at in this configurator as well. So first thing is the service settings, and so by default it's just using NT service account. So this is like a Windows service that's installed on the machine. This is something you may want to change, especially if you're inside of a big organization. Typically they have proxy rules and things that require authentication, and so I've seen a lot of cases where like the gateway can't establish outbound connections because your proxy is denying it. What you want to do is assign an actual domain username to the gateway, that way it can authenticate to the proxy, that's the easiest way to get around this. So diagnostics are useful if you want to try and debug an issue, this will extract all of it to a zip file. You can also do some network port tests. HTTPS mode is enabled by default. If you want to do anything with like custom data connectors that's there, and then your recovery key, you can set a new recovery key if you need it. All right, so our gateway is set up, so now what we want to do is go into the service. So a couple of things here, if we click on the gear, you can come down to manage gateways and connections, and one thing you'll see here is the actual connections themselves. It'll also tell you what cluster it's a part of and then you can go to on-premise data gateways and you'll see various items here. We can see the one that we just installed, gateway demo one. Now, one thing I want to do is rename this gateway to just gateway demo, so let's see if we can do that. We can go to settings, give it a name, we'll just call it gateway demo. If you want to allow certain capabilities with this gateway for people to use the personal cloud connections or custom data connectors, those are items you can enable as well and we'll go ahead and enable those for this. Okay, so we've got our gateway demo and if we look across here, we'll see that it's only got one gateway in the cluster and if we go to status. can see that everything is online and ready to go and that's great. If we go to info, you can actually see what the physical gateways are and you can enable or disable those physical gateways. This user section are the users that are admins of the gateway, so these are people that can actually manage the gateway, add data sources, things of that nature. All right, so let's go back to our data set and if we go into the data set itself, let's go into settings, come down to gateway and cloud connections. This is where we started, right? And now we'll see our gateway demo is listed here now that we just configured, so we'll go through and we've got to add this to the gateway. So we'll say add to gateway, it's going to pre-populate information, which is great. And it knows that it's a SQL server, so we'll call this Adventure Works-SQLVM. This is the server, this is the database, authentication can going to be basic. Let's do Gia reader, ignore my last pass stuff. You've got some options here also for if you want to do single sign on via Kerberos and or Azure AD stuff, we're going to not worry about that right now. So this should be okay and good to go. One important thing, this is in the context of the physical gateway. So that physical gateway on a Windows machine somewhere has to be able to talk to that data source from a network connectivity perspective, it's got to be able to reach it. And so you can't just put this on any machine because there may be other firewalls or restrictions in place. In this specific case, this SQL server is actually sitting in a VM in Azure. But the gateway's on my physical machine, can I talk to it? So that depends on how you have the server set up. So if you do are dealing with like a Vnet, which is a reason why you would want to use a gateway as well from a network connectivity perspective, you'd want to have a VM in the Vnet where you install the gateway on, and so then that could talk to the SQL server that's in the Vnet as well. And you want to get it as close to the data source as possible. So in terms of placement of the gateway, we need to know where that data source is located, where is my fabric tenant located, and or capacity. And so my home region for guy in a cube is West US, and so I want to make sure those data sources are hopefully aligned to West US as well, and more importantly that gateway is also in the region closest to the data source. All right, so let's go ahead and create this, see if it works. Bam, it did. Excellent. So now we'll can map it to that connection that we made. Next one, we're going to add for that file path, we're going to create this as well. It's going to be a Windows connection. Believe this works. Bam, that worked. Now we can see gateway demo, this maps to SQL, this maps to sub category, everything's good. We're set up. Let's hit apply. And then if we go back to our data set, we can hit refresh. Mr. Spinny's going. And then bam, it's successfully refreshed, everything is good, no errors. So this actually refreshed from on premises. One other thing I want to show you here is we can leverage shortcuts and we can leverage a gateway with a given shortcut. As of the recording of this video, what's supported is an Amazon S3 bucket, an S3 compatible bucket and or a Google Cloud storage account. So let's take a look and see what that looks like. So I'm going to go into my lake house and from a table perspective, I know that I've got something in my Amazon S3 bucket that I just want to light up. I've got the park Delta files there, so let's go ahead and say new shortcut and I'll say Amazon S3, and we got to give it a URL, right? So that's my storage account, we're going to say create a new connection, my connection name is the URL and my data gateway that I'm going to use is my gateway demo. And then we've got to provide an access key for that S3 bucket. We'll hit next, and it's going to list out what's there if I just click on internet sales. It's looking at the parquet files that are inside of that Amazon S3 bucket. I'm going to choose my internet sales folder, hit next, and then we'll create the shortcut, and then we've got our shortcutted table coming right off of an Amazon S3 bucket if I hit internet sales. Bam, we've got our internet sales data coming off of parquet files, sitting in an Amazon S3 bucket. Oh my gosh. And this is going through the gateway that I configured, and so if your Amazon resources or your Google resources are behind a VPC of some kind or Vnet type option, and you've got to go through your corporate network, you can now leverage a gateway to go get that data and represent it inside of fabric. Let me know what you think if you've got questions around gateways or whatever, drop them in the comments below, we'd love to hear it. As always, thank you so much for watching. Keep being awesome, and we'll see you in the next video.