After some request by the community and after I publish in my blog as a season of blog posts, Step by step configuration to publish BizTalk operational data on Power BI is available as a whitepaper!
Recently, the Microsoft Product team released a first feature pack for BizTalk Server 2016 (only available for Enterprise and Developer edition). This whitepaper will help you understand how to install and configure one of the new features of BizTalk Server 2016:
- Leverage operational data – View operational data from anywhere and with any device using Power BI, works and how we can configure it.
What to expect about Step by step configuration to publish BizTalk operational data on Power BI
This whitepaper will give a step-by-step explanation of what component or tools you need to install and configure to enable BizTalk operational data to be published in a Power BI report.
Table of Contents
- About the Author
- What is Operational Data?
- System Requirements to Enable BizTalk Server 2016 Operational Data
- Step-by-step Configuration to Enable BizTalk Server 2016 Operational Data Feed
- First step: Install Microsoft Power BI Desktop
- Second step: Enable operational data feed
- Third step: Use the BizTalk Server Operational Data Power BI template to publish the report to Power BI
- Fourth step: Connect Power BI BizTalkOperationalData dataset with your on-premise BizTalk environment
Where I can download it
You can download the whitepaper here:
I would like to take this opportunity also to say thanks to my amazing team of BizTalk360 for the proofreading and for once again join forces with me to publish for free another white paper.
I hope you enjoy reading this paper and any comments or suggestions are welcome.
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.
To do that we need to:
- Run the downloaded .EXE file – PowerBIGatewayInstall.exe – from your hard drive.
- In the “Security Warning” screen, click “Run”
- In the “Start your on-premises data gateway installation” screen, click “Next”
- · 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.
- In the “Reminder before you install” screen, click “Next”
- 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”
- 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”
- Finally, the last step is to configure the Gateway settings by defining a Gateway name and recovery key. To finalize the configuration, click “Configure”
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”.
- You can add a new data source by selecting a gateway and click “…” (3 dots) button and then click “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:
- 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.
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”
- On our BizTalkOperationalData dataset, click “…” (3 dots) button and then select “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”.
And now you will be able to successfully refresh manual your dataset or specify a scheduling refresh mechanism.
Following the sequence of my last post: BizTalk Server 2016 Feature Pack 1: How to publish BizTalk operational data on Power BI – Step-by-step configuration (Part 1), here we will see how we can enable BizTalk operational data feed and how we can use the BizTalk Server Operational Data Power BI template provided by Microsoft to easily publish the report to Power BI.
Step-by-step Configuration to Enable BizTalk Server 2016 Operational Data Feed
Second step: Enable BizTalk operational data feed
To enable BizTalk Server 2016 operational data feed, you should:
- Run Windows PowerShell as Administrator by:
- Press the “Windows key” to open the Start menu and type “PowerShell”
- Right click on “Windows PowerShell” option and select “Run as administrator”.
- Browse to the BizTalk Server 2016 is installation folder, normally:
- C:Program Files (x86)Microsoft BizTalk Server 2016
- And run the following command. Be sure to update your website, domainuser, password, and domaingroup with your values:
- Service: The service to be configured: “Management” or “OperationalData”.
- WebSiteName: The existing Web Site where the service will be configured. The default value is “Default Web Site”
- ApplicationPool: The Application Pool to be used by the service. If exists, will not be created. The default value is “DefaultAppPool”
- ApplicationPoolUser: Configures the application pool to run as this user identity. Must have BizTalk Server Operator or higher privileges.
- ApplicationPoolUserPassword: Password for the ApplicationPoolUser
- AuthorizationAccount: List of authorized Groups or Users to use this service
FeaturePack.ConfigureServices.ps1 -Service operationaldata -WebSiteName ‘<Default Web Site>’ -ApplicationPool <operationalDataServiceAppPool> -ApplicationPoolUser <domain><user> -ApplicationPoolUserPassword <password> -AuthorizationRoles ‘<domain><group>, <domain><user>’
If everything go well, after you run the script, if you browse the new IIS Application by:
- Open your web browser
- Go to http://localhost/BizTalkOperationalDataService
You will see a JSON file – BizTalkOperationDataService.json – being download.
Third step: Use the BizTalk Server Operational Data Power BI template to publish the report to Power BI
To access the Power BI Template file, and use the provided visualization from Microsoft, use the following steps:
- Browse to “OperationalDataService” folder under the BizTalk Server 2016 is installation folder, normally:
- C:Program Files (x86)Microsoft BizTalk Server 2016OperationalDataService
- And double click to open the “BizTalkOperationalData.pbit” file.
- When prompted from Power BI Desktop, paste the http://<BizTalkServer>/BizTalkOperationalDataService URL that you created for your OData feed.
- Normally: http://localhost/BizTalkOperationalDataService
- And then click “Load” to populate the fields in your Power BI report.
The Template file automatically generates the information and tables available from the OData feed.
The operational data is exposed through the computer and can be accessed and executed by other applications based on permissions.
Publishing your reports to the Power BI service is fast and easy. Once the data have been load you need to:
- Save the file by selecting the menu “File” and click “Save”
- And then select the “Publish” button on the Home tab in Power BI Desktop, and the process begins.
- The first step, of course, is to provide your Power BI account
- After you authenticate on Power BI, a list of destinations that you have access will be presented so you can select the destination to where you want to publish BizTalk Operational Data report.
- Select your destination, in my case, “My workspace”
- and then click “Select”
- The then the publishing process will start
- Once the upload is complete, a dialog box tells you the publishing process succeeded and a link is provided in that dialog to take you directly to your report in the Power BI service, in a web browser.
- If you access to PowerBI.com you will see that you will have BizTalk Operational Data (BizTalk Operational Data report) report available.
And if you click on it, you will be able to see the data that was load and publish by the Power BI desktop.
But the most important question is: Are this data being updated?
And the answer is: No. For that, you will need additional steps.