Forum Discussion
Refresh only update new data from Oracle database with SQL import
This is not a new topic. I have read alot but still cannot find a proper answer for this. I hope we can have an easy solution to follow here.
I have a table in Oracle database which include millions rows. I use Import Query with some SQL statements (include JOIN and PIVOT) to get exactly what I need to Power BI. My data is collected daily (as Snapshotdate) and imported to Oracle every few days (1 ~ 4 days), mostly daily.
The problem is, by the time, there are more and more data in the table and it takes time to refresh them all while the old data (past Snapshotdate) rarely or never get updated. So I just want to update latest Snapshotdates data (which is not existed in Power BI's table) and append them to the current table.
What is the easy way to do it?
Look forward to hearing from you.
Thanks
2 Replies
Anonymous , Have you tried the incremental Laod or dax append method ?
DAX append
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/I have read some:
- Incremental refresh for SQL source requires removing all SQL statement query and import data from database table. But as I mentioned I don't just load a table from Oracle, I use SQL (JOIN/PIVOT/UNION) to create a new one based on Oracle database. So I can't finish step 4 of this instruction: https://www.mssqltips.com/sqlservertip/6476/power-bi-incremental-refresh-for-sql-sources/
- About Dax append (Union), you have to import both old data and new data then Union them. It will double the data, which is millions rows, and make the PBI file become larger.
I'm just thinking about create a new table in Oracle dtb and just load that table to PBI then do Incremental refresh. But there is a problem: I have to transform and load data to the new table whenever data of new Snapshotdate is loaded to the original table, it takes time and consume more space in database as well.
So is there any possible way to do this?
