Reporting on hierarchies
Use the All Hierarchies (flat_hierarchy) table to report on relationship hierarchies and to track changes within health systems. The table displays all of the relationships and levels so you can see how HCPs and HCOs rollup to HCOs.
Note: You do not need to have the Hierarchy Explorer widget enabled to use this feature.
The parenthco table can be used to view each entity and its direct parent HCO. You can create a SQL query and join the table with other tables, but this can be complex. Using the flat_hierarchy table, you can view all the relationships and understand how HCPs and HCOs roll up to direct and indirect HCOs.
Example - parenthco table
The report displays each entity and its direct parent HCO.
Example - flat_hierarchy table
For the HCP, Adam, a row is populated for every HCO that he's connected to so you can see all the relationships and the levels to understand how he rolls up to each HCO.
Explore targets - Find the HCPs in a specific health system or understand the health systems you should target for specific therapeutic areas. For example, you can produce a report for health systems that have a large number of HCPs that are gastroenterologists.
Maintain hierarchies and tracking changes - Compare old versions of your custom hierarchy to the new version to find breakages. You an also use it to understand how data source updates impact the hierarchy. For example, if a data source inactivates an HCO, you can see how that change impacts a hierarchy.
Roll up sales and interactions to the health system - Allow analytics teams to easily roll up the interactions and sales data to the health system or hospital.
Skip levels of the hierarchy when reporting - Data Managers and analytics teams can skip levels of the hierarchy using SQL.
Export to data warehouse - Export the report to your data warehouse so you can use it for business purposes like incentive compensation. This can be done using transformation queries.
Manage the table
The All Hierarchies (flat_hierarchy) table is available by default and can be managed on the Hierarchy Management page. You can also create custom hierarchies to use in the Hierarchy Explorer widget and in reporting.
To manage hierarchies click Data Model > Hierarchy Management.
Flat hierarchy tables are also available for OpenData instances that you have access to. For more information, see Reporting on OpenData.
Supported hierarchy paths
The flattened hierarchies support HCPs, HCOs, and ParentHCO relationships.
The report displays only active and valid relationship paths. Paths that do not meet this criteria are removed from the hierarchy.
Inactive paths - Paths are considered inactive for the following reasons:
HCP or HCP - Record status is not Active, they are candidate records, or HCPs have been opted out.
Relationships - The relationship status is not Active.
Invalid paths - Paths are marked as invalid for the following reasons:
HCP/HCP - Record state is not Valid or Under Review (Invalid/Merged_Into/Deleted) or the record is unsubscribed (record state is Deleted).
Relationships - The record state is not Valid or Under_Review or the parent of the relationship is not in your Network instance.
The shortest path between two entities displays in the report. Duplicate short paths do not display to prevent duplicate entities in roll up counts.
Between Health System and Clinic A, there are two paths with a distance value of 2; however, the table displays only one row.
Between Health System and Clinic A there are two paths:
Health System → Hospital A → Clinic A
Health System → Clinic A)
The flat hierarchy table displays only the shortest path (Health System → Clinic A) to prevent duplicate counts when rolling up the data.
View the hierarchy table
The flat_hierarchy table is available in the following sections of the tree view in the SQL Query Editor (Reports):
The table contains the following fields:
|Field Name||Field Label||Field Type||Description|
|entity_vid__v||Entity VID||VID||Veeva ID of the child record.|
|entity_type__v||Entity Type||Reference||Type of the child record.|
|ancestor__vid__v||Ancestor Veeva ID||VID||Veeva ID of the ancestor record.|
|ancestor_type__v||Ancestor Entity Type||Reference||Type of the ancestor record. This value is always HCO.|
|path_distance||Path Distance||Number||Number of hops from the child to the ancestor. Direct relationships have 1 as the length.|
|record_state__v||Record State||Reference||State of the path. The value is always Valid. Paths that are not valid do not display in the reporting table.|
|path_status||Path Status||Reference||Status of the path. The value is always Active. Paths that are inactive do not display in the reporting table.|
|modified_date||Modified Date||Date Time||The last modified date for any entity or relationship in the path.|
|path_info||Path Info||Text||Path from the child record to the ancestor using names. The names are separated by the pipe (|) character.|
|path_info_vid||Path Info with Veeva IDs||Text||Path from the child record to the ancestor HCO using Veeva IDs. The VIDs are separated by the pipe (|) character.|
Flattened hierarchy report examples
Review the following examples to see how you can use the flat_hierarchy table.
Example 1 - flat_hierarchy table structure
Use this basic query to view the structure of the table.
select * from flat_hierarchy
Notes about path columns
Path Distance - Indicates the level that the entity is from the parent HCO. For example, a value of 1 means that the entity is directly connected to the parent HCO. A value of 2 means that there is another HCO between the entity and the parent HCO.
Path Info with Veeva IDs - Displays the Veeva IDs of the entities in the path separated by the pipe (|) character. The entity's ID displays first and then the parent HCO ID displays.
Path Info - Displays the names of the entities in the path separated by the pipe (|) character.
Example 2 - Query an entire health system
Report on all of the HCPs and HCOs in a specific health system.
In this example, we are reporting on all of the HCPs and HCOs in the Ascension Health health system.
SELECT * FROM flat_hierarchy WHERE ancestor_vid__v = 242979566124008448
Example 3 - Hierarchy levels and counts for a health system
Use this query to report on the levels and counts of HCOs in a health system.
This query joins the flat_hierarchy table to the hco table.
SELECT path_distance AS "Level", hco_type__v, COUNT (*) FROM flat_hierarchy LEFT JOIN hco ON entity_vid__v = hco.vid__v WHERE ancestor_vid__v = 242979566124008448 GROUP BY hco_type__v, "Level" ORDER BY "Level" ASC
Example 4 - Health system for HCPs
Use this query to find the health system where HCPs are affiliated.
This query joins the flat_hierarchy table to the hco table.
SELECT entity_vid__v, LISTAGG ( DISTINCT corporate_name__v, '| ' ) AS "Health Systems" FROM flat_hierarchy JOIN hco ON ancestor_vid__v = hco.vid__v WHERE hco_type__v = '4:37' AND entity_type__v = 'HCP' GROUP BY entity_vid__v
Example 5 - Rollup counts for HCPs and HCOs
Use this query to display roll up counts for health systems for a specific place and therapeutic area.
In this example, we want to return the rollup counts for health systems in Texas that have HCPs that are MDs and that specialize in oncology.
This query joins the flat_hierarchy table to the hco and hcp tables.
SELECT hco_anc.corporate_name__v, hco_anc.hco_type__v, locality__v || ' ' || administrative_area__v AS "location", SUM ( CASE WHEN hco_ent.major_class_of_trade__v = '32' THEN 1 ELSE 0 END ) AS "Hospital Count", SUM ( CASE WHEN hcp.primary_specialty_group__v = 'G-ON' AND medical_degrees IN ( 'MD', 'DO' ) THEN 1 ELSE 0 END ) AS "Oncologist Count", MAX( path_distance ) AS "Levels" FROM flat_hierarchy JOIN hco hco_anc ON hco_anc.vid__v = ancestor_vid__v JOIN address ON ancestor_vid__v = address.entity_vid__v AND address_ordinal__v = 1 LEFT JOIN hcp ON hcp.vid__v = flat_hierarchy.entity_vid__v LEFT JOIN hco hco_ent ON hco_ent.vid__v = flat_hierarchy.entity_vid__v WHERE hco_anc.hco_type__v = '4:37' AND administrative_area__v = 'US-TX' GROUP BY hco_anc.corporate_name__v, "location", hco_anc.hco_type__v HAVING "Oncologist Count" > 5 AND "Hospital Count" > 1 ORDER BY hco_anc.corporate_name__v
Example 6 - Skip hierarchy levels
Analytics teams may want to roll up sales and interactions to the closest hospital an HCP is affiliated to and then to the Health System the hospital is affiliated with.
We can leverage the flat hierarchy to skip levels from the HCP to the hospital to the health system.
This query involves a number of joins because we're pulling the names of the HCP, hospital, and health system.
SELECT flat_hospital.entity_vid__v as "HCP VID", formatted_name__v as "HCP Name", flat_hospital.ancestor_vid__v as "Hospital VID", hospital.corporate_name__v as "Hospital", flat_hospital.path_distance as "HCP to Hospital Distance", flat_healthsys.ancestor_vid__v as "Health System VID", healthsystem.corporate_name__v as "Health System", flat_healthsys.path_distance as "Hospital to Health System Distance" FROM flat_hierarchy flat_hospital JOIN hco hospital ON hospital.vid__v = flat_hospital.ancestor_vid__v JOIN hcp ON flat_hospital.entity_vid__v = hcp.vid__v LEFT JOIN flat_hierarchy flat_healthsys ON flat_healthsys.entity_vid__v = flat_hospital.ancestor_vid__v LEFT JOIN hco healthsystem ON healthsystem.vid__v = flat_healthsys.ancestor_vid__v WHERE flat_hospital.entity_type__v = 'HCP' AND hospital.hco_type__v IN ( '4:6', '4:35', '32_23', '32_22', '1_7', '1_3', '32_11', '4_56', '1_21', '1_10', '1_1' ) and healthsystem.hco_type__v = '4:37'
Example 7 - Find 340B status accounts
Find HCPs and HCOs that rollup to 340B accounts.
If you have a list of Veeva IDs (VIDs), you can use reporting to find the records with the 340B flag.
Upload your list of VIDs as a custom table (Reports > SQL Query Editor).
This query finds any 340B HCO that rolls up to the HCP/HCO record at any level
Change mycustom__ct to the name of your custom table that contains the list of VIDs.
SELECT entity_vid__v, ( CASE WHEN entity_type__v = 'HCP' THEN formatted_name__v ELSE hco_ent.corporate_name__v END ) AS "Entity_Name", ancestor_vid__v, hco_anc.corporate_name__v, hco_anc. "340B_eligible__v", path_distance path_info, path_info_vid FROM flat_hierarchy JOIN hco hco_anc ON hco_anc.vid__v = ancestor_vid__v AND hco_anc. "340B_eligible__v" = 'Y' LEFT JOIN hco hco_ent ON entity_vid__v = hco_ent.vid__v LEFT JOIN hcp hcp_ent ON entity_vid__v = hcp_ent.vid__v WHERE entity_vid__v IN ( SELECT vid__v FROM mycustom__ct )