Establish Connectivity to Microsoft Power BI Cloud

This article describes how to connect to Microsoft Power BI in order to load data into Datasets and Reports in Metric Insights.

PREREQUISITES

The whole process comprises:

  1. Create New Data Source
  2. Provide Required Parameters
  3. Provide Optional Parameters
  4. Advanced Configuration
  5. Microsoft Power BI Cloud Elements and User Tokens

NOTE: If you are using Microsoft Power BI Cloud plugin on MI versions prior to v7.0.1a, follow the instructions from the article Update Microsoft Power BI Cloud Configuration to Ensure Image Refresh Functionality to ensure proper image collection functionality.

1. Create New Data Source

Access Admin > Collection & Storage > Data Sources

  1. [+New Data Source]
  2. Choose "Microsoft Power BI Cloud" from Plugin drop-down list
  3. [Next]
  4. Enter a descriptive Data Source Name
  5. [Create]

2. Provide Required Parameters

  1. Select the Auth Type:
    • Password: provide Username and Plugin Password, note that the username must be a cloud only Microsoft Power BI user, or if an AD user, the AD user must exist/be synced in Azure AD for Power BI authentication
    • Identity Profile: provide an Identity Profile
    • OAuth: enter Client Secret and click [Get Token] to grant permission to Office 365 API
      • You can enable Client Secret authorization for your Azure app. After the Client Secret has been generated, the Username/Password authorization will no longer work
      • Optionally, provide Directory (tenant) Id which can be found on the Overview tab of the Azure app
    • Service Principal: Provide Client Secret value and Directory (tenant) Id
  1. Enter Application ID of the registered Azure app that represents Metric Insights (to connect to Power BI)
  2. Server: the URL of your Microsoft Power BI server (this is the same URL that you see when accessing Microsoft Power BI via your web browser)

Enabling OAuth has the following benefits compared with the Username/Password authentication model:

  • Ability to view content in iframes based on user account permissions rather than service account permissions
  • Row-level security (RLS) without the need to use User Maps on MI side (you only have to enable RLS in Power BI)

See Configure Microsoft Power BI OAuth in Azure AD for details.

3. Provide Optional Parameters

  1. App filter:
    • "*":  the plugin will collect Power BI objects from all available Apps
    • App names (comma-separated, or wildcard patterns): the plugin will collect Power BI objects from the specified Apps
  2. These two parameters work only with OAuth and configure the display of Client Secret expiration date. Note that the values for these two parameters and Directory (tenant) Id must be provided in order to display Client Secret expiration date:
    • To Check Client Expiration Date: whether or not the expiration date is displayed. If the expiration date has been retrieved and this parameter is then set to "N", the expiration date will disappear after the next Test Connection
    • Secret Id can be found on the Certificates & secrets tab of the Azure app
  3. Collect objects as admin: Set to “true” to allow the User who created the Data Source with Power BI admin account to pull all Workspaces and their objects into Metric Insights without having to explicitly grant Admin access to each workspace.

NOTE:

  • This parameter only works for Power BI admin accounts with the Tenant.Read.All permission. Without this permission, setting "Collect objects as admin" to "true" will not work.
  • If the Tenant.Read.All permission is not granted, set "Collect objects as admin" to "false." In this case, the plugin will only retrieve Workspaces that the user’s OAuth token has explicit access to. The user must be explicitly added to all required Workspaces.
  • If this parameter is set to "true", App filter is not available, Workspace Filter can filter Workspaces by name.
  1. Power BI Premium: Collect native PDFs: set to "true" to get native PDFs from Power BI
    • NOTE: Native PDFs can be generated only with Power BI Premium accounts, otherwise, PDFs are generated using collected images
  2. Power BI Premium: Collect media using API: set to "true" to extract native PNGs and PDFs from Paginated Reports, Reports and Report tabs using API.

To be able to fetch native PNGs and PDFs:

  • Go to app.powerbi.com, from the header access Settings > Admin portal > Tenant settings > Enable Export reports as image files,
  • In the Microsoft Power BI Cloud plugin connection profile, set Power BI Premium: Collect media using API to "true".

NOTE: All semantic models from the exported report, including the ones with a Direct Query connection, must reside on a Premium or Embedded capacity.

  1. Get token from Plugin: specify whether the token used to display External Report images in iframes is fetched from plugin ("Y") or in the UI ("N")

NOTE: It is recommended to set the parameter to "Y".

  • If the parameter is set to "N", a new token is fetched in the UI for each embedding and image collection. The same applies for External Reports with Drill-Down Authentication parameter set to "Power BI OAuth" in the Report type.
  1. Enter Log Analytics Workspace ID from Azure to get logs Dataset
    • Add "Log" to Object types to collect to obtain log reference
    • For OAuth also provide Directory (tenant) Id
  2. Object types to collect: by default the plugin is configured to collect Dashboards, Reports, Datasets, Tiles and Tables (you may remove any object type you do not need to fetch)
    • Enter "Workspace" and "App" to be able to collect objects from Workspaces and Apps
    • Object type REPORT returns both Reports and Paginated Reports (RDLREPORT)
  3. Old table ID: specify whether or not to collect tables from OLE-DB connector with old ID format.
  4. OleDB Data:
    • "true": get data from OleDB instead of DAX API, an RDP is required
    • "false": get data using DAX API without using an RDP

NOTES:

If data is fetched using DAX API (OleDB Data set to "false"):

  • Only data from V2 Workspaces can be fetched,
  • The returned Dataset data is limited by 100,000 rows,
  • Only DAX queries are supported.

If data is fetched from OleDB:

  • An RDP is required,
  • Additional software installed on RDP machine is required:
    • Analyze in Excel
    • .NET
  • Both MDX and DAX queries are supported.
  1. Workspace Filter:
    • "*":  the plugin will collect Power BI objects from all Workspaces the associated user has access to, except for My Workspace
    • Workspace names (comma-separated or wildcard patterns):  the plugin will collect Power BI objects from the specified Workspaces
    • Leave blank to use the personal workspace
  2. [Test Connection]

See Object Filters Cheat Sheet for details on configuring the App filter, Workspace filter, and Object types to collect parameters.

Object Filters Cheat Sheet
App filter Empty Empty Empty "*" or App name(s) "*" or App name(s) "*" or App name(s)
Workspace filter "*" or Workspace name(s) "*" or Workspace name(s) Empty "*" or Workspace name(s) Empty "*" or Workspace name(s)
Object types to collect Neither "Workspace" nor "App" are listed OR "Workspace" is listed "App" is listed Both "Workspace" and "App" are listed Neither "Workspace" nor "App" are listed "App" is listed Both "Workspace" and "App" are listed
Result Objects from the specified Workspace(s) are collected Object filtration error Object filtration error Object filtration error Objects from the specified App(s) are collected Objects from the specified App(s) and Workspace(s) are collected

4. Advanced Configuration

If you have set OleDB Data to "true" on the previous step and want to be able to get Data from Power BI Datasets (to create Metrics, Reports, and Datasets in Metric Insights), you must utilize the Metric Insights Remote Data Processor to connect to Power BI directly.

If you are using AD, you may want to run the RDP service as an AD service account:

  • Access Services > Metric Insights Data Processor Daemon > Properties > Log On, select AD service account
  1. Advanced Configuration settings can be set to default values unless Data Source requires more precise tuning
  2. Use Proxy: set to "Yes" and choose a Proxy if your Data Source connection requires a proxy server
  3. [Test connection] (this will also save your entries)

5. Microsoft Power BI Cloud Elements and User Tokens

  1. You can create Datasets and view all the elements or Datasets using this Data Source from the respective tabs
  2. Navigate to the Access tab to assign Data Source access to Groups or Power Users
  3. User Tokens are displayed in the corresponding menu and can be deleted by clicking the trashcan icon. The lifetime of each token is 45 minutes

What's Next?

Create a Dataset from Microsoft Power BI Cloud