With data in so many different locations, it can be a struggle to connect all of your data sources for reporting. This is especially true when it comes to on-prem data. You may have data silos in remote locations, older databases or applications where it would be too challenging to move the data into the cloud or off-prem. Thanks to the Power BI gateway, you don’t have to sacrifice this investment.
In this on-demand webinar, we show you how to use the Power BI gateway as a pathway between your on-prem data and your Microsoft cloud tools. We cover
- What the Power BI gateway is and how it works
- How to install the gateway on a local machine
- Configuring your Power BI service to use the gateway
Trainer and Consultant
Patrick is one of our most popular instructors, regularly receiving high marks from students for his subject matter knowledge, clarity of communication and ability to infuse fun into classwork. Patrick has over 20 years of experience in data science, business intelligence and data analytics and is fluent across multiple BI platforms. He is a Tableau Certified Associate and well versed in Power BI. An expert in Cognos, his product experience goes back to version 6. He has extensive experience in Actuate, Hyperion and Business Objects. Patrick is certified in Java, Python, C++, Microsoft SQL.Read more
Welcome to today’s webinar.
Today, we’re going to be discussing Power BI gateway: understanding installing and configuring. now some of you who’ve been to our webinars before, hello. Patty, I see you out there!
You’re not used to hearing my voice right out of the gate.
They actually let me do this one all on my own today.
Somebody’s going to regret that decision. I’m sure, so.
Let’s get this ball rolling, shall we. Please feel free to use the control panel.
We’re easily able to respond to your questions while the webinar’s in progress.
If we don’t reply immediately, we’ll cover it in the Q&A section at the end or via a written response that will post on senturus.com
I see a lot of you still coming in, so I’m going to slow my talk down just a little bit.
And everybody else who’s out there, welcome to it.
The very first question we get is, Hey, can I get a copy of this presentation? No, presentation for you.
Of course, you can.
It’ll be available on Senturus.com, go to the Resources tab, and then Knowledge Center, or you can click the link that I just posted in the GoToWebinar control panel. Look at that, it’s all there, It’s right there in that chat window.
Find the chat window, open it up, and you’ll get a beautiful copy of this.
While you’re there, we obviously have great stuff on our resources page, so please take advantage of the resources page.
And now, our agenda, various quick introductions, since it’s just me, that makes it much easier, don’t have to do a lot of introduction.
We’re going to start out by understanding what the gateway is, why would you use it, what do you use it for, who is it for.
Then I’m going to install it.
I’m going to show you an installation, and I’m going to show you how to configure it.
Hey, I’ve got a virtual machine set up already to go, hopefully, enough stuff as prep that it’ll go nice and smooth.
Then, we’ll do an overview and additional resources, because, Hey, what’s the Senturus webinar without the Senturus marketing stuff, I know that’s what you all really come for.
You all come just to see those marketing slides at the end, given time, we will do live Q&A, and, again, those of you who’ve attended before, you know that we try to get the questions answered in real time, whenever possible.
I do my best, folks, I do try.
And, speaking of that, who is this strange person talking to you?
This is me, Patrick Powers, Data Scientists, Consultant, Trainer, Senturus Inc. 25 years of experience doing business intelligence data analytics. I deliver classes on Power BI, Tableau and Cognos. I’m certified in multiple languages Cognos Tableau, Java, C ++, and database certified in SQL Server, and I’m just straight up certifiable.
I’m going to keep you all amused today.
So, let’s get into it now that everybody’s here.
Let’s talk about the on-premise gateway.
What is it? What are we doing with it?
So it acts as a bridge, and it’s a bridge to give us a way to access that data that’s on prem and different cloud services that are sitting out there.
So, whether it’s Power BI, Power Apps, Power Automate, AAS, ALA, we can use all of this on-prem data, but let’s be realistic. What are you really going to use this for?
This is for that database that’s sitting out in your warehouse out in rural no-where, that nobody is ever going to get into the cloud.
This is for that app, and those files that are sitting on that HR person’s desk, this is for that project that we created 22 years ago, and it’s still out there being used and you need that data.
So the gateway, gives us away to get into all of our data, not just the fancy, modern cloud stuff that we’ve spent years getting nice and right now.
Even that horrible data, OK.
So that data that is out there any cloud service that it can connect to now, as far as leveraging up for Snowflake, which is a question that just came in from there. I don’t have an answer to that the cloud services that I’m aware of. Our Power BI, Power Apps, Power Automate, AAS and ALA.
If somebody else has an answer to that, sure, they’ll tell me.
How does it work?
So here, again, these are the cloud services that are listed.
But again, SQL Server, SSAS, Oracle.
Thank you, Tina. So there we go. We got a real live question. Real live answer, it seems that Tina is actually using it to connect to Snowflake and it’s working well. I have seen most users use it for getting to those random data sources. Those data sources that aren’t otherwise normally accessible through the Power BI service, through these other things, whether it’s files, whether it’s SharePoint, other data sources, come on.
We aren’t doing a poll in this one, if you noticed, but I’m going to throw us out there.
How many of you do have a data source sitting around that you wish you could report on that sitting under Phill’s desk? At least a good portion of you are shaking your heads yes right now.
So what does it do?
The flow cloud service creates query.
It includes encrypted credentials for the on-prem data source.
The query and the credentials get sent to the gateway queue.
The cloud, the gateway cloud service analyzes, the query, pushes that back to the Azure service bus.
The bus sends the request to the gateway, Both the gateway and the service are implemented to only accept TLS 1.2.
So we don’t have to worry about security. We don’t have to freak out that this is going to be sending unencrypted plain text data.
The gateway gets the query, decrypt it.
Connects to those data sources, sends the query, runs it, Back, it goes.
Service then uses the results.
In the final step, a Power BI refresh that can return a lot of data.
So here’s something to think about When you start to plan this out, sometimes you can get a lot of data back.
So for these kinds of queries, the data is temporarily stored on the gateway machine.
And it keeps going until it’s able to send everything back. Because, remember, how you’re using this. Over the last decade or so we’ve all gotten very spoiled.
I think in the sense that we expect that you’re going to have a good internet connection every time, every place.
You all know what I mean by that, you go to the mall, and you expect to have the internet, You go to the airport, and you expect to have Internet you’re 50,000 feet in the air. And you expect to have Internet.
So remember, if you’re connecting to an on-prem that is at a rural location, or is that one of your overseas places, you got to take that in to consideration. It’s only going to be able to send back what it can send back it as fast as it can send it back.
Now this process is known as spooling and to help improve this, now I’m going to go over minimum system requirements in a few minutes.
But understand that one of the things they recommend is the machine you use for the gateway has an SSD drive on it.
So that way, this spooling can be sped up and to go at least as fast as it can.
Because I don’t want that go away.
We don’t want it to also suffer from a lag from the hardware we’re using.
So again, I’m going to go into this in a minute, but just keep that in the back of your mind, when you’re starting to plan this out and set it up.
So speaking of where does the gateway need to be installed, needs to be installed on a server, they can communicate with the on premise data source machine.
So if we’re talking about connecting to a file, and that file is on somebody’s desktop, that’s, a dangerous way to go in some cases, because we’ve got to make sure that machine stays on, that machine is up and running that they don’t log it out or take it on vacation with them.
Why can’t I get to this dataset anymore?
Oh, crap, Donnas on vacation, right? We don’t want that.
So the gateway gets installed on a machine that has access to the data source and I would encourage you that those data sources be on a network share.
If it’s a case of file or SharePoint or on some type of machine that is easily accessible by the gateway machine.
Think about that for a second.
I really want to get to that Excel file.
Yeah. But what if that Excel files in the Bahamas?
On vacation without you?
The on-prem AD account must match the Azure AD account because of the VPN.
So when the gateway is configured, we’re going to be logging into the gateway and it needs to match the same AAD account that you good login to the Power BI service with.
Going to be able to talk back and forth. It’s going to be able to talk.
Doesn’t matter if you add an account in your on-prem, if it doesn’t exist in the Azure AD, can’t be used.
It only knows about accounts within the Azure AD.
There are three types of gateways.
The on-prem data gateway, the on-prem data gateway personal mode, and the virtual network data gateway which is currently in preview.
So things could change, Things could be different.
We’re going to recommend number one, the difference between number one and number two.
Number one, allows multiple users to connect to multiple on prem data sources.
Single installation can point to multiple data sources.
Well suited where you’ve got multiple people accessing multiple things.
The personal mode is one user till one data source can’t be shared, can only be used with Power BI. Nothing else in the Power Apps Suite.
If you are the only one writing reports, if you are the only one who connects to a data source, if you are the only one who’s doing this stuff, maybe you can consider personal mode.
I don’t recommend it.
We, Senturus, don’t recommend it, we recommend using the actual standard mode on premise data gateway.
So the virtual, multiple users, multiple data sources that are secured by virtual networks, there’s no installation with this because it’s a managed service by Microsoft.
If you have real, complex scenarios, real situations with multiple people, multiple data sources, something worth looking into.
Again, it’s still in preview.
So I’m going to focus on one.
Hopefully everybody’s doing well.
It’s a quick handy, dandy chart for you.
Everybody knows we like our handy dandy charts.
As you can see, the standard mode is really what you want. Standard mode works with all of the cloud services, multiple users.
You can do imports and schedule refreshes. It also has direct query support.
That’s a big one.
If you don’t want to do an import on this, you can do a direct query, as long as you’re using standard mode. Same thing with a live connection to analysis services.
Can’t do that with personal mode.
What about my minimum requirements?
You say, first off, where can you get this? You can get it directly from the Power BI service.
I’ll show you that when I start the demo, again, I’ve stressed it already.
Choose standard over personal.
Here are: the minimum recommended requirements: eight core CPU, eight gig of memory, Windows Server 2012, R2, or later.
Add an SSD drive to spool this.
Now the gateway is not supported on server core installations, not supported in Windows containers.
There are some issues are not issues, but things you want to consider when you install this.
The user installing it must be the admin of the gateway.
You also cannot put it on a domain controller.
If you’re going to use Windows authentication, you going to make sure you install it on a computer that’s a member of the same Active Directory as the data sources.
I’m going to mention again, don’t put it on a machine that can go to sleep, be disconnected, taken on vacation, etc.
Also, you can install other applications on the sound, the gateway machine, You’re going to see mine, I’m doing this on a hyper V, I’ve got my on-prem and the gateway on the same machine.
But, just like everything else, it can degrade performance.
Stop trying to do so much on a single machine.
I know, who you are.
I see you out there trying to put everything on that one machine you found in the back room.
If you do install this on the same machine as other things, monitor it closely.
See, if there’s any resource contention and see if there’s any problems now, you can actually install two gateways on the same machine, one personal one standard.
So you can do that, that’s, but you can’t have more than one gateway in the same mode on the same machine, Just things to think about.
When we install it, we’ll get a little app, and we use that app to configure and manage it.
Here we can see the status, current settings.
We can run diagnostics, set recovery, keys, all sorts, fun stuff.
When you install it, you are by default an admin of that gateway.
Back in the service, you can add more users, rename it, test it, do all sorts of fun stuff.
And from that same box we add our data source so we can add a data source from the gateway.
There are some ways to troubleshoot and to monitor this one of the things that Microsoft stresses is make sure you’re updated to the latest version. You can have gateway clusters.
And when you start moving into gateway clusters, you want to make sure that all members in the cluster are in sync. Otherwise, you’re going to start having problems. You could have unexpected refresh failures.
Because you might have a gateway member that a specific query is routed to, that it might not be able to do it, because it’s a lower version.
So when you start moving into clusters, keep track of this, make sure that every gateway installation is the same version. Make sure everything’s good. Now, I have.
I downloaded, my install file, previously, will see if a new version is out. Since I downloaded it, it will tell me if there is a new version available while once the installation is complete.
And it’s going to very, forcefully recommend that I upgrade to make sure it’s a newer version.
There are common errors and solutions on the Microsoft site.
If you download this presentation, which the link is in the chat window, you’ll be able to use this link and you’ll be able to go to it.
You can also get logs from the gateway app.
You can see what’s going on.
You can export logs out of here, you can do network port tests, all sorts of stuff.
What do you say? I actually show you this darn thing.
I know, you know, we sold you an entire seat, but you’re only going to need the edge.
As I said, I’ve got the sauna hyper V going’.
First things first, where can you download this from? You can download it from Microsoft site.
I’m also going to put this in the chat window for you, or, as I said before, you can download it straight from your Power BI service.
Look at that.
Guess where this takes you? If you click on it, Oh, look, it takes you here.
So, once you’ve downloaded it, we start our installation.
Now, I’m going to, actually do the installation while we’re going live here.
I see all the you’re not paying attention symbols anymore. You’re like, well, I’m not going to watch you install something, but you never know.
It could be exciting, it could fail.
It’s like watching NASCAR, you know. You don’t watch NASCAR to watch cars go around in circles. You’ll watch it to see things explode.
You know I’m speaking the truth here.
I’m looking at the question window, and Penny, thank you. We do way too.
Many have random skunk work servers.
I can say everybody’s got at least one. I don’t care who you are. I don’t care how great you think you are. There’s at least one data source that, yeah.
You don’t know about it until you know about it and it’s when Phil retires.
And all of a sudden something stops working and everybody realized that Phil was maintaining a database under his desk for 20 years.
So if you’re going to go virtual, Paddy. So the question was, Could the gateway be on a virtual server on prem or in Azure virtual server? I’m actually going to do it on a virtual so, it can be on a virtual box.
But, remember, think about the impact of that I wouldn’t necessarily use this box that I’m using in a production environment OK.
I’m doing Windows 10 with 16 gig and avert.
So, you going to take that in consideration.
We’re almost there, folks. I promise you.
Let’s see if there’s any new, when it saw it, you know?
More on, that is a very good question. I actually don’t know the answer to that. Steve, if you’re still around, maybe you know the answer or anybody else is still around to that when you do this.
The question is, when you do a cluster, does each machine have a different gateway name or is it sharing the same name?
And if you want to throw the answer either in the chat window or in the question window, that’d be great.
I do have everything else prepped.
And so while we’re waiting for that finish up, let me show you the server.
So, as I mentioned, I do have a SQL Server installed on the same machine, OK, We’re going to be using this same SQL Server right here.
I make sure I get my name written down. It’s desktop.
So we’re going to use this, and you will see, Pattie that I’ll be able to connect up to this virtual machine.
So, I’m on. I did get a response that the other person who’s helping me out in the background questions, they’re not sure either.
So, well, we’ll look into that and we’ll answer it in the Q&A that we post up on the Senturus website, if we don’t get an answer before it’s done.
I’ve got worldwide importers.
Everybody’s favorite right up there with Adventure Works, that’s fancy.
Yeah, but there’s my database, there’s my tables.
Click tables, and I’m going to connect up to these.
So the answer that we’re getting when admin selects Manage Gateways in Power BI. You’ll see a list of registered clusters or individual gateways but you don’t see the individual gateway instances of the cluster. So it looks like the cluster has a say as a single name for the gateways in the cluster.
While that’s doing its thing, come back in here.
I’m going to show you, we manage our gateway’s from here.
So now, this is the one that I had set up for this which was taken sweep time.
But here, I would name it.
I would set my data source up, see, I’ve got my data source set to SQL Server, That’s my server name.
We’re using basic authentication on this one.
This is the contact information that says department.
Once this is configured, I use it like any other data source.
So, if I look at my datasets, if I go to my workspace and I look at my datasets, I can have a dataset.
That’s already set up and that is already set up to be connected to the gateway and I can do a refresh on it.
I can connect to this and all I would do is publish this out.
Publish out a report.
Now, this has been done an import.
So I can build a report from it. I can create a report from scratch.
So let’s go ahead and let’s create a report from scratch here.
And there are the same tables.
But you see back here, sales customer, sales customer, sales invoices, etc.
There they are.
They’re all set up and ready to go, and now I can report from them.
I want to be able to show you the setup, and I honestly don’t know why this isn’t finishing.
So let’s see if we’ve got any new questions.
Ah, can be leveraged to pull the information of the data source in the gateway server name. Now you’re going to have to, have that information Lisa. So the question was, can I pull the information in the gateway? I am going to need to tell it that information, so that’s why I had to pay attention to what my server name was here, and I will! Some point, I really need to change the server name.
Because, if I don’t write it down, I forget it.
But, you need to know the credentials. You need to know the server name. You need to know the database name.
It’s a matter of fact, when I was doing this the other day, I was typing in worldwide and borders, and I couldn’t get it to connect, and it was going nuts, thinking I had something wrong in the password. Now, I just typed the wrong name.
And so, Penny, the gateway could be on a virtual server on-prem, and is it better to use that?
I don’t know.
I go back and forth on that because I’m old.
And, honestly, sometimes, a standalone machine with nothing else on it, that’s a little bit smaller, can be a little bit better than a big, giant virtual machine that’s got a half a dozen other things on it, right?
It’s all a matter of what you’ve got available to it. But remember, those minimum requirements, server, with eight gig.
That’s all my questions.
So, as you can see, I’m going to login with my Azure account I login to Power BI with.
When I do an installation, I have the option of registering a new gateway or I can migrate restore take over an existing gateway.
I could do anything I want with it.
So I’m going to register a new one.
I’m going to give it a name.
Now I could add it to an existing cluster and I going to come up with a key.
Basically a password for this, so if I do need to restore the gateway.
Don’t do some ridiculous, auto generated, silly thing. Make it something that people can actually remember.
Yeah, make sure they match, and that you’re not being an idiot.
Oh, wait that was my local one wasn’t it?
So the gateway is online and ready to be used.
I can send use information to Microsoft if I so desire.
I think we all know how we feel about that.
I can create a gateway in Azure if I wanted to but I am ready to use it with Power Apps, Power Automate Power BI.
I can take a look at my service settings where I could restart it.
I could use a new gateway account. Here’s where the additional logging is, the gateway logs my port tests.
My network settings.
So I can use HTTPS or direct TCP?
Here’s the connector, and there’s that recovery key, so that’s, once that’s there.
I’m apparently local. I’m going to come up here, Manage Gateways.
And there’s my new gateway.
So now I can remove this old one, if I wanted to. I could add a data source, this old one.
So if I’m going to add a data source.
Tell what type. Look at all those types.
Matter of fact, they’re Snowflake.
I’m going to connect it to.
I would give it a server name.
Don’t think I have my server running on here.
Start the local SQL Server.
Now, I’ve got a local SQL Server up and running.
I can tell I go to my local machine.
We’re going to go.
Maybe you guys are getting some deep technical questions there.
So I do have some local databases. A guy Adventure works 2019. I’ve going to go, Sales. I’ve got a Finance one. So, let’s do the Adventure Works 2019.
And now I’m connected to that data source.
And I can use that data source for reporting.
So much easier when I’m showing your reporting type stuff. So now, once that’s set up, I can go to the Users tab. I can add some other users.
So if I wanted to add one of my co-workers, I could easily add my co-worker to be part of this, and now they can also use it And they can build reports And they can go and I’m connected to my local database.
I’m connected to this local adventure works database.
And I can run a report.
And again, I had one setup, this one right here setup.
So it’s already been published, and you can see that if I want to go to the data hub.
I can use this gateway right here.
And I can build a report. I can also schedule a refresh for this.
So if I wanted to refresh it directly or if I wanted to schedule a refresh under here I have to specify the gateway.
OK, so you see that, I have to specify which gateway it is.
I have to specify the data server and then I can schedule this as a refresh and notice.
I’m just having a rough day today May your install go smoother than mine, and you see Lisa see why you need to have everything written down and everything setup beforehand.
See as again, once you get it set up get those usernames, get those passwords configured, get everything going.
You’ll be able to use it just like any other one, you can set up refreshes. You can set up schedules for it.
There are a few questions that have come through. I’m going to come back to those in a second.
Let me go back to this.
Look, things, you just saw me have an issue with today.
Plan this out.
Make sure you’ve got all those names. Make sure you’ve got all the data servers correct. Make sure you’ve got everything located. All the security. Security is going to be the hardest part. I do see a question about asking if you can configure it to CyberArk.
I don’t know if you can configure it to CyberArk OK.
Penny, when you do a refresh, it’s going back out to that source. Remember, I did not set it up for direct query. I set it up for import, so it did a one-time pull of the data when I published it.
If the data changes, I need to do a refresh.
And Sarkar, once the gateway is set up, you can see the name under the Manage Gateways.
Basically the contact information is the name that this gateway was set up under.
You see it under both there.
So this is the account it’s using. But that’s still not my SQL Server or login. And that’s what apparently is giving me trouble today is my actual login here.
When you manage the gateway, that’s showing us what account it was logged in was.
OK, did I get everything that hasn’t been answered already?
What if we installed the gateway, as well as the Azure integration runtimes that’s got an answer? Thank you, Bob.
But do, make sure you plan this out.
Make sure you’re keeping it up to date.
And for finding troubleshooting, I will tell you that in my experience, the biggest thing I’ve had to troubleshoot is exactly what you saw me struggle with today. Making sure that I’ve got all of the right passwords, all the right server names, all of that stuff.
And that can be real tricky when it’s an older, outdated, different service that, you know, somebody hasn’t used, and you going to make sure that it’s all current ended up to date.
We do have some new stuff I want to talk about.
We do have a new Power BI Administration and Best Practices class.
It is a one day course, what we customize for each organization. We match your environment, and it does include the gateway.
So we go through this as a group where everything’s already pre-configured and where there’s no password problems, and everybody’s happy.
By the way, thank you all for your patience during that. I know that it’s never fun watching somebody struggle, so thank you.
You can also check out our full Power BI course catalog at Senturus.com training courses, Power BI. If you want to do more with Administration, we do have a new class. It’s Best practices, goes into working with datasets, setting up Workspaces.
It covers all sorts of great stuff.
Just from taking a tour of this service, setting up a workspace, creating a workspace, working with data sets, the gateway, integrating with teams.
Little bit about Senturus, we provide hundreds of free resources on our website. We’ve been committed to sharing all this for, over a decade. We’ve had all this stuff out there.
Check out our Knowledge Center under the resources tab where you’ll also find things like product demos, viewpoints, technical tips.
All sorts of things. You can also go to our YouTube channel, where you’ll find videos, you’ll find live, streams, recordings. You’ll find all sorts of fun stuff.
If you’re want to hear more of my voice, some upcoming things, on May 12th, we will have a Cognos Analytics performance tuning event.
On May 26th, we will have a virtual booth at a virtual conference, the Zero Gravity Conference by Incorta. Modern Cloud Data Pipeline Event.
Go to the Senturus website for more information.
I’ll be doing Power Automate for Power BI on May 19.
There’ll be a webinar on How to integrate Power, Automate, which some of you may remember is Microsoft Flow, how to integrate that into a Power BI visualization. It will be doing data alerts and sending emails and things like that from a Power BI workbook.
For those of you who are new to Senturus. We concentrate on BI modernizations and migrations across the entire stack.
We provide all sorts of services training in Power BI, Cognos, Tableau.
We also have proprietary software to accelerate bimodal BI and migrations. We really shine in Hybrid BI environments.
Some of you may have come to one of our webinars recently, where I did demos in all three products, Power BI, Tableau, and Cognos all in the same webinar.
So, we do have experience in all of the products.
We’ve been doing this a long time, over 20 years.
Thousands of clients, thousands of products. Look at all these cool people, we’ve helped some of you are even on here today. Hello, we see you.
I’m sure you’ll recognize some of these, but, look, we’re big enough to be able to handle customers like this, but we’re small enough to give you a personal attention.
We can work with you. We can customize to your needs.
If you’re interested in joining us, we are looking for a Senior Microsoft BI Consultant and a Senior Data Warehouse Cognos BI Consultant.
Job descriptions are on Senturus.com under careers and you can send your resumes to [email protected] just think you’d be able to hear my voice on a regular basis if you worked with us.
Let’s take one final look at the QA.
Bob and Steve thank you for a chipping in there and answering some of the questions.
Penny, thank you. I hope I’m glad you have fun.
Please, come on back, too, Additional webinars, and go to our website, and if you want to know more, contact us at [email protected] We’re going to be here to help you, no matter what your needs.
I’ll give it a minute, see if any additional questions come through.
So thank you all for coming today, there is our phone number for those of you who still use phones, and, again, you can get us at [email protected]