Reporting on OpenData

AD
DM

Advanced reporting users can report on country data within OpenData instances.

Reporting tables for OpenData are available in the SQL Query Editor. Users with access to Network 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. They are not available for pay-per-record country subscriptions.

Note: Reporting on OpenData is not available for subscriptions for China.

For access requirements, see the Instance-level access and User-level access sections below.

Using OpenData tables

OpenData tables are unique because they report on data that may not be in your Network instance. 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.

Access the reporting tables

The OpenData tables are available in the SQL Query Editor (Reports). The OpenData category displays below the data domains in the tree view.

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

    If your Network 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 the US or Canada at all, the OpenData NA instance does not display.

  • 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

You can access a flat hierarchy table 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

The following regions have their own flat_hierarchy table:

  • OpenData APAC - Asia Pacific

  • OpenData EMEA -Europe, Middle East, Africa

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

  • OpenData NA - North America

    The OpenData NA region also has a second table called opendata_na.flat_ownership_hierarchy, which contains US records only.

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 Reporting on hierarchies.

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 instance where you subscribe to all records. For example, if you subscribe to all records for Canada and the US, the tables in the OpenData NA instance returns results for both countries.

Tip: If you want to return results for a specific country, remember to specify the country in your query.

If you subscribe to all records for France but your subscription for Italy is pay-per-record, the OpenData EMEA reporting tables will only return results for France. Data for Italy will not be included in the tables.

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

Instance-level access

This feature is available only if your Network instance is configured for the following:

  • OpenData country subscriptions

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

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

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

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

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

User-level access

Administrators must set the following permissions on individual user pages and on data visibility profiles to give users access to OpenData data in Network reports. Field restrictions are also considered for data returned in the report results.

User page

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, or 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.

Sample queries

OpenData tables can be used to query data in the OpenData instance, or you can JOIN the tables with other Network 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 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 instance, the Profile page opens when you click the link.

If the entity has not been downloaded to your Network 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 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 Veeva IDs (VIDs) 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

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.