Skip to main content
Version: v2

Output to Power BI

In this tutorial, you will learn how to use Canner Enterprise to output data to Power BI for report application.

Before starting

Before starting this tutorial, you will need to have completed Connecting Data Sources in Canner Enterprise and Import data in your Workspace.

Integration Method​

Canner Enterprise currently supports two ways to integrate with Power BI, the first is Push Dataset, and the second is PostgreSQL Wire Protocol.

Differences

The biggest difference between PostgreSQL Wire Protocol and Push Dataset is that

  1. PostgreSQL Wire Protocol can directly execute SQL Query to access all Table/ View/ Materialized View in Workspace
  2. Push Dataset is pushing data into Power BI. When Refresh Data, it is automatically completed by Canner Enterprise. Once the data is updated, the system will push the data to Power BI Service and rebound to the report. The report will see the latest information.
Limitations

Push Dataset restrictions are as follows:

  1. A dataset can have up to 75 fields
  2. A dataset can hold a maximum of 1,000,000 data

Push Dataset​

Different from the Data Warehouse / Database through Connector, we allow users to integrate various heterogeneous data sources first, create a Materialized View for the calculated data, and automatically convert it into ZIP format and push the data to Power BI In Service, Power BI users can directly access data.

1_powerbi_data_push

PostgreSQL Wire Protocol​

Canner Enterprise implements the interface of PostgreSQL Wire Protocol, and Power BI users can directly execute SQL Queries to read all the data in the Canner Enterprise workspace.

Usage scenarios​

Push Dataset​

Power BI often fails due to problems such as insufficient computing resources. If you have routine reports to generate to Power BI, you can use push dataset to offload data computation in Canner Enterprise.

PostgreSQL Wire Protocol​

It is suitable for situations where many ad-hoc queries / direct queries are executed.

Setting up Push Dataset​

Setting in Power BI Service​

In the next few steps, you need to set the relevant permissions on the Power BI Service and obtain the required information to fill in Canner Enterprise later.

Step 1: Application Registration​

  1. Application Registration > Create an application with the name Canner Power BI Push Dataset App.

powerbi-push-step1.png

  1. Click the created Canner Enterprise Power BI Push Dataset App > copy the "Application (Client) Identification Code (Application ID)" and "Directory (Tenant User) Identification Code (Tenant ID)", we will use in the next step.

powerbi-push-step2.png · 3. Click Credentials and Secrets > Add a new client password in the Client Password section.

powerbi-push-step3.png

  1. Copy the value of Canner Power BI Push Dataset secret (Application secret).

powerbi-push-step4.png

Caution

In this step, please be sure to copy the value of the secret key; you will not be able to copy the value if you look back later.

Step 2: Create Security Groups​

  1. Go to Azure Active Directory > Groups, and click "Add Group"

powerbi-push-step5.png

Search for Azure Active Directory

powerbi-push-step6.png

Click "Add Group" on this page

  1. When creating, enter the newly created application Canner Power BI Push Dataset App in "Members", and select "Security" for the group type

powerbi-push-step7.png

  1. After the creation is completed, the following picture is shown:

powerbi-push-step8.png

Step 3: Enable Power BI Service Admin Settings​

  1. Next, in order to allow the service subject Canner Enterprise Power BI Push Dataset App to access the API of Power BI, we need to open the permission of the service subject to access the API in the Service of Power BI. The settings in this part can only set by Power BI The administrator permission of Azure (different from the administrator permission of Azure), if you want to open the administrator permission of Power BI, please refer to this document: https://docs.microsoft.com/zh-tw/power-bi/admin/service-admin-role

powerbi-push-step9.png

  1. By specifying a "specific security group", the access rights to the API can be limited to this security group. Then we can allow specific workspaces to be protected by adding this security group Power BI API access operations.

powerbi-push-step10.png

Step 4: Add the service principal to your Workspace​

  1. Create a workspace that needs to be accessed through the Power BI API through "Create Workspace"> then click on the new Workspace and open the workspace access, as follows for "test-power-bi":

powerbi-push-step11.png

note

Workspace access and creation of Workspace requires Power BI Pro product permissions, which are different from Power BI administrator role permissions, as shown in the following figure from the confirmation of User > Authorization (License) in Azure AD:

powerbi-push-step12.png

You can set it here if you do not have Power BI Pro product permissions. In the Power BI service, go to the "Admin Portal" in the upper right corner > User > Go to [Microsoft 365 System Management Center](https://portal .office.com/AdminPortal/Home#/homepage)

powerbi-push-step12-2.png

And open Microsoft, click the active user, edit the user's authorization and App authorization, and activate Power BI Pro.

powerbi-push-step12-3.png

  1. Enter the security group created in Azure AD:

powerbi-push-step13.png

This belongs to the Workspace, because with this security group Canner Enterprise Power BI App, the Canner Enterprise Power BI Push Dataset App service principal (Service Principal) belonging to the security group can obtain the Power BI API Access permissions and can only be used in the Workspace where the group is added.

Canner Enterprise set Push Dataset​

Step 1: Create Power BI Service Principal Credential​

Click the gear icon in the upper right corner, expand the menu, and select Credentials.

1_power_bi_push_dataset

Once in, click the Create a Credential button.

2_power_bi_push_dataset

Enter the obtained

  1. Application ID (application (client) identification code obtained in the information after the application is successfully registered)
  2. Application Secret (obtained in the certificate and secret after the application is successfully registered)
  3. Tenant ID (directory (rental user) identification code (Tenant ID), after the application is registered successfully)

3_power_bi_push_dataset

Step 2: Create Push Dataset​

Click the switching menu in the upper left corner to enter the Power BI Push Dataset page.

4_power_bi_push_dataset

  1. Click Create Create a Push Dataset

    10_power_bi_push_dataset

  2. Then, a form will pop up; you need to fill in the following information

    1. Credential Name: Select Power BI Service Principal Credential created in the previous step

    2. Power BI Workspace Name: Select which Power BI workspace to push to

    3. Materialized View: Select the Materialized View you want to push to Power BI

    4. Rebind Report: Under normal circumstances, Please check this field; we will update the data set bound to the report simultaneously.

When selecting Power BI Workspace Name, a window will pop up for the user to choose a workspace.

7_power_bi_push_dataset

My Workspace doesn't appear in the list?

You may notice that when you add a group workspace (Group Workspace) in Power BI, it will not be visible on Canner Enterprise immediately because the Power BI RESTful API will not get the latest data, as shown in the figure below, when you created a workspace and authorized it to Canner Power BI App according to the previous steps.

The list currently seen may be empty 6_power_bi_push_dataset

It takes a while to take effect, and the waiting time may be around 20 - 40 minutes. 7_power_bi_push_dataset

Step 3: Observe the status of Power BI Push Dataset​

When the status changes to Synced, it means that the data push is successful

9_power_bi_push_dataset

Data are also available on the Power BI Service powerbi-push-step18.png

How to set schedule update​

When generating Materialized View, if you have selected [Schedule Update](/v2/product/workspaces/sql/materialized_view#Schedule Management Settings), each time it is automatically updated, or you are on the Materialized View page, click Refresh When doing a manual update, Power BI Push Dataset will enter the Preparing state, which means that the Materialized View is being updated, and the data is being prepared to be pushed to the Power BI Service.

The meaning of status

Power BI Push Dataset has three states

  1. Preparing: Represents that the Materialized View is being updated
  2. Syncing: Indicates that data is being sent to Power BI Service
  3. Synced: Indicates that the data transmission has been completed

Setting up PostgreSQL Wire Protocol​

info

Currently, only Import Mode is supported

1. In Get Data, select PostgreSQL Database connection​

choose-pg

2. Enter connection information​

pg-connect

  • Server: Enter IP/Host and Port
  • Database: Enter the SQL Name of the Workspace
Workspace SQL Name

You can see the Workspace SQL Name in the workspace list 1_power_bi

3. Enter user/password​

pg-cred Password input Personal Access Token

4. Load Data​

After the connection is successful, a window will pop up to display all available Tables/ Views/ Materialized Views in the Workspace pg-load-data

5. Getting Started​

Then, you can use the data in Canner Enterprise in Power BI pg-pbi