Real-Time Insights: Live Data Push in Power BI

In today’s fast-paced environment, the ability to make real-time data-driven decisions is more crucial than ever. Imagine that you are working in an environment such as a production floor, warehouse, inventory management for restaurants and hotels, cleaning crew in hospitals, etc. Individuals in a supervisory role would benefit from being informed about the key performance metrics in real-time such as low inventory, rooms clean-up, ticket backlogs, etc.

It is also equally important for the Business Intelligence teams to be able to provide such capabilities with as many fewer tools as possible. Power BI, Microsoft’s powerful business intelligence tool, empowers organizations to visualize and analyze data in real-time, providing invaluable insights for proactive decision-making. In this blog, we will explore an example of how to set up the display of real-time data in Power BI using push APIs.

The Set Up

Make sure you have a license for Power BI. Once you do, head over to https://app.powerbi.com for the next steps.

The How-To

This how-to section contains a step-by-step guide to setting up the real-time dashboards. Each section contains screenshots to easily follow along. Where necessary, I will be adding scripts as well; or, feel free to use the ‘trusty’ ChatGPT to write your custom scripts.

Set up the Streaming Dataset

Head on to app.powerbi.com. Within Power BI, create a new workspace or use an existing one. Go to the workspace and click on New > Streaming Dataset.

On the resulting screen, select the API icon.

Set up the new streaming dataset parameters. Give your dataset a unique name. Add necessary column names and select their datatypes. In the example below, I have created three columns, namely, Collection_Agent, Time, and Collection_Amount. Turn On the Historic Data Analysis radio button – this is important; we will come back to this later. Click on Create.

This will bring you to the summary of streaming dataset creation. Here, you will have three options – Raw, cURL, and PowerShell. Click on PowerShell and select the auto-generated PowerShell script containing the API endpoint. We will use that momentarily. Click on Done.

Set up the Report

You should now be in the Workspace you created/used above. Click on the three ellipses next to the Semantic Model (Dataset) and select Create Report. Once the design studio opens up, go ahead and create the report as per your requirements. Pin each visual on the report to an existing or a new dashboard. Do not pin the entire report.

Set up the Data Push

Open up the PowerShell editor of your choice and let’s modify the above generated script a bit to run in a loop. In the interest of the demo, the script below runs every few seconds to generate a dummy record. Please modify this to push data relevant to your situation and environment. You can either run this script in a loop or automate it to run at a specific interval or using webhooks, fire when new data is generated – the choice is yours.

while ($true) {
    # Ankeet / 2024-02-27 / proof-of-concept

    # Capture the current system date/time and assign it to a variable
    $currentDateTime = Get-Date
    
    # Generate a random number between 10 and 20 and assign it to a variable
    $randomNumber = Get-Random -Minimum 10 -Maximum 21
    $randomCollectionAmount = Get-Random -Minimum 100.00 -Maximum 5000.00

    # For DEBUG purposes, let's print the random number and current date/time
    # Write-Host "Random Number: $randomNumber, Current Date/Time: $currentDateTime"

    # push the data for the amount of debt collected
    $endpoint = "https://api.powerbi.com/beta/a7cba70c-09bf-4509-7288b26d30d6/datasets/fff94a01-a3d9-8432a0f323b8/rows?experience=power-bi&key=3xC0gYx%2FP3FQ8hIIe808FX%2BoPgy6LItPXpksdqwO17b2l7v6UdOZbuf2JFRL5TpojALFK64NImQ%3D%3D"
    $payload = @{
        "Collection_Agent" ="Agent_" + $randomNumber.ToString()
        "Time" = $currentDateTime 
        "Collection_Amount" = $randomCollectionAmount
    }
    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

    
    # Sleep for 4 seconds before the next iteration
    $randomSleepTime = Get-Random -Minimum 60 -Maximum 300
    Write-Host "Off to sleep for $randomSleepTime seconds at $currentDateTime."
    Start-Sleep -Seconds $randomSleepTime

}

Once the above script starts executing you should see the dashboard auto-refresh!

Data Cleanup

To clean up data, within the workspace, click on the three ellipses next to the dataset and click on Edit. Turn off the Historic Data Analysis option, click on Done, go back to Edit, and turn it back On.

Additional Resource: https://learn.microsoft.com/en-us/power-bi/connect-data/service-real-time-streaming