BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 3)

To close this sequence of posts about BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration, in this post we will see how we can connect Power BI BizTalkOperationalData dataset with our on-premise BizTalk environment.

Step-by-step Configuration to Enable BizTalk Server 2016 Operational Data Feed

Fourth step: Connect Power BI BizTalkOperationalData dataset with your on-premise BizTalk environment

The final step is to install and configure a Power BI Gateway to connect your on-premise BizTalk Server environment.

With the On-premises data gateway, you can keep your data fresh by connecting to your on-premises data sources without the need to move the data. Because it installs and runs on your computer, it is best to install it on a machine that can be left running all the time.

Here are a few things to consider before installing the gateway.

  • If you are installing on a laptop, and your laptop is turned off, not connected to the internet, or asleep the gateway won’t work and the data in the cloud service will not be synchronized with your on-premises data.
  • If your machine is connected to a wireless network, the gateway may perform more slowly which will cause it to take longer to synchronize the data in the cloud service with your on-premises data.

(source: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/)

To do that we need to:

BizTalk operational data: Power BI Gateway download

  • Run the downloaded .EXE file – PowerBIGatewayInstall.exe – from your hard drive.
  • In the “Security Warning” screen, click “Run”

BizTalk operational data: Power BI Gateway Install

  • In the “Start your on-premises data gateway installation” screen, click “Next”

BizTalk operational data: Power BI Gateway Install Welcome

  • · In the “Choose the type of gateway you need” screen, select “On-premises date gateway (recommended)” and then click “Next”
    • On-premises data gateway: Multiple users can share and reuse a gateway in this mode. This gateway can be used by Power BI, PowerApps, Flow or Logic Apps. For Power BI, this includes support for both schedule refresh and DirectQuery
    • Personal: This is for Power BI only and can be used as an individual without any administrator configuration. This can only be used for on-demand refresh and schedule refresh. This will launch the installation of the personal gateway.

BizTalk operational data: Power BI Gateway Install type of gateway

  • In the “Reminder before you install” screen, click “Next”

BizTalk operational data: Power BI Gateway Install warning

  • In the “Getting ready to install the on-premises data gateway” screen, review or change the installation path, select the option “I accept the terms of use and privacy statement” and then click “Install”

BizTalk operational data: Power BI Gateway Getting ready

  • If the install is successful, then the “Almost done” screen will appear. The next step is to sign into your Power BI account to finish the configuration of the gateway. Enter your email account and then click “Sign in”

BizTalk operational data: Power BI Gateway Almost done

  • The next step is to specify that we want to register a new gateway on our environment.
    • Select the option “Register a new gateway on this computer” and then click “Next”

BizTalk operational data: Power BI Gateway Register new gateway

  • Finally, the last step is to configure the Gateway settings by defining a Gateway name and recovery key. To finalize the configuration, click “Configure”

BizTalk operational data: Power BI Gateway Configure gateway

When we click configure, the Gateway is confirmed and added to the PowerBI setup for our account.

The only thing the is missing to finalize the process is to add a data source to the gateway that we just created previously on https://app.powerbi.com portal. For that we need to:

  • Login in https://app.powerbi.com portal
  • Select the gear icon in the upper-right corner and then click “Manage gateways”.

BizTalk operational data: Power BI manage gateways

  • You can add a new data source by selecting a gateway and click “…” (3 dots) button and then click “Add data source”.

BizTalk operational data: Power BI manage gateways Add Data Source

  • In the “Data Source Setting” panel set the following configuration:
    • Data Source Name: set a name for your data source, example: BizTalkOperationalDataSource
    • Data Source Type: From the Data Source Type list, you must select “OData”.
    • URL: set the URL that we defined on the “BizTalkOperationalData.pbit” template:
      • http://localhost/BizTalkOperationalDataService
    • Authentication Method: This can either be Windows or Basic. You would want to choose Basic if you are going to use SQL Authentication instead of Windows Authentication. Then enter the credentials that will be used for this data source.

BizTalk operational data: Power BI manage gateways Add Data Source information

  • And then click “Add”.

Now let’s associate this gateway and data source with our BizTalkOperationalData dataset. To do that you need to:

  • Select your Workspace, in my case “My workspace” and then select the tab “dataset”

BizTalk operational data: Power BI BizTalkOperationalData dataset

  • On our BizTalkOperationalData dataset, click “…” (3 dots) button and then select “Settings”

BizTalk operational data: Power BI BizTalkOperationalData dataset settings

  • On our BizTalkOperationalData dataset, expand the tab “gateway connection”. There you will be able to already see a gateway available under “Use a data gateway”.
    • Select the option “Use a data gateway” and click “Apply”.

BizTalk operational data: Power BI BizTalkOperationalData dataset use data gateway

And now you will be able to successfully refresh manual your dataset or specify a scheduling refresh mechanism.

BizTalk operational data: Power BI BizTalkOperationalData dataset refresh

Related Links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

  • Alex

    If I get it right, the Gateway is a service that runs locally and provides access to the OData feed. Essentially it’s a proxy. Why does it need to be attached to a specific PowerBI account? What if I would like to provide access to the same feed to multiple PowerBI accounts?

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top