Working with mixed elements

Mixing elements from JSON, SDMX, CSV and R

The JSON and SDMX repositories can be used together to leverage the power of SDMX metadata associating it with local data. In the following examples we'll show how to configure the engine to work with CSV and R data.

CSV data with SDMX codelists

In this example we will use the CL_FREQ codelist that is available in the BIS public SDMX web service (https://stats.bis.org/api/v1) as a domain for one of the variables of a dataset that is stored as a CSV file in the CSV Environment.

The CSV environment must be enabled

The combined SDMX and JSON repositories must be configured

This is the content of the metadata json file (note the CL_FREQ type for the Id_2 identifier)

ex_1.json

{
  "datasets": [
    {
      "name": "ds_1",
      "structure": "DS_1"
    }
  ],
  "structures": [
    {
      "name": "DS_1",
      "components": [
        {
          "name": "Id_1",
          "role": "Identifier"
        },
        {
          "name": "Id_2",
          "role": "Identifier"
        },
        {
          "name": "Me_1",
          "role": "Measure"
        },
        {
          "name": "Me_2",
          "role": "Measure"
        }
      ]
    }
  ],
  "variables": [
    {
      "name": "Id_1",
      "domain": "integer"
    },
    {
      "name": "Id_2",
      "domain": "BIS:CL_FREQ(1.0)"
    },
    {
      "name": "Me_1",
      "domain": "integer"
    },
    {
      "name": "Me_2",
      "domain": "number"
    }
  ]
}

This is the content of the ds_1.csv file

Id_1,Id_2,Me_1,Me_2
10,A,5,5.0
10,M,2,10.5
11,A,3,12.2
11,M,4,20.3

In the VTL Studio Editor, you can type this simple transformation

ds := ds_1;

and check the results. You will notice that in the structure explorer, the Id_2 column is correctly defined as an identifier and its domain is BIS:CL_FREQ(1.0):string

A similar test can be done using data from the R environment

R data with SDMX DSD

In this example we will use the BIS_EER data structure definition (DSD) that is available in the BIS public SDMX web service (https://stats.bis.org/api/v1) as a structure for an R data.frame that is defined in the R environment.

The Renvironment must be enabled

The combined SDMX and JSON repositories must be configured

This time the JSON metadata contents are quite simple

ex_1.json

{
  "datasets": [
    {
      "name": "ds_2",
      "structure": "BIS:BIS_EER(1.0)"
    }
  ]
}

The R data.frame can be easily created using the SDMX Connectors

ds_2

# BIS effective change rates, daily, nominal, broad, italy
ds_2 = RJSDMX::getTimeSeriesTable('BIS_PUBLIC', 'WS_EER/D.N.B.IT')

In the VTL Studio Editor, you can type this simple transformation

ds := ds_2;

and check the results. You will notice that in the structure explorer, all components are correctly associated with roles and domains from the DSD