SDMX Matrix Generator User Guide

SDMX MATRIX GENERATOR USER GUIDE

Contents Contents Welcome to the user guide for the SDMX Matrix Generator, an SIS-CC SDMX Tool. 1. Introduction 2. Design prerequisities/Tool limitations 3. How to do certain tasks with the tool 4. Annex 1: Description of each Worksheet 5. Contact and Support

Introduction (1) Introduction (1) The main goal of the SDMX Matrix Generator is to easily and quickly design a set of related SDMX artefacts SDMX structure creation is streamlined by focusing on the most common design scenarios of DSDs, Dataflows and Codelists Excel is used as many users are familiar with its powerful interface for editing tables of information The artefact relationships are defined in the matrix worksheet

Introduction (2) Introduction (2) Author Concept Schemes, DSDs, Dataflows, Codelists, Constraints, and Categorisations Generate a simple Reporting template from a Dataflow which can be sent to reporters Maintain artefacts by using the Prefill feature The interface design was inspired by the SDMX Global DSDs design method and the guideline on Modelling Statistical Domains in SDMX

Design Design prerequisities prerequisities/Tool limitations /Tool limitations The latest version of the tool has these requirements and limitations: Each DSD must reference only the Concept Scheme that is in the worksheet 3.Concept Scheme A DSD s Local Representation is not editable, it is derived from the Concept Scheme. This will be a forthcoming feature Codelists may have in-line hierarchies defined through Code:ParentCode. Multiple hierarchies (through HierarchicalCodelists) cannot be defined using this tool (yet), but another tool could be used to do that.

How to do certain tasks with the tool How to do certain tasks with the tool This section shows some typical use cases that apply to using the SDMX Matrix Generator. A full, step-by-step data modelling guideline for this tool is available at https://gitlab.com/sis-cc/sdmx-tools/sdmx-matrix-generator/-/blob/master/Modelling- statistical-domains-in-SDMX-using_SISCC_Matrix_Gen.docx See the later section on Description of each worksheet for more details of each step.

Creating Codelists Creating Codelists If you want to create Codelists only then follow these steps: 1. Create a copy of the Worksheet New CL Template and rename it to the Id of your desired Codelist (e.g. CL_MEASURE) 2. Fill the new Codelist worksheet as required (see the Codelist Worksheet slide) 3. Go to 7. Generate SDMX and enable Codelists in the Generate? Section, disable other artefacts, and Generate SDMX Artefacts Copy New CL Template for each Codelist 7.Generate SDMX (Codelists) Fill Codelists

Creating Hierarchies Creating Hierarchies A Hierarchical Codelist (HCL) is used to describe a hierarchy(s) for an existing Codelist. HCLs can be used to impose different hierarchies onto a single existing Codelist. They overcome limitations of using a Codelist s ParentCode: Only a single hierarchy is possible with ParentCode. HCLs allow unlimited hierarchies on a Codelist With ParentCode, a codes can have one parent in a hierarchy. With HCLs a code can have multiple parents which can be used to for different groupings of the same item. For example, a country may belong to several groups. If you want to create hierarchies only then follow these steps: 1. Create a copy of the Worksheet New HCL Template and rename it to the Id of your desired Hierarchical Codelist (e.g. HCL_MEASURE) 2. Fill the new HCL worksheet as required 3. Bind the HCL to your Dataflow 4. Go to 7. Generate SDMX and enable HCLs in the Generate? Section, disable other artefacts, and Generate SDMX Artefacts For a full description of HCLs, see the Hierarchical Codelist Worksheet slides in Annex 1 Copy New HCL Template for each Codelist Fill Hierarchical Codelists 7.Generate SDMX (HCLs)

Creating Creating all artefacts from scratch all artefacts from scratch When creating a Concept Scheme, DSDs, Dataflows, Codelists, Constraints and Categorisations, follow this order in the tool. Decompose indicators is an optional step that allows to you to unpack information that is often bound-up in Indicators into separate Concepts, and make it easier to create the Concept Scheme. During the design process there will often be iteration between the steps, especially 4. DSD-Concept Matrix and the later stages while the designer is experimenting with grouping Dataflows into DSDs, filling details of Codelists, and creating Constraints. 4.DSD- Concept Matrix 7.Generate SDMX (all artefacts) 2.Decompose indicators 3.Concept Scheme 6.Dataflow s 5.DSDs Codelists HCLs Constraints

Prefilling/ Prefilling/Customising Customising a DSD To customize a DSD do the following: 1. Use the 1.Prefill worksheet to populate the matrix generator with the DSD. If the DSD is stored in a system with an SDMX REST API such as the Global Registry, put a query in the REST API URL box such as https://registry.sdmx.org/ws/public/sdmxapi/rest/schema/datastructure/IAEG- SDGs/SDG/1.0/?format=sdmx-2.1&references=all and click Prefill from REST API Note: If codelists are prefilled they are created as worksheets and the Concept Scheme references are only the Codelist Id. If codelists are not prefilled, no codelist worksheets are created and the Concept Scheme uses the 3-part identifier. 2. Edit the required CL_... HCL worksheets (if they are loaded) to add new codes, and make any other changes to artefacts 3. Change the Codelist and DSD identifiers (Id, Agency, Version and isFinal fields) to reflect the version change, and maintenance agency change (if you were not the original owner) 4. Go to 7. Generate SDMX and generate all artefacts required by choosing them in the Generate? Section a DSD Codelists, HCLs (edit, change Identifiers) DSD (change identifier) Update artefact identifiers 7.Generate SDMX 1.Prefill

Adding a new Code to a Codelist Adding a new Code to a Codelist To add a new code to a Codelist the worksheets do the following: 1. Use the 1.Prefill worksheet to load SDMX-ML including the codelist you want to change 2. Edit the required CL_... worksheet to add a new code 3. Change the Codelist (Id, Agency, Version and isFinal fields) to reflect the version change, and maintenance agency change (if you were not the original owner) 4. If there is a Hierarchical Codelist (HCL) that references the Codelist, check that for changes 5. Go to 7. Generate SDMX and enable Codelists in the Generate? Section, disable other artefacts, and Generate SDMX Artefacts Codelists (edit) Check HCLs 7.Generat e SDMX 1.Prefill

Creating a DSD using existing Codelists Creating a DSD using existing Codelists To create a Concept Scheme and/or DSD that references a Codelist(s) that already exists in the target registry: 1. In the 3. Concept Scheme worksheet, use the full identifier to refer to the Codelist: <AgencyId>:<Id>(<Version>) e.g. OECD:CL_AREA(1.0) 2. You do not need to create CL_... Worksheets for these Codelists. However, if you need to define Constraints for Concepts that use this Codelist, you must add the Codelist worksheet 3. Using the full identifier for the Codelist doesn t change anything else in the matrix generator The concepts can be a mixture of existing codelist references (as above) and CL_ codelists that are defined in the matrix generator.

Ordering Concepts and Dimensions Ordering Concepts and Dimensions A Concept Scheme s concepts are generated in the order that they appear in the Concept Scheme worksheet. A DSD s concepts are generated in the DSD s SDMX-ML in the left-to-right order that they appear in the DSD-Concept Matrix worksheet (usually the same order that they appear in the Concept Scheme worksheet but they may get out of sync). The DSDs dimensions order may be explicitly defined with the Concept Scheme worksheet s Position column. If one Position value is specified, all dimensions must have a Position value. On generation, the dimensions in the DSD-Concept Matrix worksheet are reordered according to the Position values to ensure that the DSD concept order is consistent in the workbook. Note that use of the Position column does not alter the order of the Concept Scheme s concepts.

Creating a Reporting Template (1) Creating a Reporting Template (1) A simple Reporting template Excel questionnaire can be generated from the structure of a Dataflow. The reporting template can be sent to data reporters, and the filled reported template can be transformed to SDMX-CSV with an included python script. The reporting template workbook consists of: A Guidelines worksheet. The content comes from a workbook called reportingtemplate_guidelines.xlsx in the output folder A Reporting template worksheet with: Hidden header information (rows 1 to 3) for it to be processed with the included SDMX-CSV python script A column for each Dataflow concept A row for each observation Data validation on the cells according to their type and codelist constraints Codelist worksheets Continued

Creating a Reporting Template (2) Creating a Reporting Template (2) To create a reporting template (after creating the structure) do the following 1. The text in the reporting template column headers come from the Concept Description fields in 3.Concept Scheme. Ensure that any concepts to appear in the reporting template have a description a. By default the column header is dark blue. You may change this by providing a different background colourin the Description field, e.g. 2. The order of the reporting template columns follows the left-to-right order of concepts in the 4.DSD-Concept Matrix worksheet so change the order of those columns if required 3. In the 7. Generate SDMX worksheet, select a Dataflow in the Dataflow Id dropdown list 4. You may provide a Guidelines worksheet that will be added to the workbook. To do so, create a separate workbook named reportingtemplate_guidelines.xlsx , write your guidelines in the first worksheet, and save the workbook in the path specified in the Output folder. 5. Click Generate Reporting Template 6. The workbook will be created in the Output folder. When finished, you may enter a password to protect the workbook. 7. You may customise the reporting template after it is generated with style changes, etc. A filled-in reporting template can be transformed to SDMX-CSV with the Transform_template_to_sdmxcsv.py python script (in the Gitlab repo). Example use: Transform_template_to_sdmxcsv.py --template_path= reptemplate.xlsx --sdmxcsv_path= output.csv

Constraining a Dataflow Constraining a Dataflow The matrix generator allows the creation of CubeRegion constraints on Dataflows. They are used to limit the component values in a dataset that corresponds to a Dataflow. To constrain a Dataflow do these steps: 1. Find the Dataflow row to constrain in the DSD-Concept Matrix worksheet 2. Along that row, for each concept in the constraint, enter into the concept s column either: 1. A single value that appears in the concept s Codelist, or; 2. A % symbol that means multiple values are valid 3. For each concept that is constrained to multiple values (with %): 1. Go to the Codelist for that concept 2. Find the section called CONSTRAINTS (towards the right of the worksheet, may be off screen) 3. To the right of cells Dataflows-> and Concepts->there are corresponding dropdown lists. Select the Dataflow and Concept to constrain (in the same column) 4. Staying in the same column, enter 1 for every valid code in this Dataflow constraint Here is an example of the CONSTRAINTS section for the CL_MEASURE Codelist. See slide Codelist worksheets (CL_...): Constraints for more details.

Driving Presentation and Processing Driving Presentation and Processing by using Annotations by using Annotations Annotations are defined differently in the tool: DSDs and Dataflows worksheets Some common annotations are set-up for use, e.g. LAYOUT_ROW The header row is the AnnotationType The cell value (for a DSD or Dataflow) is the AnnotationTitle If the AnnotationType is in the SDMX Annotation Guideline controlled vocabulary the annotation ID is set to @SDMX If the DSD or Dataflow annotation cell value is blank, the annotation is not created If a complex Annotation is required, you may put the column header ANNOTATION and use the JSON syntax to define the value (as in Codelists) Codelist worksheets The Annotations column is used to associate create a collection of code-based Annotations. The full SDMX Annotation model is supported, the syntax is JSON and is: [{"Id":"id1","Title":"title1","Type":"type1","URL":"http://1.org","Text":[{"Locale":"en","Label":"textEn"}]}] The keywords are in bold, the values (to replace) are in green. Id, Title and URL are optional. Here are some examples: Single annotation, single language Text: [{"Type":"CODE_HISTORY","Text":[{"Locale":"en","Label":"10053"}]}] Single annotation, multiple language Texts: [{"Type":"CODE_HISTORY","Text":[{"Locale":"en","Label":"10053"},{"Locale":"fr","Label":"20053"}]}] Multiple annotations for a code (the separate Annotations are coloured red and blue): [{"Type":"CODE_HISTORY","Text":[{"Locale":"en","Label":"10053"}]},{"Type":"ABBREV","Text":[{"Locale":"en","Label":"Affol tern"}]}]

Annex 1: Annex 1: Description Description of each This section details how to use each worksheet in the SDMX Matrix Generator of each Worksheet Worksheet

1. Prefill 1. Prefill This step is used to prefill the SDMX matrix generator from existing SDMX-ML. It can be used to update existing SDMX artefacts, migrate from older versions of the matrix generator, or to create new structures by copying existing ones. To prefill: 1. Either: enter the File path containing the SDMX-ML structures, or: enter the REST API URL which should be a valid SDMX Structural Metadata query 2. Click the button Prefill SDMX. Note that the Excel workbook is hidden during the Prefill process Notes: The 2.Decompose indicators, 4.DSD-Concept Matrix, and all Codelists, are reset during prefill. Prefilling certain artefacts is dependent on others being prefilled. For example, Constraints require compatible Codelists, Dataflows, and a Concept Scheme DSDs require compatible Codelists and a Concept Scheme Categorisations require compatible Dataflows. When loading from a file, the XML file must not have reserved namespaces such as XML. The matrix generator supports only one Concept Scheme at a time.

2.Decompose 2.Decompose indicators indicators This worksheet is used when working from an existing set of indicators that are composed of multiple characteristics. It is optional, but it can help when creating the Concept Scheme, which is the next step. In order to improve operations such as query, filter, search, and connecting other data it is usually better to separate or decompose the characteristics of each indicator into orthogonal concepts. The resulting Concepts are typically Measure (the entity being measured), the Unit of Measure (a named division of a quantity of the Measure), and possible breakdowns. These are the steps: 1. List your existing indicators vertically in the Existing indicators column 2. Examine each indicator and add its orthogonal characteristics (for example, SEX, AGE) as Concepts on the Concepts-> row 3. In the row of each Existing indicator, set each relevant Concept to the relevant value following the MATRIX LEGEND. In the example provided, all indicators may use any REF_AREA (Reference area) therefore # is entered. If only a subset of REF_AREA codes were allowed, the % symbol would have been entered. If only one value of REF_AREA is allowed, then that value would have been entered. 4. Once the indicators have been decompsoed, click the button Update Concept Scheme which will add the Concepts from this step to the 3.Concept Scheme worksheet and go there. The Source column is optional and may contain a handy reference to the original data structure. Note that, because the Decompose indicators information is not an SDMX artefact itself, it is not prefilled. An example is included in the template from the SDMX Modelling Guidelines which shows a subset of Labour indicators

3.Concept Scheme 3.Concept Scheme This worksheet is used to define a Concept Scheme. It has two sections: The CONCEPT SCHEME ARTEFACT INFORMATION which is the "header" information for the Codelist, and the CONCEPTS section which is used to define each Concept on a row. Required fields are marked with a * 1. Complete the CONCEPT SCHEME ARTEFACT INFORMATION details (Agency Id, Version, Id, Is final? The localised Name and Description of the Codelist requires a Language, Name and Description. More languages can be added by adding those 3 fields. The Language drop-down lists the ISO 639-1 codes, and the Language link shows their descriptions. 2. Fill the CONCEPTS section. 1. Commonly used concepts are already included, remove those that are not required. 2. If you have completed step 2. Decompose indicators those Concepts may have been already added, or you may use the button on the left to add the CONCEPTS section from that worksheet. 3. Add any additional concepts required; aligning the concept IDs and definitions with the SDMX Glossary 4. Choose whether the Concept is a Dimension, Attribute, or Measure in the first column. That column contains several types of attribute attachment level and optionality. Attributes may be attached to a group by: a) Selecting a group attachment level in column A, and; b) Defining the group in the column Group Concepts. The group concepts should be a comma-separated list, e.g. FREQ,MEASURE 5. To include several languages for the Concepts, add Concept Name:<language> and Concept Description:<language>columns to the Concepts table 6. For each coded concept, in Codelist add either: a) its Codelist ID, or; b) the full identifier <AgencyId>:<Id>(<Version>). See slide Create a DSD using existing Codelists for more details. 7. For uncodedconcepts, enter "Uncoded" into the Code List... Column and choose the datatype from the Type column drop-down list. 8. Click the button Update DSD matrix which will prepare and go to the 4.DSD-Concept Matrix, and generate the Codelist worksheets. Note that by default, the Concept Scheme worksheet concept definitions create the DSD LocalRepresentations. These can be overridden in the worksheet 5.DSDs

4.DSD 4.DSD- -Concept Matrix Concept Matrix The DSD-Concept Matrix is used for several things and is required to create DSDs and Dataflows: It relates each DSD to a set of Concepts It relates each Dataflow to a DSD It shows if and how each Dataflow is Constrained (though further details are specified in the Codelist worksheets) 1. The Concepts-> columns should be prefilled from the 3.Concept Scheme worksheet with the button Update DSD Matrix 2. Allocate Data flows to DSDs by entering a DSD Id in the column. Note that all Data flows using the DSD must use the same Concepts. For guidance and best practice on how to do this, refer to the Modelling Statistical Domains in SDMX. 3. It is possible to change the codelist that a concept uses for certain dataflows. An example is where Geocoding may be at the national level for some Dataflows but at the subnational level for others, and the codes are in separate codelists. To do this, append the codelist ID to the concept ID in the CONCEPTS-> row. E.g. REF_AREA:CL_AREA or REF_AREA:CL_REGION. The codelists must be in the matrix generator file. 4. For each Data flow, enter a Data flow Id and in that row mark how the Concepts are used for it using the MATRIX LEGEND: 1. If any code is allowed in the Dataflow s Concept put # 2. If a specific code is allowed in the Dataflow s Concept put the code (a Test Dataflow example is included where FREQ is set to A:Annual) 3. If some codes are allowed but others are not, put %. Then define the constraint in the concept s codelist (see Codelist worksheets (CL_...): Constraints) 5. Move to the next step by clicking the button Go to DSDs

5.DSDs 5.DSDs This worksheet is used to fully define Data Structure Definitions. Each row defines one DSD, the Id column is pre-filled from the DSD-Concept Matrix. The required fields are marked with a *. To add a name and description language, add new columns for the required Name:<language> and Description:<language>, where <language> is a code from ISO 639-1. The LocalRepresentation for each DSD is derived from the 3.Concept Scheme worksheet, unless overridden by a Local Representation definition (see next slide) Annotations may be added to the DSDs like so: Annotations are optional and may be added to the columns to the right of the last Description field. For a DSD to use an Annotation, enter a value in the row for the DSD underneath the Annotation column, otherwise an Annotation is not generated It is recommended to add comments to an Annotation s header column to explain it The Annotation header is generated as the AnnotationType, and the cell value is the AnnotationTitle Move to the next step by clicking the button Go to Dataflows

5.DSDs: Concept Local Representations 5.DSDs: Concept Local Representations By default, a DSD s concept definitions come from the information in the 3.Concept Scheme worksheet, and all DSDs in the matrix generator workbook have the same concept definitions. However, this can be changed by defining a Local representation in the 5.DSDs worksheet which changes concepts for that DSD. To make authoring and maintenance easy, a DSD s local representation only has to include the concepts to override (not all of the DSD s concepts). The local representation definition is in a JSON string. The general syntax of the JSON string is: [{"ConceptID": <conceptID> , <Key>": <Value>", <Key>": <Value>"},{"ConceptID": <conceptID>", <Key>": <Value>", }] where the <conceptID> is the concept to override in the DSD, <Key> is the attribute to change, and <Value> contains the value of the key. Each concept may have multiple attributes changed, and multiple concepts may be overridden. Here are some examples : DSD1: Change the data type of OBS_VALUE to Boolean and make REF_AREA a conditional series-level attribute [{"ConceptID":"OBS_VALUE","Representation":"Boolean"},{"ConceptID":"REF_AREA","DimAttrMeas":"A","AttachmentLevel":"series","Atta chmentOptionality":"Conditional"}] DSD2: Change the data type of OBS_VALUE to Alpha-numeric, make REF_AREA a mandatory observation-level attribute, and make UNIT_MEASURE an attribute with group attachment [{"ConceptID":"OBS_VALUE","Representation":"AlphaNumeric"},{"ConceptID":"REF_AREA","DimAttrMeas":"A","AttachmentLevel":"obs","At tachmentOptionality":"Mandatory"},{"ConceptID":"UNIT_MEASURE","AttachmentLevel":"group","GroupConcepts":"REF_AREA,MEASURE"}] To easily define the JSON strings, it is recommended to use a JSON editor such as https://jsonformatter.org/json-editor The following table explains the values of the settings in the local representation JSON string:

5.DSDs: Concept Local Representations 5.DSDs: Concept Local Representations Attribute concepts (A) require AttachmentLevel and AttachmentOptionality. Uncoded concepts require Representation Key Value Comment DimAttrMeas A/D/D (Frequency)/D (Time)/M A=Attribute, D=Dimension, M=Measure CodelistID <AgencyId>:<Id>(<Version>) The full identifieris required. AttachmentLevel dataset/series/obs/group Only applies to attributes. Required for attributes AttachmentOptionality Conditional/Mandatory Only applies to attributes. Required for attributes GroupConcepts <concept>,<concept>, Representation <Any SDMX data type, see 3.Concept Scheme for list> Only applies to non-coded. Required for uncoded MaxLength <integer> Only applies to non-coded. MinLength <integer> Only applies to non-coded. Position <integer> ConceptRole <SDMX concept roles, see 3.Concept Scheme for list>

6.Dataflows 6.Dataflows This worksheet is used to fully define Data flows. Each row defines one Dataflow, the Id column is pre-filled from the DSD-Concept Matrix. The required fields are marked with a *. The Category fields (starting with Cat.) are only required when generating Categorisations. The Category Scheme itself is defined externally. The Constraint ID field allows you to manually specify the ID of the Constraint. If it is blank on generation, the ID is autogenerated using the CR_<Dataflow ID>. The Constraint s agency and version always matches the Dataflow, and isFinal is false. To add a name and description language, add new columns for the required Name:<language> and Description:<language>, where <language> is a code from ISO 639-1. The annotation columns, e.g. LAYOUT_ROW, NOT_DISPLAYED are for assigning specific behaviour to Concepts if supported by the platform: Annotations are optional and are listed to the right of the Category field, and several default ones are provided. More Annotations may be added by typing their Annotation type in the header row. For a Data flow to use an Annotation, enter a value in the row for the Data flow underneath the corresponding Annotation column, otherwise an Annotation is not generated The meaning of the Annotation is in the header comments (hover over the red triangle). If adding an Annotation, also add a comments to explain it Multiple concepts in one cell are comma-separated, e.g. FREQ,REF_AREA. If an annotation value is blank, it is not created. The Annotation header is generated as the AnnotationType, and the cell value is the AnnotationTitle Move to the next step by clicking the button Update Constraints Go to Codelists

7.Generate SDMX Generate SDMX This worksheet is used to generate SDMX from the structures defined in preceding steps and Codelist worksheets. To generate the artefacts: 1. Enter 1 in the Generate? column next to the artefact type(s) required 2. Set the Parameters as required 3. Click the button Generate SDMX Artefacts. Note that the Excel workbook is hidden during the Generate process 4. Check the result of the generation in the Output section and/or the Log file Note that it is possible to output to a file, directly to an SDMX Webservice, or both. You may also create a reporting template following the structure of a Dataflow. See the section on Create a Reporting Template On this worksheet there are also Helper actions to help update all artefact s Final Status to True or False, or set all the Agency fields to the specified value.

Codelist worksheets (CL_...): Creating Codelist worksheets (CL_...): Creating A Codelist worksheet must have the Id of the Codelist CL_<Concept>. There is one worksheet for each Codelist. A Codelist worksheet may be created in the following ways: By using the step 1.Prefill to load SDMX-ML describing existing Codelists On the Concept Scheme, click the button Update DSD matrix, Codelists from Concept Scheme By manually copying the worksheet New CL Template and renaming it to CL_<Concept>. The worksheet has three main sections: The CODELIST ARTEFACT INFORMATION which is the "header" information for the Codelist; the CODELIST ITEMS which is used to define each Code on a row; the CONSTRAINTS section used to specify which Codes are in a constraint for a Data flow Complete the CODELIST ARTEFACT INFORMATION details (Agency Id, Version, Id, Is final?) The localised Name and Description of the Codelist requires a Language, Name and optional Description. More languages can be added by adding those 3 fields. The Language drop-down lists the ISO 639-1 codes, and the Language link shows their descriptions. You may stop the generation of a Codelist by setting Generate? to FALSE. Continued

Codelist worksheets (CL_...): Codes Codelist worksheets (CL_...): Codes The CODELIST ITEMS section is used to define the codes, one on each row: The ParentCode is used to define a hierarchy by indicating the parent Code for this code; if the Code is at the top level or if the Codelist has no hierarchy, leave it blank. An alternative, much more flexible way to create hierarchies is by using Hierarchical Codelists (HCLs). See the later slides on that. The Order:<language> column is used to specify the localised order of the Code in the Codelist relative to the Code's siblings (the Order restarts on each new branch). It is useful to leave a gap in the order number in case it is required to add a code, using 10s for example A code may have multiple sets of Name and Description. To add another language, insert two new columns for the required Name:<language> and Description:<language>, where <language> is a code from ISO 639-1. The Annotations column may be used to associate one or a collection of code-based Annotations. See the slide Defining Annotations for more details Continued

Codelist worksheets (CL_...): Constraints Codelist worksheets (CL_...): Constraints The CONSTRAINTS section is used to define the Constraints for Data flows. This section should have been prefilled by clicking the button Update Constraints... on the 6.Dataflows worksheet The Constraint needs to be manually completed when the DSD-Concept matrix shows that a Concept for a Dataflow is constrained to a subset (%). In this case, for each % Concept: 1. Go to the Codelist worksheet for the constrained Concept, e.g. Go to CL_TOURISM_DIRECTION for the TOURISM_DIRECTION Concept 2. In the CONSTRAINTS section, find the Dataflow and Concept Id column (it should be prefilled) 3. For each Code in the Dataflow/Concept column that is valid in the Dataflow, enter 1 See the example below which shows how the Constraint information appears in the DSD-Concept Matrix and the CL_TOURISM_DIRECTION Codelist on the right. The top 3 Dataflows have, for the TOURISM_DIRECTION Concept, a single Code in the Constraint. The DF_INOUT_TOURISM Dataflow has more than one code in the Constraint (the % symbol), and the relevant Codes must be chosen in the Codelist. You can see that this has been done has been done (I and O).

Constraints Summary Constraints Summary This worksheet is a compact read-only display of the Dataflow Constraints. In each row it shows how the Dataflow is constrained for each Concept. It is useful because a Dataflow s Constraints are specified in each Codelist worksheet (because the Codes are there see the slide on Codelists), so it can be difficult to quickly see exactly how a Dataflow is Constrained. Note that this worksheet is only updated when constraints are generated or with the "Remove all Constraints" action.

Hierarchical Codelist worksheets (HCL_...): Creating Hierarchical Codelist worksheets (HCL_...): Creating A HCL worksheet must have the Id HCL_<Id>. There is one worksheet for each HCL. It may be created in the following ways: By using the step 1.Prefill to load SDMX-ML describing existing HCLs By manually copying the worksheet New HCL Templateand renaming it to HCL_<Id>. The worksheet has three main sections: The HIERARCHICAL CODELIST ARTEFACT INFORMATION which is the "header" information for the HCL; the HIERARCHICAL CODELIST ITEMS which is used to define each Code and its children on a row; Complete the HIERARCHICAL CODELIST ARTEFACT INFORMATION details (Agency Id, Version, Id, Is final?, Codelist) The localised Name and Description of the HCL requires a Language, Name and optional Description. More languages can be added by adding those 3 fields. The Language drop-down lists the ISO 639-1 codes, and the Language link shows their descriptions. You may stop the generation of a HCL by setting Generate?to FALSE. As an example, the HCL_MEASURE worksheet contains the HCL equivalent of the hierarchy described in CL_MEASURE using ParentCode CL_MEASURE:ParentCode HCL_MEASURE equivalent Code* _T HW VAC_N VAC_N_PUBLIC VAC_U ParentCode Code _T _T:VAC_N Children HW+VAC_N+VAC_U VAC_N_PUBLIC _T _T VAC_N _T Continued

Hierarchical Codelist worksheets (HCL_...): Codes Hierarchical Codelist worksheets (HCL_...): Codes A HCL can contain multiple different hierarchies if required, one per language. Each pair of Code:<language> and Children columns define a separate hierarchy. The HIERARCHICAL CODELIST ITEMS section is used to define the codes, one on each row: The Code refers to an existing code in a Codelist. Because a Code may appear in different places in the hierarchy for multiple parents, each code must be prefixed with all of its ancestors, separated by a colon : e.g. Code Children _T A+B+C+D+E+F+G+H+I+J+K+L+M+N+O+P+Q+R+S+T+U+_U+_X _T:A A01+A02+A03 _T:A:A01 A011+A012+A013+A014+A015+A016+A017 _T:A:A01:A011 A0111+A0112+A0113+A0114+A0115+A0116+A0119 The Children column lists children (direct descendants only) of the Code in a comma-separated list. If a code does not have children (it is a leaf node), you do not have to list in the Code column. The hierarchy code order is left-to-right in the children and top-to-bottom in the Codes. Different hierarchies may be created for non-localised and multiple languages, e.g. this for non-localised, English and French Code _T _T:VAC_N Children HW+VAC_N+VAC_U VAC_N_PUBLIC Code:en _T _T:VAC_N Children VAC_N+VAC_U _T VAC_N_PUBLIC Code:fr Children HW+VAC_N _T:VAC_N VAC_N_PUBLIC Continued

Hierarchical Codelist worksheets (HCL_...): Binding Hierarchical Codelist worksheets (HCL_...): Binding to a Dataflow to a Dataflow Once a HCL is defined, we need to say where it will be used. The binding is specified in a Dataflow HIER_CONTEXT annotation and has these parts: The Concept that the hierarchy applies to, e.g. REF_AREA (optional) specific languages for which hierarchies were defined on the worksheet, e.g. (en,fr) for the example on the previous slide The id of the HCL, e.g. HCL_REF_AREA The annotation syntax is different to the Codelist JSON syntax to make it easier. This is the full syntax: <component><(locale,locale, )>:<HCL identifier>;<component><(locale,locale, )>:<HCL identifier>;< > Examples Non localised, one concept/hierarchy: MEASURE:AGENCY:HCL_MEASURE(1.0) English and French localised hierarchies: MEASURE(en,fr):AGENCY:HCL_MEASURE(1.0) Non-localised and English and French localised hierarchies: MEASURE:AGENCY:HCL_MEASURE(1.0);MEASURE(en,fr):AGENCY:HCL_MEASURE(1.0) Different concept/HCL bindings, localised and non-localised: MEASURE(en,fr):AGENCY:HCL_MEASURE(1.0);MEASURE(es):OECD.SDD.NAD:HCL_MEASURE(1.0);SECTOR:OECD.SDD:HCL_SECTOR(1.0);ACTIVITY(es):OECD.SDD:HCL_ACTIVITY(1.0)

Contact and Support Contact and Support For any questions, suggestions, or issues regarding the tool either: Send an email to david.barraclough@oecd.org, or; Raise or comment on an issue at https://gitlab.com/sis-cc/sdmx- tools/sdmx-matrix-generator/-/issues Have fun! David Barraclough david.barraclough@oecd.org