Defining a Data Source

Last modified on 2024-06-11

Overview

Data sources are the basis for importing data from a source system or a source file into Disclosure Management. To import the desired data into Disclosure Management and use it in reports, the data source must be configured accordingly in the  Import Management area.

The following is defined during configuration of the data source:

  • Which data from the data source should be taken into account for the import (Parameterization)
  • Which columns contain which data (Mapping)
  • Whether and how certain data should be converted or enriched during the import (Transformation)
  • Whether and how certain data should be summarized during the import (Aggregation)

 

The configuration of the data source depends on the type of data source (i.e. whether you obtain data from a file, from a Lucanet database or from Lucanet ESG reporting). 

 

The Import Management functional area is only activated for users who have been assigned the global administrator role in Disclosure Management role management, as only this user role is authorized to manage imports (ImportData). (For additional information about user roles in Disclosure Management, see Creating and Editing User Roles for Disclosure Management)

Prerequisites

To create a data source, you must first define a connection for this data source in the Connections workspace. For additional information, see Defining a Connection to a Data Source. Several data sources can be created with the same connection.

Creating a Data Source

Data sources are created and configured in the Disclosure Management Import Management area:

Shows the Import management functional area and the Data sources workspace. The button that can be used to create a new data source is also highlighted in red. Create a data source

To create a data source:

  1. Click in the function bar on Import Management and then in the overview on Data sources to open the workspace.
  2. Click the + icon next to Data source.
  3. Configure the data source as described in the following sections.
  4. Save the data source.

An overview of all data sources already created is displayed on the left-hand side of the detail view. From here, you can:

  • Create a new data source
  • Select and edit existing data sources

 

By default, a data source for importing from Lucanet ESG Reporting is already created in the overview, which is preconfigured and can be used by all users who have licensed Lucanet ESG Reporting. For additional information on this data source see section Default Data Source for Importing from Lucanet ESG Reporting.

Configuring a Data Source

To configure a data source, you must open the editing mode by clicking the  icon at the top right of the detail view of the data source.

General Settings
Displays the input fields for configuring the general settings of a data source. Buttons for opening a preview, deleting the data source and canceling and saving the entries can be seen at the bottom. General settings of a data source
Description

Name

Name of the data source

Connection

Selection of the connection to the data source that was previously configured in the Connections workspace.

  • Several data sources can be created with the same connection.
  • When importing an Excel file, a separate data source must be created for each worksheet.
  • As soon as the connection has been selected, further configuration fields and commands are displayed.

Description

Optional description for the connection

Import parameters

Opens the configuration screen for defining the import parameters (see section Defining Import Parameters).

Mappings

Used to create one or more mappings for the data source (i.e. specifications on how the imported data should be assigned within Disclosure Management).

Transformations and aggregations of the data to be imported can also be configured via the configuration of the mappings (see section Defining Mapping).

Open preview in new window

Opens a preview of the imported data before and after applying the configuration created under Mappings (see section Displaying a Preview of the Imported Data).

Other commands

You can also delete the data source, save it, and cancel editing of the details.

Defining Import Parameters

You can use import parameters to specify which data should be imported from a data source. The required parameters for this depend on the type of of data source (i.e. whether you obtain data from a file, from a Lucanet database or from Lucanet ESG reporting).

In the data source interface, click Import parameters to open the configuration of the import parameters.

An explanation of the parameters that you must or can define for an import is provided on the user interface.

When importing data from an XLSX file: 
  • The parameters Worksheet and Header are required. 
  • All other parameters are optional. If you do not specify anything, all rows and columns containing data on the specified worksheet are imported.
  • When importing an XLSX file, a separate data source must be created for each worksheet to be imported.
Mandatory parameters for importing from an Excel file Parameters for Importing from an Excel File

No additional parameters are required when importing a CSV file.

When importing from a Lucanet database: 

  • You can select all elements available in the Lucanet database from which you want to import.
  • If multiple selection is possible, check boxes for selecting the individual elements are displayed in the drop-down lists.
  • Select the Can be overwritten check box if the parameters you specify here can be overwritten by parameters you specify when performing the import. For additional information on parameterization when performing the import, see Configuring and Executing a Data Import.
Required Parameters
Displays the input fields for the mandatory parameters for the import from a Lucanet database. Mandatory parameters for importing from a Lucanet database
Optional Parameters
Displays the input fields for the optional parameters that must be specified when parameterizing a data source for import from a Lucanet database. Optional parameters for importing from a Lucanet database

Displays the parameter that must be defined for a data source for the import from Lucanet ESG Reporting Import parameters for importing from Lucanet ESG Reporting
Description

Report name

Select the ESG report from which data is to be imported.

  • All reports that have been created in the Lucanet ESG Reporting solution and for which the data has already been aggregated are available for selection.
    For further information on data aggregation of an ESG report can be found in Aggregating Data chapter of the Lucanet ESG Reporting solution documentation.
  • The predefined default data source ESG Reporting Datasource is available in the overview of data sources and can be used by all users who have licensed Lucanet ESG Reporting (see section Default Data Source for Importing from Lucanet ESG Reporting)
Defining Mapping

Mappings are required for subsequent use of the imported data in Disclosure Management.

Mappings are used to define which columns of an imported Excel file contain which values. The columns are stored when programming an Excel file that is used in a report so that the correct data is inserted in the report. For additional information on programming the reporting data in an Excel file, see Inserting Report Values in MS Excel.

In addition, one or more transformations and/or aggregations can optionally be defined for each mapping (i.e. whether the data should be converted, enriched and/or summarized before import).

Several mappings can be defined for a data source and selected during the import process. For example, a mapping can be created for different periods or periodicities. 

All already-defined mappings are displayed in the Mappings area and can be edited there or deleted using the trash can icon.

To create a mapping, open the editing mode of the data source and click the + symbol in the Mappings area. 

Then configure the fields for the mapping:

Displays the input fields that must be specified for mapping a data source. The default is new Mapping.
Description

Title

Name of the mapping. The default is new Mapping.

Description

Optional description for the mapping

Name column

Select the range in the data source that contains the names to be inserted in the name column of the programmed Excel file used in the report (e.g. the names of the accounts and/or items or the page of the balance sheet).

Value column

Select the range in the data source that contains the values to be inserted into the value column of the programmed Excel file used in the report (e.g. values in a specific reporting year).

Programming column

Select the range in the data source that contains unique identifiers to be inserted into programming column of the programmed Excel file used in the report (e.g. account numbers).

Importing texts

Activate this option if cells with text are to be included in the import.

Import zero values

Activate this option if cells with zero values are to be taken into account during the import.

You can use transformations to specify whether source data should be converted or enriched before import. Transformations always create a new column in the imported data.

To create a transformation within a mapping:

 

  1. If necessary, open the editing mode of the data source.
  2. In the Transformations area, click the + symbol
  3. Edit the displayed fields.
    An explanation of the fields is provided on the user interface.
  4. Click the Open preview in new window button and check the Imported items tab in the displayed window to determine whether the transformation provides the desired result.

The following transformation types are currently available:

Suffix
Displays the input fields for the configuration of the transformation of type Suffix. The transformation type to be selected is outlined in red. Configuration of the 'Suffix' transformation
Description

The Suffix transformation is used to append a definable text to a value and write it to a new column.

Example

New column name: Item name_Group

Source column: Item name

Suffix: _Group

 

Column before transformation:

As an example, display a column in Excel with the name Item name before the transformation of type Suffix

New column after transformation:

As an example, displays a column in Excel with the name Item name and a column with the name Item name_Group after the transformation of type Suffix
Join
Displays the input fields for the configuration of the transformation of type Join. The transformation type to be selected is outlined in red. Configuration of the 'Join' transformation
Description

The transformation type Join is used to connect the values of two selectable columns and write them to a new column.

Example

New column name: Account_with_Transaction_type

First source column: Account number

Second source column: Transaction type

Separator: #

 

Columns before transformation:

As an example, displays two columns in Excel with the names Account number and Transaction type before the transformation of type Join

New column after transformation:

Displays a column in Excel with the name Account_with-Transaction type as an example after the transformation of the Join type.

You can use aggregations to specify whether source data should be aggregated (i.e. summarized) before import. 

To create an aggregation within a mapping:

 

  1. If necessary, open the editing mode of the data source.
  2. In the Transformations area, click the + symbol
  3. Edit the displayed fields.
    An explanation of the fields is provided on the user interface.
  4. Click the Open preview in new window button and check the Imported items tab in the displayed window to determine whether the aggregation provides the desired result.

The following aggregation types are currently available:

Sum
Displays the input fields for the configuration of the Aggregation of type Sum. The transformation type to be selected is outlined in red. Configuration of the aggregation 'Sum'
Description

You can use the total aggregation to total all numerical values in the value column – grouped according to the source column to which the total function is applied.

Example

Source column: Column2

 

Columns before aggregation:

Displays the columns in Excel to which the aggregation is applied as an example before the transformation of type Sum

Columns after aggregation:

Displays as an example the columns in Excel after the transformation of type Sum, which now contain the summed values grouped by column 2
Filter
Displays the input fields for the configuration of the aggregation of type Filter. The aggregation type to be selected is outlined in red. Configuration of the aggregation of type 'Filter'
Description

You can use the Filter aggregation type to import only values that match the defined filter condition.

The condition must be entered manually into the Filter field. The filter value or text must be placed in double inverted commas.

The following arithmetical and logical operators are available for defining the filter condition:

Arithmetical operators for text filters:

  • '=' imports all rows that match the specified filter text exactly.
  • < imports all rows whose text comes after the specified filter text in their relative position in the alphabetical sort order.
  • <= imports all rows whose text in their relative position in the alphabetical sort order is in the same position or after the specified filter text.
  • > imports all rows whose text comes before the specified filter text in their relative position in the alphabetical sort order.
  • >= imports all rows whose text in their relative position in the alphabetical sort order is in the same position or before the specified filter text.
  • <> displays all rows that do not match the specified filter text exactly.
  • contains displays all rows that contain the specified filter text at any location.
  • notContains displays all rows that do not contain the specified filter text.

 

Arithmetical operators for number filters:
  • < displays all rows with a value greater than the specified number. If the value in a row is a text, it is not taken into account.
  • <= displays all rows with a value greater than or equal to the specified number. If the value in a row is a text, it is not taken into account.
  • > displays all rows with a value that is less than the specified number. If the value in a row is a text, it is not taken into account.
  • >= displays all rows with a value less than or equal to the specified number. If the value in a row is a text, it is not taken into account.
  • <> displays all rows that do not match the specified filter text exactly
  • contains displays all rows that contain the specified filter value at any location
  • notContains displays all rows that do not contain the specified filter value

 

Logical operators:

Filter conditions can be linked with and and or.

 

Example

Source column: Transaction type

Filter: contains "110"

 

Column before aggregation:

Displays the columns in Excel to which the aggregation is applied as an example before the transformation of type Filter

Column after aggregation:

For example, after the transformation of type Filter, only the lines that correspond to movement type 110 are displayed
Displaying a Preview of the Imported Data

To check whether the configured mapping(s) are configured correctly and deliver the desired result, you can display a preview of the imported data in a separate window. 

To do this, click in the data source interface on Open preview in new window.
The preview of the imported data is displayed as follows, for example:

Displays the window with the preview of the data import. The drop-down list for selecting the mapping is highlighted in red. Preview of the data import

The following elements are displayed in the import preview:

Element

Description

Datasource

Name of the data source

Mapping

Selection of the mapping for which a preview of the import is to be displayed.

All mappings defined in the data source can be selected.

'Source data' tab

Source data that serves as the basis for the import

'Processed data' tab

Preview of the data after applying transformations and/or aggregations that may have been configured.

If no aggregations and/or transformations have been configured, the same data are displayed on this tab as on the Source data tab.

'Imported data' tab

Preview of the data that will be imported at the end.

Last updated    

Time of the last update of the import preview

Update 

Click this button if you want to execute the import preview again.

Default Data Source for Importing from Lucanet ESG Reporting

To avoid additional configuration effort, a predefined default data source is already available for importing report data from Lucanet ESG Reporting. 

The following data is imported from an ESG report using this data source:

  • The question from the ESG questionnaire in text form ( Namecolumn)
  • The unique ID of the entry that is required for programming the Excel file in which the value is to be displayed. (The ID is made up of the number of the question from the ESG questionnaire with the row and column index of the CSV file that is generated in the background when the ESG reporting data is aggregated)
  • The value that is stored in the report for this entry

 

In the Import preview, the imported data is displayed as follows, for example:

Displays a preview of the imported data with the columns Source data, Processed data and Imported data when selecting the predefined data source for the import from ESG Reporting Example of an import preview with the ESG reporting data source

To use the default data source:

  1. Click on the data source ESG Reporting Datasource in the Data sources area.
  2. In the Import parameters area, select the ESG report from which you want to import the data from the drop-down list. 
  3. If necessary, display a preview of the imported data (see Displaying a Preview of the Imported Data).
  • This default data source can be used by all users who have licensed Lucanet ESG Reporting. For all users without a license for Lucanet ESG Reporting, the data source is displayed but cannot be loaded.
  • All reports that have been created in the Lucanet ESG Reporting solution and for which the data has already been aggregated are available for selection. Further information on data aggregation of an ESG report can be found in the Aggregating data chapter of the Lucanet ESG Reporting solution documentation.
  • This default data source cannot be edited any further . To import the above-mentioned data, it already contains a predefined mapping and two predefined transformations.
  • If you want to import data other than the above from an ESG report, you can create a custom data source and define mapping(s) and, if necessary, transformations and/or aggregations as described in the previous sections.