locked
MDS 2016 not loading records from _leaf table RRS feed

  • Question

  • I am having an issue loading records into MDS entities using the import tool and _leaf tables.  After I load the records into the _leaf table the batch shows up in the import tool.  When I run the batch, it executes and indicates it has completed with no errors.  However, no members are loaded into the entity and the import status on the _leaf table is still 0 (should be changed to 1 if successful or 2 if it failed).

    When this initially happened, I was able to work around the issue by clearing all of the batches from the import tool, deleting all of the records from the _leaf table, and then reloading the batch into the _leaf table.  However, this issue has occurred again and that workaround did not work.  I have also tried run an update on the _leaf table to assign a new batch ID but the new batch does not appear in the import tool.

    Does anyone have a way to get the import tool working again?

    Thursday, October 20, 2016 1:39 PM

Answers

  • We can use staging tables to import data into MDS. The usual technique to import data with staging tables consists of two stages:

    1. Create SSIS routines to merge the data. You can create Stored Procedures, but there are also staging procedures within MDS which would be better used for Stored Procedures.
    2. Once data is loaded into the staging tables you need to start a batch to import data from the staging tables into MDS.

    Up to three tables can exist for each entity created.

    Leaf Members

         Staging table:  stg.EntityName_Leaf

         Stored procedure to load model:  stg.udp_EntityName_Leaf

         View which displays errors: stg.viw_EntityName_MemberErrorDetails

    Reference
    Importing Data Into Master Data Services

    Thursday, October 27, 2016 3:13 AM
  • I was encountering the same issue and ran the stored procedure directly and was able to then see the error(s) that prevented the load from successfully completing.

    This was AFTER it didn't work in the web app. I would see the "Completed" batch, but not data was actually inserted/updated in the entity. So I took the batch_id and ran the stored procedure as follows:

    USE [<MY_DB>]
    GO

    DECLARE @return_value int

    EXEC @return_value = [stg].[udp_<ENTITYNAME>_Leaf]
    @VersionName = N'VERSION_1', ----<the proper version here>
    @LogFlag = 1,
    @Batch_ID = 275, --<the batch id that failed silently here>
    @UserName = N'<MY_USERNAME_HERE>',
    @User_ID = 1      -- <my user_id from mdm.tblUser>

    SELECT 'Return Value' = @return_value

    GO

    In MY case, the error that seemed to be occurring that didn't make it back to the web application was a unique index on a column where I tried to insert a record that violated that uniqueness. Maybe you had a similar issue?

    Not sure the Microsoft MDS team is aware of the issue. I'll try to enter a ticket today

    Wednesday, December 14, 2016 3:23 PM

All replies

  • We can use staging tables to import data into MDS. The usual technique to import data with staging tables consists of two stages:

    1. Create SSIS routines to merge the data. You can create Stored Procedures, but there are also staging procedures within MDS which would be better used for Stored Procedures.
    2. Once data is loaded into the staging tables you need to start a batch to import data from the staging tables into MDS.

    Up to three tables can exist for each entity created.

    Leaf Members

         Staging table:  stg.EntityName_Leaf

         Stored procedure to load model:  stg.udp_EntityName_Leaf

         View which displays errors: stg.viw_EntityName_MemberErrorDetails

    Reference
    Importing Data Into Master Data Services

    Thursday, October 27, 2016 3:13 AM
  • Have you found a solution to this problem? I am having the same difficulty. What I have figured out is that a new batch is created in the mdm.tblStgBatch table, but the _leaf table is not updated with the corresponding BATCH_ID, so the created batch is essentially empty. I have tried bouncing the instance, renewing the broker, bouncing the agent service, and have now manually updated the BATCH_ID in the _leaf table to see if that resolves the issue, but the SB conversation is nowhere to be found. The batch status remains QueuedToRun.
    Wednesday, November 2, 2016 9:27 AM
  • I was encountering the same issue and ran the stored procedure directly and was able to then see the error(s) that prevented the load from successfully completing.

    This was AFTER it didn't work in the web app. I would see the "Completed" batch, but not data was actually inserted/updated in the entity. So I took the batch_id and ran the stored procedure as follows:

    USE [<MY_DB>]
    GO

    DECLARE @return_value int

    EXEC @return_value = [stg].[udp_<ENTITYNAME>_Leaf]
    @VersionName = N'VERSION_1', ----<the proper version here>
    @LogFlag = 1,
    @Batch_ID = 275, --<the batch id that failed silently here>
    @UserName = N'<MY_USERNAME_HERE>',
    @User_ID = 1      -- <my user_id from mdm.tblUser>

    SELECT 'Return Value' = @return_value

    GO

    In MY case, the error that seemed to be occurring that didn't make it back to the web application was a unique index on a column where I tried to insert a record that violated that uniqueness. Maybe you had a similar issue?

    Not sure the Microsoft MDS team is aware of the issue. I'll try to enter a ticket today

    Wednesday, December 14, 2016 3:23 PM
  • I believe the issue may be specific to SQL 2016 CU2.  I am experiencing a similar issue.  Mine is related to Committing a Version - the Version Management page allows me to press Commit and reports no error.  However, the version is not committed.  In the SQL Log, I see an error which reads

    The activated proc '[mdm].[udpValidationQueueActivate]' running on queue '[<MY_DB>].mdm.microsoft/mdm/queue/validation' output the following: 'The EXECUTE permission was denied on the object 'udpValidationQueueActivate', database 'MDS_APEXSPC', schema 'mdm'.

    I also see similar permission errors for all the other queues, including:

    The activated proc '[mdm].[udpStagingQueueActivate]' running on queue '[<MY_DB>].mdm.microsoft/mdm/queue/stagingbatch' output the following: 'The EXECUTE permission was denied on the object 'udpStagingBatchQueueActivate', database 'MDS_APEXSPC', schema 'mdm'.

    What account is lacking the required permission?  I believe the answer is mds_ssb_user, because that account is the principal named as the Execute As for queues, but that account has no explicit permissions.  I believe it should have permission to execute any objects in the mdm schema.

    Did you enter a ticket?  Is this issue resolved in CU3 or CU4?

    Thank you.


    Brian Berry

    Wednesday, March 8, 2017 5:44 PM
  • hi Briberry,

    I am not aware of any tickets related to this . anyways , please provide DB back up and exact repro steps to help me understand the issue more so that I can repro and log a defect to continue investigation

    thanks,

    sunny

    Thursday, March 9, 2017 3:31 AM
  • Thanks Sunny. To reproduce the issue in CU2.

    Create a simple model in MDS 2016 CU2 - or use a sample model

    Go to Version Management Page

    Lock VERSION_1 of the model

    Click Manage -> Versions

    Click Commit

    Return to the Version Management page.  VERSION_1 remains locked.

    Open SSMS and connect to the DB where the MDS database is located.

    Open SQL Logs - Log should read that the EXECUTE permission is denied as stated above.

    NOTE: the following script seems to address the issue.

    use <DB NAME>
    go

    GRANT EXECUTE ON SCHEMA :: usr TO mds_schema_user;
    go

    GRANT EXECUTE ON SCHEMA :: mdm TO mds_email_user;
    GRANT SELECT ON SCHEMA :: mdm TO mds_email_user;
    GRANT UPDATE ON SCHEMA :: mdm TO mds_email_user;
    go

    GRANT EXECUTE ON SCHEMA :: mdm TO mds_ssb_user;
    GRANT INSERT ON SCHEMA :: mdm TO mds_ssb_user;
    GRANT SELECT ON SCHEMA :: mdm TO mds_ssb_user;
    GRANT DELETE ON SCHEMA :: mdm TO mds_ssb_user;
    GRANT UPDATE ON SCHEMA :: mdm TO mds_ssb_user;
    GRANT REFERENCES ON SCHEMA :: mdm TO mds_ssb_user;
    go

    GRANT EXECUTE ON SCHEMA :: mdq TO mds_ssb_user;
    GRANT INSERT ON SCHEMA :: mdq TO mds_ssb_user;
    GRANT SELECT ON SCHEMA :: mdq TO mds_ssb_user;
    GRANT DELETE ON SCHEMA :: mdq TO mds_ssb_user;
    GRANT UPDATE ON SCHEMA :: mdq TO mds_ssb_user;
    GRANT REFERENCES ON SCHEMA :: mdq TO mds_ssb_user;
    go

    GRANT EXECUTE ON SCHEMA :: stg TO mds_ssb_user;
    GRANT INSERT ON SCHEMA :: stg TO mds_ssb_user;
    GRANT SELECT ON SCHEMA :: stg TO mds_ssb_user;
    GRANT DELETE ON SCHEMA :: stg TO mds_ssb_user;
    GRANT UPDATE ON SCHEMA :: stg TO mds_ssb_user;
    GRANT REFERENCES ON SCHEMA :: stg TO mds_ssb_user;
    go

    GRANT SELECT ON SCHEMA :: mdm TO mds_br_user;
    go

    GRANT EXECUTE ON SCHEMA :: stg TO mds_br_user;
    GRANT INSERT ON SCHEMA :: stg TO mds_br_user;
    GRANT SELECT ON SCHEMA :: stg TO mds_br_user;
    GRANT DELETE ON SCHEMA :: stg TO mds_br_user;
    GRANT UPDATE ON SCHEMA :: stg TO mds_br_user;
    go

    GRANT EXECUTE ON TYPE :: mdm.MemberId to mds_br_user;
    go


    Brian Berry

    Thursday, March 9, 2017 1:45 PM
  • hi Briberry,

    are you super user on that instance of MDS ? can you see yourself added into the MDS database security with what kind of permissions? DB_owner ? I tried the repro and I can access it without permissions issue ( I am super user on my MDS instance )

    let me know about it

    thanks,

    sunny

    Monday, March 13, 2017 6:10 AM