Sign up with your email address to be the first to know about new publications

Power BI API Dataset Limit

Streaming real-time information to Power BI Service datasets is extremely easy but you need to understand its limitations. In this post we will hit the maximum number of Post Requests per minute which is 120 requests per dataset.

Before we start, one “must do” while you are working with IoT information is to store the raw information while you send it to the application.

You can find the Power BI Rest API limitations here.

If you are experiencing the following errors, you may be hitting these limits:.

  • The remote server returned an error: (503) Server Unavailable.
  • The remote server returned an error: (429)

Requirements:

  • Good Internet Connection
  • Access to Power BI Service
  • Power Shell ISE (Optional)
  • This Script (Optional), which you can copy from below.

Create the dataset with Historic Data Analysis

Checkbox enables the PushStreaming dataset.

 

Copy the following code into Power Shell ISE:.

#Include the Power BI Dataset Url in the following variable
$Endpoint = ""
$NumberofAttempts = 121
$RowId = 1

Do
{
# Define correct timestamp format
$date= $(Get-Date).ToString("yyyy-MM-ddTHH:MM:ss.fffZ")

# Incremental Number
$RowId = $RowId

# Json body request
$payload = @{
"Timestamp" =$date
"RowId" =$RowId
}
# Post the row
Invoke-RestMethod -Method Post -Uri "$Endpoint" -Body (ConvertTo-Json @($payload))

write-host $date"-"$RowId


} While (++$RowId -le $NumberofAttempts)

 

Let’s run the script!

Make it fail and verify our dataset.

 

FAQ

Why does the grid display only 120 rows and the card the value 121?

Both are different datasets behind the scene with different infrastructure, 1 is the push dataset (grid) and the other is the streaming dataset (card). Behind the scenes the architecture is different. More info here.

How can we solve this problem?

There are many ways to solve it:

  • Wait before you send the following request (in Power Shell “Start-Sleep -m 500” will wait 500 milliseconds)
  • Send multiple rows in a single request (up to 10.000) but be aware of the limitation per hour (1.000.000).
  • Aggregate/Summarize the information before sending it to Power BI with time windows (must do!).

 

I hope you have enjoyed the post and found it helpful.

2 Responses
  • Rafal
    03 . 10 . 2019

    Hey,
    how to properly aggregate data with time windows? I have dataset that is being updated per category in different time frames.
    I would like to show the latest data per category. Have you found solution of doing it?

    • David Alzamendi
      15 . 10 . 2019

      Hi Rafal,

      If you are working with IoT scenarios where you send many rows per second, you don’t usually want to send the most granular level (all the rows) information to the Power BI dataset. Therefore, you will need to perform some sort of aggregation before sending the rows to Power BI. Some tools like Azure Stream Analytics or many database systems, provide these time window functions so you can easily use them.

      If you are sending only a few rows per second, you will be able to achieve it by using a PushStreaming dataset and applying some filters in Power BI Desktop.

      Let me know which technology you are using to push the information and I can provide further information!

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *