locked
Invoking the Business rules with a sproc in Master Data RRS feed

  • Question

  • Hi folks,

    I'm trying to do an initial ingestion of legacy data into my MDS system. I'm driving the entire process through SSIS and I'm able load the staging tables and call udpStagingSweep sproc to process the data. Now all the data appears on the explorer, but still pending business rule validation.

    Question -

    Is there a sproc that I can call to automatically apply all the business rules that I have defined and validate the data as well?

    Thanks.

     

    Thursday, June 2, 2011 5:18 PM

Answers

  • Hi Koshal,

    You need to call the stored procedure called mdm.udpValidateModel, as per the following example:

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

     There are examples of this in the following links:

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/Importing-Data-by-Using-the-Staging-Process.aspx

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

    Hope that helps

    Jeremy

     


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx If a post answers your question, please click the Mark As Answer button.
    Thursday, June 2, 2011 5:50 PM

All replies

  • Hi Koshal,

    You need to call the stored procedure called mdm.udpValidateModel, as per the following example:

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

     There are examples of this in the following links:

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/Importing-Data-by-Using-the-Staging-Process.aspx

    http://sqlblog.com/blogs/mds_team/archive/2010/02/10/staging-examples.aspx

    Hope that helps

    Jeremy

     


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx If a post answers your question, please click the Mark As Answer button.
    Thursday, June 2, 2011 5:50 PM
  • Awesome! Thanks for the help, Jeremy.
    Thursday, June 2, 2011 6:29 PM
  • Hi Koshal,


    Not sure it would work in your situation but another alternative is to call udpStagingProcess instead of udpStagingSweep. This sproc has a parameter called @DoValidate and if you set the value to 1 it will run business rules after staging is complete. Example:

    EXEC mdm.udpStagingProcess @User_ID=@userID, @Version_ID=@versionID, @StagingType_ID=4, @LogFlag=1, @DoValidate=1, @Batch_ID=@batchID

    -----------------------------------------------------------------------------
    Thanks,

    Brian Barnett
    Profisee
    www.profisee.com

    Thursday, June 2, 2011 7:40 PM
  • Brian,

    Is the @BatchID parameter optional? All my entries into the staging tables today are done without a batch identifier and I liked the fact that udpStagingSweep auto-generated the BatchID for me.

    Friday, June 3, 2011 4:29 PM
  • Yes, using udpStagingProcess does require a little more work on your part, like calling udpStagingBatchSave to create the Batch, and then including the newly created BatchID in the staging records you insert.  So, there are tradeoffs but it depends on how much control you need on your staging process and how much control you want deciding which records you would like to process and when.  If you are the only one staging in records and want to process all unbatched records then udpStagingSweep should work fine.  You would then just need to what Jeremy recommends regarding running the validation process.

    -----------------------------------------------------------------------------
    Thanks,

    Brian Barnett
    Profisee
    www.profisee.com

    Tuesday, June 7, 2011 12:19 PM