For only 10 bucks a month you can start visualizing your data and finally get the dashboard your executives have been waiting for!! What’s that? You need to refresh the data–perhaps even multiple times a day? Oh, well it’s still $10 a month, but you are also going to install a gateway. What’s that you say? Well, in this episode we begin our discussion on Power BI with one of the first components you will need outside of the Power BI Desktop for your users to start interacting with updated data. We discuss what it is, some of the requirements, and our experiences setting one up.
Power BI is so easy to use, you would think that installing/configuring/using a Data Gateway would be easy. It is, if you answer some questions before you get started.
Are you mixing your data sources?
Are you using more than one type of data in your dataset? For example, are you using Excel spreadsheets and SQL Server? Or CSV files and Oracle? If so, you will be in for a big surprise. Currently, only homogeneous datasets are supported in the Data Gateway. If you have heterogeneous datasets (Excel and SQL Server), you will need to use the Data Gateway in Personal Mode. Now, I say currently, but Microsoft is constantly adding new features and improving upon existing features so this may change in the future (no guarantee though) so keep checking.
Where to install your Data Gateway?
Because the Data Gateway acts kind of like an operator, just answering calls and passing them through to the requested data source, you need make sure that you install the Data Gateway as close to your data source as possible to reduce network latency. You don’t want to have your data going through multiple hops to get back to the Data Gateway. Oh, and don’t plan on installing your Data Gateway on a domain controller, because it’s not supported and won’t work.
Are you going through a firewall?
I know this seems like a silly question, of course you’re going through a firewall to get to on-premises data, because you are all security conscious data people. More than likely your firewall will be fairly locked down, which means you will need to open some ports. Luckily they are all outbound ports, so your security administrator shouldn’t give you too much grief in opening them up. In addition to the ports, you will need to whitelist some IP addresses. But keep in mind that Microsoft is constantly adding new data centers and your list of IP addresses can change (the list is updated weekly). This means you will most likely need to automate the retrieval of the IP addresses from Microsoft and apply them to your firewall.
Do you need to use HTTPS instead of direct TCP?
If you are going to be using HTTPS, there’s good news and bad news. Bad news is that you will most likely encounter slower performance. The good news is that the gateway will be strictly using FQDN instead of IP addresses. So instead of having to keep up with all the constantly changing IP addresses for your firewall whitelist, you can simple supply the list of FQDNs to your firewall administrator and no automation will be necessary.
Are you okay with using a local account for the Gateway Service?
When you install the Data Gateway, by default it is installed under a local account that is created by the install process. This account will be granted “log on a service” permissions, so if you’ve got System Administrators that don’t like having lots of local accounts, then you can use a domain account instead.
What account should you use to register the data gateway in Power BI?
While I know that most users are super excited to start accessing on-premises data, you must resist the temptation to register your Data Gateway under a specific user’s account in the Power BI service. What happens if that person goes on vacation or worse yet, leaves the company? You are better off creating a domain account that will act as a service account for registering the Data Gateway. Just make sure that the service account is set up to receive email as Microsoft will send a verification email when the account is first registered with Power BI.
Answering these questions can save you a lot of time down the road. This is not all inclusive list of questions that you need to ask prior to installing/configuring/using a Data Gateway, but they should get you off to a good start.
“Data Gateway is definitely a very good resource for being able to pull data from different types of data sources.”
“With the Data Gateway, it’s basically just looking to see what’s your data source, do we have the credentials, what reports are mapped to it, and it just seems to work.”
“The ideal scenario in any circumstance is that the Gateway is as close to the data as possible without impacting the server, so unless you’re running a tiny workload, you probably don’t want to run the Gateway right on the SQL Server.”
Listen to Learn
02:07 Compañero Shout-Outs
03:34 What Have I Learned
06:40 SQL Server in the News
15:06 Intro to the topic
17:04 Installation…it’s easy, until it’s difficult
18:37 There are two different types of Data Gateways
21:22 Compare and contrast Data Gateway with SSIS and its multiple services
24:50 Data Gateway needs quite a bit of space
27:12 Two possible scenarios where you might have more than one Gateway
28:59 There are three ways to implement row level security
30:48 What if you’re not accessing Microsoft data sources?
33:14 Query folding in Power Query might be a challenge
34:21 Closing Thoughts and how to connect with the panelists
Music for SQL Server in the News by Mansardian
Meet the Hosts
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.