How to use IBM App Connect with Microsoft Power BI

Microsoft Power BI is a unified, scalable BI platform that gives users capabilities for collecting, analysing, visualising, and sharing data. Power BI's user interface is fairly simple, allowing users to visualise any data and seamlessly integrate the visuals into the applications they use every day.

Availability:
  • App Connect Enterprise as a Service connector
  • A local connector in a Designer instance of IBM App Connect in containers (Continuous Delivery release)Local connector in containers (Continuous Delivery release) 12.0.7.0-r5 or later
  • A local connector in a Designer instance of IBM App Connect in containers (Support Cycle 2)Local connector in containers (Long Term Support Cycle-2 release)

Connecting to Microsoft Power BI

Complete the connection fields that you see in the App Connect Designer Catalog page or flow editor. If necessary, work with your Microsoft Power BI administrator to obtain these values.

Microsoft Power BI connection fields:

To connect App Connect to a Microsoft Power BI account, select your user type from the following:
  • Non-admin user (default)
  • Admin user
Then, select your preferred authorization type:
Authorization type Connection fields Applicability
Use the application's website to sign in (OAUTH 2.0 AUTH CODE)
Tip: Authorize connection to Microsoft Power BI by signing in to your account.
 App Connect Enterprise as a Service
Provide a username, password, and client credentials (OAUTH 2.0 PASSWORD) Username App Connect Enterprise as a Service and App Connect in containers
Password
Client ID
Client secret
Provide client credentials (OAUTH 2.0 CREDENTIALS) Client ID App Connect Enterprise as a Service and App Connect in containers
Client secret
Tenant ID
Provide credentials for App Connect to use (BASIC OAUTH) Client ID App Connect Enterprise as a Service and App Connect in containers
Client secret
Access token
Refresh token
Description of the connection fields:
Connection field Description
Username The email address of your registered Microsoft Power BI account.
Password The password for the specified username.
Client ID The unique identifier generated after the Microsoft Azure app registration gets mapped to the specific project requests.
Client secret The application client secret for the project-specific client ID.
Access token The access token generated from the application client ID and client secret.
Refresh token The refresh token generated from the application client ID and client secret.
Tenant ID The identifier of your Microsoft Azure Active Directory (Azure AD) tenant. Get the tenant ID from the Properties page of the Azure Active Directory portal.

To generate these values and connect to Microsoft Power BI, you need to register an application with the required permissions in Microsoft Azure, which will enable App Connect to integrate with Microsoft Power BI by using APIs and protocols.

To connect by using different authorization methods, you need to obtain client ID, client secret, access token, refresh token, and tenant ID for your registered app, and then configure permissions.
Note: These instructions assume that you are registering an application in Microsoft Azure for the first time.
Note: Choose the applicable steps for the connection values based on the authorization method you selected.
  1. To register an application with Microsoft Azure, for use with App Connect:
    1. Log in to the Microsoft Azure portal, and then locate and click App registrations.
    2. If you have access to more than one tenant, switch to the tenant where you want to register the app by using the Directories + subscriptions filter in the banner and then click the Close icon (X) to return to the previous page.
    3. In the App registrations page, click New registration.
    4. In the Register an application page, specify a unique name for your app, select Accounts in any organizational directory (Any Azure AD directory - Multitenant) as the account type, and accept the default values for the remaining fields.
      registering an application
    5. Click Register. The Overview page for the application is displayed.
      Overview page for the registered application
    6. Make a note of the Application (client) ID value because you need to specify it as a connection value when creating the account in App Connect.
  2. To generate a client secret for your registered application:
    1. Next to Client credentials on the Overview page, click Add a certificate or secret. This displays the Certificates & secrets page.
    2. Click New client secret
    3. In the Add a client secret panel, specify a description for the secret (for example, App Connect secret) and then select an expiry period.
    4. Click Add. The generated client secret is displayed on the Certificates & secrets page.
    5. Generated client secret for the registered app
    6. Copy and store the client secret value because you need to specify it as a connection value when creating the account in App Connect.
      Note: The client secret value won't be shown again in full after you leave this page.
  3. Configure the permissions that App Connect needs.
    1. In the left pane, click API permissions and then click Add a permission > Power BI Service > Delegated permissions to add each of the following permissions in turn. You can search for and select a permission, and then click Add permissions.
      Permissions Description
      App.Read.All The app can view all Power BI apps you have access to.
      Capacity.Read.All The app can view all Power BI Premium and Power BI Embedded capacities that you have access to.
      Capacity.ReadWrite.All The app can view and edit all Power BI Premium and Power BI Embedded capacities that you have access to.
      Content.Create App can automatically create content and datasets for you.
      Dataflow.Read.All The app can view all dataflows that you have access to.
      Dataflow.ReadWrite.All The app can view and edit all dataflows that you have access to.
      Dataset.Read.All The app can view all your datasets and any datasets that you have access to.
      Dataset.ReadWrite.All The app can view and write to all your datasets and any datasets that you have access to.
      Gateway.Read.All The app can view all gateways that you are an admin of.
      Gateway.ReadWrite.All The app can view and edit all gateways that you are an admin of.
      Report.Read.All The app can view all your reports and reports that you have access to. The app can also see the data within the reports as well as its structure.
      Report.ReadWrite.All The app can view and edit all your reports and any reports that you have access to.
      StorageAccount.Read.All The app can view all storage accounts registered with Power BI that you are an admin of.
      StorageAccount.ReadWrite.All The app can view and edit all storage accounts registered with Power BI that you are an admin of.
      Tenant.Read.All The app can view all content in your tenant if the signed in user is in the Global administrator or Power BI service administrator role.
      Tenant.Read.All The app can view all content in the tenant without a signed in user.
      Tenant.ReadWrite.All The app can create, edit, view, and delete all content in your tenant if the signed in user is in the Global administrator or Power BI service administrator role.
      Tenant.ReadWrite.All The app can create, edit, view, and delete all content in the tenant without a signed in user.
      UserState.ReadWrite.All The app can view and edit your user settings and the user-specific state associated with content you have access to.
      Workspace.Read.All The app can view all workspaces that you have access to.
      Workspace.ReadWrite.All The app can view and edit all workspaces that you have access to.
    2. If the status of any permission is shown as Not granted for myDomain, click Grant admin consent for myDomain, where myDomain is your domain name. Then click Yes to confirm. (This updates the status of all permissions to Granted for myDomain.)
      Required permissions for the registered app
  4. To find the Tenant ID, go to the Microsoft Azure portal login page, then Azure Active Directory > Properties.
  5. Click Copy to copy the Tenant ID, and save it somewhere safe.
    Locating the Tenant ID in Microsoft Azure

    For more information about Tenant ID, see How to find your Azure Active Directory tenant ID on the Microsoft Documentation page.

  6. Use an application such as IBM API Connect Test and Monitor or Postman to submit a POST request to generate an access token and a refresh token that will be used to interact with Microsoft Power BI on your behalf. Specify the following parameters:
    • Request URL: 
      https://login.microsoftonline.com/organizations/oauth2/v2.0/token
    • Content-Type: application/x-www-form-urlencoded
    • Request parameters:
      Key Value
      client_id Set this to the Application (client) ID value that was generated for your registered app.
      scope Directory.ReadWrite.All offline_access
      grant_type password
      client_secret Set this to the client secret value that was generated under Certificates & secrets for your registered app.
      userName Set this to the username that was used to log in to the Azure portal.
      password Set this to the associated password for the username

  • Postman Version 7.29.1 was used in these instructions, so there might be a slight variation in the fields that you see if your version is different.

    1. Start a new POST request and specify the request URL.
    2. Click the Body tab and select x-www-form-urlencoded. This option automatically adds the Content-Type: application/x-www-form-urlencoded setting in the request header.
    3. Specify the request parameters.
    Configuring the request parameters in Postman

    When you click Send, an access token and refresh token are returned in the response. Make a note of these values because you need to specify them as connection values when creating the account.

    Generated tokens in the response returned for the POST call in Postman
    Note: The generated access token is valid for 1hour, and the refresh token will expire after 90 days of inactivity. So it is expected that you need to generate new tokens only if the refresh token has been revoked or has not been used in 90 days.

To connect to a Microsoft Power BI endpoint from the App Connect Designer Catalog page for the first time, expand Microsoft Power BI, then click Connect. For more information about ways to connect to Microsoft Power BI, see Managing accounts in App Connect.

Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Catalog page, select the account, open its options menu (⋮), then click Rename Account.

General considerations

Before you use App Connect Designer with Microsoft Power BI, take note of the following considerations:

  • To control the Microsoft Power BI features that are made available to your organization, see Tenant settings on the Microsoft Documentation page.
  • If you would like to use Admin API, see Developer tenant settings on the Microsoft Documentation page.
  • To use service principals to use read-only Microsoft Power BI admin APIs, see Admin API tenant settings on the Microsoft Documentation page.
  • (General consideration) You can see lists of the trigger events and actions that are available on the Catalog page of the App Connect Designer.

    For some applications, the events and actions in the catalog depend on the environment and whether the connector supports configurable events and dynamic discovery of actions. If the application supports configurable events, you see a Show more configurable events link under the events list. If the application supports dynamic discovery of actions, you see a Show more link under the actions list.

  • (General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Events and actions

Microsoft Power BI events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Note: Events are not available for changes in this application. You can trigger a flow in other ways, such as at a scheduled interval or at specific dates and times.

Microsoft Power BI actions

Your flow completes these actions on this application.

Datasets
Retrieve datasets
Retrieve dataset gateways
Retrieve dataset user access
Retrieve dataset to dataflow links
Delete dataset
Add dataset user
Update dataset
Update dataset user access
Refresh dataset
Bind to gateway
Gateway data sources
Create gateway data source
Retrieve gateway data sources
Update gateway data source
Delete a gateway data source
Goals
Create goal
Create goal values
Retrieve goals
Retrieve goal values
Update goal
Update goal values
Delete goal
Delete a goal values
Delete current goal value connection
Delete current target value connection
Refresh current goal value
Refresh current target value
Push datasets
Create push dataset
Retrieve tables
Delete rows
Update table
Insert multiple rows
Workspaces
Create workspace
Retrieve workspace users
Retrieve workspaces
Add workspace user
Delete workspace
Update workspace user
Remove workspace user

Examples

Dashboard tile for a template that uses Microsoft Power BI
Dashboard tile for a template that uses Microsoft Power BI
Dashboard tile for a template that uses Microsoft Power BI

Use templates to quickly create flows for Microsoft Power BI

Learn how to use App Connect templates to quickly create flows that perform actions on Microsoft Power BI. For example, open the Templates gallery, and then search for Microsoft Power BI.

Learn more

Microsoft Power BI flow in detailed view

Use IBM® App Connect to build flows that integrate with Microsoft Power BI.

Read the blog in the IBM Community to learn how to retrieve leads from Insightly and add the lead data to Microsoft Power BI. Click Read the blog to go to the blog.

Read the blog