S-DWH layered architecture – Statiscs Finland

S-DWH layered architecture – Statiscs Finland

NSI: Statistics Finland Name: Antti Santaharju Institutional Output Date: 7.5.2014 Reporting Analysis Data mining Macro Data Data Acces Layer GSBPM Phase 7 Disseminate Micro Data Data cubes YTY S-DWH for validated micro data Data cubes Meta Data & Process Control System F Reports Interpretation and data analysis Layer u GSBPM Phase 6 Analysis t u r e p YTY operational database l a n Integration Layer GSBPM Phase 5 Process Statistics Finland’s other databases 11 Direct surveys 16 Administrative Data sources Sources Layer GSBPM Phase 4 Collect

YTY business statistics production system ”Dependent” statistics of the integrated YTY operational database Business Register Some SBS and other statistics: * Financial statement * Regional and industrial statistics * International trade in services * FATS statistics (inward and outward) * Industrial output /Commodity (PRODCOM) STS statistics (partial integration) Turnover indices Wage and salary indices Antti Santaharju 19.03.2023 3

1. YTY Source layer Direct surveys are carried out by web application Administrative datasets (sequential text files) are transmitted from other administrative institutes to Statistics Finland as sequential textfiles All data sources (and every variable) are described in the Statistics Finland’s metadata database Metadata descriptions are accessible by Variable Editor application Using the metadata received administrative datasets are converted into SAS datasets are technically validated All data sources (external and internal) are stored into YTY Source layer as SAS datasets (raw data warehouse) All the processing is controlled by tailor made process control system process engine X (procX) Antti Santaharju 19.03.2023 4

YTY Integration layer & YTY Interpretation and data analysis layer YTY Integration layer & YTY Interpretation and data analysis layer work in a single database called YTY operational database Microsoft SQL2012 database All database tables are described in the Statistics Finland’s metadata database All the processing is controlled by Statistics Finland’s tailor made process control system procX Antti Santaharju 19.03.2023 5

2. YTY Integration layer Data sources are uploaded to YTY integration layer By SAS programs Some information is extracted directly from Statistics Finland’s other databases to YTY operational database Input for estimation Data update launches modular data integration, coding, validating, editing and imputing processes These process modules are SAS/.Net programs Erroneous units are flagged Flagged units are analyzed and edited manually by Statistics Finland's tailor made .Net application Tietopalveluyksikkö/Viestintä 19.03.2023 6

3. Interpretation and data analysis layer Data analysis and data mining is done by SSAS Database cubes MS Excel SAS EG Microsoft SQL2012 report builder reports Data analysis and data mining is based on real time data in YTY production database Tietopalveluyksikkö/Viestintä 19.03.2023 7

4. Access layer All validated micro data are loaded to access layer Microsoft SQL2012 server database for validated micro data Data analysis, data mining, dissemination and delivery is based on SSAS Database cubes Daily updated MOLAP cubes One data cube for each statistics Publication process creates frozen micro data version into database Publication process tools: SAS, Tau-Argus, PC-Axis, PX Web. In future Some validated information is loaded directly from other production databases to YTY S-DWH for validated micro data (YTY Access layer) Tietopalveluyksikkö/Viestintä 19.03.2023 8