locked
How cane I get Deleted members ? RRS feed

  • Question

  • Hello,When I use MDS , I found a problem:

    How can I use mds's WCF API to get which members has been deleted?

    thx


    Wednesday, January 4, 2012 11:50 AM

Answers

  • Interesting and I think this is really a good point,

    If I am understanding correctly, you need those deleted members propagated to the slave systems.

    Initially think to use a custom workflow but it is complicated.

    A more simple way maybe is to add one more  field for each entity named as 'IsActive'

    Then I will not delete those members, but to set the status IsActive as 0 if I want to disable them.

    Then I can use the EntityMembersGet to get those inactive members.

     memberGetCriteria.SearchTerm = String.Format(" [IsActive] = '{0}' ", "0");

    And you can have a night batch to do the sync and real delete work by calling the EntityMembersDelete method of the service

    I hope the future release of MDS can include features like member level version control, history tree etc...


    • Edited by Hu yiqian Thursday, January 5, 2012 7:15 AM
    • Proposed as answer by Challen Fu Friday, January 6, 2012 11:19 AM
    • Marked as answer by Challen Fu Monday, January 9, 2012 5:38 AM
    Thursday, January 5, 2012 7:03 AM

All replies

  • Checking MDS codes below, "delete" is a logic action which set the member status =2 and assign a new code to the member

    Data Access layer 
    -------------------------------------------------------------------------------------------------------------------------------------------------
    internal static void Delete(EntityMembers members, RequestContext context, ref 
    
    OperationResult results)
    {
         ...
         while (enumerator.MoveNext())
         {
           member = enumerator.Current;
     	string str = MasterDataAccess.EntityMemberStatusSet(context.DatabaseContext, 
    
    context.CurrentUser.Identifier.Id, 
                         members.VersionId.Id, members.EntityId.Id, member.Code, 2, 
    
    memberType);
            ...
         }
     }
    

    SP: udpMemberStatusSet
    ---------------------------------------------------------------------------------------------------------------------------------------------------
      BEGIN  
    	    --Change the Code to a Guid before the status is set so it can be reused.  See TFS task #95825 for more info.  
    	    DECLARE @NewCodeValue NVARCHAR(250);  
    	    SET @NewCodeValue = CONVERT(NVARCHAR(250),newid());  
    	    EXEC mdm.udpMemberAttributeSave   
    	    @User_ID = @User_ID,   
    	    @Version_ID = @Version_ID,   
    	    @Entity_ID = @Entity_ID,   
    	    @MemberCode = NULL,   
    	    @Member_ID = @Member_ID,   
    	    @MemberType_ID = @MemberType_ID,   
    	    @AttributeName = 'Code',   
    	    @AttributeValue = @NewCodeValue,   
    	    @LogFlag = @LogTransactionFlag; -- use the logging flag to indicate whether the save should log a transaction.  
    	END  
      
    	--Get the prior value  
    	EXEC mdm.udpMemberPriorValueGet @Version_ID,@TempTableName,'Status_ID',@Member_ID,@TempPriorvalue OUTPUT  
      
    	--Update the status in the correct entity table  
    	SET @TempSQLString = N'  
    		UPDATE mdm.' + quotename(@TempTableName) + N'  
    			SET Status_ID = ' + CONVERT(VARCHAR(30), @Status_ID) + '   
    		WHERE  
    			ID = ' + CONVERT(VARCHAR(30), @Member_ID) + ' AND  
    			Version_ID = ' + CONVERT(VARCHAR(30), @Version_ID);  
    

    And I tried below, but it does not work.

     [TestMethod()]
            public void EntityMembersGetTest()
            {
                EntityMembersGetRequest getRequest = new EntityMembersGetRequest();
                EntityMembersGetResponse getResponse = new EntityMembersGetResponse();
                Microsoft.MasterDataServices.Services.Service target = new Microsoft.MasterDataServices.Services.Service();
                //Represents a request complex type that defines the EntityMember criteria for the operations result set.
                EntityMembersGetCriteria memberGetCriteria = new EntityMembersGetCriteria();
                //Sets GUID or the exact name of the model.
                memberGetCriteria.ModelId = new Identifier() { Name = "Vehicle" };
                //Sets a GUID or the exact name of the entity.
                memberGetCriteria.EntityId = new Identifier() { Name = "Vehicle" };
                //Sets the GUID or the exact name of the version.
                memberGetCriteria.VersionId = new Identifier() { Name = "VERSION_1" };
                
                //Sets a WHERE clause search criteria to filter records. In this sample, we want to get members, which have the attribute 'TestAttribute' with value 'abc'
                memberGetCriteria.SearchTerm = String.Format(" [Status_ID] = '{0}' ", "2");
                memberGetCriteria.PageSize = 200;
                
                getRequest.MembersGetCriteria = memberGetCriteria;
                getResponse = target.EntityMembersGet(getRequest);
                Assert.Inconclusive("Verify the correctness of this test method.");
            }



    The answer is there is no such method in service to find deleted members.

    A work around to recover/find deleted member is just to update the status in the T-SQL.  

    1) SQL to find deleted members: (To locate the table, please refer to the meta data entity in MDS GUI.)

    SELECT * FROM [MDS].[mdm].[tbl_3_20_EN] WHERE Status_ID=2

    2) To reactivate deleted memebers, just use below script.

    UPDATE [MDS].[mdm].[tbl_3_20_EN] 
    SET Status_ID=1,ValidationStatus_ID=3
    WHERE Name ='XXX'

    3) I do not know the impact of this kind of recovery, perhaps one thing is all logs will be lost during the deletion and can not be recovered.

    BTW, Please do not used code in the where clause as it has been changed during the deletion.




    • Proposed as answer by Hu yiqian Thursday, January 5, 2012 2:14 AM
    • Edited by Hu yiqian Thursday, January 5, 2012 2:42 AM
    • Unproposed as answer by Hu yiqian Thursday, January 5, 2012 3:38 AM
    Thursday, January 5, 2012 2:05 AM
  • Thank you for you answer first .I know this method before (use sql to get the deleted from entity table where status_ID =2) .

    but I have a lot of models and entities , and some other application Database need to be update when the mds db has been changed ,like some record was deleted,If I use this method,I have to program much code, can any body give some simple solution? Thank you?

    Thursday, January 5, 2012 3:15 AM
  • Interesting and I think this is really a good point,

    If I am understanding correctly, you need those deleted members propagated to the slave systems.

    Initially think to use a custom workflow but it is complicated.

    A more simple way maybe is to add one more  field for each entity named as 'IsActive'

    Then I will not delete those members, but to set the status IsActive as 0 if I want to disable them.

    Then I can use the EntityMembersGet to get those inactive members.

     memberGetCriteria.SearchTerm = String.Format(" [IsActive] = '{0}' ", "0");

    And you can have a night batch to do the sync and real delete work by calling the EntityMembersDelete method of the service

    I hope the future release of MDS can include features like member level version control, history tree etc...


    • Edited by Hu yiqian Thursday, January 5, 2012 7:15 AM
    • Proposed as answer by Challen Fu Friday, January 6, 2012 11:19 AM
    • Marked as answer by Challen Fu Monday, January 9, 2012 5:38 AM
    Thursday, January 5, 2012 7:03 AM
  • first thank you very much for you replay.

    It's my point ,yes,  add another attribute to flag the deleted members ,but in face ,the status_id is active ,So I will use the SQL to select the deleted members .

    I hope the MDS's new version will resolve ths problem.

     

    And If I meet questions and problems in future with mds , Please tell  me the good methods .

     

    Thank you again.

    Friday, January 6, 2012 11:39 AM
  • No problem, you are mostly welcome.

    Also thank you for the discussions to let me understand more about MDS. 

    Saturday, January 7, 2012 1:45 AM