SDWH layered architecture Statiscs Finland NSI Statistics 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 Acces Layer GSBPM Phase 7 Disseminate Micro 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 n ”Dependent” statistics of the integrated YTY operational database l Business Register l Some SBS and other statistics: * Financial statement * Regional and industrial statistics * International trade in services * FATS statistics (inward and outward) * Industrial output /Commodity (PRODCOM) l STS statistics (partial integration) l Turnover indices l Wage and salary indices Antti Santaharju 26. 9. 2020 3

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

YTY Integration layer & YTY Interpretation and data analysis layer work in a single database called YTY operational database l Microsoft SQL 2012 database n All database tables are described in the Statistics Finland’s metadatabase n All the processing is controlled by Statistics Finland’s tailor made process control system proc. X n Antti Santaharju 26. 9. 2020 5

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

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

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