Incremental refresh on SCD type 2 tables in Power BI

[SC_Mohan128256]

Greetings community.

Currently I’m working on a project where I have a SCD type 2 table which has more than 100+ millions of records of data.
The table structure looks as below. Because of data security issue I have shown only few records.

In the model, pbi_eligmember is the main table which used for every calculations and to be precise, BIEFFECTIVEDATE and BIENDDATE are the two columns which been used.

You can see the image below for reference.

Now the problem is,

1. the table size is huge (100+Millions of records)
2. It is a SCD type 2 table - new data getting added and the older date records may have an update at any time.
3. As the number of calculations are increasing the performance of the report is low being the centered table all the filters and calculations flows through this.
4. I cannot convert this table generating DATE column with the list of dates between for each GUID's BIEFFECTIVEDATE and BIENDDATE. because the table is already huge and if i do this then it is reaching to 500+Millions of records as the date ranges are long.

How to configure incremental refresh policies on SCD Type 2 tables ensuring the data refresh process correctly identifies and processes new and updated records which helps to managing historical data with effective date ranges.

Please help.

Thanks,

Mohan V.

[lbendlin]

Incremental Refresh and SCD type 2 are largely incompatible as you are creating row duplicates (albeit with the changes).  You would have to create a process that filters out obsoleted rows and refreshes historical partitions accordingly.  Be clear that that WILL remove the history (which was the main reason for SCD 2 in the first place).