Loading Data Map Templates

A Data_Map_Template_vod record defines how the data is loaded into the Fixed Reports repository as well as some metadata about the data contained within the sales file. Data map templates represent the mapping of the fields contained within the sale data files and the fields contained within the Fixed Reports repository. Similar to the standards of major data providers, the Fixed Reports repository and mapping process uses a wide table structure. See the Veeva CRM Data Model Guide for information on the Sales_Transaction_vod object that mirrors the Fixed Reports Repository.

Data is not loaded into the Sales_Transaction_vod object as this object exists for reference purposes only.

Data_Map_Template_vod records are associated with sales data files as part of the file loading process. See Loading Sales Data Files for more information. As file formats do not regularly change, data map templates are a one-time setup.

Data_Map_Template_vod records can be manually created from the Data Map Templates tab. This tab should be restricted to admins only.

Field

Description

Data Map Template Name

The Name of the Data Map Template.  If the Data Map Template record is active, then this name must be unique.

Column Labels Included

The Column Labels Included check box determines if the first row of data contained in the Sales Data file will contain column headers. If this field is checked, then the first row of data in the file will be ignored and not loaded.

Column Label

The Column label defines the type of data for the data template that corresponds to the data stored within the uploaded file. A few standard examples include: TRx, NRx, NQt, TQt, DDD$, etc. This value is used to display the Units type on the Analytics Reports page.

Second Column Label

If a second data type exists in the data file, the Second Column Label represents the Column Label that corresponds to the second group of sales data buckets in a sales file. As an example, the Column Label will correspond to buckets B1 through B24 and the Second Column Label will correspond to Buckets B25 through B48.

Formulary for Rep Label

Indicates which column of data (First Column Label or Second Column Label) to use for Formulary for Rep. If the Formulary for Rep feature is enabled and this field is not populated, Fixed Reports does not know which data type to use in the report display.

Buckets

Determines the number of data buckets contained in the sales data file for a given Column Label. As an example, if the sales data file has two years of monthly data for each Column Label, then the Buckets field value is 24.

Scale

Determines what the scale of each of the buckets of data represents (Weekly and Monthly are the available options).

Active

Determines if the Data Map Template record will be available for selection on the Analytics Admin page.

Payer/Plan Mapped?

Determines if the file that is being loaded contains Payer/Formulary information as part of the Data File, such as an Xponent PlanTrak file.

Markets Mapped?

Defines whether or not the sales data file contains Multiple Markets of Sales Data. This requires that the Market is defined for each record within the Sales Data file and that a Data Map Template Field record exists to map this column to the Market_vod__c field.

Territory Names Included?

Defines if pre-aggregated data for Territory level information is contained within the Sales Data file.

Override Account Threshold

Select this check box to always download all Sales Data for all Accounts to which the user has access to.

Decimal Places

Determines how many digits to the right of the decimal point will be displayed for all non-percentage figures. The least significant visible digit will be rounded.

Contains Goals

Select this check box to indicate that this Data Map Template refers to a file that contains sales goals and targets.

External ID Used for data loading.
Enable for Offline Designates that the data types defined in the template should be downloaded to the mobile device for offline Fixed Reports. Used in conjunction with the VMOC WHERE clause for the Data Map Template object.
Incremental Flag to determine whether the data file is to be loaded using the JD-NET upload process.

Below is the Data_Map_Template_vod object page layout. For each Data_Map_Template_vod record, the Data_Map_Template_Field_vod records represent the mapping of columns to fields. Column numbers should begin at 1 for the leftmost column.

Data_Map_Template_field_vod records can map to the following fields on the Sales_Transaction_vod object:

Field Description

B1_vod - B208_vod

The data buckets defined for sales data, where B1_vod is the most recent. Can be used as weekly or monthly buckets. The number of bucket fields used is determined by the Buckets field on the Data Map Template.

Brick_vod Stores the lowest granular level of the Brick hierarchy to which the Sales Data record corresponds.
Child_ID_vod Indicates the identifiers of any child accounts, as defined by the uploaded ID mapping file.
Data_Channel_vod Maps to the Name field of Analytics Data Channel and determines the Channel of delivery (Mail Order, Retail, etc.) corresponding to the Sales Transaction record.
ID_vod Links Sales Transaction records to Account with matching ID.
Market_vod The associated Market of the Sales Data Transaction.
Payer_Id_vod The payer Id for Formulary for the Rep.
Payer_Plan_vod The textual representation of the Payer Plan for a Sales Transaction record. Used with files such as Plantrak.
Product_Group_vod Maps to the Name field of Analytics Product Group Map and determines the Product Group corresponding to the Sales Transaction record.
Territory_Name_vod Indicates aggregated levels of data when a customer defines the aggregation of Territory level data for rollups.
Type_vod Corresponds to the Data Template used when loading the data file and provides information about the data contained within the Sales Transaction record, such as units, # of columns, etc.
Zip_vod Stores the zip code where the Sales Transaction takes place.

Considerations

  • Each data map template is related to up to two labels (i.e., TRx, NRx, Units, etc.)
  • A data map template marked Active must have a unique name
  • For a given data map template, the column numbers for the Data_Map_Template_Field_vod records must be unique
  • If a sales data file contains multiple markets, then a column must exist within the sales data file for each record that defines the market. The Market column must be mapped in the data map template fields.
  • If a sales data file containing payer data is loaded, you must also load a corresponding file with sales data rolled up to the account level. Each file has its own Data_Map_Template_vod record. The payer file must have the Payer_Plan_Mapped_vod check box selected. This allows both payer and non-payer charts to display correctly.
  • If pre-aggregated territory level data is loaded, it takes precedence over any account level data that may have been loaded with the same column labels
  • If the Decimal_Places_vod field is left blank, then figures across all reports display without any rounding manipulation (i.e. displayed as formatted within the loaded sales file) up to three decimal places. Within the pop-ups that display when hovering over data points on the various Fixed Reports graphs, numeric figures are displayed to the nearest tenth, except for percentage figures, which are displayed to the nearest hundredth.
  • Data map templates can be loaded either manually through Veeva CRM Online or via the Apex Data Loader