Transcript Why SDMX? - CIRCABC

STS SDMX Webinar
Implementation of the DSD
"ESTAT+STSALL+2.0"
Webinar on 8 March 2016 at 10.30 – 12.30
Programme of this Webinar
• Introduction (Simo)
• Tools and support (Christian)
 SDMX Converter
 SDMX Reference Infrastructure
 Support from Eurostat and NSIs
 Documentation
• Tests in 2016 (Simo)
Webinar on 8 March 2016 at 10.30 – 12.30
2
INTRODUCTION
Webinar on 8 March 2016 at 10.30 – 12.30
3
Current STS data exchange
• There are 38 EDAMIS
flows for the STS data
• All data files arrive in
GESMES/TS format
• Oct-14 – Sep-15: 6851
data transmissions
• Peak in Mar and Sep
(<<700 files)
800
700
600
500
400
300
200
100
0
Webinar on 8 March 2016 at 10.30 – 12.30
4
Why SDMX?
• Eurostat's tools are based on SDMX in data
exchange (EDAMIS) and validation services
(EDIT) – Genedi is not supported anymore
• Development in other statistical domains,
in particular National Accounts with ESA
2010
• International organisations (ILO, IMF,
OECD etc.) and the ECB use SDMX-ML
Webinar on 8 March 2016 at 10.30 – 12.30
5
DSD: Structure
• STS Data Structure Definition was prepared
in 2015:
 Starting from GESMES/TS "Key Family"
 Meetings with international organisations, to
avoid re-inventing the wheel (code lists)
 Online survey of the Member States and the
ECB, to take stake holders' views into account
• Result: DSD ESTAT+STSALL+2.0
Webinar on 8 March 2016 at 10.30 – 12.30
6
DSD ESTAT+STSALL+2.0
01 FREQ
02 REF_AREA
03 ADJUSTMENT
04 INDICATOR
05 ACTIVITY
FREQ
REF_AREA
ADJUSTMENT
STS_INDICATOR
STS_ACTIVITY
STS_INSTITUTION
BASE_YEAR
STS_INSTITUTION
06 BASE_YEAR
07 TIME_PERIOD
08 OBS_VALUE
09 OBS_STATUS
10 CONF_STATUS
11 COMMENT_OBS
TIME_PERIOD
12 EMBARGO_TIME
TIME_FORMAT
13 COMMENT_DSET
14 UNIT_MULT
OBS_VALUE
OBS_STATUS
OBS_CONF
OBS_PRE_BREAK
Not needed
15 UNIT
16 DECIMALS
17 TRANSFORMATION
Not absolutely
needed for indices
18 TIME_FORMAT
19 COMMENT_TS
20 PRE_BREAK_VALUE
Asked by ECB
7
DSD: Code lists and constraints
• 13 code lists
• Using existing lists from Global Registry and
Euro SDMX Registry, except for Indicator and
Activity
• Values are restricted according EDAMIS flow ("data
flow" or "data set", e.g. STSIND_PROD_M)
• See details in:
 The DSD in Euro SDMX Registry
 "Human readable" SDMX-STS_DSD-Matrix
(MS Excel format) => demo
Webinar on 8 March 2016 at 10.30 – 12.30
8
TOOLS
Webinar on 8 March 2016 at 10.30 – 12.30
9
Transmitting data using SDMX
• Transmission files must
according to the DSD
• The transmission
(COMPACT)
format
be
structured
is
SDMX-ML
• Eurostat makes a variety of tools available
to Member States to implement SDMX for
data transmission
Webinar on 8 March 2016 at 10.30 – 12.30
10
One goal – different possibilities
Database
export
SDMX
Converter
Database
SDMX
Reference
Infrastructure
SDMX-ML file
SDMX-ML file
SDMX Web Service
Same
DSD

Same
Format
Today's webinar will give you an overview of these
two options
Webinar on 8 March 2016 at 10.30 – 12.30
11
Data Validation Process
 Before/During Transmission
(“First Level”)
After Transmission
( “Second Level”)
- Covered by SDMX today
- Not yet covered by SDMX
- Format Check (SDMX-ML)
- Detailed value check
- Mirror check
-…
- Code Check (SDMX DSD)
12
Registry
• The DSD can be downloaded from the Euro
Registry:
https://webgate.ec.europa.eu/sdmxregistry/
13
SDMX Converter
• The SDMX converter is a Java application that converts files
between all the following formats*
 SDMX 2.0
− Generic, Compact, Utility And Cross-sectional
 SDMX 2.1
− Generic Data, Generic TS, Structure-specific, Structure-specific TS
 GESMES
− TS, 2.1, DSIS




CSV, FLR
MESSAGE GROUP (special SDMX 2.0 format)
DSPL
Excel
• In order to do so, it needs at least the data file and the data
structure definition file (DSD).
*Limitations may apply. Please, check the User Manual.
Webinar on 8 March 2016 at 10.30 – 12.30
14
Design/Implementation
Steps to proceed a conversion
 Reading the input message
o
o
Parsing of the message
Populating the data model of the tool
(based on the SDMX information model)
 Reading the DSD
o
o
The DSD is retrieved from the Registry in order to complete the conversion
The DSD can be loaded from files so no connection is needed
 Writing the converted message
o
Uses the data model to write the output message in the target format
Webinar on 8 March 2016 at 10.30 – 12.30
15
Mandatory for any conversion
1. Input file
2. Output file (complete path)
3. Format for input and output files
4. Specify DSD
1. DSD file
2. Reference to a DSD file in the Registry
3. Reference to a Dataflow file in the Registry
Webinar on 8 March 2016 at 10.30 – 12.30
16
GUI
2.a Select the
DSD in the local
drive
2. If the local DSD
includes multiple
versions, we can
specify the one
desired
3. Excel parameter file
3. SDMX header
(.prop file)
Only for flat and
excel files
4. Mapping and
Transcoding
5. CSV quotation
(Converter 5.2.1)
1. Selection of the
input/output files and
their format
2.b Identify a DSD to
download from the
SDMX Registry
(configuration required)
2.c Identify a dataflow
linked to the DSD to
download from the
SDMX Registry
(configuration required)
CSV parameters
6. SDMX (output)
validation
XML parameters for
SDMX output formats
17
18
19
Example
1. Input and
output files
2. Format
3. DSD file or
reference
4. "Old" codes
can be replaced
by the "new"
ones: e.g.
"B_C_D" =>
"BTD"
20
Transcoding
21
Conversion
• CSV to SDMX v. 2.0 Compact
• Attention! The DSD needs to be extracted
from the Registry as the same version (2.0)
to allow the SDMX validation of the data file
Webinar on 8 March 2016 at 10.30 – 12.30
22
23
Where to find the SDMX Converter
• You can download the latest version of the
SDMX converter on CIRCABC
https://circabc.europa.eu/w/browse/76a63fc2-3d22-42cc-85f24f6f30b4d8eb
• Available packages
 SDMX
 SDMX
 SDMX
 SDMX
Converter
Converter
Converter
Converter
Documentation
Platform Independent
Web Service
installer for Windows 32-bit
Webinar on 8 March 2016 at 10.30 – 12.30
24
What is SDMX-RI
• Framework for SDMX data provision
• Set of pick-and-choose reusable building
blocks
allowing
a
statistical
office
to
disseminate SDMX data to the external world
• Designed to provide data and structural
metadata based on mappings to each
organization's dissemination data warehouse
• Uses SDMX standards including the one for
Web Services
Webinar on 8 March 2016 at 10.30 – 12.30
25
Why SDMX-RI
• Developed to simplify the exchange of data
• Provides standard software and components,
allowing individual statistical organizations to
interact and exchange their data using the same
software and methodology
• Modular approach, users can
 use part or the entire infrastructure,
 extend it by adding new modules or modify it in any other
way to suit their own purposes
• Developed in both Java and .NET
Webinar on 8 March 2016 at 10.30 – 12.30
26
Supported DBs
Oracle
• Oracle
• SQL Server
SQL
Server
ODBC
Supported
DBs
• MySQL
• Pc Axis
• ODBC
PC
Axis
Webinar on 8 March 2016 at 10.30 – 12.30
MySQL
27
SDMX-RI components
• Mapping Assistant
 Graphical tool to create the mapping between the
DSD and the dissemination database.
• Test Client
 Used to test your dataflow locally
• NSI Web Service
 Allows you to share your dataflow
• NSI Client
 Web interface to interact with the Web Service
Webinar on 8 March 2016 at 10.30 – 12.30
28
DSD
Local
environment
NSI Client
Mapping
Mapping
Store
Web Service
Dissemin
ation DB
Is my
dataflow Is my WS
working? working?
Test Client
(Desktop
Software)
Webinar on 8 March 2016 at 10.30 – 12.30
Data Consumer (external)
SDMX-RI process
29
Dissemination Database
• The SDMX-RI is configured to directly map
the dissemination Database
• An SDMX output is generated by the
SDMX-RI using the PULL mode (the
Receiver Organisation directly requests
data and receives an SDMX file with no
intervention of the Sender Organisation)
30
Dissemination Database
Webinar on 8 March 2016 at 10.30 – 12.30
31
Mapping Assistant
• Facilitates the mapping between the structural
metadata provided by an SDMX-ML Data Structure
Definition (DSD) and those that reside in a
database of a dissemination environment
• Maintains a Mapping Store for keeping the
mappings between the SDMX and the local data
storage scheme
• In the SDMX Reference Infrastructure, provides
mapping information to the Data Retriever
Webinar on 8 March 2016 at 10.30 – 12.30
32
33
34
Transcoding
Webinar on 8 March 2016 at 10.30 – 12.30
36
Web Client
• Web Interface to directly request data.
• Graphical view of the results
• Generates the output in different formats
Webinar on 8 March 2016 at 10.30 – 12.30
37
NSI Web Client : Welcome screen
Webinar on 8 March 2016 at 10.30 – 12.30
38
Support from Eurostat and NSIs
 For STS requests:
 Contact: [email protected]
 For SDMX Support:
 Contact: [email protected]
 Local contact point (Census-hub): Eurostat can provide a list
Webinar on 8 March 2016 at 10.30 – 12.30
40
Documentation
SDMX
for
SHORT-TERM BUSINESS
STATISTICS (STS)
Version 1.0 – 26/02/2016
Webinar on 8 March 2016 at 10.30 – 12.30
41
TESTS
Webinar on 8 March 2016 at 10.30 – 12.30
42
Tests in 2016
• Structural validation
 May 2016 onwards
 Test data flows to be created in EDAMIS - Any
preferences?
 Automatic feedback via EDAMIS back channel
• Content validation
 October 2016 onwards (?)
 Correct files (structural validation ok) loaded to
Eurostat's test production database by domain
managers
• Contact: [email protected]
Webinar on 8 March 2016 at 10.30 – 12.30
43
Thank you for your attention!
Questions
Webinar on 8 March 2016 at 10.30 – 12.30
44