Dataflow incremental refresh changing keys

[Anonymous]

Hi there!

I am actually involved in a project where incremental refresh is required. I am working with dataflows:

- Fact table: only 1 date/time column. The rest of the columns are surrogate keys to make the relationship with the dimension tables. This datetime is the column I am using for incremental refresh.

- Dimension tables: consist of surrogate key column and text columns. There is no date on them.

By doing the refresh, it is true that more rows are being added to fact table BUT here is the issue, the surrogate keys for the dimensions are changing.

ID_FACT	NUM_FACT	ID_DATE	ID_DIM FACT
90	DPP000006469	30/06/2019 0:00	8
95	M000007732	30/06/2019 0:00	11

ID_FACT	NUM_FACT	ID_DATE	ID_DIM FACT
90	DPP000006469	30/06/2019 0:00	10
92	M000007732	30/06/2019 0:00	14
97	05370033AFTP0001	31/07/2019 0:00	4
110	B000013418	31/07/2019 0:00	8

Any idea to make incremental refresh without the modification of these keys?

Thanks!!

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, change the configuration time in the incremental refresh to an interval from the current day to July. And turn on the "Delete data change" checkbox. After reconfiguring the incremental refresh, observe whether the results are refreshed correctly.

This function allows you to select the "Date/Time" column to identify and refresh only the date of the data change. This action assumes that the columns normally used for auditing exist in the source system. The maximum value of this column will be evaluated for each period in the incremental range. If the data has not changed since the last refresh, there is no need to refresh the time period.

For more details, you can read related document link:

Using incremental refresh with dataflows | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and operation screenshoots. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.