Mapping Assistant JeanFrancois LEBLANC Christian SEBASTIAN 11 13

Mapping Assistant Jean-Francois LEBLANC Christian SEBASTIAN 11 -13 May 2016 Eurostat, Unit B 3 – IT solutions for statistical production 1 Eurostat

Table of Contents 1. 2. 3. 4. Objectives What do we need before the demo SDMX RI Creation of the Mapping Store DB 1. Creation of the Mapping Store DB with Orcale 2. Creation of the Mapping Store DB with SQL Server 5. Mapping Assistant 1. 2. 3. 4. 5. First Start Environment created Category Scheme creation Categories creation Addition of the NA_MAIN DSD Eurostat 2

Table of Contents 6. Addition of the dataflow definition 7. Dataflow categoristaion 8. Connection to the dissemination DB 9. Dataset creation 10. Mapping Set creation 11. Mapping between the dissemination DB and the DSD 12. Transcoding 3 Eurostat

1. Objectives • Launch Mapping Assistant for the first time • Configure mapping store database (MASTORE_DB) • Setup a dissemination tree for National Accounts • Create a mapping between a NA DSD and the sample disseminiation database 4 Eurostat

2. What do we need before the demo • Install an Oracle Db • Create an empty database MASTORE_NA • Create a demo database NA with some sample data • Install SDMX-RI tools (xcopy ultimate versions) • • • Mapping Assistant. net 5. 7. 0 Test. Client. net 4. 7. 0 NSI Web Service Java 5. 3. 4 NSI Web Client Java 2. 3. 4 Configured Web Service and Web Client in Weblogic Downloaded SDMX-NA package (DSDs, Dataflows) 5 Eurostat

3. SDMX Reference Infrastructure Mapping Store DSD Local environment Is my dataflow Is my WS working? Test Client (Desktop Software) NSI Client Mapping Web Service Dissemin ation DB Data Consumer (external) https: //webgate. ec. europa. eu/fpfis/mwikis/sdmx/index. php/SDMX_Reference_Infrastr ucture_SDMX-RI 6 Eurostat

4. 1 Creation of the Mapping Sotre DB with Oracle • Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11 g Express Edition, and then Run SQL Command Line. • Connect as SYSTEM user: • Type: connect • Enter user-name: system • Enter password: <password-for-system> 7 Eurostat

4. 1 Creation of the Mapping Sotre DB with Oracle • Create the user: • SQL> create user MSUSER identified by <password-for- MSUSER >; • Grant the user the necessary privileges. For example: • SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE to MSUSER ; 8 Eurostat

4. 2 Creation of the mapping store DB with SQL server Name your mapping store 9 Eurostat

4. 2. 1 Create an user Create a user associated to a login used to access SQL Server 10 Eurostat

4. 2. 2 Grant the rights needed by the MA You are ready!!!! 11 Eurostat

5. 1 Mapping Assistant : first start 1 2 3 4 12 Eurostat

5. 2 Environment created If you now check your mapping store DB, you can see the Mapping Assistant has created all the tables needed 13 Eurostat

5. 3 Category Scheme creation 1 3 No 2 4 14 Eurostat

5. 4 Category creation Note: The Category Scheme and the Catgories below represents the "navigation tree" of the dissemination system. It can be customised as you wish. It will be displayed like you set it up here in the web client later on. In that example we only create a single Category. 15 Eurostat

5. 5 Addition of the NA_MAIN DSD Note: Browse to • Desktop • Workshop • SDMX-NA • DSDs And choose • ESTAT+NA_MAIN+1. 4 Then wait a few seconds… 16 Eurostat

5. 6 Addition of the dataflow definition Browse for: C: SDMX TrainingSourcesStructure Files Note: The download contains all National Accounts dataflows, but we only imported the NA_MAIN DSD. Thus an error may appear for non-NA_MAIN dataflows, which you can ignore. 17 Eurostat

5. 7 Dataflow categoristaion 2 possibilites: Drag & drop Or 1 2 Right-click Attach OK 18 Eurostat

5. 8 Connection to the dissemination DB Settings: NA-Connection Sql. Server localhostSQLEXPRESS NA √ Enter login/password Test connection Save 19 Eurostat

5. 9 Dataset Creation 1 2 3 4 20 Eurostat

Select table and click OK 21 Eurostat

5. 10 Mapping Set creation 22 Eurostat

5. 11 Mapping between the dissemination DB and the DSD 23 Eurostat

Dataset DSD Comment FREQ TIME_FORMAT Transcode: A A, P 1 Y ADJUSTMENT Transcode: NOT_ADJ N Others 1: 1 REF_AREA Constant: AT COUNTERPART_AREA Constant: W 2 INSTR_ASSET Constant: _Z REF_PERIOD_DETAIL Constant: C DECIMALS Constant: 2 TABLE_IDENTIFIER Constant: T 0103 UNIT_MULT Constant: 6 COMPILING_ORG Constant: AT 1 Eurostat 24

5. 12 Transcoding 25 Eurostat

26 Eurostat

Mapping Assistant 27 Eurostat