Forum Discussion
Helper IVDaily Incremental Refresh with Delta Capture and Reporting
Hey folks,
I need to design a process that stages and appends a daily CSV file that is a snapshot of a table in a database (no direct connection allowed). This process needs to identify if any records that changed between each day, and then make that information available to the team.
For example, if on Monday, record 1 changed valueA from "Low" to "High" then that record needs to be flagged for review in a sub-process that uses the output of this report as an input. I.e., if a record shows up on this report, then it gets manually entered into a tracking tool to investigate.
I was thinking of staging this data in a folder in SharePoint, having the dba automate an e-mail delivery of the CSV daily an then having PowerAutomate save the file into the target folder with a file name DD-MM-YYYY.CSV. And then using the PowerBI server to do an incremental refresh.
I think I can figure out the incremental refresh, but I'm not sure how I would identify and flag records that changed from day to day. Do you have any suggestions?
Day 1 Snapshot - Export table example
| ID | Monitored_Field |
| 1 | Low |
| 2 | Moderate |
| 3 | High |
The change happens in real life and ID1 escalates to "Moderate" and ID2 escalates to "High" or even "High to "Low". Items moving to High or from High are the ones I'd want to have further investigated.
Day 2 Snapshot - Export table example
| ID | Monitored_Field |
| 1 | Moderate |
| 2 | High |
| 3 | Low |
So the output report would show Record 2 and Record 3 and if it's possible to show what the original value was and what it changed to that would be awesome. I just cannot figure out how to do it.
30 Replies
- lbendlin
Super User
This is only an incremental refresh in terms of the file date. What you are doing in fact is storing a bunch of snapshots of (nearly) the same data.
You need to identify a column in your CSV files that can serve as unique transaction key (maybe your ID column), and then you can use that in combination with the file date to run your change tracking for inserts/updates/deletes.
Depending on how big the CSVs are and how many you keep for your comparison you may not even need the incremental refresh.
gemcityzachGood morning! I do have unique IDs in each of the table snapshots for every record. When you say use unique key and file date, are you referring to the actual file name "mm-dd-yyyy.csv"? I also add a 'snapshot_date' field to the CSVs in the data model for snapshot selections by the user. I.e., the user can pick their 'version of history' they want to look at.
The CSVs range in file size from 500KiB to 200MiB. The daily files would be 3MiB to 8MiB.
Do you have a method for actually identifying "changed", "new" or "deleted" records over time? I cannot seem to find a good tutorial.
- lbendlin
That seems like a manageable amount of data. I would just ingest the CSVs as is. If you have the file datetime inside the data then that is ok too, but the filename datetime is usually enough.
For the delta processing you can use the standard period over period patterns but also have a look at EXCEPT and INTERSECT.