Reports

Reporting on OpenData hierarchies

Advanced reporting users can use two new hierarchy tables to report on data in OpenData instances.

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

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

These tables are available by default if Reporting on OpenData is enabled in your Network MDM instance.

For detailed information about reporting on data in the OpenData regional instances, see Reporting on OpenData.

Access the OpenData tables

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

1. On the Network menu bar, click Reports > SQL Query Editor.

2. In the tree view, expand the OpenData category and the region folder, for example, OpenData NA.

   The tables are supported for the following regions:

   • NA (US)

   • EMEA

   • AU

   • LatAm

OpenData Flat Primary Hierarchy

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

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 fields (columns)

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

OpenData Primary Hierarchy table (rollup)

The opendata_<region>.rollup_hierarchy table stores a fixed-level hierarchy of OpenData's primary hierarchy for each record.

Table definition

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

Each OpenData regions has its own rules to define the records that will be selected as primary in the bucket. The level can be repeated in the hierarchy if there is a missing level.

Note: The table uses OpenData 2.0 fields. The fields do not need to be enabled in your Network MDM instance to see the data.

Table components

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.

  • Primary Affiliation - The primary direct parent for the child HCP/HCO.

  • Main Org - The HCP's main workplace.

  • Top Org - The HCO at the top of the hierarchy.

  • Hospital Parent - The hospital the HCO rolls up to.

  • Regional Health System - The HCO that is a child of a Health System and has key_hco_network__v = 'Y'.

Example report

Report on the rollup levels for a specific HCP.

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

Results

Data for hierarchy tables

The tables contain the most current data from the regional OpenData instance.

Veeva ID links in results

Report results that include the Veeva ID (veevaid__v) field display a link for each ID. Click the link to open the record profile in a new browser tab.

This enhancement is enabled by default.

Revision history reporting

Advanced reporting users can now easily track the source of record changes by including DCR IDs in their revision history reports (SQL Query Editor > Revision History).

On a record's Revision History, each revision made from a change request includes the DCR ID. If the revision did not originate from a change request, this field is empty.

The DCR ID (change_request_id) is now available in the Revision History reporting table. Join the revision table to the change request table using the new column. The report results display the DCR ID which you can click to open the associated task.

This enhancement is enabled by default in your Network MDM instance.

Available data

Data will be populated for all new revisions created by change requests. Historical data will not be back-filled.

Example query

SELECT 	revision.entity_vid__v,
		revision.record_version__v,
		change_request.change_request_id,
		change_request.created_by AS "Requestor"
FROM revision
JOIN change_request USING (change_request_id)

Example results