Skip to Content

Wiki source code of 5 Data Bases and SDMX

Version 6.1 by Artur on 2025/07/14 10:19
Show last authors
1 {{box title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 == 5.1 Scope of this Chapter ==
6
7 [[image:SDMX_2-1_User_Guide_draft_0-1_html_560921d6b5e976e3.png||data-xwiki-image-style-alignment="center" height="442" width="699"]]
8
9 == 5.2 Introduction ==
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
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
15 == 5.3 Database and DSD Mapping ==
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
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]].
21
22 In order to better explain this process the following real life example will be used.
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}}.
25
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
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 (% style="text-align: center;" %)
31 **{{id name="image_7"/}}**
32
33 (% style="text-align: center;" %)
34 **Figure 7: Database schema for ConIstat**
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.
37
38 The main (% style="color:#e74c3c" %)[[concepts>>doc:sdmx:Glossary.Concept.WebHome]](%%) used in order to describe each time-series are the following:
39
40 **Category**: short term statistical indicator
41
42 **Type**: [[adjustment>>doc:sdmx:Glossary.Adjustment.WebHome]] indicator
43
44 **Vs**: stock/flow
45
46 **Classification**: NACE, SEC95, other classifications
47
48 **Freq**: frequency
49
50 **Um**: [[Unit of measure>>doc:sdmx:Glossary.Unit of measure.WebHome]] + Unit multiply + Base year
51
52 **Start_Period**: start period of the time-series
53
54 **End_Period**: last available period of the time-series
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:
57
58 **Category**: 11 (index of industrial production)
59
60 **Type**: g (neither seasonally or working day adjusted)
61
62 **Vs**: R (flow)
63
64 **Classification**: C (Mining and quarrying)
65
66 **Freq**: 12 (monthly)
67
68 **Um**: PE (index number - base 2000)
69
70 (% class="wikigeneratedid" id="HStart_Period:01_1990End_Period:08_2008" %)
71 **Start_Period: 01_1990 End_Period: 08_2008**
72
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
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)
77
78
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 **{{id name="image_5.3.8"/}}Figure 5.3.8: Database schema with additional tables for mapping**
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}}.
87
88 ==== 5.3.1.1 Mapping of concepts ====
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:
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).
96
97 ==== 5.3.1.2 Mapping of codes ====
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]]:
100
101 * [[code list>>doc:sdmx:Glossary.Code list.WebHome]] associated with the frequency local (% style="color:#e74c3c" %)[[concept>>doc:sdmx:Glossary.Concept.WebHome]]
102
103 (((
104 (% style="width:487.446px" %)
105 |(% style="width:152px" %)**CODE**|(% style="width:331px" %)**DESCRIPTION**
106 |(% style="width:152px" %)1|(% style="width:331px" %)Annual
107 |(% style="width:152px" %)12|(% style="width:331px" %)Monthly
108 |(% style="width:152px" %)365|(% style="width:331px" %)Daily
109 |(% style="width:152px" %)4|(% style="width:331px" %)Quarterly
110 |(% style="width:152px" %)52|(% style="width:331px" %)Weekly
111 )))
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]]
114
115 (((
116 (% style="width:528.446px" %)
117 |(% style="width:193px" %)**CODE**|(% style="width:331px" %)**DESCRIPTION**
118 |(% style="width:193px" %)A|(% style="width:331px" %)Annual
119 |(% style="width:193px" %)M|(% style="width:331px" %)Monthly
120 |(% style="width:193px" %)D|(% style="width:331px" %)Daily
121 |(% style="width:193px" %)Q|(% style="width:331px" %)Quarterly
122 |(% style="width:193px" %)W|(% style="width:331px" %)Weekly
123 |(% style="width:193px" %)H|(% style="width:331px" %)Half-yearly
124 |(% style="width:193px" %)B|(% style="width:331px" %)Business
125 )))
126
127 The mapping process will produce the following result:
128
129 (((
130 (% style="width:536.446px" %)
131 |(% style="width:162px" %)**DSD CODE**|(% style="width:195px" %)**Local CODE**|(% style="width:175px" %)**DESCRIPTION**
132 |(% style="width:162px" %)A|(% style="width:195px" %)1|(% style="width:175px" %)Annual
133 |(% style="width:162px" %)M|(% style="width:195px" %)12|(% style="width:175px" %)Monthly
134 |(% style="width:162px" %)D|(% style="width:195px" %)365|(% style="width:175px" %)Daily
135 |(% style="width:162px" %)Q|(% style="width:195px" %)4|(% style="width:175px" %)Quarterly
136 |(% style="width:162px" %)W|(% style="width:195px" %)52|(% style="width:175px" %)Weekly
137 |(% style="width:162px" %)H|(% style="width:195px" %) |(% style="width:175px" %)Half-yearly
138 |(% style="width:162px" %)B|(% style="width:195px" %) |(% style="width:175px" %)Business
139 )))
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]].
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.
144
145 **Table 5.3.1: Mapping result example**
146
147 |**CATE GORY**|**TYPE**|**CLASSI FICATION**|**FREQ**|**UM**|**DATAFLOW**|**STS_INDICATOR**|**STS_ACTIVITY**|**UNIT**|**BASE_ YEAR**|**ADJU STMENT**|**FREQUE NCY**
148 |18|G|DL300|12|PE|SSTSIND_ORD_M|ORDT|N13000|PURE_N UMB|2000|N|M
149 |18|G|DL31|12|PE|SSTSIND_ORD_M|ORDT|N13100|PURE_N UMB|2000|N|M
150 |18|G|DL311|12|PE|SSTSIND_ORD_M|ORDT|N13110|PURE_N UMB|2000|N|M
151
152 For example:
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.
159
160 == 5.4 Reading and Writing SDMX to and from a Database ==
161
162 === 5.4.1 Mechanisms ===
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:
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
167 * the desired output format is read or written directly to/from the application
168
169 [[image:SDMX_2-1_User_Guide_draft_0-1_html_c7e88a04d7c85a06.jpg||data-xwiki-image-style-alignment="center" height="212" width="655"]]
170
171 (% style="text-align: center;" %)
172 **{{id name="image_9"/}}Figure 9: External Transformation of SDMX Formats**
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.
175
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
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.
180
181 === 5.4.2 SDMX Information Model ===
182
183 The [[SDMX Information Model>>doc:sdmx:Glossary.SDMX Information Model.WebHome]] for data recognizes the following fundamental structures for data:
184
185 * [[Group Key>>doc:sdmx:Glossary.Group key.WebHome]] - comprising Dimensions
186 * Series Key - comprising Dimensions
187 * Observation – possibly including time
188 * [[Attribute>>doc:sdmx:Glossary.Attribute.WebHome]]
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:
191
192 * By means of a data reader and data writer software component
193 * By means of a data and structure mapping tool
194
195 == 5.5 Data Reader and Data Writer ==
196
197 === 5.5.1 Schematic ===
198
199 [[image:SDMX_2-1_User_Guide_draft_0-1_html_b8f2df37169beb6a.png||data-xwiki-image-style-alignment="center" height="297" width="657"]]
200
201 (% style="text-align: center;" %)
202 **{{id name="image_10"/}}Figure 10: Reading and Writing SDMX Data**
203
204 === 5.5.2 SDMX Data Writer Interface ===
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.
207
208 === 5.5.3 Data Mapping Tool ===
209
210
211 == 5.6 Data Base Table Structure ==
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
215 [[image:SDMX_2-1_User_Guide_draft_0-1_html_f884c2191e589a1f.jpg||data-xwiki-image-style-alignment="center" height="179" width="575"]]
216
217 (% style="text-align: center;" %)
218 **{{id name="image_11"/}}Figure 11: Schematic of a Database Schema Derived from a DSD**
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
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.
223
224 [[image:SDMX_2-1_User_Guide_draft_0-1_html_44d1683c443fea76.png||data-xwiki-image-style-alignment="center" height="176" width="357"]]
225
226 [[image:SDMX_2-1_User_Guide_draft_0-1_html_82cc6a2aeb7ef07a.png||data-xwiki-image-style-alignment="center" height="526" width="665"]]
227
228 [[image:SDMX_2-1_User_Guide_draft_0-1_html_8b3e6351a1864a96.png||data-xwiki-image-style-alignment="center" height="390" width="369"]]
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.
231
232 [[image:SDMX_2-1_User_Guide_draft_0-1_html_bb92f342ba05b747.jpg||data-xwiki-image-style-alignment="center" height="251" width="576"]]
233
234 (% style="text-align: center;" %)
235 **{{id name="image_12"/}}Figure 12: Schematic of a Database Schema Derived from the ECB_EXR1 DSD**
236
237 == 5.7 Processing Queries ==
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:
240
241 __For structure__
242
243 * [[Dimension>>doc:sdmx:Glossary.Dimension.WebHome]]
244 * Time [[Dimension>>doc:sdmx:Glossary.Dimension.WebHome]]
245 * Observation
246 * Data [[Attribute>>doc:sdmx:Glossary.Attribute.WebHome]]
247 * Group
248
249 __For data__
250
251 * Series Key
252 * Observation
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.
255
256 [[image:SDMX_2-1_User_Guide_draft_0-1_html_3c55cbaa13d12a4b.png||data-xwiki-image-style-alignment="center" height="386" width="681"]]
257
258 (% style="text-align: center;" %)
259 **{{id name="image_13"/}}Figure 13: SDMX Query Reader**
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.
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).
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.
266
267 ----
268
269 {{putFootnotes/}}