cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amien Member
Member

Using Power BI REST APIs to not only push new records, but also update existing records possible?

I read this artical:

 

https://docs.microsoft.com/en-us/power-bi/service-real-time-streaming

 

when i have a table loaded with millions of records .. it's possible to add new records to this table, but is it also possible to UPDATE? records?

 

So lets say i want to update the amount of 3 invoices without loading all the data again, is this possible with the API? or can only add date? Or perhaps is delete and add possible instead of update?

 

If its not possible, what's the best approach in powerBI if i have million of records and the table should be up-to-date every 10 minutes with it's source and the changes in the table is limited (few records).

 

Thanks in advanced

7 REPLIES 7
jppp
Advisor

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

The Power BI REST API can indeed only add new rows or delete all rows. There is no update available.

Also a push dataset can only contain either maximum 200.000 rows before rows are dropped from the dataset or a total of 5.000.000 rows without automatically deleting records: https://msdn.microsoft.com/en-us/library/dn950053.aspx

 

Given your scenario, updating invoices, this is not typical for REST API's. Probably refreshing a dataset containing your invoices is a better solution. But if you want to update a row in an push dataset: the only option is to add two records: one that neutralizes the old record and one that is the new version.

 

-JP

amien Member
Member

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

@jppp 

 

Thanks for your reply and the limitation url.

 

Do you know if the limitation of 5mil also applies to a on-premises PowerBI solution?

 

I know REST API is not typical for doing this. But refreshing the complete dataset for just 3 new invoices (need to update a few times a hour), sounds also not very efficient. 

 

Thanks for the contribution.

jppp
Advisor

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

The 5mil row limit is only for datasets created/available for the REST API. So only push or streaming datasets.

 

Regular datasets do not have a row limit and can be schedules. Depending on your source system this can be configured to refresh every 15 minutes (DirectQuery sources).

 

-JP

amien Member
Member

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

my hard business requirement is less then 10 minutes. a change in the production system should be visibile within 10 minutes in PowerBI

 

i don't have 5 mil record changes in a week. when i do a full refresh every sunday or every night, is it true that than i will not hit the 5 mil limit? in other words; will this 5 mil limit be reset after every full reload?

jppp
Advisor

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

Oke, then either go with the push datasets and 'negative' the old record and insert the new one. And indeed regular 'srat over again' with your dataset.

 

But probably (I don't know your sourcesystem) DirectQuery is the easiest and cheapest way to achieve this. The cache is only for performance (minimal of 15 minutes). But is you refreshes the report after initial showing, you can refresh the data directly from the source.

 

Also there are some custom tools that can also deliver different scheduling option.

 

-JP

amien Member
Member

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

Thanks for your feedback jppp.. very helpfull

 

The sourcesystem is Oracle (which is supported by DirectQuery). I understand you can refesh the data directly from the source,

but i doubt (i know i'm sceptical) that you can retreive mil. of records, apply the business logic and push it to the end user, with a good performance (users need to wait for the data comes back).

 

And it's not just 1 fact table. It's also all the dimension tables and hierarchies.

 

Highlighted
roofi00 Regular Visitor
Regular Visitor

Re: Using Power BI REST APIs to not only push new records, but also update existing records possible

Hey @jppp , could you explain more about this method of setting 'negative' old records?

I have similar case where I need to take into report only the latest data.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)