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

[amien]

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

[jppp]

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]

@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]

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]

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]

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

[Anonymous]

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.

[amien]

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.