MDS validation methods don’t validate all members for attribute uniqueness correctly. Is this a bug with SQL 2012 Master Data Services?

Question

How to replicate:

1. Create empty model “TestModel”.
2. Create an entity in this model as “TestEntity”. Add text attribute named as “Identifier”.  Now this entity will have 3 attributes i.e., Code, Name and Identifier.
3. Create a business rule for TestEntity to have unique identifier attribute and publish.
4. Add some test members to TestEntity as shown in attached image.

1. Now change the member D’s identifier value from 400 to 300.  This will change the D’s status to validation failed.
2. Our goal here is to have member C’s validation status also to become validation failed.  We tried following validation methods without success:

            

Validation   Method	Does MDS validate   this business rule as expected?	Comments
SP - udpValidateModel	No	This action   validates all members of all entities of a given model version.
MDS UI –   Validate Version	No	This action   validates all members of all entities of a given model version.
MDS UI –   Apply Rules	Yes   (sometimes but not always)	This action   validates whatever rows (entity members) are visible on screen.
MDS UI –   Apply to All	No	This action   validates all members of currently selected entity.

P.S. - This has been tested on SQL 2012 Master Data Services  + SP1.

Answer 1

Looks like there is an year old thread similar to this issue.  How come there is no enough discussion on this major product flaw?

http://social.msdn.microsoft.com/Forums/zh/sqldenmds/thread/489003be-9b6e-4a9a-91c9-9f22c918b93b

Other observation on random nature of 3rd validation method in my table above ( "MDS UI –   Apply Rules" action):

MDS business rule gets applied on member only if current validation staus is "Failed" not if it is "Passed".

Answer 2

Hi

is there still this issue with SQL SERVER 2012 SP1 ?

http://www.microsoft.com/en-us/download/details.aspx?id=35575

and

http://www.microsoft.com/fr-fr/download/details.aspx?id=35580

and with API code ?

I will try to test asap

  // Creates a member that causes a validation issue, processes the validation and gets the list of validation issues.
        private static void GetBRValidationIssue(string modelName, string entityName, string ruleName, string versionName)
        {
            try
            {
                // Set Model, Entity, and Version objects.
                Identifier modelId = new Identifier { Name = modelName };
                Identifier entityId = new Identifier { Name = entityName };
                Identifier versionId = new Identifier { Name = versionName };

                // Create the request object.
                MDSTestService.BusinessRulesCreateRequest ruleCreateRequest = new MDSTestService.BusinessRulesCreateRequest();
                ruleCreateRequest.ReturnCreatedIdentifiers = true;
                ruleCreateRequest.BusinessRuleSet = new BusinessRules();

                // Create the request object.
                MDSTestService.EntityMembersCreateRequest memberCreateRequest = new MDSTestService.EntityMembersCreateRequest();
                memberCreateRequest.Members = new EntityMembers();
                memberCreateRequest.Members.ModelId = modelId;
                memberCreateRequest.Members.EntityId = entityId;
                memberCreateRequest.Members.VersionId = versionId;
                memberCreateRequest.Members.MemberType = MemberType.Leaf;
                Member aMember = new Member();
                aMember.MemberId = new MemberIdentifier();
                aMember.MemberId.Code = "Test12";
                aMember.MemberId.Name = "AA";
                aMember.MemberId.MemberType = MemberType.Leaf;
                memberCreateRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
                memberCreateRequest.Members.Members.Add(aMember);

                // Add a member that triggers the validation error.
                MDSTestService.EntityMembersCreateResponse memberCreateResponse = clientProxy.EntityMembersCreate(memberCreateRequest);
                HandleOperationErrors(memberCreateResponse.OperationResult);

                // Create the request object.
                MDSTestService.ValidationProcessRequest validationProcessRequest = new MDSTestService.ValidationProcessRequest();
                validationProcessRequest.ValidationProcessCriteria = new ValidationProcessCriteria();
                validationProcessRequest.ValidationProcessCriteria.ModelId = modelId;
                validationProcessRequest.ValidationProcessCriteria.EntityId = entityId;
                validationProcessRequest.ValidationProcessCriteria.VersionId = versionId;
                validationProcessRequest.ValidationProcessOptions = new ValidationProcessOptions();
                validationProcessRequest.ValidationProcessOptions.ReturnValidationResults = true;

                // Process validation and get a validation issue.
                MDSTestService.ValidationProcessResponse validationProcessResponse = clientProxy.ValidationProcess(validationProcessRequest);
                HandleOperationErrors(validationProcessResponse.OperationResult);

                // Show the validation issue's description. 
                if (validationProcessResponse.ValidationIssueList.Count > 0)
                {
                    ValidationIssue validationIssue = validationProcessResponse.ValidationIssueList[0];
                    Console.WriteLine("Validation issue: " + validationIssue.Description);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex);
            }

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 3

Yes, issue still exists even after installing SP1.

Answer 4

This is a major flaw in the core functionality of the application.  How come this issue was raised a year ago and still no relief provided either in RTM or SP1 + CUs?

Answer 5

I contacted the product team, and they have decided that the current behavior is preferable to the proposed change.  (And they apologize for not updating this thread after they took that decision.)  Here's what they said:

"After looking at this issue we have decided not to change the existing behavior.  The existing behavior guarantees that there can be only on validated entity member with a the set of attributes marked as unique. 

The problem with invalidating all the entity members on entry of a duplicate entity member is that it will have unintended consequences.  If a user enters an entity member that fails a unique business rule and that invalidates another, already-validated entity member that could trigger emails and other external actions, and prevent the valid entity member from being exported to downstream systems.   Instead, for entities with a uniqueness business rule, you can ensure that no duplicates are visible in downstream systems by only exporting entity members that have passed business rules validation."

So, effectively, uniqueness is only guaranteed among valid entity members.  You can have any number of duplicates among the invalid entity members, but at most one validated one.   And this aligns with the MDS philosophy that business rules aren't there to prevent bad data from getting in to the master data hub; they are there to prevent bad data from getting out.

Please let me know on this thread or directly if you have any additional questions, or need help working with this behavior.

Regards,

David (dbrowne at microsoft dot com)

---

David http://blogs.msdn.com/b/dbrowne/

Answer 6

Ok let me describe our business scenario:

Members look like as below after first nightly import through staging process and then calling SP “udpValidateModel” to validate the model.

Validation Status	Name	Code	Identifier
Valid	A	1	100
Valid	B	2	200
Invalid	C	3	300
Invalid	D	4	300

Business realizes that member D is wrong and deletes D in the source system.  Now members look like after 2^nd or subsequent nightly import through staging process and then calling SP “udpValidateModel” to validate the model.

Validation Status	Name	Code	Identifier
Valid	A	1	100
Valid	B	2	200
Invalid	C	3	300

Now why is member C still invalid?  What is the work around to have this member validated automatically without human intervention during STAGING ETL process?

Note that in our scenario we have more than 500K members in this entity.

Answer 7

How are you deleting the invalid member - i.e., D? Are you de-activating it -  i.e., Import Type 5? Are you permanently deleting it -  i.e., Import Type 6? If you are just de-activating the member, this might well be the reason why after validating the model C is still invalid.

In other words, is C invalid after deleting D and re-validating the model because there is another member with the same value in the Identifier column that has been de-activated and not permanently deleted?

If this is the case, does this mean that business rules apply to active and inactive members in the model? Personally, I hope this is not the case because it is nightmare to diagnose.

Answer 8

I am using Import type 6 for delete. 

The point here is that when entity members are in state as shown in 2nd table, then only "Apply Rules" (which is manual) works but not any other validation methods.  I want this validation through validation procedure.  I agree with Microsoft vision for MDS "All good and bad members can come in but only good goes out".  But in my scenario, good record is still flagged as BAD so it does not go out at all.  This is surely a bug and would appreciate if Microsoft validates that.  I am looking for any workaround if there is any.

Thanks,

Answer 9

Hi wwr123

as a workaround you could force member validation using 

udpMemberValidationStatusUpdate --> link :

http://social.msdn.microsoft.com/Forums/en-US/sqlmds/thread/38c252c1-a95b-4f96-a6b3-d93ac1bedba0

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 10

@wrr123 I believe that you are correct and this is a different issue and the behavior you are seeing is not optimal.  I have raised this separately with the product team and will update this thread when I hear anything. 

The issue is that already-invalidated members are not revalidated with bulk validation is run.  There is a performance optimization that suppresses revalidation of invalid members if the member hasn't changed and the rule hasn't changed.  But with a uniqueness rule you pass a rule you previously failed because some _other_ member was changed.

The workaround posted by Xavier is the right approach for a workaround and should work, although I'm not sure if udpMemberValidationStatusUpdate is considered as part of the officially documented and supported database API for MDS.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 11

Thanks Xavier and David for your responses.

I can use "mdm.udpMembersStatusSet" for updating the *(ALL that has failed validation)* member's validation to Awaiting Revalidation and then call bulk validation SP.  This will solve half of the issue where current invalid members (which are supposed to be valid after applying rules) were not being validated.

Other half of the issue is when member is currently valid (but supposed to be invalid after applying rules) but doesn't get validated properly.  This member should not go out from the MDS but goes out due to green flag.

Please note that I can not make all member's validation status to Awaiting Revalidation because that will immediately hide all valid members to downstream applications.

Also this doesn't seem to be the documented API.

Answer 12

>Other half of the issue is when member is currently valid (but supposed to be invalid after applying rules) but doesn't get validated properly.  This member should >not go out from the MDS but goes out due to green flag.

This is by design.  When a member is valid, adding a duplicate member will not invalidate it.  The duplicate will be invalid, but the original one will not.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 13

Hi wwr123

you could maybe use udpMemberValidationStatusUpdate to update the *(ALL that has failed validation)* members BUT ALSO valid members that doesn't get validated properly

ValidationStatus_Failed  INT = 2;

ValidationStatus_Succeeded INT = 3;

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

Answer 14

> This is by design.  When a member is valid, adding a duplicate member will not invalidate it.  The duplicate will be invalid, but the original one will not.

I am a little worried about this comment. Could you please qualify? I have seen quite a lot of 'funny' (struggling for a better word) things with business rules. Surely, if I add a new business rule, the status of all valid members should change to invalid, otherwise what is the point of adding incrementally new business rules to an existing model.

>Other half of the issue is when member is currently valid (but supposed to be invalid after applying rules) but doesn't get validated properly.  This member should >not go out from the MDS but goes out due to green flag.

To address this part of the problem have you try creating a business rule that invalidates all members, something like

If [attribute name] is not equal to 1234567890qwertyuiop then the member is invalid

Once you have applied the rule and invalidated the members you can exclude the rule and only use it if you need it again.

Kind regards,

M

Answer 15

>>if I add a new business rule, the status of all valid members should change to invalid

Yes.  If you add or change a business rule all existing will have status "Waiting to be revalidated".  

David

---

David http://blogs.msdn.com/b/dbrowne/