14 Feb 2024 Unlocking the Potential of the Power BI REST API

Imagine you need to query which users have access to a specific dataset or report, or you want to periodically refresh a dataset through your application. These are just two of the many features that the Power BI REST API offers, and being an API, it can be seamlessly integrated into web developments, allowing you to interact with Power BI objects quickly and easily.

Here at ClearPeaks, we’re always up-to-date with the latest technology news, and we’ve already looked at how to use Power BI in web applications in previous articles: Power BI Chatbot and All-in-one Analytics & BI Portal with Observation Deck.

In this article, we’ll present a specific use case to meet the needs of a customer: aligning their application permissions with Power BI reports permissions. But before diving into the details, let’s explore what this API, developed by Microsoft, can offer us!

Use Cases

We all know those typical scenarios like retrieving user access to a report or refreshing a dataset, but the Power BI REST API opens up a whole new world of possibilities, some of which we’ve already covered in earlier blog posts. Today we’re going to offer you an overview of the most common use cases:

• User Access Management: You can use the Power BI REST API to retrieve information about which users or groups have access to specific datasets, reports or workspaces, an essential functionality for managing permissions and ensuring that the right people have access to the right data.
• Automated Data Refresh: By using the API, you can schedule and trigger data refreshes for your datasets. This feature is invaluable for maintaining data freshness in near-real time or according to a predetermined schedule, all managed via your application.
• Custom Reporting Solutions: The Power BI REST API allows you to create custom reporting solutions tailored to an organisation’s specific needs, enabling the design of unique visualisations, custom dashboards, and automated report generation.
• Monitoring and Auditing: You can use the API to monitor usage, track user interactions, and perform auditing. This is crucial for understanding how reports and datasets are being used as well as for compliance purposes.
• Others: With this being an official Microsoft API, there are regular updates, new features, performance improvements, and bug fixes.

Advantages

• Integration Flexibility: The Power BI REST API can be integrated into any web development stack, making it highly versatile for various programming languages and platforms.
• Real-time Data: It keeps data up-to-date with real-time or scheduled refreshes, ensuring that users always have access to the latest information.
• Security and Permissions: It provides robust security features and allows you to manage permissions effectively, ensuring that data is only accessible to authorised people.
• Customisation: The API allows you to customise the user experience, covering branding, filtering and interactions, to match the look and feel of your application.
• Scalability: The Power BI REST API can scale in tandem with the growth of the application, handling increased data volume and user demands.
• Analytics and Insights: You can extract valuable insights from usage data and performance metrics collected through the API, which helps you make data-driven decisions to improve your application.
• Seamless User Experience: Integrating Power BI reports directly into your application provides a seamless user experience, allowing users to access comprehensive insights without the need to navigate away, streamlining their workflow and enhancing overall efficiency.

Incorporating the Power BI REST API into web development projects means a whole new ball game for data management, visualisation and reporting, effectively enhancing the capabilities of your applications.

Authentication

Prerequisites

Before diving into the code, make sure you have the following prerequisites in place:

1. Power BI Account: You need a Power BI account and the required permissions to access the report you wish to query. In our case we created a Service Principal* to get access to the admin endpoints of the Power BI REST API. You can see how to do so in the relevant Microsoft documentation.

2. Azure Active Directory Application: You also have to register an Azure Active Directory (now Microsoft Entra ID) application to obtain the required authentication credentials (client ID and client secret) to access the Power BI REST API; see the Microsoft documentation.

* A service principal is a security entity that represents an application or service, enabling its authentication and authorisation in computer systems. It helps applications interact securely with protected resources without requiring user credentials.

Get the Access Token

Once you have completed the above steps, you’ll need to call the endpoint that authenticates you and returns the necessary access token to access any endpoint of the Power BI REST API.

In this example, we will use JavaScript and Microsoft’s MSAL library. We have used this library on previous occasions, for example, to embed a Power BI report into our website.

Example code:

let authenticateServicePrincipal = async () => {
  try {
    // 1. Create MSAL Configuration
    const msalConfig = {
      auth: {
        clientId: pbiClientIdPbi,
        authority: `https://login.microsoftonline.com/${tenantIdPbi}`,
        clientSecret: pbiClientSecretPbi,
      },
    };

    // 2. Initialize MSAL Client Application
    const cca = new msal.ConfidentialClientApplication(msalConfig);

    // 3. Request access token
    const clientCredentialRequest = { scopes: [AzureScopes.PowerBiDefault] };
    const authenticationResult = await    cca.acquireTokenByClientCredential(clientCredentialRequest);

    return authenticationResult;
  } catch (error) {
    logger.error('ERROR authenticating Service Principal', error);
  }
}; 

1. Create MSAL Configuration: This involves initialising an object named msalConfig with essential authentication details such as the client ID, authority URL, and client secret necessary for authentication with Azure Active Directory (now Microsoft Entra ID).
2. Initialise MSAL Client Application: The msal.ConfidentialClientApplication is initialised with the previously defined msalConfig, thus preparing the Microsoft Authentication Library client for interaction with Azure Active Directory.
3. Request Access Token: This step involves acquiring an access token with client credentials. A clientCredentialRequest object is created, specifying the required scopes that usually indicate the necessary permissions. Using the MSAL library, the code obtains an access token. The obtained authenticationResult includes the access token and related information.

Real-World Use Case: Who Has Access to the Report?

Having authenticated, let’s explore a real use case for a ClearPeaks customer: identifying users with report access.

Endpoint:

GET https://api.powerbi.com/v1.0/myorg/admin/reports/{reportId}/users 

We make a GET request to the URL above, specifying the reportId parameter as the identifier of the report for which we want access information. To call this endpoint, we have to pass the access token as “Authorization” in the request header, specifying that the type is a bearer token.

Response sample:

{
    "value": [
        {
            "displayName": "John Nick",
            "emailAddress": "john@contoso.com",
            "reportUserAccessRight": "Owner",
            "identifier": "john@contoso.com",
            "graphId": "3fadb5e2-121c-4a8f-aeac-416e38c66756",
            "principalType": "User"
        }
    ]
}

What does each field mean?

• displayName: Username, «John Nick» in this example.
• emailAddress: User email address, «john@contoso.com» in this example.
• reportUserAccessRight: User permission level.
• identifier: Typically a unique user identifier; in this case, it is also «john@contoso.com,» matching the email address.
• graphId: A unique identifier associated with the user, which is «3fadb6e4-130c-4a8f-aeac-416e38b66756» in this example.
• principalType: This field specifies the type of principal, in this case «User», indicating that this entry represents a user (for example, another specific type is “Group”).

Finally, our real case:

We used this endpoint for a specific customer to allow access to reports on a corporate website, conditional on the user having the necessary permissions. The call demonstrates how the endpoint’s response informs us of multiple users’ ability to view the report.

Consequently, if a user is included in that list, they are granted access to view the report’s details on the corporate website.

Conclusion

Integration with Power BI through its REST API offers the ability to present advanced, customised visualisations in real time, with interactive features and security control. It is also compatible with multiple platforms and enables a personalised user experience.

This scalable solution with advanced analytics facilitates decision-making without changing context, enables user tracking, and can lead to the development of specific analytic applications.

Once again, ClearPeaks has seamlessly integrated an external API tailored to a customer’s particular needs, echoing our previous achievements with SharePoint and other platforms.

Whether it’s querying user access, automating dataset refreshes, or creating reporting solutions, ClearPeaks is your expert partner in navigating the complexities of the Power BI REST API. If you need to harness the full capabilities of Power BI and transform your data visualisation and reporting processes, contact us today!