Hi Deepak,
I would always think that part of BP in data quality check is checking through an external app that all the numbers tally and that your data model has been built in the right way.
One other technique I think is quite useful is using the exists function to make sure that underlying values match, especially on key (linking) fieldnames in QV. As you know, QV does linking of tables via shared fieldnames but there is nothing to say if the values in 2 tables match. Hence, exists function is useful.
For example,
Table1:
Load
LinkField,
LinkField as LinkFieldFromTable1,
LinkFieldCheck
from Table1;
Table2:
LinkField,
exists(LinkFieldCheck, LinkField) as DQCheck,
LinkField as LinkFieldFromTable2,
from Table2;
What exists(X,Y) function does is compare the underlying values in X to the underlying values in Y. When condition is fulfilled, i.e. there is a match, it returns Boolean -1, when values don't match it returns zero.
Hence, if you expect a match between LinkField in Table1 and Table2 in example above, when you put a Listbox for DQCheck in front-end, there should be no value 0 (zero) in it. Otherwise, click on it and put down listboxes for LinkFieldFromTable1 and LinkFieldFromTable2 and see where the values don't match. Very useful for ensuring linking fields have a match.
Second DQ check is via the Table Viewer.
Couple of pieces of info. When you hover over a field, Information Density tells you amount of nulls in that fieldname, i.e. if less than 100%, contains nulls.
On a linking field, the subset ratio tells you the % number of values in that linking field in that table that is found in the whole data model. Say for some reason, I don't 2 tables with a linking field, on hover over of one field value, subset ratio is 100%, and the other it is 60%, that means that the 60% fieldname only has 60% of all the linkfield values it sees in the WHOLE data model.
I use 3 rules of thumb across linking fields:
If sum of subset ratios across 2 tables is 200%, all is well.
If sum of subset ratios across 2 tables is 100-200%, then one table contains less linking field values than the other, i.e. worth investigating if there is a business rationale that justifies the mismatch.
If sum of subset ratios across 2 tables is less than 100% (usually when substanitially less than 100%, it means that linking field is shared over more than 2 tables, otherwise it will be 100% exactly), then serious problem, as this means that there is no matching values in the underlying tables. Needs serious investigation.
Lastly, a few pieces of info on hover-over of a linking field in Table Viewer:
Perfect Key - subset ratio is 100% and all values therein are distinct (no duplicates).
Primary Key - subset ratio is less than 100% and all values therein are distinct (no duplicates).
Key - Non distinct, duplicates contained.
Hope this helps. But would be keen on hearing others techniques on data validation.
.png "Luminary Alumni"){kind=icon}
