Introduction

The Excel import service reads xls definitions for both types and entities and send them to openBIS. It is the replacement of the old master data scripts adding support for the creation of openBIS entities.

The goals are:

For common users an import format with the following features to avoid the shortcomings of the old format:

• Recognisable labels as column names.
• Multi-type imports.
• Parents/Children creation and linking on a single import.

For advanced users like consultants and plugin developers a tool that allows to specify on an Excel sheet:

• Metadata model.
• Basic entity structures used for navigation.

Modes

To support different use cases the import service supports the next modes, specifying one of them is mandatory.

• UPDATE IF EXISTS: This one should be the default mode to use to make incremental updates.
• IGNORE EXISTING: This mode should be used when the intention is to ignore updates. Existing entities will be ignored. That way is possible to avoid unintentionally updating entities and at the same time adding new ones.
• FAIL IF EXISTS: This mode should be used when the intention is to fail if anything is found. That way is possible to avoid making any unintentional changes.

Organising Definition Files

All data can be arranged according to the needs of the user, in any number of files and any number of worksheets. All files have to be in one directory.

The names of the files and worksheets are ignored by the service, the user is advised to use descriptive names that they can quickly remember/refer to later.

Example:

We want to define vocabularies and sample types with properties using these vocabularies. We can arrange our files in several ways:

1. put vocabulary and sample types in separate files named i.e vocabulary.xls and sample_types.xlsx respectively
2. put vocabulary and sample types in different worksheets in the same xls file
3. put everything in one worksheet in the same file

Organising Definitions

Type definitions:

The order of type definitions is not important for the Excel import service, with exception of Vocabularies, those need to be placed before the property types that use them.

Entity definitions:

Type definitions for the entities should already exist in the database at the time when entities are registered. Generally Entity definitions are placed at the end.

Text cell formatting (colours, fonts, font style, text decorations)

All types of formatting are permitted, and users are encouraged to use them to make their excel files more readable. Adding any non text element (table, clipart) will cause the import to fail.

(A valid, but not easily readable, example)

Definition, rows and sheet formatting

• A valid sheet has to start with definition on the first row.
• Each definition has to be separated by one empty row.
• Two or more consecutive empty rows mark the end of the definitions.
• Empty spaces at the beginning or end of headers are silently eliminated.

Entity Types Definitions

All entity types can be created. There are differences due to the nature of the defined elements themselves.

Vocabulary and Vocabulary Term

Vocabulary

Vocabulary Term

Example

Experiment Type

Example

Sample Type

Example

Dataset Type

Example

Property Type

A property type can exist unassigned to an entity type or assigned to an entity type.

A property type requires a data type to be defined, valid data types are.

Example Unassigned Property

In this case, the property is registered without being assigned to a type, and  the block of property types uses the PROPERTY_TYPE block.

Example Assigned

In this case the property types are assigned to a sample type and the block of property types belong to the entity type block (SAMPLE_TYPE in this case).

Entity Type Validation Script and Property Type Dynamic Script

Scripts have to reside in .py files in the scripts directory within the folder that contains the Excel files.

Within scripts, files can be organised in any suitable setup:

In order to refer to a validation or dynamic script (e.g. storage_position_validation.py below), the relative path (from the scripts directory) to the file has to be provided in the relevant column. See the example columns below.

Example

Entity Types Versioning

General Usage 

Version is a mandatory field for entity types, it just starts at 1; and during updating a type definition is expected to increment it; otherwise, the system will ignore the changes.

Explanation

Additionally, use the keyword FORCE to reinstall the type even if is present and has been deleted.

The system keeps track of what versions of entities have been installed storing this information, so in the future when one updates their types, the version specified in the spreadsheet is checked against the stored version.

For every TYPE found in the Excel sheet the next algorithm is performed:

IF ENTITY OR (TYPE.Version > STORED_VERSION) OR (TYPE.Version == FORCE): // If is a new version
	IF ITEM NOT EXISTS in openBIS:
		CREATE ITEM                             
	ELSE: // Doesn't exist branch
		IF FAIL_IF_EXISTS:
			THROW EXCEPTION
		IF UPDATE_IF_EXISTS:
			UPDATE ITEM
		ELSE IF IGNORE_EXISTING:
     		PASS // Ignore as requested
ELSE:
	PASS // Ignore object that have not been updated

Entity Definitions

Most entities can be created, excluding DataSets. There are differences due to the nature of the defined elements themselves.

Space

Example

Project

Example

Experiment

Example

Sample

Example

Defining Parent and Children in Samples

Parent and child columns can be used to define relations between samples. Samples can be addressed by:

1. $ : Variables, only really useful during batch inserts for samples with autogenerated codes since Identifiers can't be known. Variables SHOULD start with $.
2. Identifiers

Properties and Sample Variables

As a general rule, properties would only accept data of the specified type.

Sample properties would typically require an Identifier to be given but a variable '$' could be used instead for a sample declared at any point of the document, including cyclical dependencies. This is useful for scenarios where Sample codes are autogenerated and can't be known in advance.

Entity Versioning

They don't have versioning, only entity types have versioning.

Master Data as a Core Plugin

The master data plugin is an AS core plugin.

Directory structure (important) :

Use standard initialize-master-data.py handle as it is ingested by openbis on startup. Excel files should be organised in master-data directory in the same plugin and scripts should be contained in scripts directory under master-data.

Contents of initialize-master-data.py:

from ch.ethz.sis.openbis.generic.server.asapi.v3 import ApplicationServerApi
from ch.systemsx.cisd.openbis.generic.server import CommonServiceProvider
from ch.ethz.sis.openbis.generic.asapi.v3.dto.service.id import CustomASServiceCode
from ch.ethz.sis.openbis.generic.asapi.v3.dto.service import CustomASServiceExecutionOptions
from ch.systemsx.cisd.openbis.generic.server.jython.api.v1.impl import MasterDataRegistrationHelper
import sys

helper = MasterDataRegistrationHelper(sys.path)
api = CommonServiceProvider.getApplicationContext().getBean(ApplicationServerApi.INTERNAL_SERVICE_NAME)
sessionToken = api.loginAsSystem()
props = CustomASServiceExecutionOptions().withParameter('xls', helper.listXlsByteArrays()) \
    .withParameter('xls_name', 'ELN-LIMS-LIFE-SCIENCES').withParameter('update_mode', 'UPDATE_IF_EXISTS') \
    .withParameter('scripts', helper.getAllScripts())
result = api.executeCustomASService(sessionToken, CustomASServiceCode("xls-import-api"), props)

There are following parameters to fill (Easiest is to use MasterDataRegistrationHelper to evaluate parameter values):

• 'xls': Array of excel files. It can be easily acquired by calling helper.listXlsByteArrays or listCsvByteArrays.
• 'xls_name' - Name for the batch, it is used by versioning system.
• 'update_mode' - See "Modes" section.
• 'scripts' - if you have any scripts in your data, provide them here. It is easiest to get it with MasterDataRegistrationHelper getAllScripts function.

'results' object is a summary of what has been created.

Example

For an complete up to date example, please check the eln-lims-life-sciences plugin that ships with the installer or on the official Git repository:

https://sissource.ethz.ch/sispub/openbis/-/tree/master/openbis_standard_technologies/dist/core-plugins/eln-lims-life-sciences/1/as

Or download the complete plugin using the next link:

https://sissource.ethz.ch/sispub/openbis/-/archive/master/openbis-master.zip?path=openbis_standard_technologies/dist/core-plugins/eln-lims-life-sciences

Known Limitations

• Property type assignments to entity types cannot be updated since the current V3 API does not support this functionality. This means that a change in the order of assignments or group names during an update will be ignored.