Jan Kaczanowski Department of Statistics Table Structure Definitions
Jan Kaczanowski - Department of Statistics Table Structure Definitions for SDMX data 6 -th Global SDMX Conference, Addis Ababa, October 2017
Table Structure Definitions for SDMX data Plan I New challenges for SDMX standard II Idea of Table Definition Queries III SDMX Web-Services and Observation Identification IV Main advantages of Table Definition Queries V Summary & Conclusions 2
Table Structure Definitions for SDMX data I. New challenges for SDMX standard The current version of SDMX standard solves two problems: 1) exchange of statistical data messages 2) defines a method of systematic classification of Time Series The new challenges for development SDMX standard are: 1) user-friendly presentation of SDMX data 2) effective production of statistical publications These new challanges are important for end-users: economists working on SDMX data. 3
Table Structure Definitions for SDMX data I. New challenges for SDMX standard SDMX data -<Data. Set action="Replace"> -<Series COMPILING_ORG="4 F 0" TIME_PER_COLLECT="S" DECIMALS="0" UNIT_MULT="6" TITLE="Goods" TIME_FORMAT="P 3 M" COMP_METHOD="N" VALUATION="_X" CURRENCY_DENOM="_T" UNIT_MEASURE="EUR" MATURITY="_Z" INSTR_ASSET="_Z" FUNCTIONAL_CAT="_Z" INT_ACC_ITEM="G" ACCOUNTING_ENTRY="C" FLOW_STOCK_ENTRY="T" COUNTERPART_SECTOR="S 1" REF_SECTOR="S 1" COUNTERPART_AREA="W 1" REF_AREA="PL" ADJUSTMENT="N" FREQ="Q"> <Obs CONF_STATUS="F" OBS_STATUS="A" OBS_VALUE="43927" TIME_PERIOD="2016 -Q 2"/> </Series> </Data. Set> </Message. Group> User friendly presentation of data 4
5 Table Structure Definitions for SDMX data II. Idea of Table Definition Queries Inspiration from SQL (Structure Query Language) Query Select SQL Data Base Engine Set of records with internal structure SQL Data Base Table Definition Query for SDMX Table Definition Query -TDQ Application Web Services SDMX Data Warehouse SDW ECB Result Data Table
Table Structure Definitions for SDMX data II. Idea of Table Definition Queries Application: Excel client of SDW ECB (TDQ_Public_V 0_1. xslm) based on Web-Service technology Input: Table Definition Query Requirements for SDMX data and table structure (compact XML file) Output: Result Data Table SDMX data organized in table structure (XML file) Result Data Table: could be transformed and presented in HTML, Excel and pdf. 6
Table Structure Definitions for SDMX data II. Idea of Table Definition Queries Example: Input file Table Definition Query <SELECT> <FOR_COLUMNS> <CAPTION DESC_VAL ="Current Account BOP " PL_DESC_VAL="Rachunek Bieżący "> <CAPTION DESC_VAL="mln. EUR" PL_DESC_VAL="mln. Eur"> <SPREAD_OVER_LABELS DIM_ID="COLS_1" TYPE="DEF_DESC" DESC_ID="BOP_ENTRY"> < SPREAD_OVER_DATES > <CELL TS_TEMP="BP 6. Q. N. PL. W 1. S 1. T. [? COLS_1? ]. [? ROWS_1? ]. _Z. _Z. EUR. _T. _X. N " FORMULA="_C 14"> < OBS_VAR OBS_ID="_C 14" TS_TEMP="BP 6. Q. N. PL. W 1. S 1. T. [? COLS_1? ]. [? ROWS_1? ]. _Z. _Z. EUR. _T. _X. N " DATE_FORM="[? DATE_CELL? ]" /> </ CELL> </ SPREAD_OVER_DATES > </SPREAD_OVER_LABELS > </CAPTION> </FOR_COLUMNS> <FOR_ROWS> <SPREAD_OVER_LABELS DIM_ID="ROWS_1" TYPE="DEF_DESC" DESC_ID="BOP_ITEM"> </SPREAD_OVER_LABELS > </FOR_ROWS> <WHERE> <DATE_LIST DATE_BASE="2016 -06 -30"> <DATE_FORM="[? DATE_BASE? ]" DATE_VAL="2016 -06 -30"/> </DATE_LIST> <LABEL_LIST DESC_ID="BOP_ITEM" CL_LIST="CL_ACCOUNTS_ITEM " AGENCY="IMF"> <LABEL DESC_ID="CA" DESC_VAL="Current Account" PL_DESC_VAL="Rachunek Bieżący " /> <LABEL DESC_ID="G" DESC_VAL="Goods" /> <LABEL DESC_ID="S" PL_DESC_VAL="Services"/> <LABEL DESC_ID="IN 1" PL_DESC_VAL="Primary Income" /> <LABEL DESC_ID="IN 2" PL_DESC_VAL="Secondary Income " /> </LABEL_LIST> <LABEL_LIST DESC_ID="BOP_ENTRY" CL_LIST="CL_ACCOUNT_ENTRY " AGENCY="IMF"> <LABEL DESC_ID="C" PL_DESC_VAL="Credit" /> <LABEL DESC_ID="D" PL_DESC_VAL="Debit" /> <LABEL DESC_ID="B" PL_DESC_VAL="Balance"/> </LABEL_LIST> </WHERE> </SELECT> 7
Table Structure Definitions for SDMX data II. Idea of Table Definition Queries Example: Output Result Data. Table Presentation Result Data Table (xml) transformed by style sheet xslt. 8
Table Structure Definitions for SDMX data II. Idea of Table Definition Queries Example: Output Result Data. Table Presentation of Result Data Table in Excel 9
Table Structure Definitions for SDMX data III. SDMX Web-Services and Observation Identification Application imports SDMX observation using Web Service technology. Two parameters points univocally observation: 1) Time Series ID, written in the form: KF_ID. vd 1. vd 2. vd 3. vd 4. vd 5. vd 6 KF_ID – identifier of Key. Family vdi – value of dimension with i-number 2) Date of observation 10
Table Structure Definitions for SDMX data III. Identification of SDMX Data: Time Series, Observations Example: The observation of Quarterly Export of Goods of Sweden for 2016 Q 1 (first quarter) in SDW– ECB can be identified using the parameters: 1) Time Series Identifier BP 6. Q. N. SE. W 1. S 1. T. C. G. _Z. _Z. EUR. _T. _X. N 2) Date – 2016 -03 -31 points the observation with the value: 1) Value of observation - 33748 mln Eur http: //sdw. ecb. europa. eu/ - address of public version SDW ECB 11
Table Structure Definitions for SDMX data IV. Main advantages of Table Definition Queries Table Definitione Queries could contain: - transformation formulas - validation rules - sort clause 12
Table Structure Definitions for SDMX data IV. Main advantages of Table Definition Queries Transformation: calculation of new Time Series from existing SDMX data. Example: Second indicator from the report „Macroeconomic Imbalances Procedure”: Ratio of Net International Investment Position to GDP in percents Formula=((_C 14)/(_A 11+_A 12+_A 13+_A 14))*100 Each ID points one observation: _C 14 – International Investment Position, Net (Asstets minus Liabilities) stocks, _A 11 – Gross Domestic Product (quarterly) of quarter 1 _A 12 – Gross Domestic Product (quarterly) of quarter 2 _A 13 – Gross Domestic Product (quarterly) of quarter 3 _A 14 – Gross Domestic Product (quarterly) of quarter 4 (_A 11+_A 12+_A 13+_A 14) – GDP for one year 13
14 Table Structure Definitions for SDMX data IV. Main advantages of Table Definition Queries Example: Table Definitione Query for Table with presentation of Net Investment Position as a percents of GDP for European Countries Tranformation: Formula <SELECT> <FOR_COLUMNS> <CAPTION DESC_VAL ="Net Inernational Investment Position" PL_DESC_VAL="Międzynarodowa Pozycja Inwestycyjna"> <CAPTION DESC_VAL="GDP %" PL_DESC_VAL="PKB %"> <SPREAD_OVER_DATES > <CELL TS_TEMP="BP 6. Q. N. [? ROWS 1_ID? ]. W 1. S 1. LE. N. FA. _Z. _Z. XDC_R_B 1 GQ_CY. _T. _X. N" COL_SORT="LAST" SORT="DESC" FORMULA="(_C 14/(_A 11+_A 12+_A 13+_A 14))*100" CONDITION="(CELL_VAL. GT. (-35. 0))"> <OBS_VAR OBS_ID="_A 11" TS_TEMP="MNA. Q. N. [? ROWS 1_ID? ]. W 2. S 1. B. B 1 GQ. _Z. _Z. EUR. V. N" DATE_FORM="[? DATE_CELL? ]-3*Q" /> <OBS_VAR OBS_ID="_A 12" TS_TEMP="MNA. Q. N. [? ROWS 1_ID? ]. W 2. S 1. B. B 1 GQ. _Z. _Z. EUR. V. N" DATE_FORM="[? DATE_CELL? ]-2*Q" /> <OBS_VAR OBS_ID="_A 13" TS_TEMP="MNA. Q. N. [? ROWS 1_ID? ]. W 2. S 1. B. B 1 GQ. _Z. _Z. EUR. V. N" DATE_FORM="[? DATE_CELL? ]-1*Q" /> <OBS_VAR OBS_ID="_A 14" TS_TEMP="MNA. Q. N. [? ROWS 1_ID? ]. W 2. S 1. B. B 1 GQ. _Z. _Z. EUR. V. N" DATE_FORM="[? DATE_CELL? ]" /> <OBS_VAR OBS_ID="_C 14" TS_TEMP="BP 6. Q. N. [? ROWS 1_ID? ]. W 1. S 1. LE. N. FA. _T. F. _Z. EUR. _T. _X. N" DATE_FORM="[? DATE_CELL? ]" /> </CELL> </SPREAD_OVER_DATES> </CAPTION> </FOR_COLUMNS> <FOR_ROWS> <SPREAD_OVER_LABELS DIM_ID="ROWS 1_ID" TYPE="DEF_DESC" DESC_ID="REGION"> </SPREAD_OVER_LABELS> </FOR_ROWS> <WHERE> <DATE_LIST DATE_BASE="2015 -12 -31"> <DATE_FORM="[? DATE_BASE? ]-4*Y" DATE_VAL="2011 -12 -31"/> <DATE_FORM="[? DATE_BASE? ]-3*Y" DATE_VAL="2012 -12 -31"/> <DATE_FORM="[? DATE_BASE? ]-2*Y" DATE_VAL="2013 -12 -31"/> <DATE_FORM="[? DATE_BASE? ]-1*Y" DATE_VAL="2014 -12 -31"/>va Valdation: CONDITION Sorting: SORT, COL_SORT
Table Structure Definitions for SDMX data IV. Main advantages of Table Definition Queries Example of Result Data Table with tranformation formulas Yellow fields do not fulfilled validation rule that indicator should be greater than -35%. 15
16 Table Structure Definitions for SDMX data V. Summary & Conclusions Current solution Table Definition Query -TDQ Application Result Data Table Web Services SDMX Data Warehouse SDW ECB Potential solution for future Table Definition Query -TDQ SDMX Data Warehouse Result Data Table
Table Structure Definitions for SDMX data V. Summary & Conclusions There were designed new structures: Table Definition Queries (language for effective work with SDMX data) Result Data Table (user-friendly presentation of table with SDMX data) Request Data Table (template of table for SDMX data) These new structures contain: transformation formulas validation rules sorting clauses Proposition: to develope and include these new structures in new version of SDMX standard. 17
Table Structure Definitions for SDMX data V. Summary & Conclusions There were elaborated: - prototype program for processing of Table Definition Queries: TDQ_Public_V 0_1. xlsm - Manual with detail description of new structures: Table Structure Definition and Queries for SDMX data. docx Contact person: Jan Kaczanowski, email: jan. kaczanowski@nbp. pl National Bank of Poland, Department of Statistics Świetokrzyska 11/21, Warszawa 00 -919, Poland 18
Table Structure Definitions for SDMX data Thank you for your attention 19
- Slides: 19