Mapping Keys for Import

There are scenarios where the 3rd party data may have different values for a Study, Site, Subject or Event in the data file that don’t match any EDC values, based on how the Study is configured in EDC. Instead of having the data vendor modify the data, which can be expensive or impossible, CDB supports the ability to map incoming values in a 3rd party data load to existing EDC data.

Prerequisites

Required Permissions

Users with the standard CDMS Super User study role can perform the actions described below by default. If your organization uses custom Study Roles, your role must grant the following permissions %}

Type Permission Label Controls
Standard Tab Workbench Tab

Ability to access and use the Data Workbench application, via the Workbench tab
Functional Permission View Import

Ability to access the Import page
Functional Permission Manage Key Mappings

Ability to create and manage key mappings for import

If your Study contains restricted data, you must have the Restricted Data Access permission to view it.

Learn more about Study Roles.

Mapping Types

There are four types, or levels, of key mapping: Study, Site, Subject, and Event. Workbench includes supplemental data in the ZIP file depending on the type of key mapping template. The supplemental data for each key mapping type is as follows:

  • Study: A CSV file that lists the Study Name and the External ID of the study.
  • Site: A CSV file that lists the Site Number and Site Name for all the Sites in the Study Environment.
  • Subject: A CSV file that lists all the Subjects in the Study.
  • Event:
    • A CSV file that lists all the Event Definitions, based on the last export from EDC.
    • A CSV file that lists all the Event Group Definitions, based on the last export from EDC.

Key maps are defined for a Study on a Source/Key Mapping Level, so it is possible to have up to four (4) different key mappings per Source. Any updates for a specified Source and Level combination replace existing mappings for the same Source and Level combination.

Accessing Key Mapping

You can access key mappings from the Import area of CDB. You can navigate to the Import area from the Navigation Drawer () or, after you select your Study, from the Study menu () on the Studies page.

Once you’ve entered the Import area, click vpn_key Key Mappings in the header to open the subtab.

Columns

The following columns display in Import > vpn_key Key Mapping:

Column Description
Source The Name of the Source for the key mapping.
Key Mapping Type The Type (level) of the key mapping.
Status The status of the mapping file upload. The possible statuses are Pending, Complete, and Error.
Uploaded The date and time that the mapping file was uploaded (UTC).

Create the Mapping File

To download a template:

  1. Navigate to Import > vpn_key Key Mapping.
  2. Click Key Map Template.
  3. Select a Template.

Study

To create a Study mapping:

  • For Mapping Type, enter “name” or “external_id”, based on which value you want to match against.
  • For Study, enter the Study Name or External ID as it exists in Workbench (listed in the Study_Definitions.csv file).
  • For Incoming Value, enter the value for Study as it exists in the import package.

Create additional rows for each possible key value pair.

The table below is an example Study_mapping_keys.csv file:

Mapping Type Study Incoming Value
name Cholecap Cholecap_Protocol1

Site

To create a Site mapping:

  • For Mapping Type, enter either “name” or “number”, based on which value you want to match against.
  • For Site, enter the Site Name or Study Site Number (based on your selection for Mapping Type) as it exists in Workbench (listed in the Sites.csv file).
  • For Incoming Value, enter the value for Site as it exists in the import package.

Create additional rows for each possible key value pair.

The table below is an example Site_mapping_keys.csv file:

Mapping Type Site Incoming Value
name Pleasanton General Hospital West PGH-W
name Pleasanton General Hospital East PGH-E

Subject

To create a Subject mapping:

  • For Subject, enter the Subject ID as it exists in Workbench.
  • For Incoming Value, enter the Subject ID as it exists in the import package.

Create additional rows for each possible key value pair.

The table below is an example Subject_mapping_keys.csv file:

Subject Incoming Value
0101-10001 101-10001
0101-10002 101-10002
0101-10003 101-10003
0102-10001 102-10001
0102-10002 102-10002

Event

To create an Event mapping:

  • For Mapping Type, enter “name” or “external_id”, based on which value you want to match against.
  • For Event, enter the Event Name or External ID as it exists in Workbench.
  • For Event Sequence, enter the Sequence Number as it exists in Workbench.
  • For Incoming Value, enter the Event as it exists in the import package.

Create additional rows for each possible key value pair.

The table below is an example Event_mapping_keys.csv file:

Mapping Type Event Event Sequence Incoming Value
name Visit_1 1 Visit_1
name Visit_2 1 Visit_2
name Visit_3 1 Visit_3
name Visit_1 2 Visit_4
name Visit_2 2 Visit_5
name Visit_3 2 Visit_6

Import the Mapping

To import a key mapping file:

  1. Navigate to Import > vpn_key Key Mapping.
  2. Click Import Mapping.
  3. Select a Source.
  4. Select a Key Type.
  5. Drag and drop a file into the Choose a File (CSV) area.
  6. Click Import.

Import Statuses

Workbench tracks key mapping imports using these three (3) statuses:

Status Description
Pending Import is incomplete.
Complete Import finished successfully.
Error Import did not finish due to one or more errors.

Errors

If there were any errors during import, you can view them from the Errors panel.

Study

The table below lists possible errors for study key mappings:

Message Explanation Resolution
One or more required column headers “Mapping Type”, “Study”, and “Incoming Value” are missing. Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. Update your CSV file to have the “Mapping Type”, “Study”, and “Incoming Value” columns with those exact labels.
File contains columns beside “Mapping Type”, “Study”, and “Incoming Value”. Your mapping CSV should have only three columns: Mapping Type, Study, and Incoming Value. Remove any columns other than Mapping Type, Study, and Incoming Value.
File contains invalid “Mapping Type” values. Value must be either “External_ID” or “Name” Your Mapping Type column contains an invalid value. Update the values in your Mapping Type column to use only “External_ID” or “Name”.
File contains mixed “Mapping Type” values. Value must either all be “External_ID” or “Name”. You can only use one Mapping Type per key mapping file. Update the mappings so that you only use External_ID or Name for mapping type instead of both.
File does not contain any records. Your mapping file doesn’t have any mapping rows. Enter mappings into your file.
File contains multiple rows where the “Incoming Value” are the same. Your mapping file contains one or more rows with the same value for Incoming Value. Remove the duplicate rows or update the Incoming Value column so that there are no duplicates.

Site

The table below lists possible errors for site key mappings:

Message Explanation Resolution
One or more required column headers “Mapping Type”, “Site”, and “Incoming Value” are missing. Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. Update your CSV file to have the “Mapping Type”, “Site”, and “Incoming Value” columns with those exact labels.
File contains columns beside “Mapping Type”, “Site”, and “Incoming Value”. Your mapping CSV should have only three columns: Mapping Type, Site, and Incoming Value. Remove any columns other than Mapping Type, Site, and Incoming Value.
File contains invalid “Mapping Type” values. Value must either be “Name” or “Number”. Your Mapping Type column contains an invalid value. Update the values in your Mapping Type column to use only “Name” or “Number”.
File contains mixed “Mapping Type” values. Value must either all be “Name” or “Number”. You can only use one Mapping Type per key mapping file. Update the mappings so that you only use Number or Name for mapping type instead of both.
File does not contain any records. Your mapping file doesn’t have any mapping rows. Enter mappings into your file.
File contains multiple rows where the “Incoming Value” are the same. Your mapping file contains one or more rows with the same value for Incoming Value. Remove the duplicate rows or update the Incoming Value column so that there are no duplicates.

Subject

The table below lists possible errors for subject key mappings:

Message Explanation Resolution
One or more required column headers “Subject” and “Incoming Value” are missing. Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. Update your CSV file to have the “Subject” and “Incoming Value” columns with those exact labels.
File contains columns beside “Subject” and “Incoming Value”. Your mapping CSV should have only two columns: Subject and Incoming Value. Remove any columns other than Subject and Incoming Value.
File does not contain any records. Your mapping file doesn’t have any mapping rows. Enter mappings into your file.
File contains multiple rows where the “Incoming Value” are the same. Your mapping file contains one or more rows with the same value for Incoming Value. Remove the duplicate rows or update the Incoming Value column so that there are no duplicates.

Event

The table below lists possible errors for event key mappings:

Message Explanation Resolution
One or more required column headers “Mapping Type”, “Event”, “Event Sequence”, and “Incoming Value” are missing. Your mapping CSV file is missing one of the required columns, or the CSV file has a header with the wrong label. Update your CSV file to have the “Mapping Type”, “Event”, “Event Sequence”, and “Incoming Value” columns with those exact labels.
File contains columns beside “Mapping Type”, “Event”, “Event Sequence”, and “Incoming Value”. Your mapping CSV should have only four columns: Mapping Type, Event, Event Sequence, and Incoming Value. Remove any columns other than Mapping Type, Event, Event Sequence, and Incoming Value.
File contains invalid “Mapping Type” values. Value must either be “External_ID” or “Name”. Your Mapping Type column contains an invalid value. Update the values in your Mapping Type column to use only “Name” or “External_ID”.
File contains mixed “Mapping Type” values. Value must either all be “External_ID” or “Name”. You can only use one Mapping Type per key mapping file. Update the mappings so that you only use External_ID or Name for mapping type instead of both.
File contains invalid “Event Sequence” values. Value must be an integer greater than 0. The Event Sequence column requires an integer greater than 0. Update the Event Sequence column to only contain integers greater than 0.
File does not contain any records. Your mapping file doesn’t have any mapping rows. Enter mappings into your file.
File contains multiple rows where the “Incoming Value” are the same. Your mapping file contains one or more rows with the same value for Incoming Value. Remove the duplicate rows or update the Incoming Value column so that there are no duplicates.

Download a Mapping File

You can download current mapping files. To download a mapping file:

  1. Navigate to Import > vpn_key Key Mapping.
  2. In the list, locate the Key Mapping that you want to download.
  3. From the Mapping menu (), select Download Mapping.

Delete a Mapping

You can delete mappings. If you delete a mapping, any future packages will be unable to import using those keys.

To delete a key mapping:

  1. Navigate to Import > vpn_key Key Mapping.
  2. In the list, locate the Key Mapping that you want to delete.
  3. From the Mapping menu (), select Delete Mapping.
  4. Optional: In the confirmation dialog, select Do not show this again to not require confirmation on future deletes.
  5. Click OK.