how to transfer records from MDS to another table then delete certain records from MDS without deleting them from the transferred table

Question

I'm using Master Data Service (Enterprise version for SQLServer2016) to collect data from different departments, so each group will have one or many attribute groups with update access. To indicate the cycle completion an attribute "Cycle Status" is set with value "Yes". Due to many business rule and multiple attributes groups the performance is getting very slow, so what I'm thinking of is: 1.transfering all records that have completed the cycle to a table then 2.delete those records from Master Data Service but this will not delete them from the Table(Point_1). is there any clue how to do it?, I'm aware of having a subscription view in MDS but don't know if this can serve my case? and how?

Answer 1

E.IK,

1st Approach: Conditional Evaluation of business rules

You can look at adding a Condition to all your Business Rules to only evaluate business rules

IF Cycle Status = "No"

This might reduce the performance impact you're seeing now (not sure). This can can be done by (in Edit Business Rule screen)

Conditions > Value Comparison > is equal to > Select attribute:="Cycle Status" > Attribute Value / Attribute := "No"

This way you wouldn't have to move your data.

2nd Approach: Move data to another (physical) table

• This approach would entail you to create a separate table (physical DB Table, Not Entity, so as to avoid MDS overheads) either in the same MDS DB or another DB.
• Then create an SP that will Insert rows with Cycle Status = "yes" to the 2nd table & mark them as Deleted in the MDS Entity.
• You may want to create an SQL Agent Job that can be scheduled to run this SP at whatever frequency you like (Daily, Weekly.. etc).
• The challenges with this approach would be that your users will no longer be able to easily access this Archived data.

Another alternative approach...

would be to create a separate MDS Model - "Archive". Create a similar entity there (like a mirror) & rather than moving Closed records to a Db Table, add them to the mirror entity in the Archive model. You may want to give all users only Read-Only access to this new entity.