Visualize Data with Microsoft Power BI Custom Connector

Contents

Overview

Microsoft Power BI (Power BI) is a tool provided by Microsoft that supports decision-making through data visualization and sharing.
This article introduces how to retrieve and visualize Kintone data using Power BI's custom connector.

What Is Power BI

Power BI is a business analytics platform that helps transform data into actionable insights.
The retrieved data can be visualized and shared within your organization.

Power BI mainly consists of the following services:

Service Name Overview
Power BI Desktop A free Windows app that enables data retrieval, formatting, and visualization.
Power BI Service A cloud service for sharing and managing reports.
Requires a Microsoft account and license to use.

For more information, refer to the following link:
What is Power BI (External link)

What Are Custom Connectors

Custom Connectors are extensions for connecting to data sources that Power BI doesn't support by default.
To import Kintone data into Power BI, use a custom connector for Kintone (the Kintone connector).

Kintone Connector

Disclaimer

  1. Please use the Kintone Connector at your own discretion and responsibility. Cybozu shall not be liable for any consequences arising from such use, nor Cybozu be responsible for any claims made by third parties.
  2. Cybozu makes no warranties that the Kintone Connector will meet your requirements, operate without interruption, or that any defects (including but not limited to bugs and structural issues) will be corrected. In addition, any information or advice provided by Cybozu, whether oral or written, shall not constitute a warranty or expand the scope of any existing warranty in any way.
  3. For all other matters, the provisions set forth in the Terms of Services (External link) of this website shall apply.

Features

By configuring the Kintone connector in Power BI Desktop, users can import record data from Kintone Apps into Power BI. They can then publish and share reports created in Power BI Desktop.
It also supports automatic updates with specified intervals and times.

Connection Information

Parameter Required Description
Subdomain Yes The Kintone subdomain.
{subdomain}.kintone.com
Domain Yes The Kintone domain.
kintone.com
App ID Yes The Kintone App ID.
Guest Space ID The Kintone guest space ID.
Set this for Apps within guest spaces.
Field Codes Field codes to retrieve.
Separated by commas.
Example: To retrieve data with field codes Single Line Text and Number, use Single Line Text, Number
Record language setting Language for retrieving Kintone records.
Set this when Kintone records support multiple languages and the display language is set to Follow web browser settings.

Create Reports In Power BI Desktop

Installation

  1. Install Power BI Desktop.
    Power BI Desktop (External link)
  2. Download the Kintone connector.
    Kintone connector (mez file) (External link)

Setting up the Kintone Connector

information

This setting allows loading of unverified custom connectors.

  1. Place the Kintone connector (mez file) in the Documents/Power BI Desktop/Custom Connectors folder.

    • Create the Custom Connectors folder if it doesn't exist.
    • The following official documentation describes it as "Microsoft Power BI Desktop", but it works with "Power BI Desktop".
      Custom connectors (External link)

  2. Launch Power BI Desktop and change the security settings.

    • From Power BI Desktop's File menu, select Options and settings.
    • Select Options and choose Security from the left side of the screen.
    • In the Data Extensions section, select (Not Recommended) Allow any extension to load without validation or warning and click OK.
    • Once complete, restart Power BI Desktop.

  3. Select Get data → kintone (Custom) and click Connect.

  4. A warning about the connector will be displayed, click Continue.

  5. Enter the following information and click OK.
    Connection information

  6. Review the data to retrieve and select Load.

For more information, refer to the following link:
Connector extensibility in Power BI (External link)

Publish Reports And Automatically Update Data In Power BI Service

Publish Reports

Select Publish from Power BI Desktop to publish the report to Power BI Service.

Set On-premises Data Gateway for Automatic Data Updates

Install and Configure the On-premises Data Gateway

The on-premises data gateway must be running when updating data.
Choose an environment that is running during the desired data update times.
It doesn't have to be the same environment where Power BI Desktop is installed.

  1. Install the on-premises data gateway from the following link.
    Install an on-premises data gateway (External link)
    Log in with the same account as the user who will use Power BI Service.
    Take note of the recovery key, as it will be used in a later step.

  2. Save the Kintone connector (mez file) in the environment where the on-premises data gateway is installed.

  3. Open the Connectors tab of the on-premises data gateway and specify the directory where the Kintone connector (mez file) is placed.

  4. Set folder access permissions.
    In the folder where the Kintone connector (mez file) is placed, follow these steps:

    • Right-click the folder and select Properties.
    • Open the Security tab.
    • Check if PBIEgwService is included in Group or user names.
    • If not included, click Edit → Add, enter NT SERVICE\PBIEgwService in Object names and click OK.
    • Check the Read and execute and Read permissions and click Apply.
    warning
    Warning

    This operation requires administrator permission.

  5. Restart the on-premises data gateway.
    If "kintone" appears after a few seconds, it's successful.

For more information, refer to the following link:
Use custom data connectors with the on-premises data gateway (External link)

Gateway Integration Settings on Power BI Service

  1. Log in to Power BI Service, click the gear icon in the upper right corner of the screen, and click Manage connections and gateways from the menu.

  2. Select the On-premises data gateways tab and confirm that the gateway name you just set is displayed.

  3. Select the gateway and click Settings at the top of the screen.

  4. The settings screen will be displayed, check Allow user's custom data connectors to refresh through this gateway cluster and click Save.

  5. Open the semantic model of the report to be updated.

  6. Open Schedule refresh from Refresh in the upper left corner of the screen.

  7. Click Discover Data sources at the top of the screen.

  8. Gateway and cloud connections will be displayed on the screen, click it.

  9. From the list of on-premises data gateways, click the triangle button of the gateway where you set the Kintone connector (mez file), and click Add gateway manually.

  10. The add screen will be displayed. Then enter the following information used in Power BI Desktop and click Create.
    Connection information

  11. When the connection is successful, it will return to the settings screen. Click Gateway and cloud connections again.

  12. Select the configured connection name from the dropdown and click Apply.

Execute and Automate Data Updates

Open the semantic model to update and click Refresh now from Refresh to reflect the current Kintone App data in the report.

Next, open Schedule refresh from Refresh and click Keep your data up to date to set up automatic updates.
It is recommended to schedule automatic updates during times when there are few users, such as late at night, considering the load on Kintone.

Notes

When the Kintone App has 0 records, the error "We cannot convert the value null to type Record" appears when connecting to Kintone. This can be solved by registering records in the Kintone App.
If this doesn't resolve the issue, refer to the following link to delete cached data:
Troubleshooting Power BI Desktop publishing (External link)