Reporting on hierarchies

AD
DM

Querying hierarchical data can be challenging for users who are writing SQL queries.

Example issues:

  • key data points are on different reporting tables (for example, for the Customer Master domain, the data is on HCP, HCO, and ParentHCO tables)

  • many joins are needed in order query each level of the hierarchy

  • complex queries are not scalable as levels increase in the hierarchy

  • maintenance cost

Network's hierarchy tables

Network provides hierarchy tables (flat hierarchy) to quickly report on hierarchical data. The tables are configurable; Administrators can define the relationship fields, hierarchy filter, and additional helper fields to use.

The flat hierarchy tables enable reporting users to easily query hierarchies with the following benefits:

  • reduced number of joins to do SQL queries against hierarchy data

  • ability to send the data to your data warehouse

  • drag and drop configuration to define the flat hierarchy table

  • ability to schedule the calculation of the table

Note: You do not need to have the Hierarchy Explorer widget enabled to use this feature.

Support for flat hierarchy tables

  • Customer Master domain (HCP, HCO, ParentHCO)

  • Custom domains (for example, Product Master, Employee Master, and so on).

Note: The flat hierarchy tables only support entities and relationships that are Active and Valid.

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 relationships paths for Customer Master objects (HCPs, HCOs, and ParentHCOs) and custom domains (for example, Product Master objects).

The report displays only active and valid objects and relationship paths.

Paths that do not meet this criteria are removed from the hierarchy.

Unsupported paths

  • Inactive paths - Paths are considered inactive for the following reasons:

    • Entities (for example, HCPs, HCOs) - 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:

    • Entities (for example, HCPs, HCOs) - 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.

View the hierarchy tables

The flat hierarchy tables are available in the following sections of the tree view in the SQL Query Editor (Reports):

  • Flattened Hierarchies

  • Domain specified in the Hierarchy Management configuration (for example, Customer Master)

The default flat hierarchy tables contains the following fields:

Field Name Field Label Field Type Description
entity_vid__v Entity Network ID VID VID of the child record.
entity_type__v Entity Type Reference Type of the child record.
ancestor__vid__v Ancestor Veeva ID VID VID of the ancestor record.
ancestor_type__v Ancestor Entity Type Reference Type of the ancestor record.
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 VIDs Text Path from the child record to the ancestor using VIDs. The VIDs are separated by the pipe (|) character.

If the flat hierarchy table is extended (through the Admin configuration), reporting users will see additional fields.

Fields from the ancestor - These fields are suffixed with _ancestor. For example, hco_type__v_ancestor.

Fields from the entity - These fields are suffixed with _entity. For example, hco_type__v_entity.

Shortest path

The shortest path between two entities displays in the report. Duplicate short paths do not display to prevent duplicate entities in roll up counts.

The following examples use Customer Master objects, but they are also applicable to custom domains (for example, Product Master).

Example 1

Between Health System and Clinic A, there are two paths with a distance value of 2; however, the table displays only one row.

Example 2

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.

Customer Master flat hierarchies

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. To make this easier, Network provides the following out-of-the-box tables:

  • flat_hierarchy

  • flat_ownership_hierarchy

  flat_hierarchy flat_ownership_hierarchy
Supported countries Available for all countries Available for US data only
Filters No filter Filters on relationships where the relationship type is one of the following: Ownership, Affiliation, or Kaiser Affiliation
Supported objects Supports entities and relationships that are active and valid Supports entities and relationships that are active and valid.

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.

Customer Master flat hierarchy benefits

  • 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.

Example 1 - flat_hierarchy table structure

Use this basic query to view the structure of the table.

Query 

select * from flat_hierarchy

Results

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 VIDs - Displays the VIDs of the entities in the path separated by the pipe (|) character. The VID of the entity displays first and then the parent HCO VID.

  • 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.

Query

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

Results

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.

Query

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

Results

Example 4 - Health system for HCPs

Use this query to find the health system where HCPs are affiliated.

Query

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

Results

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.

Query

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

Results

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.

You can leverage the flat hierarchy to skip levels from the HCP to the hospital to the health system.

Query

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'

Results

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.

Prerequisite

Upload your list of VIDs as a custom table (Reports > SQL Query Editor).

Query

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
        )

Results

Custom domain flat hierarchies

Querying hierarchies can be more difficult with custom domains due to the number of relationships involved in the full hierarchy. For example, in Product Master domains, the number of relationships objects and entity objects that are used can range from six to more than 10. The more relationships and entities there are, the more complex the hierarchy queries can be.

The example below features a Product Master hierarchy, but it can be applied to any domain with relationship objects.

Hierarchy example

This is a Product Master hierarchy for Cholecap with only half of the nodes displayed. There are many relationships between the Market Basket and Package level.

To traverse this Cholecap hierarchy through all the objects, we need to do the following:

  • Traverse Market Baskets that roll up to the Cholestoral Reducer Market Basket

  • Traverse another level of Market Baskets

  • Traverse to the Brand level.

  • Traverse to the Product level.

  • Traverse through the Packages level.

Creating a query to traverse this Product hierarchy is very complex and will take a large effort to come up the appropriate SQL. To query the full hierarchy, we would need to join on all the relationship and entity objects.

Leveraging the flat hierarchies, you can create a reporting table through drag and drop, that will make it easier to query the product hierarchy.

Example 1 - Identify all packages under the Cholesterol Market Basket

Based on the example above, the query would need to contain at least five JOINs on the relationships and several more JOINs on each entity.

If you create a Product hierarchy reporting table, no JOINs are required in the SQL query to find this data.

SELECT
         entity_vid__v,
         entity_name__v,
         entity_type__v,
         package_v_regulatory_identifier__c_entity,
         package_v_type__c_entity,
         package_v_upc__c_entity,
         ancestor_vid__v,
         ancestor_name__v,
         ancestor_type__v,
         path_info,
         path_info_vid
     FROM
         flat_product_hierarchy
     WHERE
         ancestor_vid__v = 941611810352334444
         AND entity_type__v = 'PACKAGE_V__C'

Results

The packages that roll-up to the Cholesterol Reducer Market Basket display in the report.