Unique numbers are numbers that, typically, only belong to one entity and are not shared by multiple entities. Checking to see if multiple entities share the same unique numbers is an excellent way to test for data anomalies and verify that your resolution rules are working as expected. You can use the Unique Number Shared by Multiple Entities query to uncover entities that share the same unique number. The query counts a unique number for a single entity only once, regardless of how many identity records for that entity contain the same unique number.
select num_type, num_value, count(distinct ENTITY_ID) as cnt from nums, num_type Where nums.num_type_id=num_type.num_type_id and num_type.unique_FLAG='Y' Group by num_type num_value Having count(distinct ENTITY_ID)>1 Order by count(distinct ENTITY_ID)desc;
In the Visualizer, use the Find by Attribute screen to look up each number returned by the Unique Numbers Shared by Multiple Entities SQL query. In the Results pane, review the entity information for each entity that shares the unique number. You can also review the entity resumes of these entities to help determine why the entities share the same unique number.
You might discover interesting relationships between entities, based on the unique number. For example, you could discover that two different entities are using the same Social Security Number.
Or you might detect an issue with the UMF coding for unique numbers. For example, you could discover that the same passport number is shared between two entities, because the incoming UMF identity record did not use NUM_LOC to indicate the country (location) issuing the passport number. Numbers like passports and drivers licenses are only unique to a particular location, such as country or state. In themselves, these numbers might not be as unique as you think.
NUM_TYPE | NUM_VALUE | cnt |
---|---|---|
SSN | 000-00-0000 | 9 |
SSN | 111-11-1111 | 9 |
SSN | 555-55-5555 | 5 |
SSN | 611-00-6666 | 2 |
SSN | 999-99-9999 | 3 |