Analyze data for multiple master support

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

These reports will identify the HCO/HCP types already being used in your Network 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 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.