Data & Intelligence

Create a React app that shows PowerBI data using REST

Pbireact

Intro

Many times a client wants some sort of customized view of the data they have in PowerBI. The truth is, as powerful as it is PowerBI, like all BI tools, offer limited customizability. However, PowerBI comes with a powerful RESTful interface that we can use to show the underlying data however we wish. This has the benefit of using PowerBI’s caching, aggregation engine, and many other unique enhancements and insights to the data in our own custom interface. This can be anything from a webapp to a mobile app to specific hardware. In this quick post I’ll show you the basics of using a PowerBI dataset in a custom webapp via REST.

Requirements

  • Access to an Azure subscription (you can get one for free)
  • Access to PowerBI
  • A Github account (it’s free)
  • Visual Studio Code
  • NodeJS

Setup

React Application

We will be cloning a repository, you do not have to manually create the react app at this point.

VS code Extensions

Within VS code install both the Github extension and the Azure Static Web Apps extension. Once both are installed, we will link github to vs code. This guide shows how to link the account, however you don’t need to create a repository just yet as that will be done in a later stage.

Creating the webapp

Now, from the Azure extension, we will select the “create new webapp” button. This should walk you through the motions of creating the application in azure as well as creating a new repository in Github.
Once this is done, you can go to source control and create an empty commit. This will publish your webapp to Azure.
If you go to your Azure portal under static webapps, you should see the url for your webapp.

Getting PowerBI ready

Creating a dataset

For this purpose, we’re going to create a small dataset in PowerBI with some data that we will later aggregate in order to show some KPIs.

The Dataset

I created an excel sheet with some sample data.
Year Sales Customers
2022 20000 120
2021 25000 130
2020 21000 150
I imported the dataset into PowerBI and grabbed the ID from the URL as we’re going to need it later.

Enabling REST

In order to use the PowerBI REST apis, we will need to enable it first. We do this by registering the app with Azure. Here is a guide on how to do this. We will use the embed for my organization option and once you’re done, you should have an app id and secret. When selecting the APIs that will be available, you can select Read all reports and Read all datasets.

Adding Endpoints

This would have created an app registration for you in Azure. Go into your apps (under Home -> App Registrations) and you should see it there. Add the endpoints for testing your app. Ultimately, you will be adding your webapp’s final domain to it.

Building the App

Sample App

For the purposes of this demo I used a sample application that already uses the Azure MSAL node modules. You can see the app here: https://github.com/AzureAD/microsoft-authentication-library-for-js/tree/dev/samples/msal-react-samples/react-router-sample. What I did was clone the app and then publish it to my github repository.

Authentication configuration

The authConfig.js file contains information about the authentication. Modify the Scopes in line 57 to include the following:
["User.Read", "Mail.Read", "https://analysis.windows.net/powerbi/api/Dataset.Read.All"]
Add the PowerBI endpoint to the protected resources.
pbiDataset: {
    endpoint: "https://api.powerbi.com/v1.0/myorg/datasets",
    scopes: ["https://analysis.windows.net/powerbi/api/Dataset.Read.All"]
}

Adding a PowerBI tab.

The above application has a tab named Tenant. It makes use of the REST API through fetch. We will duplicate this and modify the fetch.js file a bit to handle the POST requests that PowerBI needs to make.

Modifying fetch.js

Go to src/fetch.js of your sample application and modify the file as such:
/*
 * Copyright (c) Microsoft Corporation. All rights reserved.
 * Licensed under the MIT License.
 */

/**
 * Makes a GET request using authorization header. For more, visit:
 * https://tools.ietf.org/html/rfc6750
 * @param {string} accessToken 
 * @param {string} apiEndpoint
 * @param {string} method
 * @param {json} body
 */
export const callApiWithToken = async(accessToken, apiEndpoint, method = "GET", headers = new Headers(), body) => {
    //const headers = new Headers();
    const bearer = `Bearer ${accessToken}`;

    headers.append("Authorization", bearer);

    const options = {
        method: method,
        headers: headers,
        body: JSON.stringify(body)
    };

    return fetch(apiEndpoint, options)
        .then(response => response.json())
        .catch(error => console.log(error));
}
By doing this, we’re adding the capability to modify the method, the headers, and submit a body.

Creating a KPI tab

Go to the Tenant.jsx file, duplicate it, and rename it to KPIs.jsx. We will be modifying it as below. The modifications include calls to the newly configured PowerBI endpoints, creating KPI boxes from the PowerBI data, and coloring the boxes.
import { useEffect, useState } from "react";

import { MsalAuthenticationTemplate, useMsal, useAccount } from "@azure/msal-react";
import { InteractionType, EventType } from "@azure/msal-browser";

import { loginRequest, protectedResources } from "../authConfig";
import { callApiWithToken } from "../fetch";
import Button from "react-bootstrap/Button";
import Card from "react-bootstrap/Card";
import Row from "react-bootstrap/Row";
import Col from "react-bootstrap/Col";

const body = {
    "queries": [
        {
            "query": "EVALUATE ALL(Sheet1)"
        }
    ]
}

const bgColors = ["#219ebc","#023047","#fb8500"];

const KPIContent = () => {

    /**
     * useMsal is hook that returns the PublicClientApplication instance, 
     * an array of all accounts currently signed in and an inProgress value 
     * that tells you what msal is currently doing. For more, visit: 
     * https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-react/docs/hooks.md
     */
    const { instance, accounts, inProgress } = useMsal();
    const account = useAccount(accounts[0] || {});
    const [datasetData, setDatasetData] = useState(null);



    useEffect(() => {


        /**
         * In order to get the direct response from calling acquireTokenRedirect() API, register an event
         * and listen for ACQUIRE_TOKEN_SUCCESS. Make sure to remove the event once component unmounts. For more, 
         * visit: https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-react/docs/events.md
         */

        // This will be run on component mount
        const callbackId = instance.addEventCallback((message) => {
            // This will be run every time an event is emitted after registering this callback
            if (message.eventType === EventType.ACQUIRE_TOKEN_SUCCESS) {
                const response = message.payload;
                const headers = new Headers();
                headers.append("Content-Type", "application/json");
                // Do something with the response
                callApiWithToken(response.accessToken, protectedResources.pbiDataset.endpoint + '/ac6ea259-x1e4-4329-8ae4-fc0368fa3668/executeQueries', "POST", headers, body)
                    .then(response => { console.log("requesting"); setDatasetData(response); console.log(response); });
            }
        });


        return () => {
            // This will be run on component unmount
            if (callbackId) {
                instance.removeEventCallback(callbackId);

            }

        }
    }, [account, inProgress, instance]);

    const requestDatasetData = () => {
        instance.acquireTokenRedirect({
            scopes: protectedResources.pbiDataset.scopes,
        }).catch(error => console.log(error))
    }

    return (
        <>
            <Row className="align-items-center mb-5">{datasetData ? datasetData.results[0].tables[0].rows.map((kpi, i) => {
                return <Col className="d-flex justify-content-center" key={i}>
                    <Card style={{ color:"white", width: '18rem', backgroundColor: bgColors[i] }}>
                        <Card.Body>
                            <Card.Title>${kpi["Sheet1[Sales]"]}</Card.Title>
                            <Card.Subtitle className="mb-2 text">Year: {kpi["Sheet1[Year]"]}</Card.Subtitle>
                            <Card.Text>*Sales for {kpi["Sheet1[Customers]"]} customers.</Card.Text>
                        </Card.Body>
                    </Card></Col>
            }) : "nothing yet"}</Row>
            <div className="row justify-content-center">
                <Button variant="secondary" onClick={requestDatasetData}>Request Dataset Data</Button>
            </div>
        </>
    );
};

/**
 * The `MsalAuthenticationTemplate` component will render its children if a user is authenticated 
 * or attempt to sign a user in. Just provide it with the interaction type you would like to use 
 * (redirect or popup) and optionally a [request object](https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-browser/docs/request-response-object.md)
 * to be passed to the login API, a component to display while authentication is in progress or a component to display if an error occurs. For more, visit:
 * https://github.com/AzureAD/microsoft-authentication-library-for-js/blob/dev/lib/msal-react/docs/getting-started.md
 */
export const KPIs = () => {
    const authRequest = {
        ...loginRequest
    };

    return (
        <MsalAuthenticationTemplate
            interactionType={InteractionType.Popup}
            authenticationRequest={authRequest}
        >
            <KPIContent />
        </MsalAuthenticationTemplate>
    )
};

Run your application

Type in npm run build in your VS terminal and a browser should popup with your application. Navigate to the “KPIs” tab that you created and click on “Request Dataset Data”. It should go through the process of authenticating you and you should be able to see your KPIs live fed from PowerBI!

Conclusion

There you have it! Although this was a simple example, you can easily see how we’re able to fully manipulate the way we want to show PowerBI’s data. This can be used in a number of applications, not just web. Can you think of any other applications? Do you find this useful? Reach out to me on LinkedIn!
About the Author

Roberto is an engineer (M.S.) with a passion for automation, data-driven solutions, and technology in general. He is almost always talking about something new in the technology world and enjoys learning about it. He's also done some pretty interesting stuff both personal and professional. Currently, he develops solutions for all sorts of clients at Perficient, Inc. His strongest competency is Analytics and Business Intelligence (MicroStrategy, Tableau, PowerBI), but has experience designing a plenitude of solutions and integrations ranging from front-end and client-facing to back-end, transactional and server-side. Prior to Perficient, He worked at MicroStrategy, Inc. where he helped make the product better by guiding his team through performance and reliability testing as well as dealing with escalated customer issues. Before that he worked as a project engineer and web developer.

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.