Values outside of domain values should be considered Invalid - How to?

Question

All,

I am using SQL Server 2012 Data Quality Services. I would like to consider any value that does not exist under domain values as Invalid. For example, if I have the values of 'abc', 'def' listed as correct in my domain values tab under domain management within a knowledge base but any value outside should be considered INVALID.

I have tried setting a domain rule that uses "Value is not in" then manually typed in the values 'abc', and 'def' but don't get an INVALID result when I cleanse data that has a domain value of 'xyz'. Is there a better method within DQS that will allow me to consider any values outside of the values listed under domain values as INVALID?

Thanks for any help.

Answer 1

Values that are not known in the KB's Domain will be marked as New (aka unknown). During the cleansing review, you can push the button to mark them all as rejected, and they will be marked invalid for that project.

To have the product always mark them as Invalid would means that DQS would assume all unknowns as bad data, which is not the case for most people who use the product.

To make a rule seems like a possible workaround, but the rule would be too huge if you have very many values to do so. I think you make the rule in the opposite approach - as a positive equation - if Value = abc  or Value = def then the domain is valid, else it is invalid.

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 2

Thanks for your reply Jason!

What I ended up doing is setting a conditional split in my SSIS package so that new values found can be sent to a table called, "Invalid".  This works like a champ.  When a data steward changes a new record to be valid or invalid via DQS client tool then my SSIS package will cleanse the once called, "new value" record appropriately.  Once a data steward changes a new record to be valid or invalid, then my SSIS package will clear the record in the Invalid table.

Thanks,

Michael