Introduction
This guide explains how to connect KNIME Analytics Platform with Microsoft Power BI® which allows you to push KNIME tables into Power BI datasets. The following sections will explain how to install the Power BI Integration and how to configure the "Send to Power BI" node.
Installing the Power BI Extension
Navigate to File → Install KNIME Extensions and type "Power BI" in the search box that appears. When you do, you will see the screen below.
Select and install the "KNIME Power BI Integration".
Once you have installed the extension, restart KNIME Analytics Platform and you should see the "Microsoft Power BI" category in the node repository under KNIME Labs. You can now drag and drop the "Send to Power BI" node into your workflow.
Pushing Data to Microsoft Power BI
This section explains how to configure the "Send to Power BI" node to send one or multiple KNIME tables to a Power BI dataset.
Node configuration
Authentication
To authenticate with Power BI click the "Authenticate" button in the node configuration. A new browser will be opened and you will be asked to log-in to your Microsoft account.
| To be able to authenticate against Microsoft services, the privilege to consent to third party apps like KNIME Software needs to be granted. In order to do so, you or your Microsoft Administrator will have to activate this feature as described at this link. |
To allow KNIME Analytics Platform to upload data to your Power BI workspace the following permissions need to be granted.
-
View all datasets: Needed to check if a dataset already exists in your Power BI workspace.
-
Read and write all datasets: Needed to upload a table to a Power BI dataset in your workspace.
-
View all workspaces: Needed to get the identifier of the selected Power BI workspace.
-
Maintain access to data you have given it access to: Needed to access the Power BI API during the node execution without asking you to log in again.
Once you are logged-in and accepted the permissions you are redirected to a page that states "Received verification code. You may now close this window…". The configuration dialog indicates now that the status is "Authenticated".
You have three options on how to save the authentication:
-
Memory: The authentication credentials will be kept in memory. They are discarded when closing KNIME Analytics Platform.
-
Local File: The authentication credentials will be saved and loaded to and from the selected file.
-
Node: The authentication credentials will be saved in the node settings. If you choose this option, the workflow will contain your authentication information after is has been saved.
| If you intend to share workflows containg the "Send to Power BI" node, we strongly discourage you from storing the authentication as part of the node settings as people otherwise will have access to your Microsoft account. |
Selecting the dataset location
To select the dataset location you have to be authenticated.
Workspace selection
Once authenticated the node dialog will fetch all workspaces you have access to. You can now select the workspace you want to use. Note that "default" indicates your personal workspace which is called "My workspace" in the Power BI web interface.
Dataset selection
You have two options when selecting the dataset.
-
Option 1 - Creating a new dataset: To create a new dataset in your Power BI workspace, select the radio button "Create new Dataset" and choose a dataset name and table name. If a dataset with the same name already exists you can check the option "Delete and create new if dataset exists".
| The delete option will also delete reports and dashboard tiles that are linked to the dataset. |
-
Option 2 - Updating an existing dataset: To update an existing dataset in your Power BI workspace, select the radio button "Select existing Dataset" and use the checkboxes to select an existing dataset. You can choose if you want to append the rows to the existing tables or if you want to overwrite the existing rows.
Note that the dataset has to be a "Push" dataset, see the Power BI documentation. You can check if a dataset is a "Push" dataset by checking the value in the "API ACCESS" column in the dataset list of the Power BI service.
Uploading a dataset with multiple tables
To create or update a dataset with multiple tables you can add input ports to the node. This can be done by clicking on the three little dots in the bottom left corner of the node and choosing "Add input port" (ports can also be added in the right-click menu of node). The node configuration will now show multiple table names to select. All tables will be added to the same dataset using the configured table names. You can update as many tables as you like from an existing dataset with multiple tables.
