Diversity Collection

Export Wizard Transformation

The exported data may be transformed e.g. to adapt them to a format demanded by the user. Click on the button to open a window as shown below. For an introduction see a short tutorial .

Here you can enter 6 types of transformation that should be applied to your data. Cut out parts,  Translate contents from the file, RegEx apply regular expressions or Replace text and apply Calculations Σ or Filters on the data from the file. All transformations will be applied in the sequence they had been entered. Finally, if a prefix and/or a postfix are defined, these will be added after the transformation. To remove a transformation, select it and click on the button.

Cut

With the cut transformation you can restrict the data taken from the file to a part of the text in the file. This is done by splitters and the position after splitting. In the example below, the month of a date should be extracted from the information. To achieve this, the splitter '.' is added and then the position set to 2. You can change the direction of the sequence with the button Seq starting at the first position and starting at the last position. Click on the button Test the transformation to see the result of your transformation.

With the Start at Pos. option the given splitters will be converted into space (' ') and the whole string starting with the given position will be used (see below).

Translate

The translate transformation translates values from the file into values entered by the user. In the example above, the values of the month should be translated from roman into numeric notation. To do this click on the button to add a translation transformation (see below). To list all different values present in the data, click on the button. A list as shown below will be created. You may as well use the and buttons to add or remove values from the list or the button to clear the list. Then enter the translations as shown below. Use the save button to save entries and the Test the transformation button to see the result. 

To load a predefined list for the transformation use the   button. A window as shown below will open. Choose the Encoding of the data in your translation source and indicate if the First line contains column definition. Click OK to use the values from the file for the translation.

Regular expression

The transformation using regular expressions will transform the values according to the entered Regular expression and Replace by values. For more details please see documentations about regular expressions.

Replacement

The replacement transformation replaces any text in the data by a text specified by the user. In the example shown below, the text "." is replaced by "-". 

Calculation 

The calculation transformation Σ performs a calculation on numeric value, dependent on an optional condition. In the example below, 2 calculations were applied to convert 2-digit values into 4 digit years.

Filter 

The filter transformation compares the values from the data with a value entered by the user. As a result you can either Export content into file or Export fixed value. To select another column that should be compared, click on the button and choose a column from the file in the window that will open. If the column that should be compared is not the column of the transformation, the number of the column will be shown instead of the symbol. To add further filter conditions use the button. For the combination of the conditions you can choose among AND and OR. 

Diversity Collection

Export Wizard

Tutorial

This tutorial demonstrates the export of a small sample from the database. For an introduction see a short tutorial .

Choosing the data

In the main form, select the data that should be exported (only the data displayed in the query results are exported).

Exporting the data

Choose Data → Export → Wizard → Organism ... from the menu. A window as shown below will open where the available tables for export are listed in the upper left area. To show the data columns of a table, select this table in the list.

Adding additional tables

In this example, we want to add as many parallel identification tables as present in the data. To do this, click on the button of the Identification table. At the end of the list (depending on your data) the additional tables are added (see below).

Setting the sequence for the tables

To set the sequence of the Identifications, select the first table and for the column IdentificationSequence set sorting sequence to 1 and the direction for sorting to descending . 

Choosing data from linked modules

Some columns provide the possibility to add data from linked tables or modules. In this example we choose the column NameURI linking to the module DiversityTaxonNames (see below).

To provide linked values, click on the button. A window as shown below will open, where you can choose among the provided services.

After the service is selected, you will be asked for the value provided by the service (see below).

Now the selected link is added underneath the column as shown below. You can add as many links as you need for your export.

For some modules there are values that refer to other modules with a name like [Link to ...] as shown in the example below.

If you select one of theses values, you will be asked to select the service or database linked to this modul (see below)

... and then to select one of the provided columns (see below)

Within the form this linked values will be marked as shown below. If several results are retrieved these will be separated with by " | ".

Adding columns to the file

To add columns to the exported file, click on the buttons for the columns resp. linked values. In this example select all Family values and the TaxonomicName (see below).

Fusing columns

The families should appear as one column and as the sources can exist only once for each identification we can fuse these columns. To do so, click on the delimiters between these columns (see below).

Setting the headers

By default the headers for the exported data are set according to the names of the columns in the database. To change this, edit them as shown below where TaxonomicName has been changed to Taxon (see below). For fused columns only the header in the first column will be used.

Testing

To test the export, click on the Test export button. The result depends on the content in your data but should look similar as shown below.

Export

To finally export the data, choose the Export tab. By default the data will be exported into tab separated file in a directory in the application directory (see below). You can change the directory (click on the button). You can choose the Include schema option to create a schema that you may reuse in a later export.

Diversity Collection

Data Flow

Import / Export - example for a data flow

In the image below, an expample for a data flow from the original source to the final GBIF-portal is shown. As a first step the data are imported via the Import wizard are imported into the database. After the data are given free for publication, they are transferred into the cachedatabase .  From there they are transferred into a Postgresdatabase containing a package for conversion into ABCD. Finally the BioCASE tool for mapping the data is used to provide the data for GBIF.

Cache Database

Tutorial

Basic steps for publication of data via the cache database

1 - Create the cache database

To create a cache database as shown in a short tutorial and in the chapter Creation of the cachedatabase you need to be a system administrator (s. Login administration). After this step the cache database should be available and you can create a Postgres database as final target of your data.

2 - Create a Postgres database

The final formatting of the data e.g. for publication via webservice are performed in a Postgres database. If no server providing Postgres is available, you may install Postgres on your local machine (see https://www.postgresql.org/ for further information). The creation and administration of a Postgres database is described in a short tutorial and in chapter Administration of the Postgres cachedatabases.

3 - Insert sources for taxonomic names, scientific terms, agents etc.

This step is optional and depends upon the availability of a source for e.g. taxonomic names. You may either use sources from your local server or the public available sources provided by tnt.diversityworkbench.de (turn to http://www.snsb.info for further information). For a introduction see a short tutorial . The needed settings are described in chapter Sources from othermodules.  

4 - Insert a project

The data published in the cache database are organized according to the projects. Add a project as shown in a short tutorial and described in chapter Projects in the cachedatabase. In the source database, make sure that the data within this project are not withheld from publication (see chapter Availability of data sets for more details) and that the ranges you want to publish are set properly (see chapter Restrictions for the datatransfer into the cachedatabase).  

5 - Transfer the data

The final transfer of the data is described in chapter Sources for other modules and chapter Transfer of the data.

6 - Publish

Publish or export the data

To export the data or prepare them for publication according to the specifications of webservices etc. the data frequently need to be formatted. This is done with packages as described in chapter Administration of the Packages.

7 - BioCASe

Map data via BioCASe (only for ABCD consuming publishers like GBIF)

For publishers using ABCD like GBIF, use the BioCASe provider software and mapping tool to link the data formatted with the ABCDpackage.   

Create Cache Database

Creation of the cache database

To create a cache database you need to be a system administrator (s. Login administration). To create the cache database, choose Data - Cache database ... from the menu. If so far no cache database exists, you will be asked if a new one should be generated. Next you have to select the corresponding DiversityProjects database placed on the same server. If the stable identifier has not been defined in this DiversityProjects database, you get a message, that this has to be done first. Please see the manual of DiversityProjects for details. Next you have to select the corresponding DiversityAgents database placed on the same server. Finally you are asked for the name of the Cachedatabase. We recommend to accept the suggestion shown in the dialog. After the generation of the cache database a window as shown below will open. For an introduction see a short tutorial .

Click on the Update button to update the database to the latest version. A window as shown below will open. Click on Start update to execute all the scripts needed for the latest version of the database.

During the update you may encounter under certain circumstances the message that the test of the generic functions failed (see below).

The most probable reason for this is that the name of your projects database does not correspond to the specifications of the update-script. This error can easily be fixed. As an administrator use the Microsoft SQL Server Management Studio. In the directory of your cache database select the Scalar-valued Function dbo.ProjectsDatabase()

... and use Modify from the context menu. In the Code of the function (see below) change the last set @DB = ... to the real name of your projects database.

The result may look as shown below. After the change, press F5 to execute the script.

After the function returns the correct name of the ProjectsDatabase, the update script should proceed without further error messages.

Cache Database

Logins

Cache database - User administration

There are 2 roles in the cache database with a general access to the data: CacheAdmin (for the administration and transfer of the data) and CacheUser (with read only access).

To administrate the logins in the SQL-Server database, click on the button to open a window as shown below. To administrate the access for other logins, you have to be a System administator. For further details please see the chapter about the login administration for the main database.

Postgres database

To handle the logins and user groups on the Postgres database server, click on the button. A window as shown below will open, where you can create and delete logins and groups. For the logins you can change their membership in groups and their properties (see below). On the left you find 2 lists, with the upper list containing the logins and the list below with the groups resp. roles. For the logins you can set the general properties as shown below. The login postgres is created with the installation of the database and is the login for the administration of the database including the updates etc. For details about predefined properties like Is superuser, please turn to the Postgresdocumentation. 

In the Membership in groups area you can define the groups in which the login is a member (see below). 

For the groups you can change their membership in other groups and their permissions (see below). 

Cache Database Configuration

Configuratation of the cache databases

The cache databases for DiversityCollection are designed as sources for preformated data for publication in e.g. public user portals like GBIF. There may be several cache databases which can be located on several servers. The restrictions of the published data are defined in the main database via projects, data withholding and embargos. The publication of the data is allways related to a project, defined in DiversityProjects, holding the metadata that will be transfered into the cache database. Therefore every dataset copied from the source into the cache database contains a reference to the project (ProjectID). The publication of the data includes several steps:

• Setting of restrictions within the original data with data withholding
• Selection of the project
• Transfer of the data into the cache database
• Conversion of the data into the format required by the portal

In Addition to the data transfered from DiversityCollection, the data for the taxonomy has to be transfered from the relevant sources. The links to these sources and the project dependent retrieval are stored in the cache database.

The image below gives an overview for the process described above.

To configure your cache databases, choose Administration → Cache database from the menu. A window will open as shown below.

Creation of a cache database

If no cache database has been defined so far, use the button to create a new cache database. You have to be a System administrator to be able to create a cache database. You will be asked for the server, the port used by the server, the directory of the database files, the name of the new cache database and finally the name of the projects database where the metadata of the projects transfered into the cache database are stored.

To delete a once created cache database, use the button. 

Updates of the cache database

After the new cache database has been created or if you select an outdated cache database, a button Update database will appear, instructing you to run updates for the cache database. Click on the button to open a window as shown below. 

All update scripts for the database will be listed. Click on the Start update button to update the database to the current version. 

Login administration

To handle the data for the cache database a user needs access to the data on the source database, the cache database, the project database and the taxon databases. To administrate the users that can transfer data into the cache database use the button Login administration. For details see the chapter Loginadministration. 

Configuration, Projects

Data transfer to the cache database is linked to projects . To add a project of which the data should be transfered into the cache database click on the button. For every project that should be transferred you have several options for configuration:

• Restriction of transfered taxonomic groups
• Restriction of transfered material categories
• Restriction of transfered localisations
  • Restriction of the precision of the coordinates
• Restriction of transfered images

Data types handle the data for the cache database. A user needs access to the data in the [source] database, the [cache] database, the [project] database and the [taxon] databases. To administrate the users who can transfer data into the cache database, use the button Login administration. For details see the chapter Login administration. 

Restriction

• Taxonomic groups
• Material categories
• Localisation systems
• Images

To restrict the Taxonomic groups, Material catagories, Localisation systems or Images that are transferred to the cache database choose the corresponding options and select those that should be transferred into the cache database in the tab pages that are added. 

Coordinate precision

To reduce the precision of the coordinates of the localisation systems transferred to the cache database you can check the corresponding option and determine the number of digits after the decimal point. 

Taxonomy

The collection data may be linked to sources holding taxonomic information ( DiversityTaxonNames). To provide this information add all sources used in your collection data and transfer the corresponding data into the cache database. The data in the taxonomic sources are organized by projects, thus, you need to provide the sequence of the projects that should be imported into the cache database for every source. A name will be imported only once. This means that the name with synonymy to the first imported project will be imported, all following data with this name will be ignored.

Anonym Colletors

Anonymous collectors

If collectors should be published as an anonym string, edit the Anonymous collectors list. Use the > button to move a collector from the selected project into the list of anonymous collectors. To remove a collector from this list, just delete it from the table (see below).

In the cache database these collectors will be translated into the selected Anonymisation and a number, e.g. Anonymus 1. So the data of one collector can still be recognized without revealing the name of the collector.

Cache Database

Linked Server

Transfer of data to Postgres via linked server

For projects with great amounts of data the preferred way to transfer data is a linked server. To use a linked server you have too install the ODBC driver software for Postgres on your SQL-Server, e.g. provided here: postgresql. Download and install the software, e.g.:

After the software has been installed, add a ODBC datasource.

Configured to access your Postgres cache database.

Now you can add a linked server in the SQL-Server Management Studio (see below). 

Configure the linked server using Microsoft OLE DB provider for ODBC Drivers and the new created ODBC source as Data source (see below).

Now you are prepared to transfer your data on the fast route to the postgres database.

Configure the linked server using Microsoft OLE DB provider for ODBC Drivers and the new created ODBC source as Data source (see below).ow you can add a linked server in the SQL-Server Management Studio (see below). edit the general settings for the transfer, click on the  button in the main form. A window as shown below will open. Here you can set the timeout for the transfer in minutes. The value 0 means that no time limit is set and the program should try inifinite to transfer the data. Furthermore you can set the parameters for the transfer of the data in chunks. If the amount of data is above a certain threshold, it is faster to devide the data into smaller chunks. The threshold for transfer into the cache database and into the Postgres database can be set as shown below, together with the maximal size of the chunks.

The scheduled transfer is meant to be lanched on a server on a regular basis, e.g. once a week, once a day, every hour etc. . The transfer of the data via the scheduled transfer will take place according to the settings. This means the program will check if the next planned time for a data transfer is passed and only than start to transfer the data. To include a source in the schedule, check the selector for the scheduler. To set the time and days scheduled for a transfer, click on the button. A window as shown below will open where you can select the time and the day(s) of the week when the transfer should be executed.

The planned points in time a shown in the form as shown below.

The protocol of the last transfer can seen as in the window above or if you click on the button. If an error occurred this can be inspected with a click no the button.

If another transfer on the same source has been started, no further transfer will be started. In the program this competing transfer is shown as below.

You can remove this block with a click on the button. In opening window (see below) click on the button. This will as well remove error messages from previous transfers.

A further option for restriction of the transfers is the comparision of the date when the last transfer has been executed. Click on the button to change it to . In this state the program will compare the dates of the transfers and execute the transfer only if new data are available.

Cache Database

Projects

Projects in the cache database

The data transferred into the [cache database] are always transferred according to a project they belong to. If no projects were added so far the window will appear like shown below. For an introduction see a short tutorial .

To add a new project for the transfer into the cache database, click on the Add project button. In the area below a new entry as shown below will appear. The area on the right shows the number of datasets in the project in the [source database] together with the date of the last update. To ensure the separation of the data between the projects, DiversityCollection creates a separate schema for every project named Project_[name of the project] together with needed roles, tables etc..

In case there are projects where you do not have access to, this will be indicated as shown below.

In case a project has been renamed in the main database, a button will appear as shown below. The displayed name corresponds to the name in the main database. To see the original name, click on the button.

Before transferring data you have to update the project schema to the latest version, indicated by the appearance of an update button . Click on the button to open a window as shown below. Click on the Start update button to update the schema to the latest version.

After the update the database is ready to transfer data into. 

Besides the restrictions in the source database, you can set further restrictions for this transfer. Click on the button to edit the datawithholding reasons for the data of the project. Click on the button and choose the ranges of the data that should be transferred (see below).

To transfer the data you have 3 options as described in the Transfer chapter.

Afterwards the number and date of the transferred data are visible as shown below.

To inspect the transferred data use the View content button. A window as shown below will open where all tables containing the data of the project are listed.

Click on the button to filter the content. A window as shown below will open. Choose the column for the filter, the operator (e.g. = ) and the filter value (see below).

Now click on the button to add the filter criteria to the table filter. You may add as many criteria as needed (see below). With the button you can clear the filter..

Before you can transfer the data into the Postgresdatabase, you have to connect to the Postgres database and click on the button to establish the project and run necessary updates . After the project is established and up to date, use the button to transfer the data in the Postgres area (see below).

If a project is exported into another Postgres database on the same server, these databases will be listed underneath the Postgres block (see image below). For an overview of all target Postgres databases click on the button.

If the target is placed on the current server, the text will appear in black (see image below). Packages will be listed for the other targets as well.

In the Postgres database you can install packages to adapt the data to any needed format.

Cache Database

Diagnostics for the cache database

To test data completeness of data targets you can use the diagnostics . Choose the project and the target you want to test and start the diagnostics. If the data in a postgres database should be included in the test, please connect to this database before starting the test. The result of the diagnose as shown below marks missing information. 

Diversity Collection

Cache database

Infrastructure

For the administration of the data that are published via the cache database, certain tables as shown below are used. These are either placed in the schema dbo or a schema named according to the published project, e.g. Project_Test for a project with the name Test. 

Central tables

There are a number of tables placed in the schema dbo that are accessible by all projects. 

Project tables

The central project tables contain the information about the projects that are published together with the target (Postgres) databases and the packages including optional add-ons into which they had been transferred. This information is used to ensure a recovery in case of a loss of the targets.

• ProjectPublished
• ProjectTarget
• ProjectTargetPackage
• ProjectTransfer
• Target

Source tables

To access sources from other modules (e.g. DiversityAgents) there are tables for the storage of the principal access to the modules and a number of tables containing the data (depending on the module).

Access tables

These tables contain the principal access like the name of the view defined to access the data. The example below lists the tables defined for the module DiversityAgents, but there are corresponding tables for every module accessed by the cache database. 

• AgentSource
• AgentSourceTarget

Data tables

These tables contain the data provided by the module and therefore depend on the module. The example below lists the tables defined for the module DiversityAgents, but there are corresponding tables for every module accessed by the cache database. 

• Agent
• AgentContactInformation

To access the data in the module there are views generated by the client. The name of these views are composed according to the name of the database, the server and the project to ensure a unique name. These are stored in the table AgentSource and are used by the client for a transfer of the data from the module database into the tables in the cache database. The example below lists the views for the module DiversityAgents. 

• Agents_BSMcollectors
• Agents_BSMcollectors_C

Project tables

These tables contain the data of the projects with every project having its own schema. These tables correspond to the tables in the main database of the module with certain limitations (no logging columns, internal notes etc.)

• CacheAnalysis
• CacheAnnotation
• CacheCollection
• CacheCollectionAgent
• CacheCollectionEvent
• CacheCollectionEventLocalisation
• CacheCollectionEventProperty
• CacheCollectionExternalDatasource
• CacheCollectionSpecimen
• CacheCollectionSpecimenImage
• CacheCollectionSpecimenPart
• CacheCollectionSpecimenProcessing
• CacheCollectionSpecimenReference
• CacheCollectionSpecimenRelation
• CacheExternalIdentifier
• CacheIdentification
• CacheIdentificationUnit
• CacheIdentificationUnitAnalysis
• CacheIdentificationUnitGeoAnalysis
• CacheIdentificationUnitInPart
• CacheLocalisationSystem
• CacheMetadata
• CacheProcessing
• CacheProjectAgent
• CacheProjectAgentRole
• CacheProjectReference
• ProjectAnalysis
• ProjectMaterialCategory
• ProjectTaxonomicGroup

Project procedures for the data transfer into the project tables

For every project table there is a procedure that transfers the data from the main database into the cache table. The names of these procedures are procPublish + the name of the table in the main database e.g. procPublishAnalysis for the transfer from the table Analysis into the table CacheAnalysis.