Setup Incremental refresh on Many to Many relationship table

[Anonymous]

Hi All,

I have model where relationship between DIM & FACT is Many to Many , I have used bridge table to reslove m:m issue converting it to M:1:M , but i had to bring all the columns from FACT to bridge table and hidden the original fact table , because users were getting report errors  when they pulls column from DIM & FACT and they are not using aggregation in report to resolve the issue.

Bridge table is huge and need to implement incremental refresh but i am getting below error.  

Problem: Refresh fails because of duplicate values

Cause: Post dates have changed

Is it not possible to implement incremental refresh on m:m table , though we use bridge table.

Thanks in Advance.

[hsantosh]

Hi All
I am facing some issue with incremental refresh,
I have data of 50k in fact table, when i apply icremental refresh its coming 25 lakhs.
Fact table having multiple joins with different tables,
from each table we are fetching updated date and again among all which is max date taking that , on that incremental refresh is apllied, please any one help me wnything wrong i am doing, why that much duplicate records i am getting

[Anonymous]

Hi @Anonymous 

1.Check that when you set the rangestart and rangend paramaters the date should be [date]>=rangestart and [date]<rangend (cannot equal to rangend).

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#filter-data

2.The error message in your incremental refresh, it means that, because of the post date change, the unique id appears in another partition so that it return the duplicate values errors, you can refer to the following link about the error.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-troubleshoot#problem-refresh-fails-because-of-duplicate-values

So it needs to use SSMS to refresh all partitions from the point where the change is located up to the current refresh partition.

And you can also refer to the similar case.

Incremental Refresh - Duplicates or Missing Update... - Microsoft Fabric Community

Solved: Incremental Refresh - Duplicate Rows for Refresh W... - Microsoft Fabric Community

Solved: Re: Incremental refresh failing due duplicate reco... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

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

[amitchandak]

@Anonymous , what issue are you getting for the relation M-M when refreshing data?

Ideally, we do not want many to many, but it should work.

[Anonymous · Accepted solution]

@amitchandak  But the below statement from Microsoft docs contradicts with incremental refresh works for many to many , because it expects to have unique values in 1 side of the table , which mean many to many will always fails the above condition.

[Anonymous]

Below is the error i am getting and basically it has SCD type 2 fact table in db, but to resolve m:m in power bi , i am bringing only active recrods ans converting as 1: M and setting up incremental refresh on this fact table.