Importing Reference Data

CDB allows you to import non-clinical data via reference objects. Reference objects are a specific type of dataset that can be ingested into CDB alongside clinical data for cleaning and export purposes. They are like Forms, in that they can have a limited set of Items contained within them, but they are different in how they relate to other header and form data. Forms have a predefined relationship to a Study, Site, and Event, but you must define the relationship between a reference object and the header and form data during ingestion. After data in a reference object is imported into CDB, you can use it like any other Form in CQL, as long as its related data is included in the selection.

Relating to Clinical Data

You define keys for your reference object to relate it to clinical data. The keys are the Items in reference objects that are related to Items in another Form or Header. You can only relate reference data to a single Form or Header. The data type for a key must match the data type of the related Item. For example, if the data type for the Key in the reference object is INT (integer), then the related Item on the Form must also use INT.

A reference object can have up to four (4) keys.

The Key or combinations of Keys must be unique across the data set. For this reason, reference objects can’t be repeating.

Reference objects don’t have attributes, such as Name or CreatedDate, like forms and headers do. So, you can’t use @Form or @ItemGroup.

Reference objects can be joined with header data (@HDR). You don’t need to specify this join in your CQL statement, as CQL is already aware of it. However, if you use a reference object with forms in a selection clause, then the reference object must be related to at least one of the forms in the selection.

Reference objects are always joined to their source data as a left outer join.

Preparing Source Data for Import

Unlike clinical data, there aren’t any column requirements for reference data CSV files. Include one column per data item. CDB uses the column header as the Name of the Item.

Like clinical data import, CDB uses the filename of the CSV to name the reference object.

Creating the Reference Manifest File

Your reference 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. 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 mappings, as the value for “reference_data”.

{
"study": "Veeofen",
"source": "Lab",
"reference_data": [
        {
        "filename": "GENDER.csv",
        "related": [
                    { 
                      "related_form": "Demographics", 
                      "related_itemgroup": "gender_itemgroup", 
                      "related_item": "genderCode", 
                      "related_key": "gender_id" 
                    },
                    { 
                      "related_form": "@HDR", 
                      "related_item": "@HDR.Subject.Name", 
                      "related_key": "gender_name" 
                    } 
                 ], 
        "items": {
            "gender_id": "integer",
            "gender_name": "text",
            "created_date": {
                "type": "datetime",
                "format": "yyyy-MM-dd HH:mm:ss"
                }
            }
        }
    ]
}

You can include multiple entries in reference_data. Each entry represents a different reference data table.

To relate the reference object to the Header (@HDR), use @HDR for the related_form, and the header property for the related_item.

Use the following keys in your reference_data entries to define your reference object, its items, and its relationship to the clinical data.

Date & DateTime Formats

Import Package Requirements

Your import package must be a ZIP file containing your reference_manifest.json and any reference data CSV files for the chosen source. Don’t include any folders in the ZIP folder. All CSV files and the manifest file must be at the same level.

Importing the Reference Data Package

For CDB to ingest reference data and validate it, clinical data from the related forms must either already exist in CDB or it must be imported in the same upload package as the reference data. To import reference data alongside clinical data, include the reference_data array key in the same manifest file as the clinical 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 records from the reference objects.
• CDB automatically sets the Source field with the value provided in your reference manifest on all imported records to uniquely identify the data source.
• 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.

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 Reference Data in Workbench

You can query for reference data using CQL.

Select from a reference object in the same way you would as the header or a form. If you select *, CQL returns all Items on the reference object.

select * from Reference_Object_Name

To retrieve a list of all reference objects for your Study, use the SHOW keyword:

SHOW Reference

Then, you can use the DESCRIBE keyword to retrieve a list of Items on a given reference object:

DESCRIBE Reference <Reference_Object_Name>

Use the ReferenceRelations keyword to retrieve a table of key value pairs for the following properties: reference_object_name, related_form, related_item, and related_key.

ReferenceRelations <Reference_Object_Name>

You can use the KEYMATCH function to check if there is a match between the related_item and the related_key. If there’s a match, CQL returns true. Otherwise, CQL returns false.

KEYMATCH(form_name, reference_object)

For text-type reference object Items, CQL supports the following functions:

• CONCAT
• LENGTH
• TRIM
• LTRIM
• RTRIM
• UPPER
• LOWER
• LEFT
• RIGHT

For date-type reference object Items, CQL supports the following functions:

• ADDDATE
• SUBDATE
• DATEDIFF
• DATE_FORMAT
• SITENORMALIZEDDATE
• RAWDATE
• SDTMDATEFORMAT
• STR_TO_DATE
• CURDATE
• NOW
• YEAR
• MONTH
• WEEK
• DAY
• HOUR
• MINUTE
• SECOND
• LAST_DAY

CQL Limitations

CQL limitations apply when working with reference objects.

The following functions are not available for use with reference data:

• FROZEN
• ILB
• LABEL
• LASTCODED
• LASTMODIFIEDDATE
• LOCKED
• SIGNED
• STATUS