MDS 2012/2014: Where my soft deletes at?

  • Article

 

In Master Data Services, a soft delete is the result of removing a member via the MDS UI or the Excel add-in. What this means is that the member isn't actually deleted, it's just a new state that the record acquires. This mechanism allows the recovering of a certain member by reverting the transaction where the delete occurred.

There is no way of doing a hard delete via those interfaces at the moment, and you can only actually delete them via Entity Based Staging with Import Type 4 or 6 (https://msdn.microsoft.com/en-us/library/ee633854).

But in order to actually execute the delete, we must first find out which records are in that state.

Master Data Services supporting database allows us to find that, via the tables that actually support those entities. We can follow this procedure to find those records:

  1. We start of by finding our Model identifier in the mdm.tblEntity table:

    SELECT
    [ID],[Name]
    FROM
    [mdm].[tblModel] WHERE Name = '<MODEL_NAME>'

  2. Then we look at the entities table to get our entity internal identifier by using the ID we got on the first step and the entities name:
    SELECT
    [ID],[Model_ID],[Name],[EntityTable]
    FROM
    [mdm].[tblEntity] WHERE Name = '<ENTITY_NAME>' and Model_ID = <MODEL_ID> 

  3. In the previous step we got a column called EntityTable which has the actual name of the table containing the entity's records;

  4. All the records with a Status_ID = 2 on that table, are soft-deleted members;

  5. Now you can use entity based staging to remove them.

HTH,
@DarthSQL

 

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

crossposted from https://sqlargonauts.com/2014/09/18/mds-20122014-where-my-soft-deletes-at/