ESSNET on SDMX II
WP2: PC-Axis SDMX Integration
Attributes in the Nordic SQL Data model
Version Draft 1.
2012-03-19
Analysis and Design of the Test Client v 0.1 / Date Updated: 9/17/2012 10:11:11 AM / Page ii of 9 Page iiPrepared by: TC, IB / Reviewed by:
Type of Document / Project deliverable
Reference: / ESSnet for SDMX Working Package II: PC-Axis SDMX Integration
Issue: / Revision: / Status: / [Draft;Final]
Created by: / Else-Marie Alström / Date: / 2012-03-19
Updated by: / Lars Nordbäck
Approved by:
Document Change Record
Issue/ Revision / Date / Change2012-03-19
Table of contents Page
1 Introduction 4
1.1 Purpose 4
1.2 Structure 4
1.3 Reference Documents and Standards 4
2 High level architecture 5
3 Use case view 6
3.1 Requirements overview 6
3.1.1 Functional requirements 6
4 Attributes on observation values in the Nordic Data model 7
1. Introduction 7
2. Two new tables in the Meta Database 7
3. The new look of the Data table 9
4. Example 10
List of figures Page
Figure 21: High level architecture 5
List of tables Page
Table 11: Terms and Abbreviations 4
[Title][version] / Date Updated / Page ii of 11 Page ii
Prepared by: / Reviewed by:
1 Introduction
1.1 Purpose
This document stands for deliverable “Documentation of the Nordic Data Model for tables that contains attributes on Cell level in the PC-Axis dissemination environment of an NSI”, as a result of “ESSnet for SDMX Phase 2 Working Package 2 PC-Axis SDMX Integration”. The document will act as input for development of software containing the context of attributes on cell level for a table. Example of such software is SQL Paxiom Builder developed in Statistics Norway for the PC-Axis family.
1.2 Structure
The structure of this document is as follows:
Section 2 includes a short presentation of application’s architecture.
Section 3 describes the Use Case view. Functional
Section 4 describes the Attributes on cell level in the Nordic Metadata model
1.3 Reference Documents and Standards
Insert all references to documents or convention or standard used in the document. This part can be divided in:
Graph of the Nordic Data Model 2.2: http://www.scb.se/Pages/List____314010.aspx
Description of the Nordic Data Model 2.2: http://www.scb.se/Pages/List____314010.aspx
Standards and Conventions
Documents or standard named in the document
Terms and abbreviations
Terms or abbreviations used in the document
Acronym / Definition /Table 11: Terms and Abbreviations
Definitions:
Definitions used in the document
2 High level architecture
The PX-Web application acts like a front-end for a configured NSI PC-Axis file database instance. The following picture presents the high level architecture:
Figure 21: High level architecture
There is a wish to be able to specify attributes on the observation values in PX files. This means that we must expand the file format with additional keywords in order to meet these needs.
3 Use case view
3.1 Requirements overview
3.1.1 Functional requirements
There is a wish to be able to specify attributes on the observation values in tables in the CensusHub environment. This means that we must expand the data model with additional information in order to meet these needs.
4 Attributes on observation values in the Nordic Data model
1. Introduction
The Nordic Data Model has been extended with attributes to fulfil the demands Eurostat puts on in information linked to the data cells when delivered to Eurostat.
2. Two new tables in the Meta Database
Using the name Attribute (Attribut in Swedish)
Swedish name / English name / Data type / Mandatory? / Description /Huvudtabell / Maintable / varchar (20) / Yes / The Main table that the attribute is linked to.
Attribut / Attribute / varchar (20) / Yes / Name of the Attribute
AttributKolumn / AttributeColumn / varchar(41) / Yes / The name of the Column
The lenght prepared for future needs to add a prefix linked to Content (Innehall in the Swedish version).
PresText *) / PresText / varchar (250) / No / Presentation text that will be used by the User interfaces.
Ordningsnr / SequenceNo / smallint / Yes / The place in the data table or the Column or place within the Column for the Attribute
1-
Beskrivning / Description / varchar (200) / No / Description/Explanation for the Attribute
Vardemangd / ValueSet / varchar (30) / No / Links a list with valid codes to the Attribute. Can be omitted if an Attribute contains a comment.
KolumnLangd / ColumnLength / smallint / Ja / Maximal length for the Attribute. The Attribute is in the data type varchar.
*) PresText is needed since all attribute columns will not always have a value set. In value sets that are used for attributes prestext need not to be set since it is not mandatory. On the other hand it must always be set when the value set is missing for the attributes.
And with the name Attribut_Eng (Attribute_Eng)
Swedish name / English name / Data type / Mandatory? / Description /Huvudtabell / Maintable / varchar (20) / Yes / Main table that the Attribute is linked to.
Attribut / Attribute / varchar (20) / Yes / The name of the Attribute.
AttributKolumn / AttributeColumn / varchar(41) / Yes / The name of the Column
The length prepared for future needs to add a prefix linked to Content (Innehall in the Swedish version).
PrestText / PresText / Varchar(250) / No
Beskrivning / Description / varchar (200) / No / Description/Explanation for the Attribute
In both of the tables are Main table (Huvudtabell) and Attribute (Attribut) keys.
The suggestion does not imply any changes in the original tables in the Meta Database.
3. The new look of the Data table
Variable 1 / Variable n / Content 1 / Content m / Attribute 1 / Attribute 2 / Attribute n /Attribute 1-n affects both Content 1 and m. If different Attribute codes are needed for content 1 and n they must be in a table each of its own or be structured as a variable in one and the same table if the unit and other metadata are the same.
4. Example
Example 1 – Several attribute columns
Attribute table
Main table / AttributeColumn / Attribute / PresText / Order
no / Description / Value
set / Column
Length /
Bef54 / Obs_Status / OBS_STATUS / Observationsstatus / 1 / Text … / ATTstatus / 1
Bef54 / Obs_Note / OBS_NOTE / Comment / 2 / Text … / null / 100
Data table
Region / Age / Time / Persons / Obs_Status / Obs_Note /0180 / 44 / 2009 / 14679 / A / Not reliable due to many no respondents in the survey
0180 / 45 / 2009 / 10765 / X
Exeaple 2 – An attribute column containing several attributes
Attribute table
Main table / AttributeColumn / Attribute / PresText / Order
no / Description / Value set / Column
Length /
Bef54 / Attribute / OBS_STATUS / null / 1 / Text … / ATTstatus / 1
Bef54 / Attribute / OBS_NOTE / Comment / 2 / Text … / null / 100
Data table
Region / Age / Time / Persons / Attribute /0180 / 44 / 2009 / 14679 / A: Not reliable due to many no respondents in the survey
0180 / 45 / 2009 / 10765 / X:
All attributes are stored in the same column but are separated with a: (colon), which also has to be entered if the attribute does not have a value.
Example 1 and 2 cannot be combined.
[Title][version] / Date Updated / Page ii of 11 Page ii
Prepared by: / Reviewed by: