locked
Exclude MDS Business Rule using a Stored Procedure call RRS feed

  • Question

  • Hi,

    I am try to exclude some specific MDS business rules before Validating the Model and then Activate them. There is way to do this using the Web Service API operation. But I am looking to use a Store Procedure call.

    Regards,

    Rehan

    Tuesday, December 25, 2012 6:27 PM

Answers

  • Hi Rehan,

    You can take a look at the following MSDN document:
    Validation Stored Procedure (Master Data Services): http://technet.microsoft.com/en-us/library/hh231023.aspx

    Sample code:

    DECLARE @ModelName nVarchar(50) = 'Customer'
    DECLARE @Model_id int
    DECLARE @UserName nvarchar(50)= 'DOMAIN\user_name'
    DECLARE @User_ID int
    DECLARE @Version_ID int

    SET @User_ID = (SELECT ID 
                     FROM mdm.tblUser u
                     WHERE u.UserName = @UserName)
    SET @Model_ID = (SELECT Top 1 Model_ID
                     FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                     WHERE Model_Name = @ModelName)
    SET @Version_ID = (SELECT MAX(ID)
                     FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                     WHERE Model_ID = @Model_ID)

    EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, December 27, 2012 8:18 AM
  • Hi

    for your information, with API : this code is working fine

    some other samples in http://sqlserversamples.codeplex.com/wikipage?title=SQL%20Server%202012%20Master%20Data%20Services%20&IsNewlyCreatedPage=true

    for exclusion:

     // Excludes the business rule and publishes it to remove the rule from the validation process.
            private static void ExcludeAndPublishBR(string modelName, string entityName, string ruleName)
            {
                try
                {
                    // Set Model and Entity objects.
                    Identifier modelId = new Identifier { Name = modelName };
                    Identifier entityId = new Identifier { Name = entityName };
    
                    // Create the request object.
                    MDSTestService.BusinessRulesGetRequest ruleGetRequest = new MDSTestService.BusinessRulesGetRequest();
                    ruleGetRequest.ResultOptions = new BRResultOptions();
                    ruleGetRequest.ResultOptions.BusinessRules = ResultType.Details;
                    ruleGetRequest.GetCriteria = new BRGetCriteria();
                    ruleGetRequest.GetCriteria.ModelId = modelId;
                    ruleGetRequest.GetCriteria.EntityId = entityId;
                    ruleGetRequest.GetCriteria.MemberType = BREntityMemberType.Leaf;
                    ruleGetRequest.GetCriteria.BusinessRuleIds = new System.Collections.ObjectModel.Collection<Identifier> { };
                    ruleGetRequest.GetCriteria.BusinessRuleIds.Add(new Identifier { Name = ruleName });
    
                    // Get the business rules.
                    MDSTestService.BusinessRulesGetResponse ruleGetResponse = clientProxy.BusinessRulesGet(ruleGetRequest);
                    HandleOperationErrors(ruleGetResponse.OperationResult);
    
                    BusinessRule selectedBusinessRule = ruleGetResponse.BusinessRuleSet.BusinessRulesMember[0];
    
                    // Set the status to pending exclusion.
                    selectedBusinessRule.Status = BRStatus.PendingExclusion;
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesUpdateRequest ruleUpdateRequest = new MDSTestService.BusinessRulesUpdateRequest();
                    ruleUpdateRequest.BusinessRuleSet = new BusinessRules();
                    ruleUpdateRequest.BusinessRuleSet.BusinessRulesMember = new System.Collections.ObjectModel.Collection<BusinessRule> { };
                    ruleUpdateRequest.BusinessRuleSet.BusinessRulesMember.Add(selectedBusinessRule);
    
                    // Update the business rule's status to pending exclusion.
                    MDSTestService.MessageResponse ruleUpdateResponse = clientProxy.BusinessRulesUpdate(ruleUpdateRequest);
    
                    HandleOperationErrors(ruleUpdateResponse.OperationResult);
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesPublishRequest rulePublishRequest = new MDSTestService.BusinessRulesPublishRequest();
                    rulePublishRequest.BRPublishCriteria = new BRPublishCriteria();
                    rulePublishRequest.BRPublishCriteria.EntityId = entityId;
                    rulePublishRequest.BRPublishCriteria.ModelId = modelId;
                    rulePublishRequest.BRPublishCriteria.MemberType = BREntityMemberType.Leaf;
    
                    // Publish the business rule.
                    MDSTestService.MessageResponse rulePublishResponse = clientProxy.BusinessRulesPublish(rulePublishRequest);
    
                    HandleOperationErrors(rulePublishResponse.OperationResult);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: {0}", ex);
                }
            }

    for deletion:

    // Deletes the business rule and publishes to remove the rule from the validation process.
            private static void DeleteAndPublishBR(string modelName, string entityName, string ruleName)
            {
                try
                {
                    // Set Model and Entity objects.
                    Identifier modelId = new Identifier { Name = modelName };
                    Identifier entityId = new Identifier { Name = entityName };
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesGetRequest ruleGetRequest = new MDSTestService.BusinessRulesGetRequest();
                    ruleGetRequest.ResultOptions = new BRResultOptions();
                    ruleGetRequest.ResultOptions.BusinessRules = ResultType.Details;
                    ruleGetRequest.GetCriteria = new BRGetCriteria();
                    ruleGetRequest.GetCriteria.ModelId = modelId;
                    ruleGetRequest.GetCriteria.EntityId = entityId;
                    ruleGetRequest.GetCriteria.MemberType = BREntityMemberType.Leaf;
                    ruleGetRequest.GetCriteria.BusinessRuleIds = new System.Collections.ObjectModel.Collection<Identifier> { };
                    ruleGetRequest.GetCriteria.BusinessRuleIds.Add(new Identifier { Name = ruleName });
    
                    // Get the business rules.
                    MDSTestService.BusinessRulesGetResponse ruleGetResponse = clientProxy.BusinessRulesGet(ruleGetRequest);
                    HandleOperationErrors(ruleGetResponse.OperationResult);
    
                    BusinessRule selectedBusinessRule = ruleGetResponse.BusinessRuleSet.BusinessRulesMember[0];
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesDeleteRequest ruleDeleteRequest = new MDSTestService.BusinessRulesDeleteRequest();
                    ruleDeleteRequest.DeleteCriteria = new BRDeleteCriteria();
                    ruleDeleteRequest.DeleteCriteria.BusinessRules = new System.Collections.ObjectModel.Collection<Guid> { };
                    ruleDeleteRequest.DeleteCriteria.BusinessRules.Add(selectedBusinessRule.Identifier.Id);
    
                    // Delete the business rule.
                    MDSTestService.MessageResponse ruleDeleteResponse = clientProxy.BusinessRulesDelete(ruleDeleteRequest);
                    HandleOperationErrors(ruleDeleteResponse.OperationResult);
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesPublishRequest rulePublishRequest = new MDSTestService.BusinessRulesPublishRequest();
                    rulePublishRequest.BRPublishCriteria = new BRPublishCriteria();
                    rulePublishRequest.BRPublishCriteria.EntityId = entityId;
                    rulePublishRequest.BRPublishCriteria.ModelId = modelId;
                    rulePublishRequest.BRPublishCriteria.MemberType = BREntityMemberType.Leaf;
    
                    // Publish the business rule.
                    MDSTestService.MessageResponse rulePublishResponse = clientProxy.BusinessRulesPublish(rulePublishRequest);
                    HandleOperationErrors(rulePublishResponse.OperationResult);
                }
                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".




    Thursday, January 31, 2013 12:11 PM
  • Hi

    and with a stored procedure:

    [mdm].[udpBusinessRuleUpdate] 
    
     @User_ID                INT = NULL,   
        @Name                   NVARCHAR(50) = NULL,   
        @Description            NVARCHAR(255) = NULL,   
        @RuleConditionText      NVARCHAR(MAX) = NULL,  
        @RuleActionText         NVARCHAR(MAX) = NULL,  
        @RuleConditionSQL       NVARCHAR(MAX) = NULL,  
        @RuleType               INT = NULL, -- 1 = AttributeMember  
        @RuleSubType            INT = NULL, -- 1 = Leaf, 2 = Consolidated  
        @Priority               INT = NULL,  
        @NotificationGroupMuid  UNIQUEIDENTIFIER = NULL,  
        @NotificationUserMuid   UNIQUEIDENTIFIER = NULL,  
        @LastChanged            DATETIME2(3) = NULL,  
    	@Status_ID			    INT = NULL,  
        @Entity_MUID            UNIQUEIDENTIFIER = NULL OUTPUT,   
        @Entity_Name            NVARCHAR(50) = NULL OUTPUT,  
        @Model_MUID             UNIQUEIDENTIFIER = NULL OUTPUT,   
        @Model_Name             NVARCHAR(50) = NULL OUTPUT,   
        @MUID                   UNIQUEIDENTIFIER = NULL OUTPUT, /*Input (Clone only) and output*/  
        @ID                     INT = NULL OUTPUT /*Output only*/  

    Set Status id to values 2 or 5 to exclude and values 1 or 3 to activate

    -- find Action_ID  
        DECLARE @Action_ID INT SET @Action_ID = 3 -- 3 = Change  
        IF @Status_ID IS NOT NULL BEGIN  
            DECLARE @CurrentStatus_ID INT SET @CurrentStatus_ID = (SELECT Status_ID FROM mdm.tblBRBusinessRule WHERE MUID = @MUID)   
            IF (@CurrentStatus_ID NOT IN (2,5) AND @Status_ID IN (2,5)) SET @Action_ID = 5 -- 5 = Exclude  
            ELSE IF (@CurrentStatus_ID IN (2,5) AND @Status_ID IN (1,3)) SET @Action_ID = 2 -- 2 = Activate  
        END  



    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".

    Friday, February 1, 2013 7:19 PM

All replies

  • Hi,

    I would suggest running the SQL Profiler when you acheive this via API. You might find the procedures that you are looking for.

    Wednesday, December 26, 2012 11:39 AM
  • Hi Rehan,

    You can take a look at the following MSDN document:
    Validation Stored Procedure (Master Data Services): http://technet.microsoft.com/en-us/library/hh231023.aspx

    Sample code:

    DECLARE @ModelName nVarchar(50) = 'Customer'
    DECLARE @Model_id int
    DECLARE @UserName nvarchar(50)= 'DOMAIN\user_name'
    DECLARE @User_ID int
    DECLARE @Version_ID int

    SET @User_ID = (SELECT ID 
                     FROM mdm.tblUser u
                     WHERE u.UserName = @UserName)
    SET @Model_ID = (SELECT Top 1 Model_ID
                     FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                     WHERE Model_Name = @ModelName)
    SET @Version_ID = (SELECT MAX(ID)
                     FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                     WHERE Model_ID = @Model_ID)

    EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, December 27, 2012 8:18 AM
  • Hi Elvis Long,

    I need a stored procedure to exclude a business rule so it does not get executed when we call the above mentioned procedure.

    Regards,

    Rehan 

    Thursday, December 27, 2012 4:08 PM
  • Hi

    for your information, with API : this code is working fine

    some other samples in http://sqlserversamples.codeplex.com/wikipage?title=SQL%20Server%202012%20Master%20Data%20Services%20&IsNewlyCreatedPage=true

    for exclusion:

     // Excludes the business rule and publishes it to remove the rule from the validation process.
            private static void ExcludeAndPublishBR(string modelName, string entityName, string ruleName)
            {
                try
                {
                    // Set Model and Entity objects.
                    Identifier modelId = new Identifier { Name = modelName };
                    Identifier entityId = new Identifier { Name = entityName };
    
                    // Create the request object.
                    MDSTestService.BusinessRulesGetRequest ruleGetRequest = new MDSTestService.BusinessRulesGetRequest();
                    ruleGetRequest.ResultOptions = new BRResultOptions();
                    ruleGetRequest.ResultOptions.BusinessRules = ResultType.Details;
                    ruleGetRequest.GetCriteria = new BRGetCriteria();
                    ruleGetRequest.GetCriteria.ModelId = modelId;
                    ruleGetRequest.GetCriteria.EntityId = entityId;
                    ruleGetRequest.GetCriteria.MemberType = BREntityMemberType.Leaf;
                    ruleGetRequest.GetCriteria.BusinessRuleIds = new System.Collections.ObjectModel.Collection<Identifier> { };
                    ruleGetRequest.GetCriteria.BusinessRuleIds.Add(new Identifier { Name = ruleName });
    
                    // Get the business rules.
                    MDSTestService.BusinessRulesGetResponse ruleGetResponse = clientProxy.BusinessRulesGet(ruleGetRequest);
                    HandleOperationErrors(ruleGetResponse.OperationResult);
    
                    BusinessRule selectedBusinessRule = ruleGetResponse.BusinessRuleSet.BusinessRulesMember[0];
    
                    // Set the status to pending exclusion.
                    selectedBusinessRule.Status = BRStatus.PendingExclusion;
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesUpdateRequest ruleUpdateRequest = new MDSTestService.BusinessRulesUpdateRequest();
                    ruleUpdateRequest.BusinessRuleSet = new BusinessRules();
                    ruleUpdateRequest.BusinessRuleSet.BusinessRulesMember = new System.Collections.ObjectModel.Collection<BusinessRule> { };
                    ruleUpdateRequest.BusinessRuleSet.BusinessRulesMember.Add(selectedBusinessRule);
    
                    // Update the business rule's status to pending exclusion.
                    MDSTestService.MessageResponse ruleUpdateResponse = clientProxy.BusinessRulesUpdate(ruleUpdateRequest);
    
                    HandleOperationErrors(ruleUpdateResponse.OperationResult);
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesPublishRequest rulePublishRequest = new MDSTestService.BusinessRulesPublishRequest();
                    rulePublishRequest.BRPublishCriteria = new BRPublishCriteria();
                    rulePublishRequest.BRPublishCriteria.EntityId = entityId;
                    rulePublishRequest.BRPublishCriteria.ModelId = modelId;
                    rulePublishRequest.BRPublishCriteria.MemberType = BREntityMemberType.Leaf;
    
                    // Publish the business rule.
                    MDSTestService.MessageResponse rulePublishResponse = clientProxy.BusinessRulesPublish(rulePublishRequest);
    
                    HandleOperationErrors(rulePublishResponse.OperationResult);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: {0}", ex);
                }
            }

    for deletion:

    // Deletes the business rule and publishes to remove the rule from the validation process.
            private static void DeleteAndPublishBR(string modelName, string entityName, string ruleName)
            {
                try
                {
                    // Set Model and Entity objects.
                    Identifier modelId = new Identifier { Name = modelName };
                    Identifier entityId = new Identifier { Name = entityName };
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesGetRequest ruleGetRequest = new MDSTestService.BusinessRulesGetRequest();
                    ruleGetRequest.ResultOptions = new BRResultOptions();
                    ruleGetRequest.ResultOptions.BusinessRules = ResultType.Details;
                    ruleGetRequest.GetCriteria = new BRGetCriteria();
                    ruleGetRequest.GetCriteria.ModelId = modelId;
                    ruleGetRequest.GetCriteria.EntityId = entityId;
                    ruleGetRequest.GetCriteria.MemberType = BREntityMemberType.Leaf;
                    ruleGetRequest.GetCriteria.BusinessRuleIds = new System.Collections.ObjectModel.Collection<Identifier> { };
                    ruleGetRequest.GetCriteria.BusinessRuleIds.Add(new Identifier { Name = ruleName });
    
                    // Get the business rules.
                    MDSTestService.BusinessRulesGetResponse ruleGetResponse = clientProxy.BusinessRulesGet(ruleGetRequest);
                    HandleOperationErrors(ruleGetResponse.OperationResult);
    
                    BusinessRule selectedBusinessRule = ruleGetResponse.BusinessRuleSet.BusinessRulesMember[0];
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesDeleteRequest ruleDeleteRequest = new MDSTestService.BusinessRulesDeleteRequest();
                    ruleDeleteRequest.DeleteCriteria = new BRDeleteCriteria();
                    ruleDeleteRequest.DeleteCriteria.BusinessRules = new System.Collections.ObjectModel.Collection<Guid> { };
                    ruleDeleteRequest.DeleteCriteria.BusinessRules.Add(selectedBusinessRule.Identifier.Id);
    
                    // Delete the business rule.
                    MDSTestService.MessageResponse ruleDeleteResponse = clientProxy.BusinessRulesDelete(ruleDeleteRequest);
                    HandleOperationErrors(ruleDeleteResponse.OperationResult);
    
                    // Create the request object. 
                    MDSTestService.BusinessRulesPublishRequest rulePublishRequest = new MDSTestService.BusinessRulesPublishRequest();
                    rulePublishRequest.BRPublishCriteria = new BRPublishCriteria();
                    rulePublishRequest.BRPublishCriteria.EntityId = entityId;
                    rulePublishRequest.BRPublishCriteria.ModelId = modelId;
                    rulePublishRequest.BRPublishCriteria.MemberType = BREntityMemberType.Leaf;
    
                    // Publish the business rule.
                    MDSTestService.MessageResponse rulePublishResponse = clientProxy.BusinessRulesPublish(rulePublishRequest);
                    HandleOperationErrors(rulePublishResponse.OperationResult);
                }
                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".




    Thursday, January 31, 2013 12:11 PM
  • Hi

    and with a stored procedure:

    [mdm].[udpBusinessRuleUpdate] 
    
     @User_ID                INT = NULL,   
        @Name                   NVARCHAR(50) = NULL,   
        @Description            NVARCHAR(255) = NULL,   
        @RuleConditionText      NVARCHAR(MAX) = NULL,  
        @RuleActionText         NVARCHAR(MAX) = NULL,  
        @RuleConditionSQL       NVARCHAR(MAX) = NULL,  
        @RuleType               INT = NULL, -- 1 = AttributeMember  
        @RuleSubType            INT = NULL, -- 1 = Leaf, 2 = Consolidated  
        @Priority               INT = NULL,  
        @NotificationGroupMuid  UNIQUEIDENTIFIER = NULL,  
        @NotificationUserMuid   UNIQUEIDENTIFIER = NULL,  
        @LastChanged            DATETIME2(3) = NULL,  
    	@Status_ID			    INT = NULL,  
        @Entity_MUID            UNIQUEIDENTIFIER = NULL OUTPUT,   
        @Entity_Name            NVARCHAR(50) = NULL OUTPUT,  
        @Model_MUID             UNIQUEIDENTIFIER = NULL OUTPUT,   
        @Model_Name             NVARCHAR(50) = NULL OUTPUT,   
        @MUID                   UNIQUEIDENTIFIER = NULL OUTPUT, /*Input (Clone only) and output*/  
        @ID                     INT = NULL OUTPUT /*Output only*/  

    Set Status id to values 2 or 5 to exclude and values 1 or 3 to activate

    -- find Action_ID  
        DECLARE @Action_ID INT SET @Action_ID = 3 -- 3 = Change  
        IF @Status_ID IS NOT NULL BEGIN  
            DECLARE @CurrentStatus_ID INT SET @CurrentStatus_ID = (SELECT Status_ID FROM mdm.tblBRBusinessRule WHERE MUID = @MUID)   
            IF (@CurrentStatus_ID NOT IN (2,5) AND @Status_ID IN (2,5)) SET @Action_ID = 5 -- 5 = Exclude  
            ELSE IF (@CurrentStatus_ID IN (2,5) AND @Status_ID IN (1,3)) SET @Action_ID = 2 -- 2 = Activate  
        END  



    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".

    Friday, February 1, 2013 7:19 PM