Analyze data for multiple master support

To review the existing data in your Network MDM instance before you enable the multiple master feature, use advanced queries in Network MDM reports (Reports > SQL Query Editor).

These reports will identify the HCO/HCP types already being used in your Network MDM instance for a specific country.

HCO/HCP types used for a specified country

To see what HCO/HCP types are currently used in your Network MDM instance, use the following queries:

HCO query

SELECT
        hco.hco_type__v,
		count(*) as count
    FROM
        hco
	WHERE hco.hco_status__v = 'A'
	      AND hco.primary_country__v = 'US'
	GROUP BY hco.hco_type__v
	ORDER BY count DESC

Results - The query results for this example will display active records using HCO types in the United States.

HCP query

SELECT
        hcp.hcp_type__v,
		count(*) as count
    FROM
        hcp
	WHERE hcp.hcp_status__v = 'A'
	      AND hcp.primary_country__v = 'US'
	GROUP BY hcp.hcp_type__v
	ORDER BY count DESC

HCO/HCP types that are being used by master sources

To see which master sources are using HCO/HCP types, use the following queries that join the HCO/HCP tables with the custom key table:

HCP query

SELECT
        hcp.hcp_type__v,
        customkey.custom_key_source_type__v,
		count(*) as count
    FROM
        hcp INNER JOIN customkey
		ON hcp.vid__v = customkey.custom_key_entity_id__v
	WHERE hcp.hcp_status__v = 'A'
	      AND hcp.primary_country__v = 'US'
	      AND customkey.custom_key_status__v = 'A'
	      AND customkey.custom_key_source_type__v in ('MASTER__v','Third_party_master')
	GROUP BY hcp.hcp_type__v, customkey.custom_key_source_type__v
	ORDER BY hcp.hcp_type__v, customkey.custom_key_source_type__v  

In the query, MASTER__v is the Veeva OpenData custom key source.

Replace Third_party_master with the custom key source that you defined for your third-party system.

Results - The query results for this example will display the HCP types used in the US for the Veeva OpenData subscription and the specified third-party system.

HCO query

SELECT
        hco.hco_type__v,
        customkey.custom_key_source_type__v,
		count(*) as count
    FROM
        hco INNER JOIN customkey
		ON hco.vid__v = customkey.custom_key_entity_id__v
	WHERE hco.hco_status__v = 'A'
	      AND hco.primary_country__v = 'US'
	      AND customkey.custom_key_status__v = 'A'	
	      AND customkey.custom_key_source_type__v in ('MASTER__v','Third_party_master')
	GROUP BY hco.hco_type__v, customkey.custom_key_source_type__v
	ORDER BY hco.hco_type__v, customkey.custom_key_source_type__v 

Iin the query, MASTER__v is the Veeva OpenData custom key source.

Replace Third_party_master with the custom key source that you defined for your third-party system.