Importing 3rd Party Data

CDB allows you to import data from 3rd party systems for cleaning and reporting in the CDB Workbench application. This is intended for importing subject data that exists outside of the subject’s Casebook in Vault EDC, for example, data about a Subject from an IRT system.

Preparing Data for Import

You can import your data as a series of CSV files to your CDB vault’s FTP server. To import your data, you’ll first need to create the CSV files, which list your clinical data, and a manifest file (.json), describing your data and how CDB should process it. Then, you’ll create a ZIP package (.zip) containing all of those files for import into CDB.

Data CSVs

Create a CSV for each data collection Form in your study that is independent of EDC. For each CSV, you must provide four (4) required columns, and then a column for each execution data item. For the four required columns, you can name them whatever you choose, as long as you define those names in your manifest file for each CSV. Note that these column names are case-sensitive and must be an exact match to the values provided in your manifest file. Aside from the study, site, subject, event, and sequence columns, CDB considers every column to be a data item and will import it as such.

Here is an example CSV file:

In this example, we have the following column mappings:

• Study: STUDY_ID
• Site: SITE
• Subject: SUBJECT_ID
• Event: VISIT

The INITIALS, DOB, GENDER, and RACE columns are all data items.

Manifest File

Your manifest file provides the Study and Source for CDB, lists the files in your ZIP, and for each file, lists which columns map to the required data points.

Source is a custom defined value that allows you to identify the contents of the package. This value is stored in the Source field in Workbench, so you can use it to identify data from this package in Workbench via CQL. The Source value must be unique within your Study.

For your Study, provide the Name of your Study (study__v) record. For your Study, provide the Name of your Study (study__v) record. If your study’s name contains a whitespace character, you must use the value with the whitespace in your manifest file, but then you must replace the whitespace with an underscore (_) in your data files.

For Source, specify the source of the data. Vault applies this source to all imported data. Then, as an array, list each CSV file, including its filename (with the extension) and its column mappings, as the value for “data”.

You can choose to include configuration metadata about each data item in your import file. See details below.

Example Manifest: Single Form

Here is an example manifest file for the Deetoza study that imports a package from an eCOA containing the Survey form:

{
  "study": "Deetoza",
  "source": "eCOA",
  "data": [
    {
      "filename": "Survey.csv",
      "study": "protocol_id",
      "site": "site_id",
      "subject": "patient",
      "event": "visit_name"
    }  
  ]   
}

Example Manifest: Multiple Forms

Here is an example manifest file for the Deetoza study that imports a package from a laboratory vendor, containing the Chemistry and Hematology forms:

{
  "study": "Deetoza",
  "source": "lab",
  "data": [
    {
      "filename": "Chemistry.csv",
      "study": "STUDY_ID",
      "site": "SITE_ID",
      "subject": "SUBJECT_ID",
      "event": "VISIT",
      "formsequence": "LAB_SEQ"
    },
    {
      "filename": "Hematology.csv",
      "study": "STUDY_ID",
      "site": "SITE_ID",
      "subject": "SUBJECT_ID",
      "event": "VISIT",
      "formsequence": "LAB_SEQ"
    }
  ]
}

You could also use a Row ID mapping and import Chemistry and Hematology lab data using a single CSV file. In that CSV file, you could use a Lab Test Set column to indicate Chemistry or Hematology for the row.

{
  "study": "Deetoza",
  "source": "lab",
  "data": [
    {
      "filename": "Labs.csv",
      "study": "STUDY_ID",
      "site": "SITE_ID",
      "subject": "SUBJECT_ID",
      "event": "VISIT",
      "rowid": ["LAB_TEST_SET", "LAB_TEST_SEQ"]
    }
  ]
} 

Optional: Event Matching

By default, Workbench matches the Event from the form’s CSV file to the Name of the matching Event Definition record in Vault EDC. You can also choose to have Workbench match the Events based on the event definition’s External ID. You can also define a default Event in the manifest file and automatically assign all rows to that Event. If your data was collected outside the event scheduled in EDC, you can choose to not map to any EDC event and have Workbench create events as needed to match those in your import package.

If your Event is within a repeating Event Group, Workbench uses the event definition’s External ID and appends a sequence number to it for matching. The sequence number increments for each unique Event in the file. If an Event is reused in more than one Event Group, it isn’t considered repeating. Instead, Workbench matches it to the first matching Event in the EDC schedule.

Default Behavior

If you don’t include any event matching configuration in your manifest file, the following defaults apply:

• Workbench matches using the Name of the Event Definition.
• Workbench attempts to match with existing EDC Events. If there is no matching Event in EDC, Workbench creates a new Event.

This default behavior is equivalent to the manifest configuration below:

{
  "study": "Deetoza",
  "source": "Labs", 
  "edc_matching": {
    "event": {
      "target": ["name"],
      "generate": true
    } 
  }
}

Match on Name (Default)

If you don’t include edc_matching in your manifest file, Workbench automatically matches using the Name of the Event Definition. You can also specify Name-based batching in the manifest file.

{
  "study": "Deetoza",
  "source": "Labs", 
  "edc_matching": {
    "event": {
      "target": ["name"]
    } 
  }
}

Match on External ID

To match on the External ID (formerly known as OID) of the Event Definition, you must include edc_matching and set the event target to external_id. See the example excerpt below:

{
  "study": "Deetoza",
  "source": "Labs", 
  "edc_matching": {
    "event": {
      "target": ["external_id"]
    } 
  }
}

Set a Default Event

You can choose an Event as the default Event from the manifest file, instead of listing an Event in the CSV. Then, Workbench automatically assigns all rows in that CSV file to that Event. This is controlled by the default key. Use the Name of the Event Definition for default. You can set a default for a specific data file or set a default at the package level.

Example: Package-level Default Event

{
  "study": "Deetoza",
  "source": "lab",
  "edc_matching": {
     "event": {
       "default": "treatment_visit"
     }
   },
  "data": [
    {
      "filename": "Labs.csv",
      "study": "STUDY_ID",
      "site": "SITE_ID",
      "subject": "SUBJECT_ID",
      "event": "VISIT",
      "rowid": ["LAB_TEST_SET", "LAB_TEST_SEQ"]
    }
  ]
} 

Example: File-level Default Event

{
  "study": "Deetoza",
  "source": "Labs",
  "edc_matching": {
    "event": {
      "default": "treatment_visit",
    }
  }
}

Handling of Unmatched Events

By default, if Workbench can’t match an Event to one in EDC, either by Name or External ID, Workbench creates new Events for any non-EDC events that are specific to the Source. These new Events become part of the overall Workbench Header Event record (accessible via CQL from @HDR.Event). This is controlled by the generate key. The manifest configuration of the default behavior is generate: true. If your data was collected outside the EDC event schedule, you can choose to not match with any EDC events by setting generate to false. If you set generate to false, Workbench won’t create new Event Definitions. Instead, import will fail for any rows without matching EDC Events.

Example: Match on External ID and Ignore Unmatched Events

{
  "study": "Deetoza",
  "source": "Labs",
  "edc_matching": {
    "event": {
      "target": ["external_id"],
      "generate": false
    }
  }
}

If your data was collected entirely outside the EDC schedule, you can choose to have Workbench create new Events for each Event, without trying to match to an EDC Event. To do so, set event to false, as shown in the example below:

{
  "study": "Deetoza",
  "source": "Labs", 
  "edc_matching": {
    "event": false
  }
}

Optional: Form & Item Group Mapping

You can choose to map rows in your CSV to different Forms and Item Groups, including defining how Workbench interprets repeating forms nad item groups within your data set. This allows Workbench to transform a single CSV into multiple Forms and Item Groups, instead of a single Form and single Item Group.

For example, a Labs form could contain multiple Item Groups - one Item Group per each lab test category. You would use an itemgroup column to indicate which Item Group the data items in the row belong to. Workbench treats all unmapped columns in the row as items. Workbench imports those items within the row’s specified item group. You can specify additional metadata for those columns in the item configuration. See details below.

Use the table below to understand how Workbench will handle your data with different Form and Item Group configuration scenarios:

Example Manifest: Multiple Forms

In the example below, the “Labs.csv” file contains multiple Forms. These are identified in the form column of the “Labs.csv” file.

{
  "study": "Deetoza",
  "source": "lab",
  "data": [
    {
      "filename": "Labs.csv",
      "study": "protocol_id",
      "site": "site_id",
      "subject": "subject_id",
      "event": "visit",
      "form": "form"
    }
  ]
}

Example Manifest: Multiple Item Groups

In the example manifest file below, the Labs form has multiple Item Groups. These are identified in the lab_category column of the “Labs.csv” file.

{
  "study": "Deetoza",
  "source": "lab",
  "data": [
    {
      "filename": "Labs.csv",
      "study": "protocol_id",
      "site": "site_id",
      "subject": "subject_id",
      "event": "visit",
      "item_group": "lab_category"
    }
  ]
}

Optional: Item Configuration

Within your manifest file, you can include Item metadata to inform how CDB Workbench handles data items. You can specify a data type for each Item, as well as additional properties depending on your data type selection.

When item configuration is omitted, CDB Workbench treats all items as text.

Simple vs. Advanced Configuration

The item “config” object has two configuration formats, simple and advanced. For simple configuration, you specify only the item’s data type. The item’s properties use the default values. For advanced configuration, you specify the data type and properties.

"items" {
    "Weight": "float"
}

"items" {
    "Weight": {
        "type": "float",
        "precision": 2
    }
}

Available Properties by Data Type

There are different configuration properties available for each data type.

"items": {
    "Subject_Initials": {
        "type": "text",
        "length": 3
    }
}

"items": {
   "Actual_Dosage": {
      "type": "integer",
      "min": 1,
      "max": 1000
   }
}

"items": {
    "Weight": {
        "type": "float",
        "length": 4,
        "precision": 1,
        "min": 80,
        "max": 400
    }
}

"items": {
    "Start_Date": {
        "type": "date",
        "format": "yyyy-MM-dd"
    }
}

"items": {
    "End_DateTime": {
        "type": "datetime",
        "format": "MM-dd-yyyy HH:mm"
    }
}

"items": {
    "Time_Collected": {
        "type": "time",
        "format": "HH:mm:ss"
    }
}

"items": {
    "Exam_Performed": "boolean"
}

Supported Date & Time Format Patterns

The format patterns listed below are available for date, datetime, and time items. For datetime items, combine a date and time pattern, for example, “yy-MM-dd HH:mm”. If you use a time format pattern for a date, or vice versa, your import will fail with an error (D-012).

Example Package: Single Form, Item Metadata

Here is an example import package from Verteo Pharma’s randomization vendor, containing the Randomization form with item metadata:

manifest.json:

{
  "study": "Cholecap",
  "source": "IRT",
  "data": [{
    "filename": "Randomization.csv",
    "study": "protocol_id",
    "site": "site_id",
    "subject": "patient",
    "event": "visit_name",
    "items": {
      "randomization_number": {
        "type": "integer",
        "length": "14"
      },
      "date_of_randomization": {
        "type": "date",
        "format": "yyyy-MM-dd"
      }
    }
  }]
}

Randomization.csv:

Optional: Restricting Data (Blinding)

You can restrict (blind) data at the Item, row, listing data file, and Source levels to hide it from users who don’t have permission to access restricted data. For example, your study may only order certain labs for subjects who are using the study drug. Knowing which labs a subject has would unblind the subject. You can restrict this information to prevent blinded users from identifying those subjects.

"items": {
  "itemname": {
    "type": "text",
    "blinded": true
  }
}

{
  "study": "Deetoza",
  "source": "Labs"
  {
    ...
    "event": "visit",
    "row_blinded": "column_id"
  }
}

{
  "study": "Deetoza",
  "source": "Labs"
  {
    "filename": "chempanel.csv",
    "blinded": true,
    ...
  }
}

{
  "study": "Deetoza",
  "source": "Labs",
  "blinded": true
  ...
}

For users who have access to restricted data (typically lead data managers), restricted data behaves the same way as unrestricted data. For blinded users (users without the Restricted Data Access permission), the following behavioral rules apply to any imported restricted data:

• If an Item (column) is restricted:
  • The CQL projection doesn’t return the restricted item’s column.
  • The CQL projection doesn’t return any derived columns that reference the restricted item.
  • If a blinded user references the restricted Item in their CQL statement, CQL still doesn’t return the column.
  • SHOW and DESCRIBE do not return the restricted Item.
• If a row is restricted:
  • The result set doesn’t return any rows from the Form or Item Group.
• If a listing file (csv) is restricted:
  • The default @HDR columns are included in the listing, but no Item columns are included.
• If a Source (package) is restricted:
  • CQL doesn’t return any Items or column results from the restricted Source in any listing.
  • CDB marks all Item Definitions, Item Group Definitions, and Form Definitions within the Source as restricted.
  • All data rows are marked as restricted.
  • The default @HDR columns will still appear in the core listings.

ZIP Package

Once you’ve finished creating your manifest file and your CSVs, zip the files together. Don’t place these files in a folder before zipping them. You can name this ZIP folder whatever you’d like. However, we recommend that you name it with a unique identifier, for example, “Study-Name_Source_datetime.zip”. Don’t include any folders in the ZIP folder. All CSV files and the manifest file must be at the same level.

Accessing your Vault’s FTP Server

Each vault in your domain has its own FTP staging server. The FTP server is a temporary storage area for files you’re uploading to or extracting from Vault.

Server URL

The URL for each staging server is the same as the corresponding vault, for example, veepharm.veevavault.com.

How to Access FTP Servers

You can access your staging server using your favorite FTP client or through the command line.

Use the following settings with an FTP client:

• Protocol: FTP (File Transfer Protocol)
• Encryption: Require explicit FTP over TLS (FTPS). This is a security requirement. Your network infrastructure must support FTPS traffic.
• Port: This does not typically need to be added and will default to Port 21.
• Host: {DNS}.veevavault.com. For example: “veepharm” is the DNS in veepharm.veevavault.com.
• User: {DNS}.veevavault.com+{USERNAME}. This uses the same user name that you log in with. For example: veepharm.veevavault.com+tchung@veepharm.com.
• Password: Your login password for this vault. This is the same password used for your standard login.
• Login Type: Normal
• Transfer File Type: Transfer files as binary

FTP Directory Structure

Inside your user directory, there is a “workbench” directory. This is where you will upload your 3rd party data. CDB is automatically aware of any files that you put here.

CDB moves any successfully imported files into “workbench/_processed”. See details below.

Importing the Data

To import your data, upload your ZIP file to the “workbench” directory of your FTP staging server, using an FTP client of your choice. Once you upload your ZIP to the FTP staging server, CDB imports it and transforms your data.

When import is finished, Workbench sends you, as well as any other users subscribed to the Source, an email notification. If the reprocessing of a package results in a change from the previous load, Workbench will also send a notification to you and users subscribed to that Source.

Successful Import

Upon successful import:

• CDB creates all definition records and the relationships between them.
• CDB automatically sets the Source field with the value provided in your manifest on all imported records to uniquely identify the data source. (Any Forms imported from Vault EDC automatically have this value set to “EDC”.)
• CDB creates a Core Listing for each unique Form in your import package. See details below.
• CDB moves your import ZIP file from “workbench” into “workbench/_processed/{study}/{source}” (for failed imports, your ZIP file stays in the root of the “workbench” directory. CDB also appends the date and time of import to the filename.

You can now view your listing in the Workbench. In the current release, Workbench doesn’t show your new listings right away. First, navigate to your study’s Listings page, click to open another Core Listing, and then return to the Listings page. Then, your new data listings will display in the list. You can now click on the Name of one of your listings to open it.

Failed Import

Upon failed import:

• CDB appends the date and time of the attempted import to the ZIP file’s filename, but it keeps your ZIP file in the root of the “workbench” directory.
• CDB creates an error log (“<import datetime>_<package name>_errors.csv”).

See this list of possible errors and how to resolve them.

Review & Approve Imports with Changes

To ensure that data being imported into CDB doesn’t change from the approved format and structure, CDB detects any changes to package configurations from one load to the next for each third party Source.

If the system detects changes in configuration at the package, file, or blinding level, or changes in the CSV file structure, CDB pauses the import process. The package isn’t processed and enters the Paused status until a user with approval rights either approves or rejects the package. If the user approves the package, CDB records the approval reason and imports the data package. Once a package is approved, CDB notifies subscribers to the approved change via email. CDB displays a change indicator on all associated Listings and Views. Users are able to dismiss this indicator and download the form change log for any listing or view. If the user rejects the package, CDB records the rejection reason, marks the package as Rejected, and assigns it the Not Imported status. CDB then reverts to the last successfully imported data package for the Source.

When a package is in the Paused status, all other uploaded packages for that same Source enter a queue and will wait for the paused package to be approved or rejected. Once the paused package is approved or rejected, CDB skips to the last queued package for processing. Any packages between the last paused package and the last package in the queue aren’t processed. For example, if “Package 1” is uploaded and paused, then packages 2-5 are uploaded while “Package 1” is still in the Paused state, once “Package 1” is approved or rejected, CDB will skip to “Package 5”, leaving packages 2-4 unprocessed.

For the first uploaded package in a new Source, CDB automatically applies the Paused status, and a user must approve or reject that first package before any data from that Source appears in the system.

For packages requiring approval, Workbench now includes a Package Detail panel with tabs for Differences and Associated Objects. The Differences tab displays the changes in the manifest between the current and previous packages. Users can review the previous and current value of the changes that they’re approving. The Associated Objects tab displays the Export Definitions, Listings, and Views that the changes in the package may potentially impact.

To approve or reject an import package requires the Approve Import permission, which is by default assigned to the standard CDMS Super User and CDMS Lead Data Manager study roles.

Approve an Import

To approve an import:

1. Navigate to Import > Packages for your Study.
2. Filter the list of import packages by Pending Approvals.

3. From the Package menu (), select View Package Details.

4. In the Package Details panel, click Differences to show the changes.
5. Review the changes.
6. Click Approve Package.
7. Optional: Enter a Reason.
8. Click Approve.

The package is approved and will now enter the queue for processing.

Reject an Import

To reject an import:

1. Navigate to Import > Packages for your Study.
2. Filter the list of import packages by Pending Approvals.

3. From the Package menu (), select View Package Details.

4. In the Package Details panel, click Differences to show the changes.
5. Review the changes.
6. Click Reject Package.
7. Optional: Enter a Reason.
8. Click Reject.
9. In the Reject Package confirmation dialog, click Confirm.

Review Associated Objects

You can review the associated objects for an import package from the Associated Objects tab of the Package Details panel. This tab lists the Export Definitions, Listings, and Views that the changes in the package may potentially impact.

To access this tab, open the Package Details panel and then click to open the Associated Objects tab.

Workbench shows a change icon (orange circle) badge on the form pill for each changed Form.

For any files that are mapped in the “form” attribute of the manifest file, the Associated Objects tab won’t display any objects potentially impacted in that imported file.

Viewing Import Status

You can check the status of your import package from Import > Packages. This page lists the status of every import package, from both Vault EDC and 3rd party tools. You can also download import packages and issue logs (errors and warnings) from this page.

Downloadable import packages and issue logs only include restricted (blinded) data when downloaded by users with permission to access restricted data.

Any time you import a package into Workbench, Workbench automatically reprocesses the most recent packages for all other sources. For example, when your nightly Workbench Export EDC job runs, and then imports into Workbench, Workbench reprocesses your most recent Lab Data and Imaging packages as well. For earlier packages from the same Source, Workbench marks them as Replaced by the newer package.

You can easily filter the list to show only complete or failed imports using the Import Status Filters. Click Error to show only failed imports, or click Complete to show completed imports.

Workbench Import Statuses

When an import package is able to import with only warnings, Workbench highlights the status in orange to indicate that there are warnings. When import finishes, you can download the issue log to review the warnings.

Download the Import Package

To download the import package:

1. Navigate to Import > Packages.
2. Locate your import package in the list.

3. Click the Package link.

4. Extract the files from the ZIP folder and view them in a tool of your choosing.

Download the Logs

You can download the Import Log (CSV) for any import and the Issue Log (CSV) for an import that fails. The Import Log lists details about the import job and the data ingestion into Workbench.

The import log lists the following:

• Transformation Start Time
• Transformation Completion Time
• Transformation Duration
• Import Start Time
• Import Completion Time
• Import Duration

To download the import log:

1. Navigate to Import > Packages.
2. Locate your import package in the list.
3. From the Package () menu, select View Package Details.
4. In the Package Details panel, click Issues.
5. Optioanl: In the Issues tab, click Download () to download a CSV of the issue log.

Issue Log

The Issue Log lists all errors and warnings that Workbench encountered while importing the package.See a list of possible errors and warnings here.

To view the issue log:

1. Navigate to Import > Packages.
2. Locate your import package in the list.
3. From the Package () menu, select View Package Details.
4. In the Package Details panel, click Issues.
5. Optional: In the Issue Log panel, click Download () to download a CSV of the log.

To download the issue log without first viewing it in the application:

1. Navigate to Import > Packages.
2. Locate your import package in the list.
3. From the Package () menu, select Download Issue Log.

Viewing Imported Data

Upon upload, Workbench creates a Form for each unique file in the import package. Workbench automatically generates a Core Listing for each unique Form in a Study, whether that form comes from Vault EDC or is imported from a 3rd party system.

The default CQL query for these Core Listings is:

SELECT @HDR, * from source.filename

In the laboratory import example above, CDB creates two Core Listings: Chemistry and Hematology (one for each CSV file), using these queries:

SELECT @HDR, * from labs.Chemistry

SELECT @HDR, * from labs.Hematology

Definitions

CDB creates a Form Definition for each CSV file to define your imported data as “forms” within the CDB Workbench application. These records use the CSV filename (without the extension) as the Name, for example, “hematology”. CDB also creates an Item Group Definition to group your data items together within the form. CDB names these by prepending “ig_” to the CSV filename (without the extension), for example, “ig_hematology”.

Both of these definitions display as a column within your listing (form_name and ig_name). You can edit the listing’s CQL query to hide these columns.