Publication Table

Overview

A Publication Table provides the means to explicitly define the structure and content of a table of data. Publication Tables can make use of variable text placeholders in the table heading, and row and column headings, it can contain variable dimensions in the cells, and include calculated data and time arithmetic. Publication Tables can also display data from multiple SDMX Dataflows in the same table and can even perform calculations across Dataflows.

The following image shows a very simple Publication Table structure, where the table headings contain static text, and each cell describes an observation to show via the series key. Other parts of this table definition describe the table title, subtitle, and what the source Dataflow is.

Table Content

Overview

A Publication Table is made up of a Table Head and Body Rows. Each Body Row has first cell which is the Row Title. The Table Head is made up of 1 or more rows, and each head cell may span 1 or more columns, and 1 or more rows. The Table Head content is made up of free text and / or variable placeholders whose values are resolved when the table is built.

A Body Row is made up of 1 or more rows. The Row Title has a fixed row span and column span of 1. The content is made up of free text and / or variable placeholders whose values are resolved when the table is built. The remainder of the Body Row is made is up Observation cells. An Observation Cell can reference a unique Observation by an Observation Key, the value for this Observation is resolved at table build time. The Observation Key may contain variable placeholders and can include calculation expressions. An Observation Cell may also contain free text.

Observation Key

The puspose of the Observation Key is to reference a Observation whose value will be resolved at table build time. The Observation Key can explicitly resolve to 1 observation value, if all parts of the Key are provided. However, it is possible to make parts of the key varaibles, allowing the values to be set at table build time.

An Observation Key is made up of 4 parts:

• Dataflow Reference
• Series Reference
• Time Period Reference
• Measure Reference

An example Observation Key with all of these parameters in place is:

DF_REG.M:UK:SUR:2008:BIRTHS

The key is broken down into the following parts:

• DF_REG - This identifies the Dataflow which has the data. The identification uses the alias, which is assiged to the Dataflow in the Publication Table definition. The Dataflow Alias is only required if the Publication Table references more then one Dataflow. If the Publication Table references more then one Dataflow, it can assign a Dataflow to a Default Alias, which means that it is required as part of the Observation Key.

• M:UK:SUR - This identifies the Series of Data for the Dataflow, by providing a single value for each of the Dimension Members of the Dataflow, separated by a ‘:’ character. In this example the Dataflow has 3 Dimensions (excluding time), the value for the first is 'M', the second 'UK' and the last 'SUR'. As the Dataflow in this example has a Time Dimension this Series Key will uniquely identify a Series of Observations where each Observation refers to a unique Time Period.

• 2008 - This identifies the Time Period in the Series of Data identified by the previous section. The Combination of the Series and Time Period for a Time Series Dataflow will uniquely identify the Observation.

• BIRTHS - If the Dataflow uses a Data Structure with multiple Measures, then the Measure ID must be included as the last part of the Observation Key. In this example the Dataflow has multiple measures (BIRTHS/DEATHS/MARRIAGES) so the Observation Key needs to include this value.

If the Publication Table only uses data from one Dataflow, and if the Dataflow only has 1 Measure Dimension then the Observation Key would look more simple:

M:UK:SUR:2008

Variable Placeholders

It is possible to omit Dimension Values from the Observation Key, when this is the case that Dimension will become a variable – whose value can be set at table build time. For example, the second Dimension value of ‘UK’, from the previous example, can be omitted like this:

M::SUR:2008

The Publication Table can now be built for any Country by passing in the variable at table build time - i.e. UK, FR, DE. By passing in different variable values, the Observation Key will change, and as such it will reference a different Series in the Dataset.

It is also possible to omit the value for Time Period, this allows the Time Period to be set at table build time, defaulting to the last period for which data exists. Extending the above example to omit the Time value results in an Observation Key whose Country and Time values can set defined at table build time.

M::SUR

When an Observation Key creates a variable, it can be used in any text provided to the table. For example, if the ID of the 2nd Dimension is REF_AREA, the Table Column could be given a title of $(REF_AREA) which would resolve to 'United Kingdom', 'France', or 'Germany' at build time depending on which varaible value is used to build the table. Varaibles are covered later in this document.

Table Variables

Dataflow Variables

Publication Tables define data cells across one or more Dataflows. Each ‘imported’ Dataflow is given an alias, which is used to refer to the Dataflow in the table headings, and table body. It is possible to refer to the Dataflow name, description, and Id using the following variable syntax. The following examples are based on a Dataflow which has been given the alias of 'EXR'

Dataflow Description

$(EXR).desc 

Dataflow Name

$(EXR).name 

Dataflow ID

$(EXR).id

Dimension Variables

When an Observation Key has variable Dimension Values, the table will be built by replacing this variable with a value to complete the Observation Key. This value can be passed explicitly to the table (for example ‘build me a table for UK’) or if not defined, it will be defaulted. If the Dimension uses a Codelist or Valuelist, then the value of the variable referrers to the Code/Value Identifier. The name of the Code can be resolved at runtime by using the following syntax $([dimension id]), for example:

$(INDICATOR)  

This would resolve to the name of the Indicator value used in the table, for example Employment

To Output the name of the Concept i.e. the label 'Indicator', then the following syntax can be used $([component id].name), for example:

$(INDICATOR.name)  

Component Values

If a fixed Dimension value is used in the table, or if there is a Series/Observation Attribute whose value is required in the table, then these can be explicitly referenced using the following syntax $([component id].[component value]), for example:

$(OBS_STATUS.M)

This would resolve to the name of the OBS_STATUS Attribute, when the value is M, for example Missing

The Component Name can be referenced using the following syntax $([component id].name), for example:

$(OBS_STATUS.name)

Coded Variables

It is possible to import additional Codelists and/or Valuelists into the Publication Table, for the purpose of using the Names or enumerated items as variables. When a Codelist/Valuelist is imported, it is given an alias, and the combination of the Alias and Item ID resolves to the Name of the Item. The syntax is $([codelist alias].[item id]) for example:

$(CURRENCY.$)

Resolving to the text Dollar in English and דוֹלָר if the Publication Table were built in Hebrew (and a Hebrew name for the corresponding Value List item).

Conditional Variables

A Conditional Variable is one whose value is conditional on the value of a Dimension Variable.

An example use case is Banknotes, where the values for NOTE1, NOTE2, or NOTE3 is dependant on the Country Dimension. A Conditional Variable could be used to set a values for each variable (NOTE1, NOTE2, NOTE3) based on the value used for the current Country Dimension i.e. US NOTE1=$5, UK NOTE1=£5.

The syntax of referring to a Conditional Variable is $([conditional variable id][variable id]), for example

$(BANKNOTE[NOTE1])

Time Variables

Overview

When the data is time series (i.e. the DSD has a Time Dimension), the Observation Key can include the time period, for example:

A:UK:EMP:2008

Where 2008 is the Observation Time Period to use. The format of the Time Period must always be the same format as the frequency for which the data are reported. For example if the data is annual, and an observation is reported for 2008, then the observation key must use 2008, not another frequency such as 2008-12-31. It is typical in a DSD for the Frequency of the Series to be the first Dimension, in all the examples on this page this convention is applied, and as such the above key A:UK:EMP refers to Annual data, depicted by the initial dimension value 'A'.

If the key omits the time period, then the time period becomes a variable which can be set at table build time, if not set it defaults to the last period for which there is data.

A:UK:EMP

When a Publication Table has a variable time period, the resolved varaible time value for this varaible is known as the base period. There can only be 1 base period for a Publication Table, however other Observation Keys (and corresponding headings) can perform time arithmetic from the base period for which the table is built.

Time Functions

The supported time functions include walking forwards and backwards in increments of the given frequency, converting to another frequency of data, and a combination of the two (convert and move, move and convert, move convert and move again). Ths supported functions are shown in the table below.

Syntax	Description	Example Observation Key	Example Resolved Key
P	The base period (default if not provided)	A:UK:EMP:P	A:UK:EMP:2008 (base period=2008)
P-n	Base Period minus 'n' Periods where n is a positive Integer	A:UK:EMP:P-1	A:UK:EMP:2007 (base period=2008)
P+n	Base Period plus 'n' Periods where n is a positive Integer	A:UK:EMP:P+1	A:UK:EMP:2008-Q2 (base period=2008-Q1)
A(P)	Base Period converted to Annual	A:UK:EMP:A(P)	A:UK:EMP:2008 (base period=2008-Q2)
S(P)	Base Period converted to Bi-Annual (Semester)	S:UK:EMP:S(P)	S:UK:EMP:2008-S2 (base period=2008-Q3)
T(P)	Base Period converted to Tri-Annual	T:UK:EMP:T(P)	T:UK:EMP:2008-T3 (base period=2008)
Q(P)	Base Period converted to Quarterly	Q:UK:EMP:Q(P)	Q:UK:EMP:2008-Q4 (base period=2008)
M(P)	Base Period converted to Monthly	M:UK:EMP:M(P)	M:UK:EMP:2008-03 (base period=2008-Q1)
W(P)	Base Period converted to Weekly	W:UK:EMP:W(P)	W:UK:EMP:2008-04 (base period=2008-01-01)
D(P)	Base Period converted to Daily	D:UK:EMP:D(P)	D:UK:EMP:2008-12-31 (base period=2008)
H(P)	Base Period converted to Hourly	H:UK:EMP:D(P)	H:UK:EMP:2008-12-31T12 (base period=2008-02-02T12:00:03)
A(P-n)	Base Period minus (or plus) 'n' periods, converted to another frequency 'A'	A:UK:EMP:A(P-4)	A:UK:EMP:2007 (base period=2008-Q4)
A(P)-n	Base Period, converted to another frequency 'A' minus (or plus) n' periods	A:UK:EMP:A(P)-4	A:UK:EMP:2004 (base period=2008-Q4)
A(P-n1)-n2	Base Period, minus (or plus) 'n1' periods, converted to another frequency 'A' minus (or plus) 'n2' periods	A:UK:EMP:A(P-4)-4	A:UK:EMP:2003 (base period=2008-Q4)

Notes on Time Period
When converting from a lower frequency to a higher frequency (Weekly to Daily for example), the cast date always resolves to the end of the Period.

It is also important to note that as the Observation Keys all include Frequency as the first Dimension, when converting to another frequency (Quarterly to Annual for example) the Observation Key changes the value for the Frequency Dimension, ensuring the correct series of data is retrieved.

Variables Summary

Variable Type	Description	Syntax	Example	Example Output
Dataflow Name	Displays the name of a Dataflow referenced by the Publication Table in the locale in which the Publication Table is built	$([dataflow alias])	$(EDU)	World Bank, Education
Dataflow Description	Displays the description of a Dataflow referenced by the Publication Table in the locale in which the Publication Table is built	$([dataflow alias].desc)	$(EDU.desc)	World Bank, Education
Dataflow Id	Displays the ID of a Dataflow referenced by the Publication Table	$([dataflow alias].id)	$(EDU.id)	DF_EDU
Variable Dimension Value	Displays the name of the variable used when an Observation key has a wildcard member	$([dimension id])	$(REF_AREA)	United Kingdom
Specific Component Value	Displays the name of the Component Value	$([component id].[component value])	$(OBS_STATUS.C)	Confidential
Component Name	Displays the name of the DSD Component (this is the name of the Concept used by the Component) in the locale in which the Publication Table is built	$([component id].name)	$(OBS_STATUS)	Confidentiality Status
Enumeration Value	Displays the name of a Code in a Codelist or Value in a Valuelist	$([list alias].[list item ID]	$(SEX.M)	Male
Conditional Variable	Displays the variable value, which is conditional on the value of a variable Dimension	$([conditional variable id][variable id])	$(BANKNOTES[NOTE1])	£5
Time	Displays the base time period for which the table is built, or an offset from this time period / cast to a different frequency	see Time Functions	$(P)	2008

Data Cell Syntax

Each data cell in a publication table is defined by a cell key, which resolves to an observation when the table is materialised.

1. A fully formed cell key consists of Dimension Values for each Dimension including Time Period if the DSD has a Time Dimension.
2. If there are multiple measures in the DSD the Measure Dimension should also be included.
3. A cell key can leave out Dimension values, in doing so the Dimension value is treated as a variable, which can be passed in at 'run time' to build the table output
4. A cell key can leave out Dimension values, in doing so the Dimension value is treated as a variable, which can be passed in at 'run time' to build the table output
5. A cell key can treat time as a variable, and also provide arithmetic on the Time Dimension to move forwards and backwards in periods, or to change Frequency
6. A cell key can include calculations

Examples:

|- ! Cell Key !! Description |- | A:UK:EMP:M || Series key without Time |- | A:UK:EMP:M:2008 || Series key with Time |- | A:UK:EMP:M:2008:BIRTHS || Series key with Time and ID of Measure Dimension |- | A:UK:EMP:M:P || Series key with variable Time |- | A::EMP:M:P || Series key with variable dimension and variable Time |- | A:UK:EMP:M:P-1 || Series key with variable Time, which is 1 period before the base period of the table |- | :=A:(UK+FR):EMP:M || A calculated value from 2 series |}

Time Arithmetic Syntax

Syntax	Description	Example Input	Example Output
Example	Example	Example	Example
Example	Example	Example	Example
Example	Example	Example	Example
Example	Example	Example	Example
Example	Example	Example	Example
Example	Example	Example	Example
Example	Example	Example	Example