SDMX Istat Toolkit overview Francesco Rizzo rizzoistat it

SDMX Istat Toolkit overview Francesco Rizzo rizzo@istat. it

SDMX Istat Toolkit– main characteristics (1/2) q A set of pick-and-choose building blocks allowing a statistical office to facilitate the standardization and industrialization of the dissemination/reporting process: q metadata handling q database building q data loading q data/metadata dissemination/reporting (M 2 M) q data/metadata dissemination/reporting (GUI) q data exchange between Organizations (Pull and Push) q Subject-matter domain independent q Built using the SDMX Common API (Sdmx. Source. NET) q Integrated with the SDMX-RI q it can be used for building: q “distributed” data warehouse SDMX-based q “stand alone” dissemination systems

SDMX Istat Toolkit– main characteristics (2/2) q License: European Union Public Licence V. 1. 1 q Input formats: CSV, SDMX 2. 0, SDMX 2. 1 q Output formats: SDMX 2. 0, SDMX 2. 1, RDF Data Cube, SDMX -CSV, SDMX-JSON, DCAT

SAS SPSS STATA Etc. Data sources Horizontal CSV Excel 2 CSV Architecture Vertical CSV Prod DB SIS-CC Data Explorer Dissemination Reporting Database (SDMX based) Create metadata Meta Manager Metadata loading Web Metadata Dissemination International Organizations Istat toolkit modules Eurostat SDMX-RI modules OECD SIS-CC modules Data Manager Internet Metadata Repository (Mapping Store) Legacy Diss. DB SDMX-RI Mapping Assistant SDMX-RI Web Client SDMX-RI WS SDMX-RI Test Client Internet

Architecture CSV SDMX-ML SDMX DDB Databases SDMX-RI Web Client Data Manager DDBs SDMX-RI WS MSDB / Registry Mapping Repository Mapping Assistant SDMX-IT building blocks SDMX-RI building blocks Legacy Dissemination database OECD SIS-CC Data Explorer Meta Manager Data Flow Registration Structure Metadata Repository DSD SDMX-ML OECD SIS-CC Data Explorer

SDMX Istat Toolkit – current version q Data. Manager (former Builder & Loader) v 8. 4. 0. 19 q Meta. Manager v 1. 1. 21 q Mapping. Store DB v 6. 5 q NSI Web Service q v 6. 16 (. NET framework 4. 6. 2) q v 7. x. x (. NET Core) q NSI Web Client v 3. 19 q Mapping Assistant v 6. 5. 2 q Excel 2 CSV v 1. 2. 5

New Meta and Data Manager - why Current q developed starting from 2014 q “old” technologies q Data. Manager (ASP. NET) q Registry GUI (ASP. NET) Meta. Manager (MVC, Jquery) q difficult to maintain and evolve q performance issues q Data. Manager (CSV data loading) q Meta. Manager (long item schemes) New q based on new technologies (REACT, . NET Core) q same technologies of “new” SDMX-RI and SIS-CC suite q improve and streamline existing functionalities and implement new functionalities (suggestions come from several training sessions and implementations) q services-based q easy integration with new building blocks

SDMX Istat Toolkit – New Meta and Data Manager q only one application that incorporates the Meta and Data Manager functionalities q functionalities are activated through an authentication/authorization mechanism q architecture service-based q completely integrated with the SDMX-RI (the Mapping Assistant WS, MSDB and the Aut. DB used intensively) q GUI developed using REACT q more user-friendly GUI (wizards, search, zoom, etc. )

New Meta and Data Manager - architecture

Meta Manager functionalities q import of SDMX artefacts from SDMX-ML files q export in SDMX-ML (2. 0 and 2. 1), SDMX-CSV, SDMX-JSON, CSV RDF/XML, RTF (only DSD and children artefacts) q interactively development of SDMX artefacts q CRUD of Codelist, Concepscheme, Categoryscheme, DSD, DF, Categorisation, Content contraints, Structure Set, Provisional agrement, Registration, Hierarchical codelist, other Item schemes q Items can be loaded from CSV files q tabular and tree representation of the code lists q big size code list management (tested with 1 Million of Items) q drag and drop functionalities for re-ordering (order is managed through a suitable configurable annotation) q automatic creation of Categorisations through a drag&drop of DFs among categories q direct links to children artefacts from a parent (e. g. to Code lists from DSD; to DSD from Dataflow, etc. )

Meta Manager functionalities (2/2) qsearch among the collection of a specific artefact (e. g. Code lists) or among the items within an artefact q artefact duplication q artefact categorisation (beyond DFs) q artefact browsing q compare DSDs q compare Item schemes q merge Item schemes q derive Item schemes q manage MSD (not creation) and MDF q artefacts’ copy WS 2 WS q DE business rules annotation management q “custom” annotation management to create nomenclature servers q artefacts download in zip files

Data Manager functionalities q Cube builder q CSV Mapping q CSV/SDMX-ML/. Stat v 7 data loader q Dataflow Builder q filters on dimensions and attributes q switch off dimensions (new DSDs are created automatically) q creation of Mapping. Set, Transcoding q creation of Content Constraints q set/unset in production q Datasets download in SDMX-ML (2. 0 and 2. 1), SDMX-CSV, SDMX-JSON, RDF/XML) q Cube list (with CSV download) q series management q import attribute files q upgrade DSD q synchronize Codelist q restore management q embargo management

Scenario 1 Node 1 NSI WS 1 MSDB 1 MA WS 1 DM API 1 META API 1 FRONT END NODE API DDB 1 RMDB 1 Auth. DB 1

Scenario 2 Node 1 NSI WS 1 MSDB 1 MA WS 1 DM API 1 META API 1 FRONT END DDB 1 Auth. DB 1 RMDB 1 NODE API Node n NSI WSn MSDBn MA WSn DM APIn META APIn DDBn RMDBn Auth. DB n

Scenario 3 Node 1 NSI WS 1 MA WS 1 DM API 1 META API 1 FRONT END DDB 1 Auth. DB 1 RMDB 1 NODE API MSDB 1 Node n NSI WSn MA WSn DM APIn META APIn DDBn RMDBn Auth. DB n

Scenario 4 Ext Node 1 NSI WS 1 MSDB Read/write mode FRONT END NODE API DM API 0 Auth. DB 0 Read/write mode Ext Node N NSI WSn Warning: MSDB for the external nodes is not directly reachable MSDB

Scenario 5 Node 1 NSI WS 1 MSDB 1 MA WS 1 DM API 1 META API 1 DDB 1 Auth. DB 1 Node n RMDB 1 NSI WS 1 FRONT END Which Auth. DB for Ext Node 1, 2? May I use Auth. DB from Node 1 or 2 Ext Node 1 NSI WS 1 MA WS 1 META API Read/write mode MSDB 1 Read/write mode MSDB Ext Node N NSI WSN Warning: MSDB for the external nodes is not directly reachable MSDB DM API 1 META API 1 DDB 1 RMDB 1 Auth. DB 1

Demonstration

Cross-cutting functionalities q application configuratio q Nodes configuration q Users authentication/autorisation

Meta Manager q develop CLs, Conceptscheme and DSD q interactively q from CSV files q from SDMX-ML files q duplicate artefacts q download of SDMX-ML and other formats

Data Manager q cube creation q CSV file Mapping q CSV/SDMX-ML data loading q Dataflows definitions q filters on Dimensions q switch off Dimensions q set dataflow in production q Mapping. Set q Transcoding q set in production q Data Constraints creation q download datasets (SDMX-ML and other formats)

Industry Data Structure Definition

Demostration use case CSV/SDMX-ML Database Cubes (star-schema) Dataflows NEW ORDER new DSD 5 Dimensions IND_PROD TURNOVER IND_CONS PRODUCTION IND_TURN CONSTRUCTION IND_ORD INDUSTRY_DSD+IT 1+1. 0 (6 Dimensions)