Transfer data to Microsoft Power BI und Microsoft Fabric with the OPC Router
Transfer data to Microsoft Power BI und Microsoft Fabric with the OPC Router
In this step-by-step tutorial we will show you how to transfer data to Microsoft Power BI with the OPC Router. This enables you to visualize data from a wide variety of sources within your company (e.g. databases or OPC servers) with Power BI.
Even better: When you transfer data to Power BI, it will be saved in the new OneLake data hub and thus be available within the Microsoft Fabric infrastructure. This means that you can also process the shared data with all other applications that are a part of Fabric.
What is Power BI?
Power BI is a business intelligence solution by Microsoft. With Power BI you’ll be able to visualize business data in the form of custom reports and dashboards and share them with other people in your company.
In addition, Power BI is integrated with Microsoft Fabric, a new data analytics platform from Microsoft that is currently in the preview phase. Power BI uses the OneLake data hub, which is also accessed by all other applications that are part of Microsoft Fabric.
What is Microsoft Fabric?
Microsoft Fabric is a new “all-in-one” data analytics solution by Microsoft.
Fabric combines a wide variety of data-related services and functionalities in a single piece of software, including data engineering, data science, real-time analytics and Business Intelligence (Power BI). The goal of Microsoft Fabric is to allow different data specialists to access all data in a central location and process it using different Microsoft services.
To do this, all data is pulled from a central repository – called OneLake. Data stored in the OneLake is accessible to all Fabric applications.
If you connect Power BI to the OPC Router as described below, you will be able to store any data in the OneLake. Prerequisite is an access to Power BI and the installation of the OPC Router (version 4 or higher).
Step-by-Step Tutorial – Data Connection to Microsoft Power BI and Microsoft Fabric
The following tutorial on how to transfer data to Power BI and Microsoft Fabric with the OPC Router is divided into two sections:
Configuring the API access within Power BI
1. Log in to Power BI (https://app.powerbi.com).
2. Click on „My workspace“ in the sidebar.
Click on „New“ and select „Streaming dataset“.
3. Select „API“ and proceed.
4. Choose any name for the streaming data set.
Next, configure the data fields and data types for the data to be transferred to Power BI.
Activate „Historic data analysis“ and click on „Create“.
5. Copy the Push URL as well as the JSON Code. You will need both for the next steps. Then, click on „Done“.
The copied Push URL will look similar to the example below:
https://api.powerbi.com/beta/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/rows?experience=power-bi&key=Y7FiThHsLEM%2B7vbni6BSDRdgnVcdBYz9b2gTjDSnswFZkBk%2BybnTiSr6Wp7wZzZf64KI4ZnxLsh48Aq2KDUwrg%3D%3D
For the configuration of the connection within the OPC Router you will need the following parts of the URL:
Base URL: https://api.powerbi.com/beta/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Endpoint: /rows
Query Parameters:
– experience: power-bi
– key: Y7FiThHsLEM%2B7vbni6BSDRdgnVcdBYz9b2gTjDSnswFZkBk%2BybnTiSr6Wp7wZzZf64KI4ZnxLsh48Aq2KDUwrg%3D%3D
Before proceeding, the key parameter has to be decoded in order to resolve certain special characters. This can be done using an online tool such as urldecoder.org and pasting the string into the upper area (1). Then click the „DECODE“ button (2) and copy the result from the area below (3) which will be the key parameter needed in the OPC Router.
The decoded key parameter in our example looks like this:
Y7FiThHsLEM+7vbni6BSDRdgnVcdBYz9b2gTjDSnswFZkBk+ybnTiSr6Wp7wZzZf64KI4ZnxLsh48Aq2KDUwrg==
Configuration of the connection with the OPC Router
1. In the OPC Router, click on Plug-ins (1) and select „REST“ (2). Then, click on the Plus icon (3) to create a new REST connection.
Choose any name and copy the Base URL from Power BI into the „Host-URL“ field. Click on „OK“.
2. Create a new REST Transfer Object.
Click on Transfer Objects in the left sidebar (1) and then on the left arrow symbol under „Connections“ (2). Drag a REST Transfer Object from the right section into the middle area (3).
3. Double-click on the REST Transfer Object to open the configuration window.
Enter /rows in the „URL-Endpoint:“ field (1).
Under „Parameter:“ (2), fill in the Query Parameters from the copied Push URL.
In our example we have two parameters called „experience“ and „key“ with the values „power-bi“ and „Y7FiThHsLEM+7vbni6BSDRdgnVcdBYz9b2gTjDSnswFZkBk+ybnTiSr6Wp7wZzZf64KI4ZnxLsh48Aq2KDUwrg==“. The parameter type will be „QueryString“ in both cases.
Set the „Method:“ to POST (3).
4. Create a „JSON Write“ Transfer Object and double-click it to go into editing mode.
Paste the copied JSON (1) and click on „Edit“ (2).
5. Use the „fx+“ button (1) or the upwards-pointing arrow (2), to create data fields for the various elements and confirm by clicking on „OK“.
6. Connect the „Json“ line of the JSON Write Transfer Object to the upper „Body (Text)“ line of the REST Transfer Object with an arrow via Drag-and-Drop (see screenshot).
Connect the JSON Write Transfer Object to a data source such as an OPC Server (see screenshot).
Next, click on the second icon from the bottom within the left sidebar. Here you can set the created connection as active.
7. You are now able to create customized reports using Power BI. The following screenshot shows an example for a real-time data visualization.
More information
Find other exciting topics here:
The OPC Router enables a smooth and secure communication via REST. With the help of its REST Plug-in, the OPC Router is not only able to address other systems through their REST API but can also be used to implement a REST API for a connected system using REST triggers. For more information about the implementation and use of REST technology in an industrial environment, please refer to our page about the REST Plug-in.
Using a REST API, web services, devices and other systems can be addressed specifically and data can be transferred securely. In our linked article, we show five use cases for the REST standard in industrial environments.
The OPC Router is a software that networks your machines and systems. It enables the connection of all existing systems and can modernize processes sustainably. Our video explains exactly how the OPC Router works, how it can be integrated into your system environment and what advantages it offers your company.