Forum Discussion
Incremental loading rolling 24 hours on database update time
Hi, is it possible to keep data for a rolling 24 hours and load new data every two hours (as the relevent old data is removed from the service)?
One of our API's makes a call to one of our internal systems on a ~2 hour basis, extracting the most relevant information for say 10% of our requests (eg the next 200 combinations of city pairs out of a total ranked 2,000) and places the results in a Oracle database (200 city pairs equals output of around 12m records).
Our Oracle database is emptied everyday at midnight meaning that we dont get the vast majority of the 'city pairs' until later in the day when everyone has gone home (because the API starts at city pair '1' and goes down the list).
I want to keep the data for say 22 hours, and load the new data as it comes in using Inremental loading, whilst dropping off the previous days 1-200 city pairs when todays pull of 1-200 city pairs is added.
How do I go about this?
Sorry if that is a bit confusing, but please let me know what more information you need to help me
Michael
10 Replies
- Hi there
With Power BI you only get 8 data refreshes a day. So if you refresh every 2 hours, you could cover 16 hours of the day.
I would then suggest that you read the data from your Oracle table into Power BI, which will refresh the entire dataset every time.
Currently there is only Incremental Loading in Power BI Premium. And based on your data I think that you could possibly refresh the entire dataset on every load.GilbertQ if I just load the entire table every time then first load of the next day will erase the entire previous days records.
This will mean the last ~1,800 city pairs records (from the last ~22 hours) will be removed from Power BI dataset when I upload the new data (from the Oracle table containing the first run of 200 city pairs)
- Hi there, ok I now understand what you are saying.
Currently Power BI will refresh all the data from a table.
So what I would suggest doing is to keep a rolling 24 hours of data in another table in Oracle. And then Power BI can simply look at this 24 hour rolling table and refresh off that.
That is how I would get it done.
