Incremental set up for table

[ddfreedie]

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

[lbendlin · Accepted solution]

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.