SDMX Matrix Generator User Guide.pptx presentation

SDMX Matrix Generator User Guide.pptx presentation

1. SDMX Matrix Generator User Guide For version 2.0 and later

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

3. 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 The .Stat Academy certification-based course An introduction to SDMX structural modelling for data producers teaches how to do SDMX structural modelling using this tool. Note that a prerequisite is to complete the short course An introduction to SDMX for data producers

4. Introduction (2) Latest version generates and prefills SDMX 2.1 artefacts Author Concept Schemes, DSDs, Dataflows, Codelists, Constraints, Categorisations , and Hierarchical Codelists (HCLs) 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

5. Design prerequisities /Tool limitations The latest version of the tool has these requirements and limitations: The Matrix’s constraint pop-up requires the codelist to be in the workbook. However, you may define the constraint by typing in the matrix cell without using the pop-up (avoiding the codelist worksheet  ) From v2.0 these are no longer limitations: DSDs that reference multiple concept schemes are now possible. Previous versions of the tool were limited to only the one concept scheme defined in the workbook A codelist’s or HCL’s worksheet name can now be anything. It does not have to be the codelist Id . Previous versions of the tool did not allow this. Codelists and HCLs do not have to start with CL_ and HCL_, though it is highly recommended The Decomposition worksheet can now be saved and prefilled

6. 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.

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

8. 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: Create a copy of the Worksheet New HCL Template and rename it to the Id of your desired Hierarchical Codelist (e.g. HCL_MEASURE). It can be a different name if required. Fill the new HCL worksheet as required Bind the HCL to your Dataflow Go to Generate and enable HCLs in the Generate? Section, disable other artefacts, and Generate Artefacts For a full description of HCLs, see the Hierarchical Codelist Worksheet slides in Annex 1

9. Creating all artefacts from scratch When creating a Concept Scheme, DSDs, Dataflows, Codelists , Constraints and Categorisations , follow this order in the tool. Decompose 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 Matrix and the later stages while the designer is experimenting with grouping Dataflows into DSDs, filling details of Codelists, and creating Constraints.

10. Prefilling/ Customising a DSD To customize a DSD do the following: Use the 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 references are only the Codelist Id. If codelists are not prefilled, no codelist worksheets are created and the Concept reference is the 3-part identifier. Edit the required CL_... HCL… worksheets (if they are loaded) to add new codes, and make any other changes to artefacts 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) Go to Generate and generate all artefacts required by choosing them in the Generate? Section

11. Adding a new Code to a Codelist To add a new code to a Codelist the worksheets do the following: Use the Prefill worksheet to load SDMX-ML including the codelist you want to change Edit the required codelist worksheet to add a new code 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) If there is a Hierarchical Codelist (HCL) that references the Codelist, check that for changes Go to Generate and enable Codelists in the Generate? Section, disable other artefacts, and Generate SDMX Artefacts

12. 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: In the Concepts worksheet, use the full identifier to refer to the Codelist: < AgencyId > : <Id> ( <Version>) e.g. OECD : CL_AREA ( 1.0 ) You do not need to create worksheets for these Codelists. 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.

13. Ordering Concepts and Dimensions A Concept Scheme’s concepts are generated in the order that they appear in the Concepts worksheet. A DSD’s concepts are generated in the left-to-right order that they appear in the Matrix worksheet (usually the same order in the Concepts worksheet but they may get out of sync). The DSDs dimensions order may be explicitly defined with the Concepts worksheet’s Position column. If one Position value is specified, all dimensions must have a Position value. On generation, the dimensions in the 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.

14. 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…

15. Creating a Reporting Template (2) To create a reporting template (after creating the structure) do the following The text in the reporting template column headers come from the Concept Description fields in Concepts . Ensure that any concepts to appear in the reporting template have a description By default the column header is dark blue. You may change this by providing a different background colour in the Description field, e.g. The order of the reporting template columns follows the left-to-right order of concepts in the Matrix worksheet so change the order of those columns if required In the Generate worksheet, select a Dataflow in the Dataflow Id dropdown list 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 . Click Generate Reporting Template The workbook will be created in the Output folder . When finished, you may enter a password to protect the workbook. 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”

16. 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: Find the Dataflow row to constrain in the Matrix worksheet For each concept that is constrained (not <blank> or #) either: Type a comma-separated list of the valid codes, or Right-click the cell, select “  Get codelist dialog” to select the codes from a searchable list (see below). Click Store constraints to update the constraint. Note : This method replaces the way that constraints were defined in codelist worksheets in v1.3 and earlier.

17. Driving Presentation and Processing 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, non-localised text: [{" Type":" ORIGINAL_CODE ", "Title":"1.1"}] 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":" Affoltern "}]} ]

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

19. 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: 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 Set the Prefill parameters . These can be used to filter out the import of artefact types from the prefill. For example, you may wish to import all codelists for a DSD but not the DSD details, or vice-versa Click the button Prefill SDMX . Note that the Excel workbook is hidden during the Prefill process Notes: The worksheets are replaced during prefill depending on the prefill parameters. If Concept Scheme is not imported, the Concepts worksheet is not reset Prefilling certain artefacts is dependent on others being prefilled. For example, Constraints require compatible Dataflows , and Concepts DSDs require compatible Codelists and Concepts 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.

20. Decompose 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 Concepts , 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: List your existing indicators vertically in the Existing indicators column Examine each indicator and add its orthogonal characteristics (for example, Sex, Age) as Concepts on the Concepts-> row 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 Reference area therefore # is entered. If only a subset of Reference area codes are allowed, the % symbol would have been entered. If only one value of Reference area is allowed, then that value would have been entered. Once the indicators have been decomposed, click the button Update Concepts … which will add the Concepts from this step to the Concepts worksheet and go there. The Source column is optional and may contain a handy reference to the original data structure. An example is included in the template from the SDMX Modelling Guidelines which shows a subset of Labour indicators Note: From v1.4 it is possible to generate and prefill the Decomposition. It is stored as an annotation on the Concept Scheme of type DECOMPOSITION , so the Concept Scheme must also be generated for this

21. Concepts: Header section 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 * 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. Next is to define the concepts themselves...

22. Concepts: The Concepts section In the CONCEPTS section, some commonly used concepts are already included, remove those that are not required. If you have completed step 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. Add any additional concepts required; aligning the concept IDs and definitions with the SDMX Glossary . Concepts in external Concept schemes may be referenced by specifying the Concept Id as <Concept Scheme full Identifier> . <Concept Id> . E.g. OECD.SDD.NAD:CS_SHARED(1.0) . REF_AREA . In this case, the concept is not included in the generated Concept Scheme and the DSD(s) concept will reference that concept scheme. Here are the Concept field description. Note that this order does not follow the modelling guidelines and is purely for reference. 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: Selecting a group attachment level in column A, and; Defining the group in the column Group Concepts . The group concepts should be a comma-separated list, e.g. FREQ,MEASURE To include several languages for the Concepts, add Concept Name:<language> and Concept Description:<language> columns to the Concepts table For each coded concept, in Codelist… add either: its Codelist ID, or; the full identifier < AgencyId > : <Id> ( <Version>). See slide Create a DSD using existing Codelists for more details. For uncoded concepts, enter " Uncoded " into the Code List... Column and choose the datatype from the Type column drop-down list. Click the button Update DSD matrix… which will prepare and go to the Matrix and generate the Codelist worksheets. Note that by default, the Concepts worksheet’s concept definitions create the DSD LocalRepresentations . These can be overridden in the worksheet DSDs

23. Matrix The Matrix is used to describe several relationships: It relates each DSD to a set of Concepts It relates each Dataflow to a DSD It contains the Dataflow Constraints The Concepts-> columns should be prefilled from the Concepts worksheet with the button Update DSD Matrix … Allocate Data flows to DSDs by entering a DSD name 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. For each Data flow, enter the Dataflow name and in that row mark how the Concepts are used for it using the MATRIX LEGEND: If any code is allowed in the Dataflow’s Concept put # If some codes are allowed but others are not (it is constrained) then define the constraint in the cell (see slide Constraining a Dataflow )

24. DSDs This worksheet is used to fully define Data Structure Definitions. Each row defines one DSD, the Name column is pre-filled from the 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 Local representation for each DSD is derived from the Concepts 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

25. DSDs: Concept Local Representations By default, a DSD’s concept definitions come from the information in the Concepts 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 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","AttachmentOptionality":"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","AttachmentOptionality":"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:

26. 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 identifier is 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 Concepts 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 Concepts for list>

27. Dataflows This worksheet is used to fully define Data flows. Each row defines one Dataflow, the Name column is pre-filled from the 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. Also, if a complex Annotation is required (for example, multiple AnnotationTexts ), put the column header ANNOTATION and use the JSON syntax to define the value (as in Codelists). 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.

28. Generate This worksheet is used to generate SDMX from the structures defined in preceding steps and Codelist worksheets. To generate the artefacts: Enter 1 in the Generate? column next to the artefact type(s) required Set the Parameters as required Click the button Generate SDMX Artefacts . Note that the Excel workbook is hidden during the Generate process 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.

29. Codelist worksheets (CL_...): Creating A Codelist worksheet usually (but it can be different) has 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 Prefill to load SDMX-ML describing existing Codelists On the Concepts worksheet, click the button Update DSD matrix, Codelists from Concepts… By manually copying the worksheet New CL Template and renaming it. It is recommended to prefix with CL_ The worksheet has these 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; 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…

30. 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

31. Hierarchical Codelist worksheets (HCL_...): Creating A HCL worksheet may (but it can be different) have the Id HCL_< Id > . There is one worksheet for each HCL. It may be created in the following ways: By using the step Prefill to load SDMX-ML describing existing HCLs By manually copying the worksheet New HCL Template and renaming it. It is recommended to prefix with HCL_ The worksheet has these 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 Continued… CL_MEASURE:ParentCode HCL_MEASURE equivalent

32. 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. The Children column lists children codes (direct descendants only) of the Code , separated by + . 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 Continued… 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 Code Children Code:en Children Code:fr Children _T HW+VAC_N+VAC_U _T VAC_N+VAC_U _T HW+VAC_N _T:VAC_N VAC_N_PUBLIC _T:VAC_N VAC_N_PUBLIC _T:VAC_N VAC_N_PUBLIC

33. Hierarchical Codelist worksheets (HCL_...): Attaching to a Dataflow Once a HCL is defined, we need to say where it will be used. The attachment(s) is specified in a Dataflow HIER_CONTEXT annotation. The attachments can either be non-localised , or localised (see the colour-coding in the syntax below). Both types of attachment are optional. The component is that part of the structure that hierarchy is for, e.g. REF_AREA The HCL identifier should match the identifier of the HCL The locale is that which is in the Code:<locale> column of the HCL sheet. If only Code is used (non-localised) then do not use the blue syntax. If there are multiple components, they are comma-separated. See the Multiple components example for a complex The HIER_CONTEXT annotation syntax is designed for flexibility while being simpler than the full JSON syntax: <component> : <HCL identifier>, <component> : <HCL identifier> { (locale) : <component>: <HCL identifier> } { (locale) : <component>: <HCL identifier> }… Examples (colour-coding matches above syntax) Non localised, one concept/hierarchy: MEASURE : AGENCY:HCL_MEASURE(1.0) English and French localised hierarchies: { ( en ) :MEASURE: OECD:HCL_MEASURE(1.0) }{ ( fr ) :MEASURE: OECD:HCL_MEASURE(1.0) } Non-localised and English and French localised hierarchies: MEASURE : AGENCY:HCL_MEASURE(1.0) { ( en ) :MEASURE: OECD:HCL_MEASURE(1.0) }{ ( fr ) :MEASURE: OECD:HCL_MEASURE(1.0) } Multiple components (comma separated): SECTOR: OECD.SDD:HCL_SECTOR(1.0) ,ACTIVITY: OECD.SDD:HCL_ACTIVITY(1.0) { (es) :ACTIVITY: OECD.SDD:HCL_ACTIVITY(1.0) ,MEASURE: OECD.SDD.NAD:HCL_MEASURE(1.0) }

34. Contact and Support For any questions, suggestions, or issues regarding the tool either: Send an email to [email protected] , or; Raise or comment on an issue at https://gitlab.com/sis-cc/sdmx-tools/sdmx-matrix-generator/-/issues The .Stat Academy course An introduction to SDMX structural modelling for data producers teaches how to do SDMX structural modelling using this tool, and rewards you with a certificate – go for it! (after completing the short An introduction to SDMX for data producers ) Have fun! David Barraclough [email protected]