IM InfoSphere Identity Insight, Version 8.0

Unique Number Shared by Multiple Entities query

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.

Unique Numbers Shared by Multiple Entities SQL Query statement

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;

What next?

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.

Example results of Unique Number Shared by Multiple Entities query

Here is an example of what the results of running the Unique Number Shared by Multiple Entities query might look like:
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


Feedback

Last updated: 2009