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).
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.
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?
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.