Skip to Content

Changes for page 5 Data Bases and SDMX

Last modified by Artur on 2025/09/10 11:19
From version 5.2
edited by Elena
on 2025/06/16 18:56
Change comment: There is no comment for this version
To version 1.1
edited by Artur
on 2025/04/21 12:12
Change comment: Imported from XAR

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -WebHome
1 +Methodology.SDMX 2\.1 User Guide.WebHome
Author
... ... @@ -1,1 +1,1 @@
1 -xwiki:XWiki.elenasemanticproorg
1 +xwiki:XWiki.arturkryazhev
Tags
... ... @@ -1,1 +1,0 @@
1 -Adjustment|Artefact|Attribute|Category|Code|Code list|Concept|Data provider|Data set|Data structure definition|Dimension|Group key|Map|Measure|Metadata set|Reference area|Reference metadata|SDMX Information Model|SDMX Registry|Statistical data and metadata exchange|Structural metadata|Unit of measure|Version
Content
... ... @@ -4,42 +4,42 @@
4 4  
5 5  == 5.1 Scope of this Chapter ==
6 6  
7 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_560921d6b5e976e3.png||data-xwiki-image-style-alignment="center" height="442" width="699"]]
7 +(% style="text-align: center;" %)
8 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_560921d6b5e976e3.png||height="442" width="699"]]
8 8  
9 9  == 5.2 Introduction ==
10 10  
11 -So far this guide has concentrated on the various [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] constructs – [[structural metadata>>doc:sdmx:Glossary.Structural metadata.WebHome]], data and [[metadata sets>>doc:sdmx:Glossary.Metadata set.WebHome]] – which enable applications to understand and process the data and [[reference metadata>>doc:sdmx:Glossary.Reference metadata.WebHome]]. The next few chapters explain how these are used in practical situations. This starts with the database: the database holds the data that are to be reported, collected, or disseminated. The database is at the kernel of any statistical system.
12 +So far this guide has concentrated on the various SDMX constructs – structural metadata, data and metadata sets – which enable applications to understand and process the data and reference metadata. The next few chapters explain how these are used in practical situations. This starts with the database: the database holds the data that are to be reported, collected, or disseminated. The database is at the kernel of any statistical system.
12 12  
13 -This Chapter explains ways that you can read and write [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] formatted data files to and from a database, and how you can process an [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] REST query for data. It also describes how you can use a [[Data Structure Definition>>doc:sdmx:Glossary.Data structure definition.WebHome]] to create database tables.
14 +This Chapter explains ways that you can read and write SDMX formatted data files to and from a database, and how you can process an SDMX REST query for data. It also describes how you can use a Data Structure Definition to create database tables.
14 14  
15 15  == 5.3 Database and DSD Mapping ==
16 16  
17 -Note that for the data reporting use case it is probable that the coding system used in the database of the data reporter is not the same as that defined in the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] (which is usually that used by the data collector), and the database column names are not the same as the [[Dimension>>doc:sdmx:Glossary.Dimension.WebHome]] and [[Attribute>>doc:sdmx:Glossary.Attribute.WebHome]] Ids in the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] used for data reporting. Even for the data collector it may be that the coding system in [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] is not the same as that used in the collector’s database. In these cases there is a need for a generic mapping mechanism. Depending on the chosen method for reading and writing data to/from the database, this mapping can be performed from within the database application or external to it (i.e. before passing the data to the database application or after the database application has written the data).
18 +Note that for the data reporting use case it is probable that the coding system used in the database of the data reporter is not the same as that defined in the DSD (which is usually that used by the data collector), and the database column names are not the same as the Dimension and Attribute Ids in the DSD used for data reporting. Even for the data collector it may be that the coding system in DSD is not the same as that used in the collector’s database. In these cases there is a need for a generic mapping mechanism. Depending on the chosen method for reading and writing data to/from the database, this mapping can be performed from within the database application or external to it (i.e. before passing the data to the database application or after the database application has written the data).
18 18  
20 +//<this is taken directly from the current user guide>//
19 19  
20 -This mechanism provides a generic metadata-driven way for the database application to map the local [[structural metadata>>doc:sdmx:Glossary.Structural metadata.WebHome]] present in the [[data providers>>doc:sdmx:Glossary.Data provider.WebHome]]’ system and those provided with the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]].
22 +This mechanism provides a generic metadata-driven way for the database application to map the local structural metadata present in the data providers’ system and those provided with the DSD.
21 21  
22 22  In order to better explain this process the following real life example will be used.
23 23  
24 -The Eurostat SODI ([[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] Open Data Interchange) project deals with certain of the set of STS (short-term statistics) indicators defined by EU statistical legislation. This project implements a data-sharing architecture using the pull mode (although the push mode is also supported). Generally the majority of the involved data producers have their data already stored in a database and described using different local [[structural metadata>>doc:sdmx:Glossary.Structural metadata.WebHome]]. This is the case for the Italian National Institute of Statistics (ISTAT), which disseminates those data through its short-term statistical databank ConIstat{{footnote}}ConIstat: http://con.istat.it/{{/footnote}}.
26 +The Eurostat SODI (SDMX Open Data Interchange) project deals with certain of the set of STS (short-term statistics) indicators defined by EU statistical legislation. This project implements a data-sharing architecture using the pull mode (although the push mode is also supported). Generally the majority of the involved data producers have their data already stored in a database and described using different local structural metadata. This is the case for the Italian National Institute of Statistics (ISTAT), which disseminates those data through its short-term statistical databank ConIstat{{footnote}}ConIstat: http://con.istat.it/{{/footnote}}.
25 25  
26 26  Inside ConIstat, data are stored in a database using local structure metadata. A simplified snapshot of the database schema is provided in **Error! Reference source not found.**.
27 27  
28 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_511a92cc4b6c720d.png||data-xwiki-image-style-alignment="center" height="221" width="508"]]
29 -
30 30  (% style="text-align: center;" %)
31 -**{{id name="image_7"/}}**
31 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_511a92cc4b6c720d.png||height="221" width="508"]]
32 32  
33 33  (% style="text-align: center;" %)
34 -**Figure 7: Database schema for ConIstat**
34 +**{{id name="image_7"/}}Figure 7: Database schema for ConIstat**
35 35  
36 -The schema is mainly based on two database tables: METADATA and DATA. The others tables can be considered as lookup tables useful to store [[code lists>>doc:sdmx:Glossary.Code list.WebHome]]. Moreover two tables, respectively named DOMAIN and SUBDOMAIN, allow categorizing data in statistical subject-matter domains.
36 +The schema is mainly based on two database tables: METADATA and DATA. The others tables can be considered as lookup tables useful to store code lists. Moreover two tables, respectively named DOMAIN and SUBDOMAIN, allow categorizing data in statistical subject-matter domains.
37 37  
38 -The main (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) used in order to describe each time-series are the following:
38 +The main concepts used in order to describe each time-series are the following:
39 39  
40 40  **Category**: short term statistical indicator
41 41  
42 -**Type**: [[adjustment>>doc:sdmx:Glossary.Adjustment.WebHome]] indicator
42 +**Type**: adjustment indicator
43 43  
44 44  **Vs**: stock/flow
45 45  
... ... @@ -47,13 +47,13 @@
47 47  
48 48  **Freq**: frequency
49 49  
50 -**Um**: [[Unit of measure>>doc:sdmx:Glossary.Unit of measure.WebHome]] + Unit multiply + Base year
50 +**Um**: Unit of measure + Unit multiply + Base year
51 51  
52 52  **Start_Period**: start period of the time-series
53 53  
54 54  **End_Period**: last available period of the time-series
55 55  
56 -Each time-series is identified through a row in the METADATA table, and each field in that table has a correspondence in a particular lookup table representing a [[code list>>doc:sdmx:Glossary.Code list.WebHome]]. So the time-series //Monthly, neither seasonally or working day adjusted, Production in industry index base 2000, Mining and quarrying// is described in the following way:
56 +Each time-series is identified through a row in the METADATA table, and each field in that table has a correspondence in a particular lookup table representing a code list. So the time-series //Monthly, neither seasonally or working day adjusted, Production in industry index base 2000, Mining and quarrying// is described in the following way:
57 57  
58 58  **Category**: 11 (index of industrial production)
59 59  
... ... @@ -67,38 +67,36 @@
67 67  
68 68  **Um**: PE (index number - base 2000)
69 69  
70 -(% class="wikigeneratedid" id="HStart_Period:01_1990End_Period:08_2008" %)
71 -**Start_Period: 01_1990 End_Period: 08_2008**
70 +==== Start_Period: 01_1990 End_Period: 08_2008 ====
72 72  
73 73  The mapping process can be achieved by storing the resulting information in a special repository outside or inside the native database. In the case of the reported example it was chosen to use a repository inside the native database but without changing anything in the original tables. For this purpose, the following tables were added to the already existing schema:
74 74  
75 75  * STS_METADATA: used to describe STS time-series (in order to describe other domains it would be necessary to add other tables, for example ESA_METADATA for National Accounts and so on);
76 -* Some lookup tables useful to store within the local database some [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] [[artefacts>>doc:sdmx:Glossary.Artefact.WebHome]] from the related [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] (for example: labels or even descriptions for (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%), [[code lists>>doc:sdmx:Glossary.Code list.WebHome]] and dataflows)
75 +* Some lookup tables useful to store within the local database some SDMX artefacts from the related DSD (for example: labels or even descriptions for concepts, code lists and dataflows)
77 77  
77 +The table STS_METADATA represents the place where the mapping process stores the mapping information. In fact, it inherits the base structure from METADATA, and some fields were added in order to cover all the concepts expressed in the SDMX DSD. The resulting database schema after adding the new tables useful for the mapping process is shown in Figure 5.3.8.
78 78  
79 +(% style="text-align: center;" %)
80 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_946fffda5f1e39f4.png||height="623" width="579"]]
79 79  
80 -The table STS_METADATA represents the place where the mapping process stores the mapping information. In fact, it inherits the base structure from METADATA, and some fields were added in order to cover all the (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) expressed in the [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]]. The resulting database schema after adding the new tables useful for the mapping process is shown in Figure 5.3.8.
81 -
82 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_946fffda5f1e39f4.png||data-xwiki-image-style-alignment="center" height="623" width="579"]]
83 -
84 84  **{{id name="image_5.3.8"/}}Figure 5.3.8: Database schema with additional tables for mapping**
85 85  
86 -In order to perform the mapping process correctly, it is necessary to consider different types of mapping: mapping of (% style="color:#e74c3c" %)concepts(%%) and mapping of [[codes>>doc:sdmx:Glossary.Code.WebHome]]{{footnote}}For further explanations of the usage of concepts and codes, see chapters XXXXXX{{/footnote}}.
84 +In order to perform the mapping process correctly, it is necessary to consider different types of mapping: mapping of concepts and mapping of codes{{footnote}}For further explanations of the usage of concepts and codes, see chapters XXXXXX{{/footnote}}.
87 87  
88 88  ==== 5.3.1.1 Mapping of concepts ====
89 89  
90 -The first step is to identify all the statistical (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) involved in the exercise. The following circumstances can occur:
88 +The first step is to identify all the statistical concepts involved in the exercise. The following circumstances can occur:
91 91  
92 -1. one (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) in the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] can be linked up with a single local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%). A typical example is the //measured value// in the [[data provider>>doc:sdmx:Glossary.Data provider.WebHome]] database that corresponds to the //Primary [[measure>>doc:sdmx:Glossary.Measure.WebHome]]// in the STS [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] used for SODI;
93 -1. one local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) must be linked up with two or more (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) in the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]]. For example in the local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) named Um there is an element as follows: “one million of Euro”. In the related STS [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] it corresponds to two (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%): Unit (Euro) and Unit multiple (one million);
94 -1. one (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) in the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] is not directly linked up with any local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%). This could be the case of the (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) “[[Reference area>>doc:sdmx:Glossary.Reference area.WebHome]]”, in fact that (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) is generally not used in a National Organisation because it is the default (Italy);
95 -1. one (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) in [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] is linked up with two or more local (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%). For example the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) “[[Adjustment>>doc:sdmx:Glossary.Adjustment.WebHome]]” has no 1-to-1 correspondence with any single local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%); it is split into two different (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) “DAYADJ” (calendar adjusted) and “SEASADJ” (adjusted for periodical variations during the measurement period), which each has a Boolean value (true/false).
90 +1. one concept in the DSD can be linked up with a single local concept. A typical example is the //measured value// in the data provider database that corresponds to the //Primary measure// in the STS DSD used for SODI;
91 +1. one local concept must be linked up with two or more concepts in the DSD. For example in the local concept named Um there is an element as follows: “one million of Euro”. In the related STS DSD it corresponds to two concepts: Unit (Euro) and Unit multiple (one million);
92 +1. one concept in the DSD is not directly linked up with any local concept. This could be the case of the concept “Reference area”, in fact that concept is generally not used in a National Organisation because it is the default (Italy);
93 +1. one concept in DSD is linked up with two or more local concepts. For example the DSD concept “Adjustment” has no 1-to-1 correspondence with any single local concept; it is split into two different concepts “DAYADJ” (calendar adjusted) and “SEASADJ” (adjusted for periodical variations during the measurement period), which each has a Boolean value (true/false).
96 96  
97 97  ==== 5.3.1.2 Mapping of codes ====
98 98  
99 -The second step is the mapping of the [[codes>>doc:sdmx:Glossary.Code.WebHome]]. Often a (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) within a [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] can assume a [[code>>doc:sdmx:Glossary.Code.WebHome]] enumerated in a [[code list>>doc:sdmx:Glossary.Code list.WebHome]] or a free value. The same thing can happen for a local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%). Assuming the (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) used in the [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] and the local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%), used in the [[data provider>>doc:sdmx:Glossary.Data provider.WebHome]]'s database, are both described using [[code lists>>doc:sdmx:Glossary.Code list.WebHome]], it may be possible to (% style="color: rgb(231, 76, 60); color: rgb(231, 76, 60)" %)map(%%) one [[code>>doc:sdmx:Glossary.Code.WebHome]] in the first [[code list>>doc:sdmx:Glossary.Code list.WebHome]] with a [[code>>doc:sdmx:Glossary.Code.WebHome]] in the second [[code list>>doc:sdmx:Glossary.Code list.WebHome]]. The following example shows two such [[code lists>>doc:sdmx:Glossary.Code list.WebHome]]:
97 +The second step is the mapping of the codes. Often a concept within a DSD can assume a code enumerated in a code list or a free value. The same thing can happen for a local concept. Assuming the concept used in the DSD and the local concept, used in the data provider's database, are both described using code lists, it may be possible to map one code in the first code list with a code in the second code list. The following example shows two such code lists:
100 100  
101 -* [[code list>>doc:sdmx:Glossary.Code list.WebHome]] associated with the frequency local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]]
99 +* code list associated with the frequency local concept
102 102  
103 103  (((
104 104  (% style="width:487.446px" %)
... ... @@ -110,7 +110,7 @@
110 110  |(% style="width:152px" %)52|(% style="width:331px" %)Weekly
111 111  )))
112 112  
113 -* [[code list>>doc:sdmx:Glossary.Code list.WebHome]] associated with the frequency (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) in the [[code list>>doc:sdmx:Glossary.Code list.WebHome]] used by the STS [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]]
111 +* code list associated with the frequency concept in the code list used by the STS DSD
114 114  
115 115  (((
116 116  (% style="width:528.446px" %)
... ... @@ -138,9 +138,9 @@
138 138  |(% style="width:162px" %)B|(% style="width:195px" %) |(% style="width:175px" %)Business
139 139  )))
140 140  
141 -Often the [[map>>doc:Glossary.Map.WebHome]] processing can be helped by some rules. For example, consider the CL_STS_ACTIVITY [[code list>>doc:sdmx:Glossary.Code list.WebHome]] and the NACE Rev 1.1 classification. The rule is: remove all dots from the NACE [[code>>doc:sdmx:Glossary.Code.WebHome]] and add as many zeros as necessary in order to reach four digits. Then add the prefix N1, or NS in case of special [[codes>>doc:sdmx:Glossary.Code.WebHome]].
139 +Often the map processing can be helped by some rules. For example, consider the CL_STS_ACTIVITY code list and the NACE Rev 1.1 classification. The rule is: remove all dots from the NACE code and add as many zeros as necessary in order to reach four digits. Then add the prefix N1, or NS in case of special codes.
142 142  
143 -After applying the above steps, the result of the mapping process in ConIstat can be set out as in Table 5.3.1, in which columns represent both [[DSD>>doc:sdmx:Glossary.Data structure definition.WebHome]] (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) and local (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%), while rows represent a combination of their [[codes>>doc:sdmx:Glossary.Code.WebHome]]. The scheme shown here reflects the way in which the mapping tables are set up at ISTAT, which was chosen for performance reasons; the mapping table could be organised in other ways.
141 +After applying the above steps, the result of the mapping process in ConIstat can be set out as in Table 5.3.1, in which columns represent both DSD concepts and local concepts, while rows represent a combination of their codes. The scheme shown here reflects the way in which the mapping tables are set up at ISTAT, which was chosen for performance reasons; the mapping table could be organised in other ways.
144 144  
145 145  **Table 5.3.1: Mapping result example**
146 146  
... ... @@ -151,43 +151,44 @@
151 151  
152 152  For example:
153 153  
154 -* the (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]](%%) named [[CATEGORY>>doc:sdmx:Glossary.Category.WebHome]] that assumes the [[code>>doc:sdmx:Glossary.Code.WebHome]] 18 (Index of total orders), from the related local [[code list>>doc:sdmx:Glossary.Code list.WebHome]], is mapped with the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named STS_INDICATOR that in the STS [[code list>>doc:sdmx:Glossary.Code list.WebHome]] is represented by the [[code>>doc:sdmx:Glossary.Code.WebHome]] ORDT;
155 -* the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named TYPE that assumes the [[code>>doc:sdmx:Glossary.Code.WebHome]] G (neither seasonally or working day adjusted), from the related local [[code list>>doc:sdmx:Glossary.Code list.WebHome]], is mapped with the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named [[ADJUSTMENT>>doc:sdmx:Glossary.Adjustment.WebHome]] that in the STS [[code list>>doc:sdmx:Glossary.Code list.WebHome]] is represented by the [[code>>doc:sdmx:Glossary.Code.WebHome]] N;
156 -* the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named FREQ that assumes the [[code>>doc:sdmx:Glossary.Code.WebHome]] 12 (Monthly), from the related local [[code list>>doc:sdmx:Glossary.Code list.WebHome]], is mapped with the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named FREQUENCY that in the STS [[code list>>doc:sdmx:Glossary.Code list.WebHome]] is represented by the [[code>>doc:sdmx:Glossary.Code.WebHome]] M;
157 -* the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named UM that assumes the [[code>>doc:sdmx:Glossary.Code.WebHome]] PE (index base=2000), from the related local [[code list>>doc:sdmx:Glossary.Code list.WebHome]], is mapped with the two [[concepts>>doc:sdmx:Glossary.Concept.WebHome]]: UNIT that in the [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] [[code list>>doc:sdmx:Glossary.Code list.WebHome]] is represented by the [[code>>doc:sdmx:Glossary.Code.WebHome]] PURE_NUMB and BASE_YEAR that in the STS [[code list>>doc:sdmx:Glossary.Code list.WebHome]] is represented by the [[code>>doc:sdmx:Glossary.Code.WebHome]] 2000;
158 -* the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] named CLASSIFICATION that assumes the [[code>>doc:sdmx:Glossary.Code.WebHome]] DL300 (Manufacture of office machinery and computers), from the related local [[code list>>doc:sdmx:Glossary.Code list.WebHome]], is mapped with the [[concept>>doc:sdmx:Glossary.Concept.WebHome]] STS_ACTIVITY that in the STS [[code list>>doc:sdmx:Glossary.Code list.WebHome]] is represented by the [[code>>doc:sdmx:Glossary.Code.WebHome]] N13000.
152 +* the concept named CATEGORY that assumes the code 18 (Index of total orders), from the related local code list, is mapped with the concept named STS_INDICATOR that in the STS code list is represented by the code ORDT;
153 +* the concept named TYPE that assumes the code G (neither seasonally or working day adjusted), from the related local code list, is mapped with the concept named ADJUSTMENT that in the STS code list is represented by the code N;
154 +* the concept named FREQ that assumes the code 12 (Monthly), from the related local code list, is mapped with the concept named FREQUENCY that in the STS code list is represented by the code M;
155 +* the concept named UM that assumes the code PE (index base=2000), from the related local code list, is mapped with the two concepts: UNIT that in the SDMX code list is represented by the code PURE_NUMB and BASE_YEAR that in the STS code list is represented by the code 2000;
156 +* the concept named CLASSIFICATION that assumes the code DL300 (Manufacture of office machinery and computers), from the related local code list, is mapped with the concept STS_ACTIVITY that in the STS code list is represented by the code N13000.
159 159  
160 160  == 5.4 Reading and Writing SDMX to and from a Database ==
161 161  
162 162  === 5.4.1 Mechanisms ===
163 163  
164 -Database applications may need to read or write different [[versions >>doc:Glossary.Version.WebHome]]of [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] data. This can, of course, be solved in many ways. Two basic ways are:
162 +Database applications may need to read or write different versions of SDMX data. This can, of course, be solved in many ways. Two basic ways are:
165 165  
166 -* the application reads or writes a specific format of data (which could be a specific format of [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]]) which is pre or post processed by a transformation tool that transforms the input/output to the desired format
164 +* the application reads or writes a specific format of data (which could be a specific format of SDMX) which is pre or post processed by a transformation tool that transforms the input/output to the desired format
167 167  * the desired output format is read or written directly to/from the application
168 168  
169 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_c7e88a04d7c85a06.jpg||data-xwiki-image-style-alignment="center" height="212" width="655"]]
167 +(% style="text-align: center;" %)
168 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_c7e88a04d7c85a06.jpg||height="212" width="655"]]
170 170  
171 171  (% style="text-align: center;" %)
172 172  **{{id name="image_9"/}}Figure 9: External Transformation of SDMX Formats**
173 173  
174 -If the application reads/writes a specific form of [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]], then there are transformation tools readily available that will convert the data to/from different [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] formats. Some may rely on reading the entire file into memory to undertake the transformation and so this may be a limiting factor on the practicality of this approach (certainly if performance is an issue). Whist a separate transformation process is a simple approach which is well understood (and as such is not discussed any more in this Chapter), it does mean reading or writing the data twice.
173 +If the application reads/writes a specific form of SDMX, then there are transformation tools readily available that will convert the data to/from different SDMX formats. Some may rely on reading the entire file into memory to undertake the transformation and so this may be a limiting factor on the practicality of this approach (certainly if performance is an issue). Whist a separate transformation process is a simple approach which is well understood (and as such is not discussed any more in this Chapter), it does mean reading or writing the data twice.
175 175  
176 176  The mechanism discussed here is concerned with what may seem to be a more complex approach, but it has two advantages over the separate transformation approach:
177 177  
178 -1. The database application need have no knowledge of the [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] [[data set>>doc:sdmx:Glossary.Data set.WebHome]] syntax.
179 -1. The [[data set>>doc:sdmx:Glossary.Data set.WebHome]] is read or written only once, and can be streamed directly to/from the database application and the [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] read/write application which means there is no size limiting factor.
177 +1. The database application need have no knowledge of the SDMX data set syntax.
178 +1. The data set is read or written only once, and can be streamed directly to/from the database application and the SDMX read/write application which means there is no size limiting factor.
180 180  
181 181  === 5.4.2 SDMX Information Model ===
182 182  
183 -The [[SDMX Information Model>>doc:sdmx:Glossary.SDMX Information Model.WebHome]] for data recognizes the following fundamental structures for data:
182 +The SDMX Information Model for data recognizes the following fundamental structures for data:
184 184  
185 -* [[Group Key>>doc:sdmx:Glossary.Group key.WebHome]] - comprising Dimensions
184 +* Group Key - comprising Dimensions
186 186  * Series Key - comprising Dimensions
187 187  * Observation – possibly including time
188 -* [[Attribute>>doc:sdmx:Glossary.Attribute.WebHome]]
187 +* Attribute
189 189  
190 -It is practical to read or write an [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] data file without the need for the database read/write application to know anything about [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]]. This can be done in two main ways:
189 +It is practical to read or write an SDMX data file without the need for the database read/write application to know anything about SDMX. This can be done in two main ways:
191 191  
192 192  * By means of a data reader and data writer software component
193 193  * By means of a data and structure mapping tool
... ... @@ -196,7 +196,8 @@
196 196  
197 197  === 5.5.1 Schematic ===
198 198  
199 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_b8f2df37169beb6a.png||data-xwiki-image-style-alignment="center" height="297" width="657"]]
198 +(% style="text-align: center;" %)
199 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_b8f2df37169beb6a.png||height="297" width="657"]]
200 200  
201 201  (% style="text-align: center;" %)
202 202  **{{id name="image_10"/}}Figure 10: Reading and Writing SDMX Data**
... ... @@ -203,33 +203,39 @@
203 203  
204 204  === 5.5.2 SDMX Data Writer Interface ===
205 205  
206 -An example of interfaces that will enable an application to read and write any type of [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] data file is shown in Annex 3.
206 +An example of interfaces that will enable an application to read and write any type of SDMX data file is shown in Annex 3.
207 207  
208 208  === 5.5.3 Data Mapping Tool ===
209 209  
210 +<it is suggested that Eurostat add some content here>
210 210  
211 211  == 5.6 Data Base Table Structure ==
212 212  
213 -The [[Data Structure Definition>>doc:sdmx:Glossary.Data structure definition.WebHome]] can be used to create a relational table structure in a database. The simplest type of structure is shown below:
214 +The Data Structure Definition can be used to create a relational table structure in a database. The simplest type of structure is shown below:
214 214  
215 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_f884c2191e589a1f.jpg||data-xwiki-image-style-alignment="center" height="179" width="575"]]
216 +(% style="text-align: center;" %)
217 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_f884c2191e589a1f.jpg||height="179" width="575"]]
216 216  
217 217  (% style="text-align: center;" %)
218 218  **{{id name="image_11"/}}Figure 11: Schematic of a Database Schema Derived from a DSD**
219 219  
220 -With this structure it is easy to implement the [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] REST web services for data. Note that this type of structure does not store any of the [[structural metadata>>doc:sdmx:Glossary.Structural metadata.WebHome]] and this must be made available from another web service, such as an [[SDMX Registry>>doc:sdmx:Glossary.SDMX Registry.WebHome]], or from additional ([[structural metadata>>doc:sdmx:Glossary.Structural metadata.WebHome]]) structures in the database
222 +With this structure it is easy to implement the SDMX REST web services for data. Note that this type of structure does not store any of the structural metadata and this must be made available from another web service, such as an SDMX Registry, or from additional (structural metadata) structures in the database
221 221  
222 -The [[structural metadata>>doc:sdmx:Glossary.Structural metadata.WebHome]] from the ECB-EXR1 [[Data Structure Definition>>doc:sdmx:Glossary.Data structure definition.WebHome]] that is relevant to the database is shown below.
224 +The structural metadata from the ECB-EXR1 Data Structure Definition that is relevant to the database is shown below.
223 223  
224 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_44d1683c443fea76.png||data-xwiki-image-style-alignment="center" height="176" width="357"]]
226 +(% style="text-align: center;" %)
227 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_44d1683c443fea76.png||height="176" width="357"]]
225 225  
226 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_82cc6a2aeb7ef07a.png||data-xwiki-image-style-alignment="center" height="526" width="665"]]
229 +(% style="text-align: center;" %)
230 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_82cc6a2aeb7ef07a.png||height="526" width="665"]]
227 227  
228 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_8b3e6351a1864a96.png||data-xwiki-image-style-alignment="center" height="390" width="369"]]
232 +(% style="text-align: center;" %)
233 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_8b3e6351a1864a96.png||height="390" width="369"]]
229 229  
230 -An example set of database tables from the ECB-EXR1 [[Data Structure Definition>>doc:sdmx:Glossary.Data structure definition.WebHome]] is shown below.
235 +An example set of database tables from the ECB-EXR1 Data Structure Definition is shown below.
231 231  
232 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_bb92f342ba05b747.jpg||data-xwiki-image-style-alignment="center" height="251" width="576"]]
237 +(% style="text-align: center;" %)
238 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_bb92f342ba05b747.jpg||height="251" width="576"]]
233 233  
234 234  (% style="text-align: center;" %)
235 235  **{{id name="image_12"/}}Figure 12: Schematic of a Database Schema Derived from the ECB_EXR1 DSD**
... ... @@ -236,14 +236,14 @@
236 236  
237 237  == 5.7 Processing Queries ==
238 238  
239 -The [[SDMX Information Model>>doc:sdmx:Glossary.SDMX Information Model.WebHome]] for data recognizes the following constructs that are relevant to a database system for reading or writing [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]], and for processing [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] REST data queries:
245 +The SDMX Information Model for data recognizes the following constructs that are relevant to a database system for reading or writing SDMX, and for processing SDMX REST data queries:
240 240  
241 241  __For structure__
242 242  
243 -* [[Dimension>>doc:sdmx:Glossary.Dimension.WebHome]]
244 -* Time [[Dimension>>doc:sdmx:Glossary.Dimension.WebHome]]
249 +* Dimension
250 +* Time Dimension
245 245  * Observation
246 -* Data [[Attribute>>doc:sdmx:Glossary.Attribute.WebHome]]
252 +* Data Attribute
247 247  * Group
248 248  
249 249  __For data__
... ... @@ -251,18 +251,19 @@
251 251  * Series Key
252 252  * Observation
253 253  
254 -A database can be made “[[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] Web Services enabled” in a similar way to the Data Reader and Data Writer described in Annex 4 – Data Reader and Data Writer Functions. This is shown schematically in the diagram below.
260 +A database can be made “SDMX Web Services enabled” in a similar way to the Data Reader and Data Writer described in Annex 4 – Data Reader and Data Writer Functions. This is shown schematically in the diagram below.
255 255  
256 -[[image:SDMX_2-1_User_Guide_draft_0-1_html_3c55cbaa13d12a4b.png||data-xwiki-image-style-alignment="center" height="386" width="681"]]
262 +(% style="text-align: center;" %)
263 +[[image:SDMX_2-1_User_Guide_draft_0-1_html_3c55cbaa13d12a4b.png||height="386" width="681"]]
257 257  
258 258  (% style="text-align: center;" %)
259 259  **{{id name="image_13"/}}Figure 13: SDMX Query Reader**
260 260  
261 -An example interface for the Query Reader API is shown in Annex 3. As for the Data Reader and Data Writer it is the interface that is the important asset here, and this is structured using the constructs of the [[SDMX Information Model>>doc:sdmx:Glossary.SDMX Information Model.WebHome]], which are implemented in some way by any of the actual query formats (see Annex 3), the database application need not be concerned with this.
268 +An example interface for the Query Reader API is shown in Annex 3. As for the Data Reader and Data Writer it is the interface that is the important asset here, and this is structured using the constructs of the SDMX Information Model, which are implemented in some way by any of the actual query formats (see Annex 3), the database application need not be concerned with this.
262 262  
263 -It will be seen from Chapter 6, that this interface presents the content of the [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] REST data query in a way that is easy for the database application to process without the need to know the syntax of the REST query (or any of the other possible query formats).
270 +It will be seen from Chapter 6, that this interface presents the content of the SDMX REST data query in a way that is easy for the database application to process without the need to know the syntax of the REST query (or any of the other possible query formats).
264 264  
265 -The database result set is output to [[SDMX>>doc:sdmx:Glossary.Statistical data and metadata exchange.WebHome]] using the relevant implementation of the Data Writer Interface. This actual implementation that the database application uses will depend upon the query response format requested by the user. However, again this technicality is hidden from the database application and it is concerned solely with the methods of the Data Writer interface.
272 +The database result set is output to SDMX using the relevant implementation of the Data Writer Interface. This actual implementation that the database application uses will depend upon the query response format requested by the user. However, again this technicality is hidden from the database application and it is concerned solely with the methods of the Data Writer interface.
266 266  
267 267  ----
268 268