locked
MDS Validation issue - "Waiting for revalidation" not changing RRS feed

  • Question

  • Hi, hope someone can help. 

    Struggling to validate all members in my MDS model. SQL 2016. 

    When validating a version through the Web app, members in 'waiting for revalidation' does not change, nor does the status of the Version change to validated.  There is no error from the webpage.  See pic below. 

    I traced the process and found it ran the following procedure in the DB:  exec mdm.udpValidationQueueSave, running this in SSMS yields no results either. It seems that this proc reads a service broker queue (microsoft/mdm/queue/validation) to get the model and version info it needs to validate, but the queue in my system is empty, so it  seems that it never gets to validation. 

    I also tried it manually, by running the updValidateModel procedure, but it ran for about 12 hours without finishing, seemingly in a loop.   

    I really need the web interface to work though, as I need the data stewards to validate and commit versions themselves.  

     


    • Edited by FPJVenter Wednesday, February 8, 2017 9:01 AM
    Wednesday, February 8, 2017 9:00 AM

Answers

  • >The only way that the model was able to validate was to exclude the Business rule. 

    Whenever you change an entity member its validation status is reset to Awaiting Validation.  When you change an entity member from a business rule, this still happens.  So you need to write your business rule so that the _next_ time the rule is evaluated the you don't update the entity member again.  Otherwise the entity member can never be successfully validated.

    You can use an IF clause in the business rule, or you can code the custom stored procedure to skip the update the next time the rule runs for that entity member.

    And there's an issue currently in this scenario where trying to validate the whole model creates an infinite loop if you have a business rule that changes the entity members each time.  So it's really important to not do that.

    David


    Microsoft Technology Center - Dallas

    My Blog

    • Marked as answer by FPJVenter Wednesday, February 15, 2017 2:01 PM
    Tuesday, February 14, 2017 2:35 PM

All replies

  • I seem to have found part of the problem. 

    I had a custom Action in a business rule, this is part of the new "Business Rule Extension" functionality in MDS 2016.  

    The action was to set the value of a member to the difference between two other members. 

    Once I excluded that business rule, and then re-validated,  all of the members moved from "Waiting for revalidation"  to "Validation Succeeded". 

    

    NOTE: That the business rule was not failing or giving an error, it worked as expected: Whenever I added a new row and published from excel,  the Proc would run and populate my field with the default value. I just could`t seem to get the validation working. The only way that the model was able to validate was to exclude the Business rule. 

    (I was able to force-set the validation statuses in the MDS DB - like shown in the link below, but that is not handy in the real production environment. 

    Ira Warren Whiteside's Blog - Guarilla MDM )

    So I am leaving the business rule disabled for now, still not sure what is causing it. Pretty sure it is a bug.  The code of the action and business rule is below, should anyone be interested. 

    CREATE PROCEDURE [usr].[DefaultPrijsControle]
    ( @MemberIdList mdm.[MemberId] READONLY
    , @ModelName NVARCHAR(MAX)
    , @VersionName NVARCHAR(MAX)
    , @EntityName NVARCHAR(MAX)
    , @BusinessRuleName NVARCHAR(MAX)
    )
    AS
    BEGIN

    INSERT INTO [stg].[ModelVersions_Leaf] ( ImportType, BatchTag, Code, PrijsControle )
    SELECT 0
    ,      N'BR'
    ,      Ml.Code
    ,      [CataloguePrice] - [FiscalPrice]

    FROM        @MemberIdList              ML
    INNER JOIN [mdm].[vwModelVersionsDev] wd on wd.Code = ML.Code


    EXEC [stg].[udp_ModelVersions_Leaf] @VersionName = @VersionName
    ,                                   @BatchTag    = N'BR'

    END
    GO


    • Edited by FPJVenter Tuesday, February 14, 2017 2:21 PM
    Tuesday, February 14, 2017 1:59 PM
  • >The only way that the model was able to validate was to exclude the Business rule. 

    Whenever you change an entity member its validation status is reset to Awaiting Validation.  When you change an entity member from a business rule, this still happens.  So you need to write your business rule so that the _next_ time the rule is evaluated the you don't update the entity member again.  Otherwise the entity member can never be successfully validated.

    You can use an IF clause in the business rule, or you can code the custom stored procedure to skip the update the next time the rule runs for that entity member.

    And there's an issue currently in this scenario where trying to validate the whole model creates an infinite loop if you have a business rule that changes the entity members each time.  So it's really important to not do that.

    David


    Microsoft Technology Center - Dallas

    My Blog

    • Marked as answer by FPJVenter Wednesday, February 15, 2017 2:01 PM
    Tuesday, February 14, 2017 2:35 PM
  • Hi David, 

    Thank you for the above information.  It is helpful as we are receiving an error on the unpublished rule even though no rules exist in this state.  

    If we are already into this infinite loop, how or what is recommended to get out of this loop?  I wish I would have read this before validating the model.

    Thank you in advance.

    Rob

    Thursday, February 16, 2017 7:15 PM
  • Just recycle the IIS Application Pool for MDS.

    David


    Microsoft Technology Center - Dallas
    My blog

    Thursday, February 16, 2017 8:25 PM
  • Hi,

    I do not know if it helps, but I have been playing a bit around these validation custom rules in MDS (through stopred procs) of late and found ways to get it working. Based on that experience- things worth noting:-

    1. In the condition of your business rule, you should make sure that the same row is not sent for validation again and again. So, if in your custom action SP, if you are setting the value of an attribute to say blank, then the "if condition" of the business rule should say- that the attribute is "not blank". This is to ensure that after the attribute is set to blank through the SP, it should not again go through the validation.

    2. Inside the SP, if you are joining to the @MemberIdList with the mdm view, make sure that you do not select anything from the mdm view. That is- in your select clause, all the values should come from @MemberIdList. The mdm table can only be used to join and put criterias (where or join clauses) but not in the select list. That just does not work.

    3. Once you have taken care  of these points above, you need to have the validation outside the SP or do it manually and it should work.

    The example that I had was I wanted to make sure that some attributes only apply if the row is parent of some other row. So, I used the mdm table only in the join clause but not on the select.

    Hope this helps. It did in my case :)


    Friday, September 1, 2017 7:32 AM