If you are a Business Intelligence consultant working in Power Platform, Azure Logic Apps and Azure Analysis Services landscape, you probably felt that On-premises Data Gateway is one of the essential parts of your engagements with the your customers. Installing On-premises Data Gateway can go smoothly if you already have a well thought implementation plan otherwise, it can quickly turn to a beast if you don’t have one. In this post I do my best to provide you some guidelines that can help you with your On-premises Data Gateway implementation planning. Consider the following points before, during and after the engagement:
- Understanding Usage
- Culture of Engagement
- Environments (with all peopleinvolved)
- Corporate/environmental firewalls
- Proxy Servers
- Identity Access Management
- Documentation/Implementation Plan
- Installation, Configuration and Testing
Here is a diagram of important point that you should consider:
You need to understand the use of On-premises Data 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
- 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 very useful for documentation purposes.
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
- 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
It is important to have a list people who are involved with the implementation process, including internal team members in your organisation, relevant people at 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…
- Phone Number
- Email Address
- Risk Level: What are the implications if the person is not available on the expected date. If it is high risk, then ask for a backup person/plan B, so you don’t hold the job when the primary person is not available
- Is Secondary/backup: Yes/No option
Communicate with relevant people in a timely manner so that they can get prepared before the engagement. Short noticing doesn’t work most of the time.
In majority of 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 all firewall configuration, proxy server settings
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:
- If there is a proxy server then you need to force https communication with Azure Service Bus. This means Azure Tenant FQDNs listed here must be whitelisted
- 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
- You may need to do user mapping for SSAS. Learn more here
- If the customer uses SQL Server Analysis Services (SSAS) then the Service Account must be defined as Server Admin
- The physical Server(s) or VM(s) to host the On-premises Data Gateway must have 24/7 access to the internet (or the white listed FQDNs and Power BI Service)
- Having 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 : 18.104.22.168 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”.
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
Believe it or not, documentation is one of the most important parts of your engagement. Doing all the steps above properly you can now prepare an implementation plan. The documentation includes but not limited to the following:
- Your implementation plan
- Remote access instructions
- Customer site access protocols, codes etc…
- Domain Service Account(s) and password
- Power BI Service Admin Pro account and password
- Contact info of all involved people (internal team, customer team(s), third party teams)
- An existing server OR
- A VM box meeting the requirements mentioned here
- The version of On-premises Data Gateway (Latest version can be downloaded from here )
Ask your team mates 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 possible scenarios
Here are some test scenarios, for sure you may need more test scenarios than the ones listed below:
- 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
- Connect to and import data from SQL Server DB
- Connect to and DirectQuery to SQL Server DB
- 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
- SQL Server data source:
- 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
- If DirectQuery is allowed to be used and Kerberos exists, then it must be considered to 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 good to read through this link to have a better understanding.
If you think I missed anything please share let me know in the comment section below.