Numerical data validation based on more complex rules

Question

Is it possible to use DQS to perform validation on the following scenarios:

- Compare a field to one or more other fields on the same row

   for instance, in a table with two dates called 'start date' and 'end date', check if 'end date' is equal to or later than 'start date'

- Compare a field from a row to fields from different rows

  suppose that I have a timesheet, and I would like to avoid that the same person registers an activity time that overlaps with any previous activity  time that he had already input

Answer 1

Compare a field to one or more other fields on the same row

Yes, that is possible using composite domains.

Here's a blog post that I wrote: http://parasdoshi.com/2013/01/02/data-quality-services-composite-domains-in-action/

- Compare a field from a row to fields from different rows

There's Matching functionality where you can find "similar" rows.

Your requirement is unclear to me so I am not sure that Matching would help. But If you explain using a dummy data-set, then it would be clearer I guess.

I hope this helps.

---

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Answer 2

I have the following rules for an activity performed at a workshop:

- No activities can be performed in parallel on the same equipment by the same person (any overlap in time would be considered a parallelism).

- No activities can be performed in parallel on different equipment by the same person (any overlap in time would be considered a parallelism).

Suppose I have the following data:

Equipment  Mechanic   Start       End

 Car A           Bill            10:00      11:30

 Car A           Mike         10:15      10:30

 Car A           Bill            11:00      13:00

 Car B           Mike          9:00       10:20

Rows 1 and 3, and rows 2 and 4 would be violating the proposed rules.

Answer 3

By any chance - to keep it simple, Are you considering using DQS matching functionality along w/ using manual process to detect overlap in time?

off the top of my head, I cannot think of rules in DQS that would satisfy would your second requirement but would be happy to see if someone else can come up w/ an automated solution for you.

I hope you are able to use composite domains for your first requirement though.

---

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Answer 4

I was hoping that DQS would offer some out of the box solution that would address my requirements. That would be nice since it has a scalable framework for building rules and correcting mistakes. I receive several reports from different databases from customers on a regular basis, and need to consolidate them in a unified database model. This data needs several checks of the kind I mentioned in the example (historical consistency, business rules adherence, data integrity), and we are currently evaluating better ways to handle error detection and data correction.

 I think I will need some custom development that possibly involves the usage of DQS for some of the scenarios.

Answer 5

I'm facing a similar task to yourself.

I'm looking to use DQS to implement various business rules and logic similar to those you stated previously

ie. Compare a field to one or more other fields on the same row

   eg, in a table with two dates called 'start date' and 'end date', check if 'end date' is equal to or later than 'start date'

Unfortunately from what I can see the Composite Domains feature won't solve this particular requirement as you can't reference another field in the criteria.  You can only combine the checks on the 2 (or more) fields rather than compare one to the other.

I've  literally just started using DQS and this was one of the first requirements, so to hit a stumbling block/limitation so early is a bit disappointing to say the least.  I was going to say it's a fairly immature product compared to something like Trillium or SAS but when you think its been round since 2008 R2 they really should have shoehorned basic comparison features such as that into it by now.

I'm a big Microsoft fan but I've got to be honest the quality I'm seeing in MDS and DQS is pretty disappointing :(

Answer 6

DQS is new to SQL 2012.

Sorry but its probably not going to meet your needs. Maybe use SSIS and compare the columns using a derived column/conditional split.

---

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