Preparing On-premises Data Gateway Implementation Plans for Enterprises

If you are a Business Intelligence consultant working on Power Platform, Azure Logic Apps and Azure Analysis Services landscape, you probably know that On-premises Data Gateway cab be one of the most essential parts of your engagements with your customers. In many cases, installing On-premises Data Gateway can be a one-man-band job but in many others, it requires teamwork effort. Either way, it can go smoothly if you already have a well-thought implementation plan otherwise, it can quickly turn into a beast that can exhaust the whole implementation team and the customer for some days.

In this post, I do my best to provide you with some guidelines that can help you with your On-premises Data Gateway implementation planning. This post may look rather long, and some of the points are generic, but it is worthwhile mentioning them. Consider the following points before, during and after the engagement:

• Understanding the use cases
• Culture of the engagement
• Environments (Dev, UAT, Prod)
• Communication
• Security
  • Corporate/environmental firewalls
  • Proxy Servers
  • Identity Access Management
• People
• Documentation
• Installation, configuration, and testing

Here is a diagram of the important points that you should consider:

Use cases

You need to understand the use cases of On-premises Data Gateway (Standard Gateway) for your customer. If they need the gateway for their Power Platform, Azure Logic Apps, Azure Analysis Services or all of them. This is important as you either need to have access to your customer’s Power BI Service or Azure Portal or both, or you need to assist your customer to configure On-premises Data Gateway in Azure or in Power BI Service. The next points are:

• Accessing customer’s Azure Portal and/or Power BI Service: The customer to decide whether to create a new account with sufficient rights for you or give you the credentials of an existing account. It is important to make sure you can access all environments and you have necessary rights to install/configure the gateway
• You assist/consult a person at customer side with the implementation: you need to make sure you communicate with that person and see if he/she understands the requirements before the implementation date. Send them a calendar invitation beforehand to make sure he/she is present at that date. Always ask for a backup person just in case of an emergency happening to the primary person.

Culture of the Engagement

There are two ways to implement the gateway:

• Remote Work: If you supposed to get the job done remotely it is important to make sure:
  • Send a calendar invitation to all people involved beforehand and make sure they are present/accessible on implementation date
  • You can access the customer environment remotely
    • You need to have remote connection instructions
  • You have access to the internet from the remote environment to download On-premises Data Gateway
  • You have enough access rights to install On-premises Data Gateway (local admin)
  • You have the customer’s service desk information just in case you face any issues
  • You have contact details of all people at the customer side that must be informed with the progress
• Physical: If you are going on-site
  • Send a calendar invitation to all people involved beforehand and make sure they are present/accessible on implementation date
  • Make sure you understand the customer’s site access protocols
    • You received/signed the customer’s codes of conduct and declarations if any
    • In some cases, a person will escort you when you’re onsite. It’s wise to know your customer’s protocols beforehand not to get surprised
  • Make sure you’ve been explained with health and safety protocols, so in the event of fire you know where to go and what to do
  • Always take notes during the implementation. Your notes will be your best friends if you face any issues during your engagement. They will also become extremely useful for documentation purposes.

Environments

You need to know how many environments your customer expects you to install the On-premises Data Gateway on. Some organisations prefer to keep it as simple as possible and install just one instance of the gateway that can be used by all environments, while some others prefer separate gateways per environment.

It is important to have hardware/software/security available for all environments.

Document a list of available environments like:

• Environment Name (DEV/TEST/UAT/LIVE):
  • Server Name: xxx
  • IP: xxx.xxx.xxx.xxx
  • Service account for On-premises Data Gateway in case there is a different service account per environment

Communication

It is important to have a list of people who are involved with the implementation process, including internal team members in your organisation, relevant people on the customer side and/or any third parties. Having a contact list of relevant people always becomes handy. If something happens you know who you need to talk to. Your list can include the information below:

• Place: Customer, internal team or third party
• Team: BI team, infrastructure team, security team…
• Name
• Position
• Role
• Phone Number
• Email Address
• Risk Level: What are the implications if the person is not available on the expected date. If there are any substantial risks, then ask for a backup person/plan B, so you don’t hold the job when the primary person is not available

Communicate with the relevant people in a timely manner so that they can get prepared before the engagement. Short notice doesn’t work most of the time.

Security

In most cases, you are not the one who takes care of initial security settings. Your customer may have an internal or external/third party security team taking care of their security system, firewall configuration, proxy server and so forth. So, the importance of having a list of all relevant people shows up again. Security can be a bottleneck holding you off if you do not communicate properly with the right people.
The following points are important:

• Setting Up Environment/Corporate Firewalls:
  • Whitelist customer’s Azure Tenant IP addresses
• If there is a proxy server then you need to force https communication with Azure Service Bus; then you may require allowing the Azure Tenant FQDNs
• Make sure either Managed Service Account(s) or Domain Service Account(s) are created. The Service Account(s) will be used for On-premises Data Gateway local service, and they must have access to the internet. Therefore,
  • If the proxy server is in Windows Authentication Mode, then make sure Domain Service Account(s) are created. In that case Managed Service Accounts won’t work.
  • If not, then either Managed or Domain Service Account(s) will work
• The Service Account(s) must have access to all data sources
  • If the customer uses SQL Server Analysis Services (SSAS) then the Service Account must be defined as Server Admin
    • You may need to do user mapping for SSAS. Learn more here
• The physical Server(s) or VM(s) to host the On-premises Data Gateway must have 24/7 access to the internet (or the whitelisted FQDNs and Power BI Service)
• Having an On-premises Data Gateway owner account handy. The gateway owner is specified when the gateway is initially installed on the corporate server. The email account input during installation will be marked as the gateway owner and will be specified as the contact in the Power BI Service.
   

• The account must be a “Power BI Service Administrator”. This can be setup from either “Office365 Admin Portal”, from “Azure Active Directory” or through a PowerShell script. Learn more here.
• Make sure that corporate firewall does not overwrite proxy server settings
• Make sure TLS 1.2 is available on the server which hosts the gateway
• If there is a corporate SSO (Single Sign On) identity access management like OKTA or Active Directory Federation Services (ADFS) then make sure all accounts including the new accounts may have been made for you to access the environments and all service accounts are synchronised
• IE 11: The older versions of IE won’t work with Power BI Service properly
• Make sure TLS and SSL protocols are enabled in Internet Options
• The servers (environments) are accessible from the cloud and hasn’t been blocked by a corporate firewall.

To do so the following PowerShell command should be run on the server:

Test-NetConnection -ComputerName watchdog.servicebus.windows.net -Port 9350

The result should look like below:

ComputerName      : watchdog.servicebus.windows.net
RemoteAddress      : 70.37.104.240
RemotePort             : 5672
InterfaceAlias          : vEthernet (Broadcom NetXtreme Gigabit Ethernet - Virtual Switch)
SourceAddress          : 10.120.60.105
PingSucceeded          : False
PingReplyDetails (RTT) : 0 ms
TcpTestSucceeded       : True

NOTE: Make sure “TcpTestSucceeded” is “True”.

People

You may need to interact with the following people before, during and after the engagement:

• Your internal BI team members
• Internal project manager(s): Some organisations assign a project manager to every single engagement
• Customer BI team members: In majority of cases the customer BI team requested the job
• Customer project manager
• Customer change management team: In some cases, you may need to raise a change request. The change management team will review and approve the changes.
• Customer IT team: To provide you laptop, internet access etc…
• Customer infrastructure team: To setup the VMs/servers
• Customer security team: Firewall configuration, Proxy server configuration, data source access rights
• Customer Office 365 team: To manage Power BI Service accounts, Power BI Admin accounts
• Customer SharePoint team: To setup SharePoint access
• Customer Azure Admins: To manage Power BI Service accounts, Power BI Admin accounts
• Customer DBAs: To setup access rights in database level (i.e.: SQL Server, Oracle), semantic layer (i.e.: SSAS)
• Customer Power BI developers: To test the gateway
• Customer service desk: To raise the issues

Documenting the Implementation Plan

Believe it or not, documentation is one of the most important parts of the engagement. The following points are vital to include in the documentation includes especially in teamwork engagements:

• The step-by-step implementation plans
• Remote access instructions
• Customer site access protocols, codes etc…
• Domain Service Account(s) and password
• Power BI Service Admin Pro account and password
• People’s contact info (the internal team, customer team(s), third party teams)
• Hardware (physical/virtual)
  • An existing server OR
  • A VM that meets the requirements mentioned here
• Software
  • The version of On-premises Data Gateway (download the latest version from here )

Ask your teammates to peer review the implementation plan internally before providing it to the customer for final review.

Installation, Configuration and Testing

Now that all the prerequisites are sorted it is time to install On-premises Data Gateway. I’m not going to explain how to install and configure the gateway as it is well explained in the below resources.

• Installing On-premises Data Gateway
• On-premises Data Gateway Resource for Azure Analysis Services
• On-premises Data Gateway for Logic Apps
• Connect to On-premises Data Gateway from Azure Logic Apps

What is important to know is that you know:

• You need to install/configure the “On-premises Data Gateway” Windows application
• You need to install/configure an “Azure Gateway Resource”
• You need to configure the gateway and all data sources in Power BI Service
• You need to test several scenarios

Here are some test scenarios:

• Power BI: Use Power BI Desktop to create the scenarios below, then publish the reports to Power BI Service and schedule data refresh:
  • SQL Server data source:
    • Connect to and import data from SQL Server DB
    • Connect to and DirectQuery to SQL Server DB
  • SSAS (Tabular/Multidimensional)
    • Connect Live to SSAS instance
  • File system:
    • Use local Excel files or CSV files
    • Import from a Folder
  • SharePoint: Create a report on top of your SharePoint resources
  • Merge/Append functions in Power Query: Mashup data from different data sources, then use “Merge” and “Append” queries from different sources and make sure your data refresh in Power BI Service doesn’t fail
    • Try Merge/Append two different local data sources, like SQL Server and Excel
    • Try Merge/Append one local data source and one online data source, like Excel and SharePoint Online
• Azure Analysis Services: After connected the Azure Analysis Services to the Azure Gateway Resource, process one table in Analysis Services
• Azure Logic Apps: After connected the Azure Logic Apps to the Azure Gateway Resource, create and run a simple data pipeline

I am sure that you have some other test scenarios than the preceding ones that you can share with us in the comments section.

Considerations

• If DirectQuery is allowed to be used and Kerberos exists, then it must be tested precisely when creating Data Sources in Power BI Service (Cloud).
• Download and install the latest version of Power BI Desktop. This is only needed to create some test cases.
• Always do your homework before starting the job. Read the following articles before starting the implementation. It is wise to send the links below to customer BI team to have a better understanding of On-premises Data Gateway. It is recommended to send the below links to the customer’s internal/third party security team as well.
  • How On-premises Data Gateway works
  • Troubleshooting the On-premises data gateway
  • Configuring proxy settings for the On-premises data gateway: As mentioned earlier, in most of cases you are not responsible for configuring the proxy settings. But it is an informative read anyway.

If you think I missed anything, please share let me know in the comments section below.