Forum Discussion
Incremental set up for table
Hi
Have questions on how i an set up incremental loads in powerbi for different types of tables
Table 1:
An SCD 2 type of table which will have start date and end date. We can set up incremental based on start date and change capture on end date. But if i have a view on top of this table which will only give the active records i.e. enddate=NULL, how can we implement incremental for that.
Table 2:
It snapshots the data on multiple time intervals (Week, Month, Quarter, Year) and no other date column. Snapshots are taken on the last day of every interval whatever is the situation on that date.
eg.,
BegDate EndDate Name Type
20200101 20200107 xxx W
20200108 20200114 xxx W
20200115 20200121 zzz W
20200122 20200128 yyy W
20200101 20200131 yyy M
20200101 20200331 yyy Q
20200101 20201231 yyy Y
Table 3:
Table with only one date column Inserted/Updated date. This column will get the date, whenever the record is inserted/updated. Rather than 2 different columns as inserteddate and updateddate, it is only one column.
The above tables might not be realistic in few cases, but just thinking of we can implement for such tables or is there no possibility at all.
Thanks..
Incremental refresh works best when you have immutable data, for example sensor readings from an IOT device.
If your data is mutable then the next best option is a "Last Modified"date in your data, and a large enough update window to capture the majority of the deletions as well.
After that you start to grasp at straws, and have to use meta data for the timestamps - for example the last modified date of your source file.
for your Table 1 you could treat null as "tomorrow"
for table 2 you can convert your various snapshot meta data into an equivalent datetime value (like "last second if covered interval")
Table 3 is relatively simple - but you need to be smart about the selected update interval as described above.
1 Reply
Incremental refresh works best when you have immutable data, for example sensor readings from an IOT device.
If your data is mutable then the next best option is a "Last Modified"date in your data, and a large enough update window to capture the majority of the deletions as well.
After that you start to grasp at straws, and have to use meta data for the timestamps - for example the last modified date of your source file.
for your Table 1 you could treat null as "tomorrow"
for table 2 you can convert your various snapshot meta data into an equivalent datetime value (like "last second if covered interval")
Table 3 is relatively simple - but you need to be smart about the selected update interval as described above.