ECB SDMX tutorial: using SDMX-ML to publish the euro foreign exchange reference rates on the ECB website

Objectives and prerequisites

The aim of this tutorial is to introduce you to SDMX-ML using a practical example – the publication of the euro foreign exchange reference rates

The tutorial will look at the technology from both sides of the fence: the side of the data provider (“How can we use SDMX-ML to publish statistical data on our website?”); and the side of the data consumer (“What kind of useful things can we do with an SDMX-ML data file?”). However, first we will present, in a nutshell, the SDMX information model and some of the SDMX-ML formats.

To make the most of this tutorial, a basic knowledge of XML and XML-related technologies, such as XML schemas, XSLT and SAX, is required. Some of the tasks described in the tutorial will also require the use of an XML-validating parser, for example Apache Xerces or xmllint from libxml2, and an XSLT processor, such as Apache Xalan, Saxon or xsltproc from libxml2.

For those who use Ant as their build system, most of the exercises can be run using the supplied build file.

SDMX overview

The Statistical Data and Metadata Exchange initiative is sponsored by seven institutions (the BIS, the ECB, Eurostat, the IMF, the OECD, the UN and the World Bank) to foster standards for the exchange of statistical information. The first version of the standard is an ISO standard (ISO/Technical Specification 17369:2005). It offers an information model for the representation of statistical data and metadata, as well as several formats to represent this model (SDMX-EDI and several SDMX-ML formats). It also proposes a standard way of implementing web services, including the use of registries.

The SDMX information model in a nutshell

The list below tells you everything you need to know about the SDMX information model ^[1] ] in order for us to start developing an application based on the SDMX standard:

1. Descriptor concepts: In order to make sense of some statistical data, we need to know the concepts associated with them. For example, on its own the figure 1.2953 is pretty meaningless, but if we know that this is an exchange rate for the US dollar against the euro on 23 November 2006, it starts to make more sense.

2. Packaging structure: Statistical data can be grouped together at the following levels: the observation level (the measurement of some phenomenon); the series level (the measurement of some phenomenon over time, usually at regular intervals); the group level (a group of series – a well-known example being the sibling group, a set of series which are identical, except for the fact that they are measured with different frequencies); and the dataset level (made up of several groups, to cover a specific statistical domain for instance). The descriptor concepts mentioned in point 1 can be attached at various levels in this hierarchy.

3. Dimensions and attributes: There are two types of descriptor concept: dimensions, which both identify and describe the data, and attributes, which are purely descriptive.

4. Keys: Dimensions are grouped into keys, which allow the identification of a particular set of data (a series, for example). The key values are attached at the series level and given in a fixed sequence. Conventionally, frequency is the first descriptor concept and the other concepts are assigned an order for that particular dataset. Partial keys can be attached to groups.

5. Code lists: Every possible value for a dimension is defined in a code list. Each value on that list is given a language-independent abbreviation (code) and a language-specific description. Attributes are represented sometimes by codes, and sometimes by free-text values. Since the purpose of an attribute is solely to describe and not to identify the data, this is not a problem.

6. Data Structure Definitions: A Data Structure Definition (key family) specifies a set of concepts, which describe and identify a set of data. It tells us which concepts are dimensions (identification and description) and which are attributes (just description), and it gives the attachment level for each of these concepts on the basis of the packaging structure (dataset, group, series or observation), as well as their status (mandatory or conditional). It also specifies which code lists provide possible values for the dimensions and gives possible values for the attributes, either as code lists or free text fields.

The various SDMX-ML formats

SDMX-ML supports various use cases and, therefore, defines several XML formats. ^[2] . For the purpose of this tutorial, the following two formats will be used:

1. The Structure Definition format : This format will be used to define the structure (concepts, code lists, dimensions, attributes, etc.) of the key families.

2. The Compact format: This format will be used to define the data file. It is not a generic format (it is specific to a Data Structure Definition), but it is designed to support validation and is much more compact so as to support the exchange of large datasets.

Now that we know the basics, we can start developing our application.

Data provider

Publishing the euro foreign exchange reference rates in SDMX-ML

We want to publish the euro foreign exchange reference rate data on our website. The first step is to analyse the kind of data we are dealing with; then we have to create the Structure Definition file to represent these data. We will then generate a schema out of the Structure Definition file, which we will use to validate the data file. Finally, we will create the XML data file to be published on the website.

The Data Structure Definition

For the purpose of this exercise, we will use the Data Structure Definition defined in the table below. ^[3] .

Creation of the SDMX-ML Structure Definition file

Now that we have a good overview of the structure of the data that we want to publish on the website, we can formally define this structure using SDMX-ML. The Structure Definition format is used for this kind of task, as it contains the description of the structural metadata, such as key families, concepts and code lists.

The header

Not including the initial XML declaration, the XML file ( ecb_exr1_structure.xml ) starts with the Structure element and the standard SDMX Header .

	
	<?xml version="1.0" encoding="UTF-8"?>
	<Structure
	xmlns="http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message"
	xmlns:message=
		"http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
		http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message 
		SDMXMessage.xsd 
		http://www.SDMX.org/resources/SDMXML/schemas/v2_0/structure 
		SDMXStructure.xsd">
	<Header>
	<ID>IREF000506</ID>
	<Test>false</Test>
	<Name>ECB structural definitions</Name>
	<Prepared>2006-10-25T14:26:00</Prepared>
	<Sender id="4F0"/>
	</Header>
	
	

The root element is defined ( Structure ), with the namespaces and schema definitions attached as attributes ( SDMXMessage.xsd and SDMXStructure.xsd ). The SDMX Message namespace is used by all other SDMX-ML namespace modules. It contains the common message constructs, including the common Header information (Message ID , Name , Prepared date, etc.). The id attribute of the Sender element specifies the sender of the data, which is the European Central Bank in this case (code 4F0 within the context of this data sender, taken from the code list CL_ORGANISATION).

Apart from the Header element, the XML file also contains the following three main elements, all belonging to the SDMX Structure namespace: CodeLists, Concepts and KeyFamilies .

The concepts

The Concepts element contains a list of concepts used to identify and describe the data. All the concepts used in the Data Structure Definition are included in this list.

Each Concept element contains two attributes: the ID of the agency responsible for the concept (“ECB”) and the concept id (for example, “UNIT_MULT”). Both are identifiers, and, as such, language independent. The Name element contains a language-dependent description of the concept (as specified in the xml:lang attribute).

	
	<Concept agencyID="ECB" id="COLLECTION">
	<Name xml:lang="en">Collection indicator</Name>
	</Concept>
	
	

The code lists

The CodeLists element contains a list of CodeLists elements, each with two attributes: the ID of the agency responsible for the code list (“ECB”) and the code list id (for example, “CL_EXR_SUFFIX”). The Name element contains a description of the code list in a specific language. Each code list also contains a list of codes, with an attribute for the code value and a language-dependent description of the code. The code lists define the possible values taken by the dimensions and the coded attributes.

	
	<CodeList agencyID="ECB" id="CL_EXR_SUFFIX">
	<Name xml:lang="en">Exch. rate series variation code list</Name>
	<Code value="A">
		<Description xml:lang="en">Average or standardised
		measure for given frequency</Description>
	</Code>
	<Code value="E">
		<Description xml:lang="en">End-of-period</Description>
	</Code>
	</CodeList>
	
	

The key families

Now that we have our list of concepts and code lists, we can start compiling our Data Structure Definition.

The KeyFamily element contains the ID of the agency responsible for defining the Data Structure Definition (“ECB”), the id for the Data Structure Definition (“ECB_EXR1”), a uri (we use the Data Structure Definition namespace for this) and the name of the Data Structure Definition in a specific language.

	
	<KeyFamily agencyID="ECB" id="ECB_EXR1" 
	uri="http://www.ecb.int/vocabulary/stats/exr/1">
	<Name xml:lang="en">Exchange Rates</Name>
	<Components>
	
	

Then the components of the Data Structure Definition are defined, starting with the dimensions.

	
	<Dimension conceptRef="FREQ" codelist="CL_FREQ" isFrequencyDimension="true"/>
	<Dimension conceptRef="CURRENCY" codelist="CL_CURRENCY"/>
	<Dimension conceptRef="CURRENCY_DENOM" codelist="CL_CURRENCY"/>
	<Dimension conceptRef="EXR_TYPE" codelist="CL_EXR_TYPE"/>
	<Dimension conceptRef="EXR_SUFFIX" codelist="CL_EXR_SUFFIX"/>
	<TimeDimension conceptRef="TIME_PERIOD"/>
	
	

Each Dimension element contains references to a descriptor concept and the code list from which the dimension value has to be taken. For example, the dimension which represents the concept of frequency takes its values from the CL_FREQ code list and, as such, can only take one of the following values: A (annual), B (business), D (daily), E (event), H (half-yearly), M (monthly), Q (quarterly) and W (weekly). The isFrequencyDimension is attached to the dimension which represents the frequency ( FREQ in this case). There can be only one such dimension per Data Structure Definition.

The TimeDimension is a special dimension that must be included in any Data Structure Definition which will be used for time series formats, such as GenericData, CompactData and UtilityData.

The order of declaration of the dimensions is important as it describes the order in which the dimensions will appear in the the keys (with the exception of the time dimension, which is not part of the key).

The Group element declares any useful groupings of data, such as sibling groups.

	
	<Group id="Group">
	<DimensionRef>CURRENCY</DimensionRef>
	<DimensionRef>CURRENCY_DENOM</DimensionRef>
	<DimensionRef>EXR_TYPE</DimensionRef>
	<DimensionRef>EXR_SUFFIX</DimensionRef>
	</Group>
	
	

Next we indicate which attribute will contain the measured value. Conventionally, it is associated with the OBS_VALUE concept.

	<PrimaryMeasure conceptRef="OBS_VALUE"/>
	

Finally, we list the attributes. An Attribute element will contain such information as the concept used for the attribute, the attachment level, i.e. observation, group, series or dataset, and whether it is mandatory or conditional. Coded attributes will indicate from which code list the values should be taken, while, for uncoded attributes, a specific format may be specified using the TextFormat element. For attributes attached to the Group level, we specify the ID of the group to which the attributes are attached with an AttachmentGroup element. The concept of time format is identified by the isTimeFormat attribute having a value of true and is typically a mandatory series level attribute the value of which is taken from ISO 8601.

	
	<Attribute conceptRef="TIME_FORMAT" attachmentLevel="Series" 
	assignmentStatus="Mandatory" isTimeFormat="true">
	<TextFormat textType="String" maxLength="3"/>
	</Attribute>
	<Attribute conceptRef="OBS_STATUS" attachmentLevel="Observation" 
	codelist="CL_OBS_STATUS" assignmentStatus="Mandatory"/>
	<Attribute conceptRef="DECIMALS" attachmentLevel="Group"
	codelist="CL_DECIMALS" assignmentStatus="Mandatory">
	<AttachmentGroup>Group</AttachmentGroup>
	</Attribute>
	
	

As a last step, we use an XML validating parser to validate our Structure Definition file and make sure that it is compliant with the SDMX-ML standard ^[4] . You can use the task validateStructure supplied in the Ant build file to perform this step.

	bash$
	ant validateStructure
	Buildfile: build.xml

	validateStructure:
	[xmlvalidate] 1 file(s) have been successfully validated.

	BUILD SUCCESSFUL
	Total time: 2 seconds
	

Creation of the schema file

Now that we have a valid Structure Definition file, we can generate an XML schema file for our Data Structure Definition. The SDMX initiative offers some free tools to developers^[5] one of which is an XSL file ( StructureToCompact.xsl ) that creates an XML schema out of a Structure Definition file for a selected Data Structure Definition.^[6]

To create our schema, we need to:

1. use an XSL parser using our Structure Definition file ( ecb_exr1_structure.xml ) as an XML input file, the XSL file ( StructureToCompact.xsl ), and the desired filename for our XML schema file (e.g.: ecb_exr1_compact.xsd ) as parameters.

2. open the file in an editor and add the default namespace in the xs:schema element ( xmlns="http://www.ecb.int/vocabulary/stats/exr/1" ) ^[7] .

You can use the task generateCompactSchema supplied in the Ant build file to perform this step.

	bash$ ant generateCompactSchema
	Buildfile: build.xml

	generateCompactSchema:
	 [xslt] Processing ecb_exr1_structure.xml to ecb_exr1_compact.xsd
	 [xslt] Loading stylesheet StructureToCompact.xsl

	BUILD SUCCESSFUL
	Total time: 2 seconds
	

We now have an XML schema file ( ecb_exr1_compact.xsd ), which we will use to validate the XML data file before publishing it on our website.

Creation of the SDMX-ML data file for the time-series view

It is now time to create our XML data file ( ecb_exr1_compact.xml ). This is normally done by extracting the data from a database and creating the XML data file.

The header

When we open the file, we should recognise some similarities with the Structure Definition file, as all SDMX messages share some common constructs, i.e.: the elements from the SDMXMessage namespace, such as the Header .

	
	
	<CompactData	
	xmlns="http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation=
	"http://www.SDMX.org/resources/SDMXML/schemas/v2_0/message 
	SDMXMessage.xsd">
	<Header>
	<ID>EXR-HIST_2006-11-29</ID>
	<Test>false</Test>
	<Name xml:lang="en">Euro foreign exchange reference rates</Name>
	<Prepared>2006-11-23T08:26:29</Prepared>
	<Sender id="4F0">
		<Name xml:lang="en">European Central Bank</Name>
		<Contact>
			<Department xml:lang="en">DG Statistics</Department>
			<URI>mailto:[email protected]</URI>
		</Contact>
	</Sender>
	</Header>
	
	

The dataset element

The message continues with the DataSet element, the highest possible level of grouping.^[8] A namespace, an XML schema and the dataset ID are added to the element.

	
	
	<DataSet
	xmlns="http://www.ecb.int/vocabulary/stats/exr/1"
	xsi:schemaLocation="http://www.ecb.int/vocabulary/stats/exr/1 
	ecb_exr1_compact.xsd" 
	datasetID="ECB_EXR1">
	
	

The group element

The next level of grouping is the Group element. As it is a sibling group, it contains all dimensions (CURRENCY, CURRENCY_DENOM, EXR_TYPE and EXR_SUFFIX), except the frequency (FREQ), which is wild-carded, and the date/time information (TIME_PERIOD), which is attached to the obs level. In addition to the dimensions, it also includes the attributes which are attached to the Group level (DECIMALS, UNIT, UNIT_MULT and TITLE_COMPL).

	
	
	<Group CURRENCY="AUD" CURRENCY_DENOM="EUR" EXR_TYPE="SP00" 
	EXR_SUFFIX="A" DECIMALS="4" UNIT="AUD" UNIT_MULT="0" 
	TITLE_COMPL="ECB reference exchange rate, Australian dollar/Euro"/>
	
	

The series element

Lower down in the package grouping, we reach the Series level, which contains the same dimensions as the Group level, plus the frequency. It also includes the attributes which are attached to the Series level (TIME_FORMAT and COLLECTION).

	
	
	<Series FREQ="D" CURRENCY="AUD" CURRENCY_DENOM="EUR"
	EXR_TYPE="SP00" EXR_SUFFIX="A" TIME_FORMAT="P1D" COLLECTION="A">
	
	

The observation element

The Series element also contains the list of observations, at the lowest possible level in the package grouping. The observations contain the time dimension (TIME_PERIOD), the measured value (OBS_VALUE) and the attributes attached to the obs level (OBS_STATUS and OBS_CONF).

	
	<Obs TIME_PERIOD="1999-01-04" OBS_VALUE="1.9100" OBS_STATUS="A" OBS_CONF="F" />
	

These last three elements ( Group , Series and Obs ) will be repeated for all groups of data to be published.

Now that we have our data file ready, before publishing it on our website, we should validate it using an XML-validating parser. You can use the task validateData supplied in the Ant build file to perform this step.

	bash$ ant validateData
	Buildfile: build.xml

	validateData:
	[xmlvalidate] 1 file(s) have been successfully validated.

	BUILD SUCCESSFUL
	Total time: 6 seconds
	

Once this has been done, the job of the data publisher is complete and we have successfully managed to publish an SDMX-ML data file on our website. We can now use it to retrieve and display exchange rate data.

Data consumer

Retrieving and displaying exchange rates

Several XML technologies are available for processing an XML data file.^[9]

1. The XSL Transformation (XSLT) language:^[10] XSLT can be used to transform parts or all of the XML data file into other formats, such as (X)HTML, CSV, PDF, WML, etc. For instance, we could use this technology to create an (X)HTML table displaying the rates for all currencies at a specific time.

2. The Document Object Model (DOM):^[11] The DOM is an object-oriented model of an XML document, which represents it as a tree structure. You can use the DOM to read from and write to an XML data file. However, the DOM stores the entire document tree in its memory, which means that it is resource-intensive, especially for large XML data files. If all we need is a sequential read or a one-time selective read, the DOM might not be necessary.

3. The Simple API for XML (SAX):^[12] SAX uses an event-driven model and handles an XML file as a unidirectional stream of data. SAX parsing is usually faster and the memory footprint is much smaller than a DOM construct. SAX is limiting, however, since you cannot write to an XML data file, reading is unidirectional (so you cannot go back into the XML file if needed, and you have to start from the beginning again) and there is no representation of the structure of the XML data file.

For the purpose of this tutorial, we will use SAX to display the exchange rate for a certain currency on a certain date,^[13] and XSLT to extract some data from the SDMX ML data file and create an HTML table displaying the rates for all currencies at a specific time.^[14]

Parsing the SDMX-ML data file using SAX

Before using the SDMX-ML data file, we must first validate it. Then we can use a SAX parser to extract the exchange rate for a specific currency at a specific point in time.

A SAX parser is event-based. It will call up one method in the application when it encounters an element tag and another when it encounters some text, for example. Therefore, it is up to the developer to write the call-back methods. Java provides classes on and methods for working with SAX, but a tutorial on using SAX in Java is beyond the scope of this exercise.^[15]

Apart from the setup-code needed (see the main method in the SAXGetRate.java file), the startElement method shows us what we are looking for: when we find the series for the selected currency, we search for the observation that matches the given period and exit when the value has been found.

Just open a shell and try it out following this syntax:

	bash$ java -cp tutorial.jar SAXGetRate ecb_exr1_compact.xml currency period
	

So, for example

	bash$ java -cp tutorial.jar SAXGetRate ecb_exr1_compact.xml USD 2006-11-21
	Exchange rate value for USD on 2006-11-21: 1.2814
	

Using XSLT to create an (X)HTML table of the daily rates

We now want to generate an (X)HTML table containing the exchange rates for all the currencies at a specified time. We will also use the Structure Definition file to extract the names of the currencies so that we can display the full names, rather than just the currency codes.

Again, an introduction to XSLT is beyond the scope of this tutorial, but you will notice that the XSL script is fairly small. Apart from the output settings, we assign the value passed to the script for the desired period to a variable and we receive the list of currencies from the Structure Definition file ( ecb_exr1_structure.xml ). We then match the root of the SDMX-ML data file and output the basic HTML information (head, body, table, etc). Finally, we match all series; for each series we receive the observation value for the specified date and add it to the HTML table.

To generate the (X)HTML table, we use an XSLT processor, using the SDMX-ML data file ( ecb_exr1_compact.xml ) as the input file, sdmxml2html.xsl for the XSL file and the desired filename for the HTML table (e.g. ecb_exr1_table.html ) as parameters. You can use the task generateHTMLTable supplied in the Ant build file to perform this step.

	bash$ ant generateHTMLTable
	Buildfile: build.xml

	generateHTMLTable:
	 [xslt] Processing ecb_exr1_compact.xml to ecb_exr1_table.html
	 [xslt] Loading stylesheet sdmxml2html.xsl

	BUILD SUCCESSFUL
	Total time: 5 seconds
	

We have now finished creating the two utilities that the data consumer needs and we have enough knowledge to be able to build fully-fledged SDMX-based software.