Exclude MDS Business Rule using a Stored Procedure call

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

Answer 1

Hi,

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

Answer 2

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

Answer 3

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 

Answer 4

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

Answer 5

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