ESSNET on SDMX II

Analysis and Design of Loader

Version 3

01-12-2011

Type of Document / Project deliverable
Reference:
Issue: / Revision: / Status: / Final
Created by: / Laura Vignola / Date:
Updated by:
Approved by:

Document Change Record

Issue/ Revision / Date / Change
3 / 10/02/2013 / New release that contains all the changes occurred during the development and the Eurostat comments (see Assessment document)

Table of contents Page

1Introduction

1.1Scope

1.2Structure

1.3Reference Documents and Standards

2Application Architecture

3Use case view

3.1Requirements overview

3.1.1Functional requirements

3.1.2Non-functional requirements

3.1.3Scenarios

3.1.3.1Scenario 1 – Definition of settings

3.1.3.2Scenario 2 – Change of settings

3.1.3.3Scenario 3 – Alignment of database

3.1.3.4Scenario 4 – Managing of Users: Creation of a new user

3.1.3.5Scenario 5 – Managing of Users: modification of a existing user

3.1.3.6Scenario 6 – Managing of Users: deletion of a user.

3.1.3.7Scenario 7 – Managing of Namespaces

3.1.3.8Scenario 8 – Creation of Dataflow Data table (DFData table)

3.1.3.9Scenario 9 – Mapping of csv data file

3.1.3.10Scenario 10 – Mapping of gesmes data file

3.1.3.11Scenario 11 – Mapping of flr data file

3.1.3.12Scenario 12 – Loading of data file

3.1.3.13Scenario 13 – Creation of data File

3.2Actor specification

3.3Use cases model

3.3.1UseCase-UC_MS:

3.3.2UseCase-UC_ADB:

3.3.3UseCase-UC_UM:

3.3.4UseCase-UC_DT:

3.3.5UseCase-UC_MDF:

3.3.6UseCase-UC_LDF:

3.3.7UseCase-UC_CDF:

3.4Activity Diagrams

3.4.1Settings

3.5Context Diagram

4Classes

4.1Action Class package

4.1.1Action class

4.1.2DbConnectionAction

4.1.3Codelists

4.1.4Codelist

4.1.5Concepts

4.1.6Concept

4.1.7clsConnection

4.1.8clsDAOWrapper

4.1.9FileManager

4.1.10CSVManager

4.1.11FLRManager

4.1.12GESMESManager

4.1.13clsDataflows

4.1.14clsDataflow

4.1.15Settings class

4.1.16DSDs class

4.1.17DSD class

4.1.18Header class

4.1.19Contact

4.1.20Contact detail class

4.1.21clsMapping Mapping class

4.1.22clsMapping Mapping class

4.1.23DataflowDetails class

4.1.24DataMappingManager

4.1.25CSVDataMappingManager

4.1.26GESMESDataMappingManager

4.1.27FLRDataMappingManager

4.1.28SDMXDataMappingManager

4.1.29SDMXDataMappingCompactManager

4.1.30SDMXDataMappingCrossManager

4.1.31clsNamespace

4.1.32clsNamespace

4.1.33clsDSDNS

4.1.34Dataflow class

4.2Namespaces package

4.2.1Namespaces

4.2.2DSDNSs

4.3Mapping class

4.3.1Mapping class

4.4Concepts package

4.4.1Concept Type

4.4.2ConceptCoded Type

4.4.3ConceptDS Type

4.4.4Codelist class

4.4.5Code Type

4.5Header package

4.5.1Header

4.5.2PartyType

4.5.3Contact

4.6DataFile package

4.6.1DataFile

4.6.2rss

4.7LDao package

4.7.1UserDao class

4.7.2RssDao class

4.7.3DataflowDao class

4.7.4DSDDao class

4.7.5NamespaceDao class

4.7.6MappingDao class

4.7.7AlignDatabase class

4.7.8CodeListDao class

4.7.9CreateDBSchema class

4.7.10ValuesCollection datatype

5Dynamic view

5.1Sequence diagrams

6Schema

6.1Predefined schema

6.1.1DSD Table

6.1.2DATAFLOW Table

6.1.3DATAFLOW_MAPPING Table

6.1.4USER_LINK Table

6.1.5USER Table

6.1.6DSD_NS Table

6.1.7NAMESPACE Table

6.1.8COMPONENT Table

6.1.9CODELIST Table

6.1.10COMPONENT_DESCRIPTOR Table

6.2Configurable schema

6.3MSDB schema

7Grafical user interface

7.1Loader application structure

7.2Setting form

7.2.1Login form

7.2.2Align Database form

7.2.3Data Tables form

7.2.4DatWizard form (Mapping)

8Appendix

8.1Mapping of csv data file

8.2Mapping of flr data file

1

1Introduction

1.1Scope

This document stands for deliverable “Analysis and Design of the Loader”, as a result of “Task 2/Subtask 2.1 of the WP4 in ESSnet on SDMX phase II “. The purpose of this document is to provide functionality specification and technical documentation of the Loader application, the document describe all the functional and non functional requirements of the application using the UML language. This document can be used by the developers that want implement and maintain the software.

NOTE: It is recommended that readers of this document should have SDMX technical knowledge and UML (Unified Modeling Language) knowledge

1.2Structure

The structure of this document is as follows:

Section 2includes a short presentation of application’s architecture.

Section 3describes the Use Case view. Functional and non-functional requirements are presented.

Section 4describes the standalone packages diagramand the class diagrams.

Section5shows the sequence diagrams.

Section 6presents the dbSchemacreated and used by the application.

Section7presents the GUI.

1.3Reference Documents and Standards

Terms and abbreviations

Acronym / Definition
LDB / Loader database. Database Created by the Loader application
MSDB / Mapping Store database. Database created by the Mapping Assistant application
AU / Administrator user
MU / Main User

Table 11: Terms and Abbreviations

2Application Architecture

Prendere dal technical final report

3Use case view

3.1Requirements overview

3.1.1Functional requirements

The application must be allow the setting of the parameters necessary for the correct functioning of the application. The parameters are:
  • Parameter for the connection to the Loader database
  • Parameters for the connection to the Mapping Store database
  • Directory paths where are stored or from where are retrieved the necessary files for the correct functioning of the application
  • The rsslink and the rssURL

The Loader application can access to three database management system: Oracle, SQLServer, MySql
The application must allow and “alignment” with the Mapping store database.
The alignment will be done showing to the user the Data Structure Definitions and related dataflows that are stored into the Mapping Store database and are not already inside the Loader database
The application must allow the deletion of the dataflows stored in the Loader database. The dataflows can be removed from the Loader database only if a corresponding data table has not been created.
It should be possible to load from the Mapping Store database the information for every dataflow, of the header.
FR1 / The “Loader” applicationallows the creation of the predefined(paragraph 5.1) tables of the LDB schema.
The predefined part of the schema includes the following tables:
  1. tables for management of the users of the application,
  2. tables for the management of the Namespaces,
  3. tables for the storage of information necessary for the creation of RSS feed,
  4. tables for the storage of structural metadata.

The predefined table are created only at the first access to the application after to have correctly set the Setting information.
FR2 / The “Loader” application allows the creation of the “DSD-based” tables(paragraph 5.2) tables. These tables are created with the user interaction and they will contain data.
The Data Tables will have as primary key a set of columns chosen by the user during the creation of the data table. The dimension and mandatory attribute are automatically included as primary key of the table.
FR3 / The Header information, will be retrieved from the MSDBand stored in the Loader database. The structural metadata related to the DSD and dataflows loaded in the Loader database are stored in the Loader database. The MSDB tables used by the Loader application are shown in paragraph 5.3
FR4 / The Loader application must allow the creation of a Administrator user as the first user for the application. The Administrator user can create new usersat different levels of security. Three different levels of security must be guaranteed:
  • “Administrator” user having all privileges
  • “Main_User” user, that can manage one or more DSDs assigned to him (in this context “manage” means: to create tables (see FR2) and load data corresponding to the DSDs assigned)
  • “Simple_User” user that can manage one or more dataflows assigned to him (in this context “manage” means: to load data corresponding to the dataflows assigned)

FR5 / The “Loader” application must allow to create an association (named “mapping”) between the structural metadata contained in the data file and the columns of corresponding data table. The mapping is necessary to load data into the LDB and it can be done only by a Administrator user or a Main_User.
The mapping must be done for the input files having the following data format: Gesmes-TS, csv, flr. All the columns of the datafile must be mapped with a column of the data table and all the columns of the data table must be mapped.
FR6 / The data that can be loaded inside the LDB can have four different formats:
  • csv (character separated value),
  • flr (fixed length record),
  • gesmes TS,
  • SDMX Compact data message vers 2.0.
  • SDMX Cross Sectional data message vers 2.0.
  • SDMX Generic data message vers 2.0.
The data to be loadedmust be described by concepts and codes of a DSD previously loaded from the Mapping Store database into the Loader Database.
FR7 / The data inside the data file loaded by the application can belong to only one dataflow.
FR8 / The Loader application must have the functionality to check the concepts coded making a comparison between the structural metadata inside the datafile and the DSD associated to the datafile. This functionality will be named “Check of Metadata”.
FR9 / he “Loader” application must allow to create a SDMX data file (with data loaded into the LDB) in the formats:
•generic,
•cross sectional,
•compact,
and the corresponding rss file.
The creation of the SDMX data files is necessary if the NSI does not want to use the Web Service to disseminate data but only use static files. The rss files are created for the static file and they contain the URL where to retrieve SDMX datafiles.
FR10 / The data inside the SDMX data files created by application belong to only one dataflow
FR11 / When a data file is loaded inside the database, the Loader application must update the RSS table that will be used by “RSS Provider” application (see )
FR12 / The Loader application will allow the "Alignment" of the LDB with the updating of the tables about the DSDs and Dataflow already stored in the MSDB. This “Alignment” functionality will be called by the user when it is need.
The user could select, using the “Alignment” functionality the DSDs that intend to use in the LDB and Load them (with related information) into the LDB.

Table 21: Functional Requirements

3.1.2Non-functional requirements

NFR1 / The Loader application uses parameters stored in the configuration file for the correct functioning.
NFR2 / The MSDB must be installed.
NFR3 / The data files can be loaded in the LDB only if the structural metadata corresponding to them have been already loaded in the MSDB.

Table 22: Non - Functional Requirements

3.1.3Scenarios

3.1.3.1Scenario 1 – Definition of settings

Introduction: In this scenario is described how to insert settings parameters necessary for a correct functioning of the application. All the settings will be savedin the configuration file:

Requirements used: FR1, NFR1

Description:

Step1:The user opens the Loader application and selects the menu voice “Open → Settings”.

Step2:The Settings formcontains the following fields:

  • The connection parameters to the mapping store database (MSDB) and the connection parameters to the loader database (LDB)
  • The directories[1] used by the application to store or to retrieve files
  • The rss link and rss url used in the rss feed

Step3:The user fillsfieldsin the Setting form and saves them.

Step4:The system checksthe correctness of information (mandatory fields must be filled, the correctness of connection strings and directories) and storesthem intothe configuration file.

Step5:The system creates the predefined tables and inserts the “Administrator” user that represents the first user of the application.

3.1.3.2Scenario 2 – Change of settings

Introduction: This scenario describes how to modify settings parameters.

Requirements used: FR1, NFR1

Description:

Step1:The user opens the Loader application and selects the menu voice “Open → Settings”.

Step2:The "Settings" form will be shown with the information stored in the configuration file.

Step3:The user modifies fields in the "Setting" form and saves them.

Step4:The system checks the correctness of information (mandatory fields must be filled, the correctness of connection strings) and stores into the configuration file.

Step5:The system check if the predefined tables already exist and if not it creates them and inserts the “Administrator” user that represents the first user of the application

3.1.3.3Scenario 3 – Alignment of database

Introduction: In this scenario is described the functionality of alignment of the database LDB with DSDs stored inside the MSDB.

Requirements used: FR12, NFR1,NFR2,NFR3

Description:

Step1:The user selects the menu voice “Tool -> Align database”

Step2: The system will show a three with all the DSD and dataflow loaded in the MSDB and that are not already stored inside the LDB

Step3: The user selects the DSDs and related dataflow that wants to load in the LDB database and click on Align button

Step4: The system load the DSDs, dataflow, concepts and codelist of the DSDs selected.

3.1.3.4Scenario 4– Managing of Users:Creation of a new user

Introduction:This scenario describes the creation of a new user inside the User Manager functionality.The Administrator user can create a user with three different levels of security: Administrator_User, Main_Userand Simple_User.

Requirements used:FR4,NFR1

Description:

Step1:The AU selects the menu voice “Tools -> Users Manager” and clicks on the “Add” button.

Step2:The "AU" inserts all the information concerning the new user (Name, Department, email, telephone number) and assigns the new user to a group (see picture 7-18).

The groups are:

•Administrator

•Main_User

•Simple_User

Step3: The AU define all the DSDs and/or dataflows that the user can manage. If the new user is a administrator then all the DSDs and corresponding dataflows will be automatically assigned to it.

Step4: The AUsaves the information.

Step5: The system checks the correctness of information (mandatory fields must be filled, format of the email address) and stores them into the LDB.

3.1.3.5Scenario 5 – Managing of Users: modification of a existing user

Introduction: This scenario describes the modificationof a user already created inside the User Manager functionality.

Requirements used:FR4,NFR1

Description :

Step1:The AU clicks on the “Tools -> User Manger” menu voice and selects a user.

Step2:The system shows all the information associated to the user selected.

Step3: The AU modifies one or more fields of the form (the password cannot be modified) and saves the changes

Step4:The system checks correctness of changes (mandatory fields must be filled and the format of email field) and stores them into the LDB.

3.1.3.6Scenario 6 – Managing of Users: deletion ofauser.

Introduction: This scenario describes the deletion of a existing user(see scenario 3).

Requirements used:FR4,NFR1

Description:

Step1:The AU clicks on the “Tools -> User Manger” and selects a user. It clicks on the “Remove”button.

Step2:The system removes the user and all the information linked to the user from the LDB.

3.1.3.7Scenario 7 – Managing of Namespaces

Introduction: This scenario describes how insert and modify the Namespaces

Requirements used:FR1,NFR1

Description:

Step1:The AU click on the “Tools -> Namespaces Manager”.

Step2:The system shows a form with the Namespaces:

  • Namespace for the SDMX message
  • Namespace for the SDMX compact
  • Namespace for the SDMX cross
  • Namespace for the SDMX generic
  • Namespace for the SDMX structure
  • Namespace for the SDMX utility
  • A grid in which the user can insert the DSD name (first column), the prefix of DSD (second column), the Schema LocationNamespace (third column) and the Schema instance Namespace (fourth column); used in the SDMX data file creation and in the loading of SDMX data file.

Step3:The AU inserts or modifiesthe namespaces and save them.

Step4:The System saves the fields into the database.

3.1.3.8Scenario 8– Creation of Dataflow Data table (DFData table)

Introduction: This scenario describes how to create the table of data.

Requirements used: FR2,NFR1,NFR3

Description:

Step1:The user selects the “Create Data Tables” menu voice.

Step2:The system shows a form where the user selects the option of “Creation of data table”.

Step3:The system shows all the DSDs that are associated to the user. The user selects one DSD.

Step4:The system shows all the dataflow of the DSD selectedthat areassociated to the user.

Step5:The user selects a dataflow.

Step6:The system checks ifalready exist a data table with the name of the dataflow selected and if not,shows all the concepts belonging to the DSD. For every concept selected, a column in the table will be created having:

  • as name the same name of the concept and “_” like suffix,
  • as data type, the data type of the concept (string as default)
  • as size, the size of the concept size (“255” by default).

The user could select only conditional concepts while the mandatory concepts (dimensions and mandatory attribute) will be automatically included in the selection.

Step7:The user defines the order of the columns in the table and creates the table. The order of the columns in the table is important to optimize the loading of the data file.

Step8:The system creates the data table.

3.1.3.9Scenario 9 – Mapping of csv data file

Introduction: In this scenario is described the creation of the mapping for a csv data file type.In a csv data file every rows contains a number of fields divided from each other by a separator character. The fields can be considered like columns of the data file and they must contain the structural metadata (codes of codelists) of a DSD previously loaded in the LDB.

Requirements used: FR5,NFR1,NFR3

Description:

Step1:The user selects the menu voice “Mapping of Data file → csv” and select a DSD.

Step2:The system shows all the dataflowof the DSD chosen that are associated to the user and the user selectsa dataflow.

Step3:The user selectsthe file to load and the separator character.

Step4:The system showsa grid with the first 10 rows of the file and a list of all columns of the Data table.

Step5:The user must assign to every column of the grid a column of the table.

Step6:The user saves the mapping.

Step7:The system checks if the mapping is correct (all the columns that represents the primary key of the table must be mapped and a column of the Data table cannot be associated to more then one filed) and saves the mapping into the database in xml format.

3.1.3.10Scenario 10 – Mapping of gesmes data file

Introduction: In this scenario is described the creation of the mapping file for a gesmes data file type. In a gesmes file the rows containing data startby the string “ARR++” and are divided from each other by a separator character (that is usually “;”). The fields can be seen like columns and they must contain the structural metadata (codes of codelists) of a DSD previously loaded in the LDB.

Requirements used: FR5,NFR1,NFR3

Description:

Step1:The user selects the menu voice “Mapping of Data file → gesmes” and select a DSD.

Step2:The system shows all the dataflow of the DSD chosen that are associated to the user and the user selects a dataflow.

Step3:The user selects the file to load and the separator character.

Step4: The system selects the rows starting by “ARR++” and reads datastarting from the fifth character.

Step5: The system shows a grid with the first 10 rows of the file and it shows a list of all columns of the Data table.

Step6:The user must assign to every column of the grid, a column of the table.

Step7:The user saves the mapping.

Step8:The system checks if the mapping is correct (all the columns that represents the primary key of the table must be mapped and a column of the Data table cannot be associated to more then one filed) and saves the mapping into the database