locked
Custom Business Rule help RRS feed

  • Question

  • Hi,

    Currently i'm testing MDS, and are new to Master Data Services. (I have taken and online Microsoft Course)

    Today we have our own home built tool for Master Data.

    But we want to replace it with MDS.

    I need to create a Custom Business Rule, but I'm really unsure how to built it and use it.

    My challenge is that I have and ID field/ATTRIBUTE, let's call it TEAM_ID. That is a number, let's say that for one record that the TEAM_ID=100.

    The same record also have two other fields/ATTRIBUTES, START_DATE + END_DATE.

    Sample:

    Code          TEAM_ID          START_DATE          END_DATE

    1                100                 2019-01-01           2019-12-31

    2                100                 2020-01-01           2020-06-01

    3                100                 2020-05-01           2100-12-31

    I need a Business Rule that checks that:

    • START_DATE <= END_DATE
    • That the period between START_DATE and END_DATE are not overlapping any of the other periods where TEAM_ID=100 (I the sample above where Code=2 the END_DATE later that the START_DATE for Code=3, and that is not okay)

    How would I built something like that?


    Wednesday, January 30, 2019 2:45 PM

All replies

  • Found the solution:

    USE [MDS]
    GO
    /****** Object:  UserDefinedFunction [usr].[f_TeamValidate_Period]    Script Date: 01-02-2019 08:43:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER FUNCTION [usr].[f_TeamValidate_Period]
    (
    -- Add the parameters for the function here
    @value nvarchar(250)
    )
    RETURNS BIT
    AS
    BEGIN

    declare @Code as nvarchar(250)
    declare @TEAM_ID as decimal(38,0)
    declare @Start_Date as datetime2(3)
    declare @END_DATE as datetime2(3)
    declare @Test_Result as int
    declare @Test_Result_C as int

    declare @Test_TEAM_ID as decimal(38,0)
    declare @Test_Start_Date as datetime2(3)
    declare @Test_END_DATE as datetime2(3)

    Set @Test_TEAM_ID=0;
    set @Test_Result_C=0;

    SELECT @Test_TEAM_ID=[TEAM_ID], @Test_Start_Date=[Start_Date], @Test_END_DATE=[END_DATE]
    FROM [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES]
    WHERE [Code]=@value


    if @Test_TEAM_ID>0
    begin

    declare cur CURSOR LOCAL FAST_FORWARD
    for SELECT [Code]
      ,[TEAM_ID]
      ,[Start_Date]
      ,[END_DATE]
    FROM [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES]
    WHERE [TEAM_ID]=@Test_TEAM_ID and [Code] <> @value
    open cur

    fetch next from cur 
    into @Code,@TEAM_ID,@Start_Date,@END_DATE

    while @@FETCH_STATUS = 0 
    BEGIN
    SET @Test_Result=0;

    SELECT @Test_Result=COUNT(*)
    FROM [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES]
    WHERE [TEAM_ID]=@Test_TEAM_ID and 
    [Code] <> @value AND 
    (
    @Test_Start_Date BETWEEN [Start_Date] AND [END_DATE]
    OR
    @Test_END_DATE BETWEEN [Start_Date] AND [END_DATE]
    )

    if @Test_Result>0
    begin
    set @Test_Result_C = @Test_Result_C +1;
    end

    fetch next from cur 
    into @Code,@TEAM_ID,@Start_Date,@END_DATE
    END
    close cur
    deallocate cur

    end

    if @Test_Result_C>0 
    begin
    set @Test_Result = 1;
    end

    RETURN @Test_Result

    END

    Friday, February 1, 2019 7:48 AM
  •  Good idea, but use [MDS].[mdm].[viw_SYSTEM_1_3_CHILDATTRIBUTES] -  its not good idea
     When you migrate via MDSDeploy value for Model_ID and Entity_ID (_1_3_ in this case)  change. 
     More righ way - use subscripton view (SV)
     
     
     CREATE FUNCTION usr.f_Validate_Period_universal
     (
     @code as NVARCHAR (MAX)  
    ,@Entity_Name as NVARCHAR (MAX)    -- you_entity_name 
     )
     RETURNS BIT
    AS
    BEGIN
    DECLARE @return BIT = 0
    DECLARE @members_count AS INT -- count intersect members

    DECLARE @cte AS TABLE  
                           
    (   code nvarchar (MAX),  -- code element
        date_start datetime , --  START_DATE  in you case            
        date_end datetime ,   --  END_DATE  in you case 
        buisnes_key nvarchar (MAX) -- TEAM_ID in you case         


    ----- BLOCK FOR ENTITY ------------------------------------------------------------------------------------------------------------
    IF @Entity_Name = 'you_entity_name'  
    BEGIN 
    INSERT INTO @cte (code, date_start, date_end, buisnes_key )
    select  
        code, 
    START_DATE, END_DATE -- its name from you entity 
    /*==>*/  
    ,TEAM_ID  AS [buisnes_key] -- name from you entity
    -- buisnes-key also its maybe concatenated value like TEAM_ID+N'_'+MANAGER_ID  

    FROM mdm.v_you_entity_name_Leaf_SubscriptionView  AS t  -- Leaf Subscription View for your entity. must be created before
     WHERE TEAM_ID = ( SELECT TEAM_ID FROM  mdm.v_you_entity_name_Leaf_SubscriptionView WHERE code = @code ) -- find all items that have the same business key 
    /*<==*/ 

    END -- IF @Entity_Name = 'you_entity_name'
    ------ BLOCK FOR ENTITY -----------------------------------------------------------------------------------------------------------

    You can copy|past BLOCK FOR ENTITY and use it for any entity




    /****************************************************************************************************************
    * Universal code block
    * We find all member with buisnes_key wich intersect by date period
    ****************************************************************************************************************/

        SELECT @members_count = COUNT(*) FROM @cte AS c1  
         CROSS APPLY  
          (  
          SELECT count(*) AS cntALL FROM @cte AS c2  
          WHERE buisnes_key = c1.buisnes_key   
            AND  code <> c1.code     
                    AND  NOT(c1.date_start > c2.date_end OR c1.date_end < c2.date_start )      
          ) AS c2 
        WHERE cntALL > 0   AND code = @code 
              

    IF isnull(@members_count,0) > 0 
    BEGIN
    SET @return = 1
    END
       
       RETURN @return
      
    END
    Wednesday, February 6, 2019 3:34 PM