Reporting on OpenData

Advanced reporting users can report on the country data within OpenData instances. The tables contain the latest updates to entities in the OpenData instance.

Reporting tables for OpenData are available in the SQL Query Editor. Users with access to Reports and the data can view results directly from the OpenData instance.

Supported country subscriptions

OpenData reporting tables are available for countries where you subscribe to all records.

Country subscriptions not supported

  • Pay-per-record country subscriptions

  • China

Using OpenData tables

OpenData tables are unique because they report on data that may not be in your Network MDMinstance. They can, however, be used in the same way that other reporting tables can be used.

Example use cases:

  • Join OpenData tables with Network tables or custom tables.

  • Create inbound or outbound data transformation queries.

  • Create custom tables with the query results.

  • Download report results.

  • Save queries as Saved Reports.

Prerequisites

Instance-level access

The OpenData tables display if your Network MDM instance is configured for the following:

  • OpenData country subscriptions

    • Must subscribe to all records for a country; it is not available for pay-per-record subscriptions.

      Note: This applies to US OpenData Email subscriptions also. Emails are available in reporting only if all emails are included in the subscription.

    • The country subscription is enabled and configured in your Network MDM instance (System Interfaces > Veeva OpenData Subscriptions)

  • Network MDM features - The following features must be enabled in Settings > General Settings:

    • Search and Query OpenData - This setting enables you to view records in the OpenData instance that have not been downloaded to your instance.

    • Reporting View - This setting enables the reporting feature in your Network MDM instance.

User-level access

  • User permissions

    Open a user profile (Users & Permissions > Users) and set the permission value in the Additional Permissions section.

    Required Permissions Required Permission Value Description Impacted Users
    Reports Display Tab Allow users to see the Reports tab in the Network menu bar. All users

    SQL Query Editor

    		 Allow Allow users access to the SQL Query Editor feature in the Reports menu. Standard users and Data Managers

    Report Results

    		 Restricted - Limited by user's data permissions Query results observe the user's data permissions (data visibility profiles, inbox task groups) Administrators, Data Managers, System and Data Admins
    Unrestricted Query results have no restrictions applied.  

  • Data visibility profiles

    Open a DVP (Users & Permissions > Data Visibility Profile), and set the permission values in these sections.

    • Permissions section

      Required Permissions Required Permission Value Description Impacted Users
      Health Care Professional Visibility All
      Include (for specific HCPs)       		All - User can view all HCP records for the country in the query results.
      Include - User can see only the HCP records for the country that they have visibility to.       		All users
      Health Care Organization Visibility All, or Include (for specific HCOs)

      All - User can view all HCO records for the country in the query results

      Include - User can see only the HCO records for the country that they have visibility to.

      		All users

    • Search section

      Required Permissions Required Permission Value Description Impacted Users
      Can download/sync records from OpenData Instance True or False Does not restrict users from reporting on OpenData records.
      True - Users can download records from OpenData instance.
      False - Users cannot download records. The download icon is not active.       		All users
      Can search and query OpenData instance True Users with this DVP can access OpenData records for this country. All users

  • Field restrictions

    If there are restrictions on OpenData fields, it will impact the data that users see in query results.

    • No restrictions - Users have access to all fields in the query results.

    • Restricted field - Queries run on OpenData tables for that field will not return results.

      • If the field is used in the where clause, no results are returned at all.

      • If the field is simply listed in the query, the query will run, but no data displays for that field in the column.

Access the reporting tables

In the SQL Query Editor (Reports), the OpenData category displays below any data domains in the tree view.

  • Expand the OpenData category to view folders for each OpenData regional instance that you have access to.

    If your Network MDM instance is connected to an OpenData instance because you subscribe to all records for one or more countries, that instance displays.

    Example

    If you subscribe to all OpenData records for the United States (US), the OpenData NA instance displays in the list. If your subscription does not include all records, or you do not subscribe to US data, the OpenData NA instance does not display.

    To view the list of regional OpenData instances and countries managed in each instance, see OpenData countries.

  • Expand each instance to see the OpenData reporting tables.

    Available tables

    All Veeva object and sub-object tables:

    • HCP

    • HCO

    • Address

    • License

    • ParentHCO

    • Flat hierarchy tables

    Tables that are not included

    Any table that is not an entity table is not included:

    • Custom key

    • Reference Data

    • Revision History

    • Data Loading

    • Data Stewardship

    • Lookup tables

OpenData flat hierarchy tables

A flat hierarchy table is available for OpenData instances that you have access to through this feature.

Flat hierarchy tables display all of the relationships and levels in hierarchies so you can see how HCPs and HCOs rollup to HCOs.

The table has the following naming convention: <OpenData_instance>.flat_hierarchy; for example, opendata_na.flat_hierarchy.

Example flat hierarchy table

OpenData instances

OpenData instances support different regions. The following instances have their own flat_hierarchy table:

  • OpenData APAC - Asia Pacific

  • OpenData EMEA - Europe, Canada, Middle East, Africa

  • OpenData LatAm - Mexico, Caribbean, Central America, and South America

  • OpenData NA - United States

    This instance also has a second table called opendata_na.flat_ownership_hierarchy, which contains US records only.

To review the countries included in each instance, see OpenData countries.

Table updates

Flat hierarchy tables are updated daily.

The tables are read-only; OpenData teams cannot edit or change the contents of the tables. This ensures that you will always have access to the full ownership hierarchy for each OpenData region.

For information and examples for using the flat hierarchy tables, see Flat hierarchy.

OpenData NA Flattened Ownership Hierarchy

The opendata_na.flat_ownership_hierarchy table (US records only) is not set to update by default. It does not display in the OpenData NA category until it is updated for the first time.

To update the table:

  1. Click Data Model > Hierarchy Management.

  2. Open the Ownership Hierarchy table.

  3. In the Flattened Hierarchy Reporting section, click Update Flattened Hierarchy Table, or set a schedule to update the table.

When the data is updated, the table will be available in the SQL Query Editor.

Table data

Country considerations

The tables contain data for all countries in that OpenData regional instance where you subscribe to all records.

Examples

  • If you subscribe to all records for France, Germany, and Italy, the OpenData EMEA reporting tables return results for all three countries.

  • If you subscribe to all records for France and Germany but the Italy subscription is pay-per-record, the OpenData EMEA reporting tables return results only for France and Germany.

Tip: To return results for a specific country, specify the country in your query.

Supported records

The following records and fields are available to report on in the OpenData instance:

  • Records with Valid and Merged_Into record states

    Note: Record state applies to the HCOs and HCPs only. Sub-objects of any record state will be returned. For example, any invalid addresses on a valid HCP will be included in the tables.

  • All record statuses

  • All Veeva standard fields (__v)

    Not all Veeva standard fields are used in every country.

    Note: The fields must be enabled in your Network MDM instance to be available for OpenData reporting.

  • All Veeva objects and sub-objects

  • OpenData subscription fields that you subscribe to (HIN, CIP, Geo Subdivision, NCPDP, and so on)

  • Unsubscribed records

Excluded records

The following records and fields are not included in OpenData reporting results:

  • Records with Invalid and Deleted record states

  • Opted out records

  • Candidate records

  • Custom keys

  • Custom fields

Data updates

Tables contain the most current data from the OpenData regional instance.

Sample queries

OpenData tables can be used to query data in the OpenData instance, or you can JOIN the tables with other Network MDM reporting tables.

newspaper

Sample query 1 - Count of records to be downloaded (filter subscription)

If you add Specialty or Type filters in the OpenData country subscription, you can report on how many new records will be downloaded to your Network MDM instance.

Query

This query uses two values for Specialty fields and two values for the HCP Type field. 

SELECT
        us_opendata_hcps.vid__v
    FROM
        (
            SELECT
        vid__v, EXPLODE specialties AS specialty
    FROM
        opendata_na.hcp
    WHERE
        specialty IS NOT NULL
        AND specialty IN (
            'CHP',
            'CPP'
        )
        AND hcp_type__v IN (
            'D',
            'P'
        )
        AND primary_country__v = 'US'                    
        ) AS us_opendata_hcps 
        LEFT OUTER JOIN 
        (
SELECT
        vid__v, EXPLODE specialties AS specialty
    FROM
        hcp
    WHERE
        specialty IS NOT NULL
        AND specialty IN (
            'CHP',
            'CPP'
        )
        AND hcp_type__v IN (
            'D',
            'P'
        )
        AND primary_country__v = 'US'                    
        AND record_owner_type__v = 'VOD'
        ) AS downloaded_hcps
            ON us_opendata_hcps.vid__v = downloaded_hcps.vid__v
    WHERE
        downloaded_hcps.vid__v IS NULL

Sample results

The report results include a link to the entity's Profile page. If the entity has been downloaded to your Network MDM instance, the Profile page opens when you click the link.

If the entity has not been downloaded to your Network MDM instance, an error displays when you click the link.

Sample query 2 - Count of prescribers in a country

Find the count of all HCPs that are prescribers (HCP type = Prescriber) for a country in an OpenData instance.

Query

This query returns a count for prescribers in the US for the OpenDataNA instance

SELECT
        COUNT (vid__v)
    FROM
        opendata_na.hcp
    WHERE
        hcp_type__v = 'P'
        AND primary_country__v = 'US'

Sample results

Sample query 3 - List of unsubscribed HCPs

See a list of HCPs that have been unsubscribed from OpenData for your Network MDM instance for a specific country.

Note: The Include only Valid and Under_Review checkbox must be cleared for this report; otherwise, no records will be returned in the results.

Query

This query specifies HCPs in the US. 

SELECT
        vid__v
    FROM
        opendata_na.hcp
    WHERE
        primary_country__v = 'US'
        AND record_state__v = 'VALID'
        AND vid__v IN (
            SELECT
                    vid__v
                FROM
                    hcp
                WHERE
                    primary_country__v = 'US'
                    AND record_owner_type__v = 'VOD'
                    AND record_state__v = 'DELETED'
        )

Results

Sample query 4 - List of HCOs related to already downloaded HCPs or HCOs

This query finds not-yet-downloaded HCOs (one level up) that are related to already downloaded active HCPs or active HCOs. The relationship to the HCO must also be active. This helps you to find missing parents in your hierarchy.

Query

This query returns a list HCO Network IDs for the US. 

SELECT
        distinct us_opendata_hcos.parent_hco_vid__v
    FROM
        (
            SELECT
                    parenthco.parent_hco_vid__v
                FROM
                    hcp INNER JOIN opendata_na.parenthco
                        ON hcp.vid__v = parenthco.entity_vid__v
        ) us_opendata_hcos LEFT OUTER JOIN (
            SELECT
                    hco.vid__v
                FROM
                    hco
                WHERE
                    hco_status__v = 'A'
                    AND primary_country__v = 'US'
                    AND record_owner_type__v = 'VOD'
        ) downloaded_us_hcos
            ON downloaded_us_hcos.vid__v = us_opendata_hcos.parent_hco_vid__v
    WHERE
        downloaded_us_hcos.vid__v IS NULL

Results

OpenData primary tables

Use these hierarchy tables to report on primary hierarchies in OpenData instances.

  • OpenData Flat Primary Hierarchy - Use to report on the records that Veeva OpenData considers as primary.

  • OpenData Rollup Hierarchy - Use to see how HCPs and HCOs roll up within all the levels within the Hierarchy. This is a way to denormalize the hierarchy structure.

Note: The tables will be available in the SQL Query Editor in June 2026. They are enabled by default if Reporting on OpenData is enabled in your Network MDM instance.

OpenData Flat Primary Hierarchy

The opendata_<region>.flat_primary_hierarchy table stores the path of the primary relationship for each record.

The table follows the logic used for the existing flat hierarchy table. It identifies the source entity, it's primary ancestor, and provides the shortest path distance between the two entities. For details, Flat hierarchy in the Veeva Network MDM Online Help.

Table definition

The table definition is based on the Parent HCO object where the is_primary_relationship__v field is true.

The table contains the following default fields (columns). Additional columns can display depending on the region.

Field Name Field Label Field Type Description
ancestor__vid__v Ancestor Network ID Network ID Network ID of the ancestor record.
entity_vid__v Network ID of Owner Network ID Network ID of the entity record.
ancestor_name__v Ancestor Entity Name Network ID Name of the ancestor record.
ancestor_type__v Ancestor Entity Type Reference Type of the ancestor record.
entity_name__v Entity Name Text Name of the entity record.
entity_type__v Entity Type Reference Type of the child record.
hco_type__v_ancestor HCO Type (Ancestor) Reference HCO type of the ancestor record.
hco_type__v_entity HCO Type (Entity) Reference HCO type of the entity.
hcp_type__v_entity HCP Type (Entity) Reference HCP type of the entity.
modified_date Modified Date Date Time The last modified date of an entity or relationship in the path.
path_distance Path Distance Number Distance of the entity to the ancestor. Direct relationships have 1 as the distance.

A value of 2 means that there is another HCO between the entity and the parent HCO.
path_info Path Info Text Full path from entity to ancestor using names. The names are separated by the pipe (|) character.
path_info_vid Path Info with Network ID Text Full path from entity to the ancestor using Network IDs. The Network IDs are separated by the pipe (|) character. The Network ID of the entity displays first and then the parent HCO Network ID.
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.
primary_country__v_ancestor Primary Country (Ancestor) Reference The primary country of the ancestor record.
primary_country__v_entity Primary Country (Entity) Reference The primary country of the entity.
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.
veevaid__v_ancestor Veeva ID (Ancestor) Text The Veeva ID of the ancestor record.
veevaid__v_entity Veeva ID (Entity) Text The Veeva ID of the entity.

Example report

Query the table to display the flat primary hierarchy for a specific HCP. 

SELECT *
    FROM
        opendata_na.flat_primary_hierarchy
    WHERE
        veevaid__v_entity = 'V01242979819376083968'

Results

Roll-up Hierarchy table

The opendata_<region>.rollup_hierarchy table is a new materialized view designed to resolve specific analytical challenges. It simplifies complex, variable hierarchies by flattening them into fixed levels, such as L1 through L4. This allows sales and incentive compensation teams to run reports without managing the underlying hierarchy depth.

Using this table will make it easier to report on hierarchies downstream, especially if you're looking for fixed levels.

Key details

  • The reporting table is available only for querying OpenData instances.

  • The roll-up hierarchy is not part of the data model in your Network MDM instance.

  • Changes to the hierarchy must be submitted on the HCP or HCO record.

  • The materialized view is calculated on a daily basis.

Table definition

The table is based on the Parent HCO object where the is_primary_relationship__v field is true.

The different hierarchy levels are represented as the following columns:

Label Name Description
Entity Object Veeva ID entity_veevaid__v Veeva ID of the HCP or HCO that is the source of this rollup.
Entity Name entity_name__v Name of the HCP or HCO that is the source of this rollup.
Entity Object Type entity_object_type__v Either HCP or HCO.
Primary Affiliation Veeva ID prim_affl_veevaid__v Name of the primary parent this HCO or HCP rolls up to.
Primary Affiliation Name prim_affl_name__v Veeva ID of the primary parent of this HCO or HCP rollup.
Main Org Veeva ID main_org_veevaid__v Primary institution-level facility Veeva ID (e.g., hospital/clinic). Acts as an HCP's main workplace or the overarching institution for a smaller HCO.
Main Org Name main_org_name__v Primary institution-level facility name (e.g., hospital/clinic). Acts as an HCP's main workplace or the overarching institution for a smaller HCO.
Top Org Veeva ID top_org_veevaid__v The Veeva ID of the ultimate parent organization (e.g., Global Headquarters, Health System) of the HCP's primary affiliation.
Top Org Name top_org_name__v The name of the ultimate parent organization (e.g., Global Headquarters, Health System) of the HCP's primary affiliation.
Hospital Parent Veeva ID hospital_parent_veevaid__v Veeva ID of the hospital that this HCO or HCP rolls up to.
Hospital Parent Name hospital_parent_name__v Name of the hospital that this HCO or HCP rolls up to.
Regional Health System Veeva ID regional_system_veevaid__v The Veeva ID of the subsidiary system this HCO or HCP rolls up to.
Regional Health System Name regional_system_name__v Name of the subsidiary system this HCO or HCP rolls up to.

Example

Table logic

Each table contains the following components:

  • Source - The starting point (bottom) of the hierarchy. Fields: Entity Object Veeva ID, Entity Name, Entity Object Type.

  • Roll-Up Levels - The fixed levels for each hierarchy. The Veeva ID and Name of the record is stored for each level.

    Due to the varying health system layers globally, each OpenData region has their own rules to define the roll-up levels.

Regional rules to define roll-up levels

  US EMEA APAC LatAm
Primary Affiliation Direct primary affiliation of the source entity.

If there is no primary, the source record is repeated.
Main Org Direct primary of the primary affiliation.

If there is no primary, the primary affiliation record is repeated.

 Direct primary of the primary affiliation & the record must be a hospital or an Italian Outpatient Center.

If there is no primary, then the primary affiliation record is repeated.

 Direct primary of the primary affiliation & the record cannot be a department.

If there is no primary, then the primary affiliation record is repeated.

 Direct primary of the primary affiliation.

If there is no primary, the primary affiliation record is repeated.
Top Org Highest level in the hierarchy.

If there is no record above the Main Org, then the Main Org is displayed.

Highest level in the hierarchy that meets the following criteria for these countries (primary_country__v):

France
HCO Local Type = Territorial Hospital Group (THGE)
or
HCO Ownership = Private (PRIV)
OR
Germany
HCO Local Type = Health System (HSEY)
OR
Spain
HCO Local Type = Health Care System Administration(HCSA) or Health Insurance Provider (HIPE)
OR
United Kingdom
HCO Local Type = Health and Social Care Partnership (HASC), Integrated Care System (ICSN), or Health Board (HBEO)

 

Otherwise, the record Main Org will be used.

Highest level in the hierarchy. If there is no record above the Main Org, then the Main Org is displayed. Highest level in the hierarchy. If there is no record above the Main Org, then the Main Org is displayed.
Hospital Parent First hospital in the hierarchy. First hospital in the hierarchy. Highest hospital in the hierarchy. First hospital in the hierarchy.
Regional Health System Second highest IDN in the hierarchy. First health system in the hierarchy. Highest health system in the hierarchy. First health system administrative record in the hierarchy.

Examples

Example 1 - Roll-ups to a specific HCO in a health system

In this example, we'll report on the levels in the Mayo Clinic that roll-up to the Mayo Clinic-La Crosse WI hospital.

SELECT
        *
    FROM
        opendata_na.rollup_hierarchy
    WHERE
        top_org_veevaid__v = 'V02242976927730631680'
        AND hospital_parent_veevaid__v = 'V02242976961360561152'

Results

Example 2 - Roll-ups for an HCP

Report on the roll-up levels for a specific HCP. 

SELECT
        *
    FROM
        opendata_na.rollup_hierarchy
    WHERE
        entity_veevaid__v = 'V01242979819376083968'

Results

Export to data warehouse

To integrate this data into your data warehouse, include the tables in your target subscription using transformation queries.

Since delta tracking is unavailable for the roll-up hierarchy and flat hierarchy, we recommend a truncate-and-load strategy (completely clear and reload the data) to push data to your data warehouse. This is the best way to ensure your data remains accurate and consistent.

Data transformation queries

OpenData reporting tables can be used in queries for inbound and outbound data transformations.

Sandbox considerations

The OpenData data results in your Sandbox inbox might be different from the data in your Production instance for the following reasons:

  • Sandbox instances are not updated daily.

  • Veeva OpenData subscriptions are often set for all records for the country in Sandboxes. If you do not subscribe to all records for a country, you might have access to the feature for that country in your Sandbox, but not in your Production instance.