Refesh unique constraint and RI issue

[mhamilt1]

Hi

We have a dimension table that we wish to partition but we do not have a good date to use so we are adding a min first transaction date. We are doing this so we can improve our refresh time.

We have included a 24 month rolling window policy.

The join to fact table is one to many.

Now when I refresh I get a duplicate error.

This is because the date must.change and add the same dimension row into 2 partitions. I am expecting the old date to drop out of the table but I think the new data may be added before the old data outside the rolling window is deleted. Could this be true? Is there anyway to delete the old data before adding the new so that I avoid this duplicate ID in the dimension side of the many to one join?

Because the join is one to many

[lbendlin · Accepted solution]

Yes, you can use your tool of choice to refresh individual partitions (including emptying them)  in your desired sequence.  Can be SSMS, Powershell, etc.  Anything that can run XMLA queries.