MDS 2008 R2 - How to blank out an attribute via staging

Question

Hi, I hope you can help. We have an entity with a date attribute and a domain attribute alongside the standard code and name attributes. We want to blank out the date and domain attributes via the staging tables. I am loading a single record for each attribute into the MDM.tblStgMemberAttribute table. I specify the MembertypeID as 1 and for the AttributeValue supply NULL. However after calling the staging stored proc the values aren't being blanked out. I tried supplying '' instead of NULL but this didn't help. Am I doing something wrong? After running the load proc the status_ID is 0 and errorCode is blank for both staging rows. Any help appreciated. James

Answer 1

Hi,

I presume that you used an Import Type of 0. Could I suggest trying an import type of 2?

The following MSDN article, http://msdn.microsoft.com/en-us/library/ee633854.aspx, states:

"2: Create new members. Replace existing MDS data with staged data. If you import NULL values, they will overwrite existing MDS values."

Or you could try a staging type of 0 but using ~NULL~ instead of NULL in your staging tables

"0: Create new members. Replace existing MDS data with staged data, but only if the staged data is not NULL. NULL values are ignored. To change a string attribute value to NULL, set it ~NULL~."

Kind regards,

M

Answer 2

Hi, Thanks for reply. Your article relates to SQL 2012 - just to confirm we are using the 2008 R2 version. Thanks James

Answer 3

You could try to use ~NULL~ to set it to NULL but it may not work. Data staging has changed quite a lot from 2008 R2 to 2012.

The other option you have is a business rule like the one below. The condition is set for the attribute not to equal an outrageous value, and the action blanks the the attribute.



Answer 4

That feels like a bit of a clunky workaround but thanks for the idea. I would have thought the ability to update a value to blank via the staging tables was an essential feature? Are you certain there is no other way? Regards, James

Answer 5

Have you try ~NULL~ and it hasn't work.

You have more orthodox alternative, which entails modifying the stored procedure used by MDS to stage the data, either mdm.updStagingMemberSave or mdm.upStagingMemberAttributeSave.

It is not uncommon to do this.

Regards,

M

Answer 6

I tried creating a rule that blanks out the date attribute. However the rule won't set the date attribute to blank. The value remains. Is this a known issue with dates? If I change the rule to blank out one of the text attributes it works. Thanks, James

Answer 7

You mentioned in your original post that you want to blank out a date attribute and several domain attributes, is the business rule having problems blanking out just with domain attributes, just the date attribute or both?

Answer 8

Thank you for following up. The business rule is just setting the date attribute to blank. However the rule runs and the date value remains. I confirmed the rule logic is correct by changing the rule to blank out a text attribute and this works as expected. Any solution to this issue is appreciated as it is holding up our deployment. Many thanks, James

Answer 9

Sorry just to be clear - currently my business rule is trying to blank out the date attribute which isn't working. However if I put an action to blank out a domain attribute that is also NOT working. Are you able to see this functionality working in your MDS environment? Thanks

Answer 10

If it helps you diagnose the issue, when the business rule is set to blank out the domain attribute I get an error from the staging from proc "upd_system_11_89_childattributes_processrules" line 712 "Member attribute staging failed" I don't get any error when the rules just blanks out the closed date. Hopefully this helps?

Answer 11

Both of them sound like issues to me, but I have never experienced them. Could I ask why do you need to blank out all of these attributes?

Kind regards,

M

Answer 12

Hi, The reason we are blanking them out is both columns are held in two different entities in our model, with one being the master and one being the slave. We need a way of making sure any changes made to the master are reflected in the slave and this is where we use the staging tables to do this. In both cases it is perfectly valid that the user could blank out the fields in the master. Is this clear?

Answer 13

Have you though about having a special member in your domain attributes to represent blank values? Have you thought about using a special date represent a blank date - e.g., 31-Dec-2999?

If you choose this option you can use the staging process the way you want to use it.

Coming from a data warehouse background, I have a natural dislike towards NULL values. They present many challenges.

Answer 14

I considered that option but how do you explain to business users that they must use these "magic" values?

Answer 15

Why is explaining this "magic" values to business users a problem? Surely, it depends on how you are presenting the data and staging process to business users?

Answer 16

I agree with avoiding nulls in a data warehouse and this is common practise. However I think it is fair to say that the average business user would expect an MDM tool to be able to accept a blank value for a date on a record and not have to enter a "special" value because the staging does not support the ability to stage blank values. The same applies to domain attributes. In our example the domain attribute is recursive linking an employee to manager - so does this mean I have to have an "empty" employee? My wider concern is this might raise some doubts around MDS and it's capabilities as an Enterprise MDM system - we have so far managed to reassure the organisation however this issue could impact this.

Answer 17

I fully agree with you, MDS 2008 R2 and 2012 does support NULL values, but the MDS 2008 R2 staging process out of the box does not allow staging certain attributes types to a NULL. I strongly recommend that, as a workaround, you customize the staging process by modifying the mdm.updStagingMemberSave and/or mdm.upStagingMemberAttributeSave stored procedures.

This is not uncommon, I am sure I have read posts in this group with a similar suggestion for other problems.

Answer 18

Hi

M Vega is right

http://msdn.microsoft.com/en-us/library/ee633772(v=sql.105).aspx

Use the attributes staging table (mdm.tblStgMemberAttribute) in the Master Data Services database to:

• Update attribute values of existing members and collections.

• Delete members or collections.

• Reactivate members or collections.

AttributeValue

For free-form attributes, specify the new text or string value for the attribute. For domain-based attributes, specify the code for the member that will be the attribute. If you leave this field blank, when the staging process runs, the attribute value is updated to a blank value. The only exceptions are numeric values, date values, or the Name attribute. These cannot be assigned a blank value through staging. If you used MDMMemberStatus for AttributeName, use De-Activated or Active for AttributeValue. Note You cannot stage file attributes.

example here: http://smartbipeople.weebly.com/1/post/2012/08/delete-a-member-or-collection-master-data-services.html

---

Regards,

---

Xavier Averbouch
Microsoft Community Contributor
SOAT
If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".