locked
Cannot deactivate Members in self-referanceing hierarchical structure - even when not referanced anywhere RRS feed

  • Question

  • ERR210052 Error - The MemberCode cannot be deactivated because it is used as a domain-based attribute value.

    Trying to Deactivate (Okay - "De-Activate") members of an employee hierarchy who are no longer referanced in the hierarchy and no longer "active".

    Keep getting this error on all the attempts to deactive. Verified several as unreferanced.

    What could be going wrong?

    Tony

     CU7 - by the way

    ProductName                                                      ProductVersion    SchemaVersion
    Microsoft SQL Server 2008 R2 Master Data Services 10.50.0.1           1.0.6.0


    Richard A. "Tony" Eckel Rochester, NY
    Thursday, August 11, 2011 7:18 PM

Answers

All replies

  • Were you able to resolve this issue? I'm going through the same problem. I'm using this entity as a domain value for another entity. I have confirmed that all referencing entity instances have been De-activated but I keep getting this error when trying to stage the De-activation records. To the contrary, I'm able to manually delete (rather De-activate) it from the user interface. Any help will be greatly appreciated.
    Monday, October 17, 2011 6:54 PM
  • James,

     

    I suspect, but haven't proven, that the existance of the Identity in another data version will prevent deactivation -

    Due to the extreme time / cost of export and deployment for my data (10 Hrs) it is not somthing we will test lightly.

    What you may want to try is to ensure there are no alternative versions that referance that memberID in the data tables, the ones like: 3_11_EN or 12_23_EN. Each table has a complete set of all versions of the data and it uses.

     Any member who deactivated had no ID or VersionMEmber_ID in any domain attribute field for any version.

    Would like the MS MDM team to confirm this: in order to deactivate a member, no version of data may referance the internal id as a domain attribute.

     So: it looks like a member will not deactivate if there exists a version where that member is a domain attribute referance, regardless of status of the data version - which is a royal pain in the features!

     

    Tony


    Richard A. "Tony" Eckel Rochester, NY
    • Edited by Tony Eckel Tuesday, October 18, 2011 6:10 PM
    Tuesday, October 18, 2011 5:59 PM
  • The implementation I'm working with has only one version. I have confirmed that the only other entity that referenced this member has been De-activated. The thing is, if I do it (De-Activate) from the interface, the member is De-activated without an issue and is no longer visible in the UI. The problem is that I would like to automate the process by using SSIS to import to attribute staging and setting MDMMemberStatus to De-Activated. This doesn't work.

    I have tried staging it several times and they all come back with the same "ERR210052" error. What I'm wondering is why I get ERR210052 when staging and I'm able to easily delete the member using the GUI soon after getting this error?

    James

    Tuesday, October 18, 2011 7:27 PM
  • Hi James,

    Which version of MDS do you have installed?  This was an issue in the initial release of MDS and was fixed in CU2.

     

    Pam Matthews

    www.profisee.com

    Tuesday, October 18, 2011 11:00 PM
  • James / et al,

    Might have it Nailed...

    What I discovered is that if the Member Code is referenced by any deactivated member, the deactivation for the member will fail.

    My issues were in a self-referenced Hierarchy (manager-employee) where an employee was terminated and their record "De-Activated"

    When time came to deactivate the manager, the deactivation failed and there was no obvious reference to the manager in the employee entity because we had scrubbed it first. What cannot be scrubbed is the deactivated employee with the reference to the manager.

    To clear the reference, you have to NULL the manager's ID internal to the data table, meaning you have to get real deep under the hood wwith SSIS.

    First: Get the Model ID (and integer pointer) from the Models table:

    select ID "ModelID" from SCHEMA.mdm.tblModel where name = 'Model Name'

    Second: Get the version ID (an integer pointer) of the data set you are working with – each data set has a unique version id found in the mdm.tblModelVersion table.

    select ID "VersionID" from SCHEMA.mdm.tblModelVersion where name = 'Version Name'

    Third: Find the Entity ID for the entity the attribute may be stored in from the entity table: One for the member and one for each Domain Referance

    select ID "EntityID", EntityTable, "TableName" from SCHEMA.mdm.tblEntity where name = 'Entity Name' and Model_ID = ModelID

    Fourth: find the internal column name for the attribute from the tblAttribute table for the Member and for the Domain References for the member

    select ID "AttributeID", TableColumn "ColumnName" from SCHEMA.mdm.tblAttribute as a where Entity_ID = EntityID and Name = 'EntityName'

     Now the fun part, get the ID for the Member and search for records in the EntityTable that have that ID in the TableColumn – then null the references

    Select ID "MemberID", Name "MemberName" from Schema.mdm.MemberTable where Code = 'MemberIDCode' and Version_ID = VersionID

    Next – get the records from the referenced table

    select * from Schema.mdm.EntiryTable where Column1 = MemberID or Column2 = MemberID and Versions_ID = VersionID

    (Are you still with me ????)

    NOW (and you thought you were Finished?)  collect the Codes – and my money is on a 32 character GUID, and the column the MemberID is in. Now you can issue the update to set the Column to NULL where that code = GUID and the Column = MemberID

    You have to remove the referances only in the data version that is in question, so be careful to always use the version_ID when issueing the updates in the back end.

    NOW – PREVENTION means that anytime a member record with a domain reference is deactivated, all domain references would need to be removed prior to deactivation – or you get to do the above for EVERRY BLASTED ONE that remains.

    Jason – I KNOW you're watching: This is a BUG! Deactivation of member should be SMART enough to know that the referance is not active in the deactivated child (for lack of a better term) record and let it go. To have to "go behind" and Null these referances is onerous at best. This is one that should be in the NEXT CU!

    Tony

    P.S. I will bet that Deleting the member works every time due to a Cascade-Delete command on the tables or a strored procedure that takes out the garbage (so to speak)


    Richard A. "Tony" Eckel Rochester, NY
    • Edited by Tony Eckel Wednesday, October 19, 2011 1:25 AM
    Wednesday, October 19, 2011 1:18 AM
  • Pam,

    I applied CU2 in an effort to fix the problem but it persisted. I confirmed that the MDS version I'm running is 10.50.1720.0 which according to the link

    http://support.microsoft.com/kb/2072493

    is CU2.

     

    Tony,

    My model is parent-child just like yours but I'm having a problem deleting the child because that child is a domain attribute for another entity which by itself (the entity referencing the child member) has no problem De-Activating and was De-Activated via MDS staging process. Fundamentally, my issue seems to be very similar to what you are going through. 

    Since the manual delete (rather De-Activation) using the GUI works, I had captured a SQL Profile trace of behind the scene commands executed while doing so. I noticed that coincidentally the commands executed by the GUI delete align to your First, Second and Third steps. Instead of executing the fourth, the GUI executes an sp - udpMemberStatusSet using the EntityID obtained in step No. 3 to De-Activate the notorious child member successfully.

    I felt like this was a "breach" of internal MDS operations and opted to turn to this blog to make sure that there is nothing I'm missing out. Seems like I will have to go with my initial plan B (the "breach") just to get the task completed. I hope this problem does not persist in the Denali and the MDS Release candidates thereafter. Thanks guys.

     

    James

     




    • Edited by James Mburu Wednesday, October 19, 2011 2:56 PM
    Wednesday, October 19, 2011 2:54 PM