Microsoft 365 usage analytics refresh without PIM role

[chimcken_island]

Hi all!

Running into some issues with setting up automatic refreshes for the M365 usage analytics API (accessed via reports.office.com/pbi/v1.0/{tenantID}). My organization utilizes PIM roles, so when I got to upload the report to the power BI service, and set up automatic refreshes with OAuth credentials, the refresh fails, unless I'm PIMed into the reports.reader role. Is there any way to set up the refreshes on this data without having to PIM in each time? 

[Shravan133]

Setting up automatic refreshes for the M365 usage analytics API while utilizing PIM (Privileged Identity Management) roles can be challenging due to the temporary nature of these roles. Here are some strategies to manage automatic refreshes without having to PIM in each time:

Strategies to Manage Automatic Refreshes

1. Use Service Principal (App Registration):

   • Create an Azure AD application (service principal) with the necessary permissions to access the API.
   • Use this service principal to authenticate the data source in Power BI.

   Steps to Create and Use a Service Principal:

   1. Register an Application in Azure AD:

      • Go to the Azure portal.
      • Navigate to Azure Active Directory -> App registrations -> New registration.
      • Register your application and note down the Application (client) ID and Directory (tenant) ID.

   2. Grant API Permissions:

      • Go to the registered application.
      • Navigate to API permissions -> Add a permission -> APIs my organization uses -> Office 365 Management APIs.
      • Select the necessary permissions (e.g., Reports.Read.All).
      • Grant admin consent for the permissions.

   3. Create a Client Secret:

      • Navigate to Certificates & secrets.
      • Create a new client secret and note it down.

   4. Configure the Data Source in Power BI:

      • Open Power BI Desktop.
      • Use the registered application credentials to authenticate when connecting to the API.
      • Publish the report to the Power BI service.
      • Set up the data source credentials in the Power BI service using the service principal (client ID and client secret).

2. Use a Dedicated Service Account:

   • Create a dedicated service account with the required permissions to access the API.
   • Use this account to set up the data source credentials in Power BI.
   • Ensure that the service account has a permanent Reports Reader role assignment or similar, to avoid the need for PIM.

3. PIM Configuration for Long-Term Access:

   • If using PIM is mandatory, consider configuring PIM to allow for longer activation durations or permanent active roles (if your organization's security policies permit).
   • This way, the role assignment does not expire frequently, and you can avoid frequent PIM activations.

4. Automate PIM Activation:

   • Automate the PIM activation process using Azure Logic Apps, Power Automate, or Azure Functions.
   • Schedule the activation process to ensure the role is active before the refresh time.

Example: Using Service Principal in Power BI

Step-by-Step Guide:

1. Register the App and Set Permissions:

   • Follow the steps mentioned above to register an app and set the permissions.

2. Connect Power BI to the API:

   • In Power BI Desktop, when you get data from the API, choose the OAuth2 authentication method initially.
   • Once you publish the report, change the authentication method in the Power BI service to use the client ID and client secret.

3. Set Up Automatic Refresh in Power BI Service:

   • Go to the Power BI service and navigate to your dataset settings.
   • Under Data source credentials, select OAuth2 and provide the client ID, client secret, and tenant ID.
   • Set up the scheduled refresh as required.