Metadata Driven Integrated S tatistical D ata M anagement S ystem CSB of Latvia

1. Metadata Driven Integrated Statistical Data Management System CSB of Latvia By Karlis Zeila Vice President CSB of Latvia MSIS 2004, Geneva May 17 - 19

2. Any action within the system is ruled by metadata, META DATA DRIVEN ... ? • Meta data is the key element of the system, • All software modules of entire system is connected with the Core Metadata module (Meta data base). • Any changes within the system starts with the changes of meta data • Full cycle of the data processing is possible as late as the proper description process in meta data base are completed

3. Most of the system software modules are connected with the Registers module, INTEGRATED ... ? • Registers module is an integral part of the system, • All surveys are supported by adequate classifications stored in the Meta data base • In all surveys respondent data fields are connected with registers data • All data is stored in corporative data warehouse • Statistical data processing has split in unified steps for different surveys • Export / Import procedures ensure work with the system data files using different standard software packages

4. Advantages and Restrictions Advantages 1. At most standardized main business statistics data entry, processing and storage procedures, that provide the bases for transfer from stove pipe data processing approach to process oriented data processing approach. • Centralized processing and storage of the statistical data, including metadata, by using data warehouse technologies and OLAP tools. • All the data processing procedures are being hosted from common metadata system. These procedures are being described in metadata base. • Therefore for standardized procedure execution for each survey individual programming is not required. 4. The system is informatively connected with Business Register, which provides with the direct respondent data retrieval and updating. 5. Special import and export procedure is created for data exchange with other systems. 6. A link with PC Axis is created for electronic data dissemination.

5. Restrictions 1.The system is oriented towards the data processing ofdifferent periodicity business statistics surveys. 2.Metadata base does not foreseen description of confidentiality rules they are hard coded in the system. 3.Hardware and Standard software requirements: PC’s >/= Pentium II, RAM >/=128Mb equipped with W – 95 to W-2000 and MS Office 2000. 4.Metadata base does not foreseen description of algorithm for automatic creation of respondents lists for Sample surveys from the Business register frame. 5. Diagnostic tools for the metadata descriptionsare not powerful enough, thereforeexperts preparing meta data descriptions should be ofhigh experience.

6. ISDMS architecture Integrated statistical data management system Corporative data Warehouse CSB Web Site User adminis- tration data base Dissemi-nation data base Metadata base Macrodata base FIREWALL Raw data base Registers base OLAP data base Microdata base Windows 2000 Server Advanced MS Internet Information Server SQL server 2000, PC-Axis ISDMS Business application Software Modules Data entry and validation module related with DB: Data aggregation module related with DB: Data analysis module related with DB: Core metadata base module related with DB: Registers module related with DB: METADATA USER ADMINISTRATION REGISTERS USER ADMINISTRATION METADATA MICRODATAREGISTERS USER ADMINISTRATION METADATA MICRODATA REGISTERS USER ADMINISTRATION OLAP METADATA MACRODATA Data dissemination module related with DB: Data WEB entry module related with DB: User administration module related with DB: Data mass entry module related with DB: Missed data imputation module related with DB: METADATA MICRODATA REGISTERS RAW DATABASE USER ADMINISTRATION METADATA MACRODATA REGISTERS USER ADMINISTRATION METADATA MICRODATA REGISTERS USER ADMINISTRATION METADATA MICRODATA REGISTERS DATA IMPUTATION SOFTWARE METADATA MICRODATA MACRODATA USER ADMINISTRATION

8. Structure of Surveys (questionnaires) • Newsurvey should be registered in the System.For each surveyshall by createdquestionnaireversion, which is valid for at least one year. If questionnaire content and/or layout do not change, then current version and it description in Metadata base is usable for next year. • Each survey contains one or more data entry tables or chapters (data matrix)which can be constant table- with fixed rows and columns number or table withvariable rowsorcolumns number. • For each chapter we have to describe rows andcolumns with their codes and names in the Metadata base. This information is necessary for automatic data entry application generation, data validation e.t.c. • Last step in the questionnairecontent and layout description is cells formation. Cells are smallest data unit in survey data processing. Cells are created as combination of row and column from survey version side and variable from indicators and attributes side.

9. Name of Questionnaire, index, code, corroboration date, Nr. Respondents(object)code, name and address; Period (year, quarter, month) Name of chapter Structure of trade statistics questionnaire(data matrix - fixed table) Metadata repository: common table of statistical indicators, table of attributes (classifications)and table of created variables INDICATOR 1 + ATTRIBUTE I n d i c a t o r s CELL [2010,1] VARIABLE 1 A t t r i b u t e s

10. Row heading Row’s code Total Name1 Name2 N Name n-1 Name n A B 9999 NACE 1 code NACE 2 code ….. NACE n-1 code NACE n code Number of employees 1110 … Net turnover 1120 … Other income 1130 1. Data matrix - Fixed number of Rows (3) and variable number ofColumns (n) (Example)Main economical indicators of the economics activity

11. Name of production Production code (PRODCOM or CN code) Produced in natural measurement Sailed in natural measurement Income in lats (LVL) A B 1 2 3 Product 1 1234567 Product 2 2345678 … … . . . . . . . . . Product n-1 4567890 Product n 5678901 2. Data matrix - Fixed number of Columns (3) and variable number of Rows (n) (Example)Production of industry products

12. Creating of variables ATTRIBUTES(CLASSIFICATORS) =VARIABLES INDICATOR + Dimensions (Vectors) of indicators Example: Number of employees + no attribute = Number of employees, total + Local kind of activity (NACE) = Number of employees in breakdown by kind of activity (~300 variables) + Regional code (ATVK or NUTS) = Number of employees in breakdown by regions (~26 variables)

13. Dimensions of objects and indicators(example) Main dimensions (vectors) of respondents(objects O(t) ) NACE REGIONS (Teritory) OWNERSHIP AND ENTERPRENERSHIP EMPLOYEES GROUP TURNOVER GROUP Dimensions (vectors) of indicators Number of employees in breakdown by regions

14. Integrated Metadata Driven Quasy Process Oriented Technology

15. Metadata base link with Microdata and Macrodata bases META DATA BASE (REPOSITORY) General description of survey Selecting Indicators Selecting Attributes Description of survey version Creating of Variables Description of chapters (data matrix) Description of rows and columns Linking variables to cells Generation form for data entry (automatically) Data aggregation function (automatically) Defining of data aggregation rules MACRO DATABASE MICRO DATABASE IMPORT EXPORT

16. Data entry and validation META DATA BASE BUSINESS REGISTER Description of validation rules Data import from files Creating list of Respon- dents Description of data entry forms Full data validation MICRO DATA BASE Standard data entry and validation Data validation RAW DATA BASE Data transfer to Microdata Base Mass data entry F i r e w a l l RAW Web DATA BASE Web data entry and validation Web Data validation

18. LESSONS LEARNED • Design of the new information system should be based on the results of deep analysis of the statistical processes and data flows • Clear objectives of achievements have to be set up, discussed and approved by all parties involved • Statisticians • IT personal • Administration

19. LESSONS LEARNED • Within the process of the design and implementation of metadata driven integrated statistical information system both parties statisticians and IT specialists should be involved from the very beginning • Both parties have to have clear understanding of all statistical processes,which will be covered by the system, as well as metadata meaning and role within the system from production and user sides

20. LESSONS LEARNED • Initiative to move from classical stove-pipe production approach to process oriented have to come from statisticians side not from IT personal or administration • Motivation of the statisticians to move from existing to the new data processing environment is essential; • Improvement of knowledge about metadata is one of the most important tasks through out of the all process of the design and implementation phases of the project

21. LESSONS LEARNED • Clear division of the tasks and responsibilities between statisticians and IT personal is the key point to achieve successful implementation • To achieve the best performance of the entire system it is important to organize the execution of the statistical processes in the right sequence • Design of the new surveys and questionnaires particularly as well as changes in the existing ones should be done in accordance with the system requirements

22. As the result of feasibility study we clear understood, that some steps of statistical data processing for different surveys defy standardization, some surveys may require complementary functionality (non standard procedures), which is necessary just for this exact survey data processing; For solving problems with the non-standard procedures interfaces for data export/import to/from system has been developed to ensure use of the standard statistical data processingsoftware packages and other generalized software available in market; LESSONS LEARNED

23. It is necessary to establish and train special group of statisticians, which will maintain Metadata base and which will be responsible for accurateness of metadata; For the administration and maintenance of the system it is necessary to have well trained IT staff, which is familiar with the MS SQL Server 2000 administration, MS Analysis Service, other MS tools, PC AXIS family products and system Data Model, system applications; LESSONS LEARNED