Webinar SDMX Converter Fernando H Morente Oria Eurostat
Webinar SDMX Converter Fernando H Morente Oria Eurostat Unit A 3 – IT and standards for data and metadata exchange Eurostat Unit B 5 – Central data and metadata services April 2018 Eurostat
Webinar information 1/2 • This webinar • Organised by Eurostat • Focused on SDMX Converter tool • Questions: use the Web. Ex chat to ask questions during the webinar • After the webinar • Fill in online feedback questionnaire • Training materials (recording and presentation) • Training report including Q&A session Eurostat
Webinar information 2/2 • Objectives • What is the SDMX Converter? • How to find, install and use the SDMX Converter? • How the SDMX Converter can help my work? • Target audience • People willing to learn about SDMX standard and tools • (Will be) Involved in SDMX data exchange • Pre-requisites • No prior knowledge needed Eurostat
Agenda 1. What is SDMX 2. Installation 3. What is and how to use the SDMX Converter 4. SDMX Converter Demonstrations Eurostat
1. What is SDMX Eurostat
SDMX Statistical Data and Metadata e. Xchange SDMX UNSD World Bank Eurostat 6
SDMX Governing Structure Eurostat
The SDMX Components Describe statistics in a standard way Objects and their relationships § § Data Structure Definition (DSD), Concepts, Code List § Central management and standard access § SDMX Registry, SDMX Web Services § § § Cross Domain Concepts Cross Domain Code Lists Statistical Domains SDMX Glossary § Push § Provider generates and sends file to receiver Pull § § § SDMX Converter Provider opens web service to data Receiver downloads regularly Hub § § Special case of pull: receiver downloads on end user request Eurostat 8
SDMX IT tools • The SDMX IT tools is a software suite –where the SDMX Converter is included- that supports the needed functionalities within the SDMX Standard • Tools for data collectors • SDMX Registry • DSW • Tools for data providers • SDMX Registry • SDMX-RI • SDMX Converter Eurostat
Data collectors • Data Structure Wizard (DSW) Used to create, edit and test SDMX objects maintenance of SDMX registry • SDMX Registry A central repository for storing and sharing SDMX objects If you are a Data Collector Design your DSD using the DSW Make the DSD available in the SDMX Registry Eurostat Inform your data providers about the DSD
Data providers • SDMX Registry • SDMX Reference Infrastructure (SMRX-RI) A set of tools to connect your IT systems to the SDMX world • SDMX Converter If you are a Data Provider Get the DSD from the SDMX Registry If you have a DDB Data stored as files DDB SDMX-RI SDMX Converter Expose the data to be pulled (WS, HUB) Eurostat Push via e. DAMIS
National process - The business case How to…? • Structure my statistical datasets • Exchange data with other organizations • Align my definitions internationally using standards • Validate that data is correct Eurostat SDMX DSDs Push, Pull or HUB infrastructure SDMX Glossary, code lists SDMX schemas and VTL
2. Installation Eurostat
Installing SDMX Converter You can download the latest version of the SDMX converter on CIRCABC https: //circabc. europa. eu/w/browse/c 211 e 118 -1 d 53 -4953 -8 ac 8 e 63 e 0 a 2281 c 1 Available packages • • • SDMX Converter Documentation SDMX Converter Platform Independent SDMX Converter Source Code SDMX Converter Web Service SDMX Converter installer for Windows 32 -bit Eurostat
Common issue: Java_home 15 Eurostat
Need help? ESTAT-SUPPORT-SDMX@ec. europa. eu If • • • you have any problem converting, please send: Input file DSD Screenshot with the error Eurostat
More info Eurostat SDMX Info Space http: //ec. europa. eu/eurostat/web/sdmx-infospace • • Tools Tutorials Training recordings And much more! SDMX official website https: //sdmx. org Eurostat NEW!
3. What is and how to use the SDMX Converter Eurostat
What is the SDMX Converter? • The Converter is a Java application that converts statistical data files between different formats. SDMX-ML XML GESMES/TS GES Time-Series Version 2. 0 Generic Compact Utility Cross-Sectional Message Group* Flat Files Plain Text CSV, SDMX CSV Version 2. 1 Generic Structure-Specific Generic Time Series Structure-Specific Time Series Conversion to/from some formats may require to add or to subtract information. Limitations may apply. Ad-hoc Excel templates MS Office 19 Eurostat
How does the Converter work? 1. Takes • Input data file (in source format) • Data Structure Definition (DSD)/Dataflow (DFD) o Either stored in a file (offline operation), or o Given as a reference to be fetched from an online SDMX Registry • Additional information (format specific, optional) o E. g. , mappings, parameters, delimiters, message headers. • Specification of additional operations (optional) o E. g. , transcoding, SDMX validation. 2. Performs the processing 3. Generates • Converted data file (in destination format) Eurostat
Interfaces (modes of use) User interface Web service Command line Web service API Eurostat Locally installed
Graphical user interface (GUI) Eurostat
Input screen (1) 1. Select: Convert, Validate or Convert and Validate 2. Select input file by clicking on 3. Identify input format if not recognised automatically 4. Select output format and by clicking on 5. Click Next Eurostat
Structure selection screen (2) 6. Select: DSD or DFD 7. Locate DSD or DFD: Euro SDMX Registry or File 7. 1 If Euro SDMX Registry choose: Agency, Artifact ID and Version 8. Click Next Eurostat
CSV format input file 9. Select a header File or Manual Config 10. Define CSV parameters 11. Upload a mapping file or Manual Config, if needed 12. Upload a transcoding file or Manual Config, if needed 13. Click Next Eurostat
Don’t mix headers! • Don’t get confused between SDMX headers and CSV headers SDMX Header is mandatory (xml file) CSV header is optional (CSV file) Eurostat
Validation process –error report True: Found more than 10 errors Maximum number of errors Eurostat
CSV format input file–Mapping required A number of substeps will be created based on the previous selection If transcoding is performed manually Click on Next Eurostat
Command Line Interface (CLI) • Windows command line • Unix/Linux Shell • For automated conversion • Scripts / external systems • No human intervention • Useful when processing all files in a directory, etc. Example 1 command line arguments 29 Eurostat
CLI – Command line call Converter input. File; from (input format); to (output format); dsd_file • Converter • Windows OS: converter. bat • Unix OS: converter. sh using cmd. exe using the terminal • Options (correspond to GUI buttons and controls) -reg; -output. File; -wsdl; -dsd_file; -dsd_id; dsd_agency; -dsd_version; -df_id; -df_version; df_agency; -header_file; -date_format; -level; mapping_file; -ordered_input; -trans_file; -delimiter; header_row; unescape. Input. CSV; unescape. Output. CSV; -validation true|false; excel_template For further information check the User Manual Eurostat
SMDX Converter vs SDMX-RI SDMX Converter SDMX-RI Standalone application Needs to be installed on a server File repository Connected to dissemination DB Generates SDMX files from input files Generates SDMX files from customized SDMX queries Eurostat
Summary of Benefits • • • Open Source -> it’s free Easy to use Platform independent Handy for statisticians and IT Can transform wide variety of file formats Offers many configuration options Eurostat
4. SDMX Converter Demonstrations Eurostat
Converter examples 1. SDMX-ML to CSV 2. Excel to SDMX-ML 3. CSV to SDMX-ML Eurostat
Example 1: SDMX-ML to CSV • Select: Convert (Operation); Input file. xml (Input file); Generic_sdmx (Input format) and provide a name/location”. csv (Output file). Click Next 35 Eurostat
Example 1: SDMX-ML to CSV • Select: DSD (Structure type); ESTAT+AGG_ETS+1. 0 (1). xml) (file). Click Next 36 Eurostat
Example 1: SDMX-ML to CSV • Select CSV parameters: show header (Header row); SDMX format (Date format) and “; ” (Delimiter). Click Next 37 Eurostat
Example 1: SDMX-ML to CSV • Select Open file and retrieve converted file 38 Eurostat
Example 2: Excel to SDMX-ML • Select: Convert (Operation); SDMXNA_MAIN_T 0101_V 1. 2 c_Sample. xlsx (Input file); provide a name/location”. xml” (Output file). Click Next 39 Eurostat
Example 2: Excel to SDMX-ML • Select: DSD (Structure type) and NA_MAIN+ESTAT+1. 2_RI. xml (Structure file). Click Next 40 Eurostat
Example 2: Excel to SDMX-ML • Select: header. prop (File). Alternatively, Manual Config can be performed. Click Next 41 Eurostat
Example 2: Excel to SDMX-ML • Select: Open file to retrieve the converted file Eurostat
Example 3: CSV to Compact_SDMX • Select: Convert and validate (Operation); Input file_csv-xml. csv (Input file ); CSV (Input format); Compact_SDMX (Output format) and provide a name/location. xml (Path). Click Next 43 Eurostat
Example 3: CSV to Compact_SDMX • Select: DSD (Structure type) and locate ESTAT+AGG_ETS+1. 0 (1). xml (File). Click Next 44 Eurostat
Example 3: CSV to Compact_SDMX • Select: header. prop (File); SDMX Format (Data format); No header (Header row); “, ” (Delimiter); mapping. xml (Colum mapping). Click Next 45 Eurostat
Example 3: CSV to Compact_SDMX • Select: Open file and retrieve converted file 46 Eurostat
Don’t forget! To stay abreast of all ESS SDMX information: Eurostat SDMX Info. Space! http: //ec. europa. eu/eurostat/web/sdmxinfospace/welcome For enquiries and issues estat-support-sdmx@ec. europa. eu Eurostat
Vielen Dank! Mulțumesc! Hvala vam puno! ¡Muchísimas gracias! Dziękuję bardzo! Thank you very much! Ти благодарам многу! Много вам хвала! Ευχαριστώ πολύ! Faleminderit shumë! Merci beaucoup ! Eurostat
- Slides: 48