Search code examples
sql-serversql-server-2017master-data-services

Invalidate Members in MDS User Defined Script

I'm working on a proof-of-concept to implement Master Data Services in SQL Server 2017. I've loaded an entity with the staging tables and processed the batch. I've set up business rules to validate members one by one and all that's worked just fine.

Now I'm trying to create a User Defined Script (stored procedure) as a custom action that will have more complicated logic and invalidate records accordingly. With the logic being more complicated, I need this to be done in an action (stored procedure, set based) rather than in a condition (function, iterative).

How would I invalidate records programmatically in a custom action?

Solution

  • One solution would be to :-

    1. add an Attribute/Column to this entity. Let's call it "ForceFail".
    2. Make this column Read-Only for all the users (Security section).
    3. Optionally, if you don't even want the users to see this in the Web UI or the Excel Add-in, make the display width = 0.
    4. Make your Custom SP evaluate the complex condition & wherever it fails, update the ForceFail column to a "y" (let's say). This could be achieved either by:-
      • Saving records to the Staging Leaf table & supplying only the values for the Code column & the ForceFail column, then running the Staging procedure to update data. Or,
      • If you're doing this through the Web API then directly updating the value of the ForceFail column.
    5. Then, create a Business Rule for this entity
      • (Conditions > is equal to > Select Attribute:="ForceFail" > Attribute Value:="y")
      • (Actions > is not valid > choose any attribute you think should be highlighted as Invalid)
    6. Finally, validate the entity using the SP or WCF API.

    Here is what's happening :-

    • You do bulk operations of evaluating your complex rule, identify the Code values of the rows that should fail. Update their ForceFail columns to "y" (or any other value of your choosing)
    • a Business Rule evaluates this ForceFail value, whereever found, marks the row as Invalid.
    • then, you process business rules on this entity so that this evaluation takes effect.

    Challenges with this approach :-

    • The obvious one, you'll have to run additional Staging Batches or make multiple WCF API calls.
    • If your users correct the data which was causing it to fail, then unlike a regular business rule that gets evaluated there & then and the Validation Status gets updated.. you wouldn't be able to do this, until your custom SP runs again to evaluate & change the value of ForceFail.