Upload data from an SDMX-CSV or SDMX-ML (xml) file

Table of Content

• Prerequisites
• Supported type of actions
  • Details of the Merge action
  • Details of the Replace action
  • Details of the Delete action
  • Conventions to indicate the state of components
  • Intentionally missing values
  • Mixed actions and examples
• File upload request
• Maximum upload file size

Version history:
SDMX-CSV version 2.0 and XML formats supporting new action types with July 4, 2023 Release .Stat Suite dragonfruit

Prerequisites

In .Stat Suite Core, data and referential metadata cannot be uploaded together (at the same time). They have to be uploaded through separate import files.
We currently also do not support imports for different dataflows in the same import file.

Data can be uploaded only after having uploaded the targeted dataflow in the same data space.

According to the SDMX standard formats, the data file must contain the full identification of the dataflow (agency, id, version) for which data are uploaded. Therefore it is not necessary to indicate the target dataflow in the DLM. Only the target data space needs to be selected.

Supported type of actions

SDMX-CSV version 2.0 and SDMX-ML can contain specific actions to perform upon a data upload request.
For SDMX-ML imports, the action is provided per dataset.
For SDMX-CSV 2.0 imports, an ‘ACTION’ column (after the structure identification column) is used to define the upload action for each individual row (observation or attribute(s)).

The following SDMX actions are supported by the .Stat Suite when uploading data using whether a SDMX-CSV v2.0 or SDMX-ML file format:

• “I”: Information - Data is for information purposes. If such data messages are loaded into a data space, currently in .Stat Suite (for backward-compatibility reasons) the action M(erge) is assumed. This might change in the future to instead use R(eplace).
• “M”: Merge (legacy “A”: Append) - Data is for an incremental update of existing observations or partial-key attributes or for the provision of new data formerly absent. This means that only the information provided explicitly in the message should be altered. Any measure or attribute value that is to be added or changed must be provided. However, the absence of an observation value or a data attribute at any level does not imply deletion; instead it is simply implied that the value is to remain unchanged. Therefore, it is valid and acceptable to send a data message with an action of M(erge), which (in addition to identifying structure columns) contains only identifying dimensions with some attribute values. In this case, whatever the attachment level of the attributes is, the values for the attributes will be updated. Note that it is not permissible to update measure or attribute values using incomplete identification information, e.g. without the structure ID or without the necessary dimensions (full key for measures, full key/partial key/none for attributes).
• “R”: Replace - Data is for replacement. Existing observations (with all their measure and observation-level attribute values) are to be fully replaced with the provided information. Existing higher level attribute values - when provided - are to be updated or appended. Observations or attribute values formerly absent will be appended.
• “D”: Delete - Data is to be deleted. D(elete) is assumed to be an incremental deletion. The deletion is to take place at the lowest level of detail provided. Concretely, if a D(elete) set/row only contains the identification information of the structural artefact (dataflow, data structure definition or data provision agreement) without any dimension, measure and attribute values then all data for the given artefact will be deleted. If the set/row contains only the structure identification and partial dimension values then all observations and all attribute values relating to those dimension values will be deleted. If the set/row contains only the structure identification, partial dimension values as well as values for some of the related attributes then only these attribute values will be deleted. If the set/row contains only the structure identification and full dimension values then the related observation and all its observation-level attribute values will be deleted. Finally, if the set/row contains only the structure identification, full dimension values as well as values for some of the related measures and attributes then only these measure and observation-level attribute values will be deleted. The to be deleted measure and attribute values must be non-empty or alternatively all measure and attribute values must be empty.

Note that, if this column is absent then the action M(erge) is assumed.

Important note about a non-backward-compatible change: The SDMX-CSV version 1.0 messages will now systematically use the M(erge) action, which changes its upload behaviour: Empty measure or attribute column cells will not lead anymore to deleting the related values in the database.

Details of the Merge action

• M(erge) actions are assumed to take place at the lowest level of detail provided in the message.
• Dimension values cannot be omitted but they can be switched-off when uploading higher-level attribute values that are not attached at these dimensions. Note that the attachment level of attributes is always predefined.
• Apart from dimensions, whenever only specific values for measures or attributes are provided, then only these values are updated/inserted. Any not specified (omitted) value is not changed.
• All observations and attributes effectively impacted by the M(erge) action change their time stamp.
• The M(erge) action requires CanImportData or CanUpdateData permissions.

Important note: In the current release and until this ticket is implemented, it is still allowed and required to omit dimension values instead of switching them off with ‘~’ in order to merge higher-level attribute values.

Details of the Replace action

• R(eplace) actions are assumed to take place at the observation level.
• All measure and observation-level attributes values of a specified observation are fully replaced with whatever the message contains for this observation.
• Dimension values cannot be omitted but they can be switched-off when uploading higher-level attribute values that are not attached at these dimensions. Note that the attachment level of attributes is always predefined.
• Higher level attribute values are updated or added only when present, like is done in the M(erge).
• For each replaced observation, if none of the measure or observation-level attribute values is present then the whole observation will be deleted (and be marked as such).
• All observations and partial coordinates effectively impacted by the R(eplace) action change their time stamp.
• The R(eplace) action requires CanImportData or CanUpdateData permissions.

Important note: In the current release and until this ticket is implemented, it is still allowed and required to omit dimension values instead of switching them off with ‘~’ in order to replace higher-level attribute values.

Details of the Delete action

D(elete) actions are assumed to take place at the lowest level of detail provided in the message.

• When dimension values are omitted then the values of those dimensions don’t matter (SQL notion: any incl. NULL) to define the scope of the deletion. With this, a whole dataset, any slice of whole observations for dimension groups such as time series or individual whole observations can be deleted.
• Dimension values can be switched-off when deleting higher-level attribute values that are not attached at these dimensions. Note that the attachment level of attributes is always predefined.
• Measure and attribute values can be omitted: Apart from dimensions, whenever only specific values for measures or attributes are provided then only these values are deleted (within the given dimensional scope), otherwise (=when all non-dimension components are omitted) all non-dimension component values related to the given dimensional scope are deleted. Instead of real values for non-dimensional components, it is sufficient to use any valid place-holder value or an intentionally missing value. The .Stat Suite also accepts the star character ‘*’ as a place-holder for to be deleted measure and attribute values.
• When all measure or observation-level attribute values of an onservation have been set to NULL through multiple partial D(elete) actions, then the full observation is deleted.
• When all higher-level attribute values of a specific partial key have been set to NULL through multiple partial D(elete) actions, then the partial key is deleted.
• Deleting a non-existing observation or attribute is not resulting in an error.
• All observations and attributes impacted by the D(elete) action change their time stamp.
• The D(elete) action requires CanDeleteData permissions.

*Important note: In the current release and until this ticket is implemented, it is still allowed and required to omit dimension values instead of switching them off with ‘~’ in order to delete higher-level attribute values.

Conventions to indicate the state of components

*Important note: In the current release and until this ticket is implemented, it is still allowed and required to omit dimension values instead of switching them off with ‘~’ in order to merge, replace or delete higher-level attribute values.

Intentionally missing values

Intentionally missing values are currently actively supported only for observation values (measure).
This support is enabled in the .Stat Suite per DSD. Each DSD that requires this feature must have an annotation of type “SUPPORT_INTENT_MISS_VALUES” before any related dataflow is initialised or related data are loaded. This is to prevent performance loss for DSDs that do not have this need.

According to the SDMX standard, the following special values indicate an intentionally missing value in uploads and downloads:

Note: Attribute values are always stored as text including the intentionally missing values.

Mixed actions and examples

SDMX-ML and SDMX-CSV v2.0 files can contain (datasets or rows with) varying actions (D(elete),M(erge),I(nformation),A(ppend),R(eplace)) including for overlapping scopes (full or partial coordinates).

Examples:

SDMX-CSV v1.0:
DATAFLOW,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
OECD:DF_TEST_DELETE(1.0),A,A,2020,,A,AA,,DF                     --> Skip OBS_VALUE and GR_ATTR
OECD:DF_TEST_DELETE(1.0),A,A,2021,NaN,B,AA,#N/A,DF              --> Set OBS_VALUE (double) and GR_ATTR (string) to intentionally missing value

DATAFLOW,DIM_1,DIM_2,TIME_PERIOD,OBS_ATTR,TS_ATTR,DF_ATTR
OECD:DF_TEST_DELETE(1.0),A,A,2020,A,AA,DF                       --> Skip OBS_VALUE and GR_ATTR 

SDMX-CSV v2.0:
STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,,,,,                     --> Delete all data of that dataflow
dataflow,OECD:DF_TEST_DELETE(1.0),I,A,A,2020,,A,AA,,DF          --> Skip OBS_VALUE and GR_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),A,A,A,2020,,A,AA,,DF          --> Skip OBS_VALUE and GR_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),M,A,A,2020,,A,AA,,DF          --> Skip OBS_VALUE and GR_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),M,A,A,2021,NaN,A,AA,#N/A,DF   --> Set OBS_VALUE (double) and GR_ATTR (string) to intentionally missing value
dataflow,OECD:DF_TEST_DELETE(1.0),R,A,A,2021,,A,AA,,DF          --> Set OBS_VALUE and GR_ATTR to NULL 
dataflow,OECD:DF_TEST_DELETE(1.0),R,A,A,2021,,,AA,A,DF          --> Set OBS_VALUE and OBS_ATTR to NULL

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_ATTR,TS_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,,,*                     --> Delete DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),A,A,A,2020,A,AA,DF            --> Skip OBS_VALUE and GR_ATTR

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,,,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,*,*,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,*,,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,,*,*,*,*

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,,,,,*

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,B,,,,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,B,,*,*,*,*,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,B,,,,,*,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,B,,*,*,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,,,,,,*,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,A,B,,,,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,A,B,,,,*,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,A,B,,*,*,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,A,B,2021,,,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,A,B,2021,*,*,,,

STRUCTURE,STRUCTURE_ID,ACTION,DIM_1,DIM_2,TIME_PERIOD,OBS_VALUE,OBS_ATTR,TS_ATTR,GR_ATTR,DF_ATTR
dataflow,OECD:DF_TEST_DELETE(1.0),D,A,B,2021,*,,,,

File upload request

Data files can be uploaded either within the request message body (see Maximum upload file size) or by specifying the path or URL to a file that is accessible by the data upload engine through this path or URL. Technically spoken, the service account under which the transfer service runs must have read access permissions to the file on the shared folder or web URL.

The data upload requests are executed in 2 subsequent phases:

1. The file or its path/URL is submitted to the data upload engine. The data upload engine makes a preliminary file content consistency check. If the check is successful then the file upload request gets attributed a request ID, is added to a queue and treated when there are sufficient server resources available. If the check fails, the data upload is cancelled. In both cases the user is informed about the outcome in the message response.
2. When the queued file upload request has been executed, the user is informed via logs and by an e-mail about the progress and outcome of the file upload, which could again be successful or failing. For more details see E-mail notifications for data management. The e-mail address being used is the one linked to the account under which the user has logged in the DLM.

Maximum upload file size

The size of files uploaded through the request message body is limited by default to 30MB.

It is possible also to compress the data file using the ‘zip’ format, and submit the compressed file in the message body. In this case the limitation applies on the zipped file size.

The file size limit is configurable by an administrator. Please see here for more information.

Files submitted through their path or URL do not have a specific size limit. However, the data upload engine always has internal hardware-related limitations.